weeknumber calculation between two dates is wrong in tsql

Farhan Jamil 416 Reputation points
2020-12-01T13:55:54.713+00:00

Hi Guys

I have been able to get what i want from the requirement i was presented but not sure about the weeknumber as that is the only column which is incorrect. Apart from that every data I have in my result set is correct

declare @stdate as date
declare @eddate as date

set @stdate = '20201122'
set @eddate = '20201128'
select
x.SKU
, x.SKUVariant
, x.RetailPrice
, x.[Description]
, SUM(x.Units) as [Total Units]
, x.Name
, 'WGO' as [OrderType]
, DATEPART(wk,x.Startdate) as WeekNum
,x.Startdate
from
(
select
p.Code as SKU
, pv.Code as SKUVariant
, cpr.GrossPrice as RetailPrice
, h.OrderNumber
, cast(
case
when ob.BatchType = 1 then CompletedOn
when pv.Code = '235689' then i.UpdatedOn
else isnull(ob.ReleaseDate, h.StartDateTime)
end as date) as Startdate
, ISNULL(P.Description, P.Name) as [Description]
, SUM(i.Quantity) as Units
, case dm.pkDeliveryMethodId
when 1 then 'Collect From Store'
when 2 then 'Order into Store'
else 'Delivery'
end as [Name]
from
Header h with (nolock)
inner join ItemLine i with (nolock) on i.fkHeaderId = h.pkHeaderId
inner join ProductVar pv with (nolock) on pv.pkProductVariantID = i.fkProductVariantId
inner join Product p with (nolock) on p.pkProductID = pv.fkProductID
inner join [Order] o with (nolock) on o.OrderNumber = h.OrderNumber
inner join Branch b with (nolock) on b.pkBranchID = h.fkBranchId
left join Branch br with (nolock) on br.pkBranchID = o.OriginalBranchId
left join Order_Line ol with (nolock) on ol.OrderId = o.pkOrderId and ol.LineId = i.LineNumber
left join OrderBatch ob with (nolock) on ob.pkOrderBatchId = ol.OrderBatchId
left join DeliveryMethod dm with (nolock) on dm.pkDeliveryMethodId = o.DeliveryMethodId
left JOIN CompanyPriceList AS CPr WITH (NOLOCK) ON CPr.fkProductID = P.pkProductID
AND GETDATE() BETWEEN CPr.EffectiveFrom AND CPr.EffectiveTo
and cpr.fkProductVariantID is null
and cpr.countryISOcode = 'GBR'
where
br.Code > 500
and (
(
(
isnull(ob.BatchType,'4') <> 1 and cast(isnull(ob.ReleaseDate, h.StartDateTime) as date) between @StDate and @EdDate and pv.Code <> '235689'
) or pv.Code = '235689' and cast(i.UpdatedOn as date) between @StDate and @EdDate
) or ob.BatchType = 1 and cast(CompletedOn as date) between @StDate and @EdDate
)
and h.IsVoided = 0
and i.IsVoided = 0
and b.Code <> 808
and h.fkTypeId in (1,2,3,14,208,209)
and (
(
h.enmStatus in (5,6,7,36,69) and isnull(ob.BatchType,'4') <> 1
) or ob.BatchType = 1 and ob.IsComplete = 1
)
and pv.Code not in ('235689')
group by
p.Code
, pv.Code
, cpr.GrossPrice
, ISNULL(P.Description, P.Name)
, h.OrderNumber
, cast(
case
when ob.BatchType = 1 then CompletedOn
when pv.Code = '235689' then i.UpdatedOn
else isnull(ob.ReleaseDate, h.StartDateTime)
end as date)
, case dm.pkDeliveryMethodId
when 1 then 'Collect From Store'
when 2 then 'Order into Store'
else 'Delivery'
end
) x
group by
x.SKU
, x.SKUVariant
, x.RetailPrice
, x.[Description]
, x.Name
, DATEPART(wk,x.Startdate)
,x.Startdate

union

select
x.SKU
, x.SKUVariant
, x.RetailPrice
, x.[Description]
, SUM(x.Units) as [Total Units]
, x.Name
, 'SGO' as [OrderType]
, DATEPART(wk,x.Startdate) as WeekNum
,x.Startdate
from
(
select
p.Code as SKU
, pv.Code as SKUVariant
, cpr.GrossPrice as RetailPrice
, h.OrderNumber
, cast(
case
when ob.BatchType = 1 then CompletedOn
when pv.Code = '235689' then i.UpdatedOn
else isnull(ob.ReleaseDate, h.StartDateTime)
end as date) as Startdate
, ISNULL(P.Description, P.Name) as [Description]
, SUM(i.Quantity) as Units
, case dm.pkDeliveryMethodId
when 1 then 'Collect From Store'
when 2 then 'Order into Store'
else 'Delivery'
end as [Name]
from
Header h with (nolock)
inner join ItemLine i with (nolock) on i.fkHeaderId = h.pkHeaderId
inner join ProductVariant pv with (nolock) on pv.pkProductVariantID = i.fkProductVariantId
inner join Product p with (nolock) on p.pkProductID = pv.fkProductID
inner join [Order] o with (nolock) on o.OrderNumber = h.OrderNumber
inner join Branch b with (nolock) on b.pkBranchID = h.fkBranchId
left join Branch br with (nolock) on br.pkBranchID = o.OriginalBranchId
left join Order_Line ol with (nolock) on ol.OrderId = o.pkOrderId and ol.LineId = i.LineNumber
left join OrderBatch ob with (nolock) on ob.pkOrderBatchId = ol.OrderBatchId
left join DeliveryMethod dm with (nolock) on dm.pkDeliveryMethodId = o.DeliveryMethodId
left JOIN CompanyPriceList AS CPr WITH (NOLOCK) ON CPr.fkProductID = P.pkProductID
AND GETDATE() BETWEEN CPr.EffectiveFrom AND CPr.EffectiveTo
and cpr.fkProductVariantID is null
and cpr.countryISOcode = 'GBR'
where
br.Code < 500
and (
(
(
isnull(ob.BatchType,'4') <> 1 and cast(isnull(ob.ReleaseDate, h.StartDateTime) as date) between @StDate and @EdDate and pv.Code <> '235689'
) or pv.Code = '235689' and cast(i.UpdatedOn as date) between @StDate and @EdDate
) or ob.BatchType = 1 and cast(CompletedOn as date) between @StDate and @EdDate
)
and h.IsVoided = 0
and i.IsVoided = 0
and h.fkTypeId in (1,2,3,14,208,209)
and (
(
h.enmStatus in (5,6,7,36,69) and isnull(ob.BatchType,'4') <> 1
) or ob.BatchType = 1 and ob.IsComplete = 1
)
and pv.Code not in ('235689')
group by
p.Code
, pv.Code
, cpr.GrossPrice
, ISNULL(P.Description, P.Name)
, h.OrderNumber
, cast(
case
when ob.BatchType = 1 then CompletedOn
when pv.Code = '235689' then i.UpdatedOn
else isnull(ob.ReleaseDate, h.StartDateTime)
end as date)
, case dm.pkDeliveryMethodId
when 1 then 'Collect From Store'
when 2 then 'Order into Store'
else 'Delivery'
end
) x
group by
x.SKU
, x.SKUVariant
, x.RetailPrice
, x.[Description]
, x.Name
, DATEPART(wk,x.Startdate)
,x.Startdate

order by
OrderType
, x.Name
,Startdate

GO

The output i see is

44049-image.png

The week number calculation is wrong.

I investigated the matter and took this line out

that is

datepart(week,x.startdate).here i see that the output is 48. it should be week 44 as the date is between 22-11-2020 to 28-11-2020

Any help would be appreciated

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,552 questions

5 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. SM 16 Reputation points
    2020-12-01T14:59:56.367+00:00

    FWIW - you should make an attempt to post code that directly addresses your issue without all the clutter and dependencies of your GIANT, unformatted query. Help others help you. And now would be a good time to go read the documentation about week number and what it does. Perhaps you want iso_week?

    And seriously - does it take THAT MUCH MORE effort to type "week" rather than "wk" in your datepart function? Also, stop splattering your code with NOLOCK

    1 person found this answer helpful.
    0 comments
  2. Farhan Jamil 416 Reputation points
    2020-12-01T15:09:26.513+00:00

    Hi SM-7489

    Thank you for you reply.

    I doesnt make a difference if i choose wk or week. coming back to iso_week that doesnt solve the issue to get the correct weeknumber as i did try that as well

    Anyways i have sorted the issue out myself joining my so called uncluttered query with a date table.

    REgards
    Farhan Jamil

    0 comments
  3. Guoxiong 8,126 Reputation points
    2020-12-01T15:13:52.083+00:00

    It is the week 48:

    SELECT DATEPART(WEEK, '2020-11-23') -- 48
  4. Tom Phillips 17,716 Reputation points
    2020-12-01T16:53:41.357+00:00

    It depends on what you definition of "week number" is. The datepart week is very simple and documented https://learn.microsoft.com/en-us/sql/t-sql/functions/datepart-transact-sql?view=sql-server-ver15#week-and-weekday-datepart-arguments

    That is rarely useful and (as you did) you should use a calendar table to calculated it as you want rather than use datepart.

    0 comments
  5. EchoLiu-MSFT 14,571 Reputation points
    2020-12-02T05:56:39.8+00:00

    Hi @Farhan Jamil ,

    The code you posted is too readable and returns an error when I try to execute it. When you post a question, you only need to provide a minimal example and create, insert statements and the output you expect.

    Calculate weeknumber between two dates: 

        select DATEDIFF(WW,'2019-11-22' ,'2020-11-28') as weeknumber

    If you have any question, please feel free to let me know.
    If the response is helpful, please click "Accept Answer" and upvote it.

    Regards
    Echo

    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
    Hot issues November--What can I do if my transaction log is full?
    Hot issues November--How to convert Profiler trace into a SQL Server table

    0 comments