MSSQL - Getting last 6 weeks returns last 8 weeks

Question

I am having a problem with week numbers. The customers week starts on a Tuesday, so ends on a Monday. So I have done:

Set DateFirst 2

When I then use

DateAdd(ww,@WeeksToShow, Date)

It occasionally gives me 8 weeks of information. I think it is because it goes over to the previous year, but I am not sure how to fix it.

If I do:

  (DatePart(dy,Date) / 7) - @WeeksToShow

Then it works better, but obviously doesn't work going through to previous years as it just goes to minus figures.

Edit:

My currently SQL (If it helps at all without any data)

Set DateFirst 2

Select 
    DATEPART(yyyy,SessionDate) as YearNo, 
    DATEPART(ww,SessionDate) as WeekNo,
    DATEADD(DAY, 1 - DATEPART(WEEKDAY, SessionDate + SessionTime),     CAST(SessionDate +SessionTime AS DATE)) [WeekStart],
    DATEADD(DAY, 7 - DATEPART(WEEKDAY, SessionDate + SessionTime),     CAST(SessionDate + SessionTime AS DATE)) [WeekEnd],
    DateName(dw,DATEADD(DAY, 7 - DATEPART(WEEKDAY, SessionDate +     SessionTime), CAST(SessionDate + SessionTime AS DATE))) as WeekEndName,
    Case when @ConsolidateSites = 1 then 0 else SiteNo end as SiteNo,
    Case when @ConsolidateSites = 1 then 'All' else CfgSites.Name end as SiteName,
    GroupNo,
    GroupName,
    DeptNo,
    DeptName,
    SDeptNo,
    SDeptName,
    PluNo,
    PluDescription,
    SUM(Qty) as SalesQty,
    SUM(Value) as SalesValue
From 
    PluSalesExtended
Left Join
    CfgSites on PluSalesExtended.SiteNo = CfgSites.No
Where
    Exists (Select Descendant from DescendantSites where Parent in (@SiteNo) and Descendant = PluSalesExtended.SiteNo)
    AND (DATEPART(WW,SessionDate + SessionTime) !=DATEPART(WW,GETDATE()))
    AND SessionDate + SessionTime between DATEADD(ww,@NumberOfWeeks * -1,@StartingDate) and @StartingDate
    AND TermNo = 0
    AND PluEntryType <> 4
Group by
    DATEPART(yyyy,SessionDate),
    DATEPART(ww,SessionDate),
    DATEADD(DAY, 1 - DATEPART(WEEKDAY, SessionDate + SessionTime),     CAST(SessionDate +SessionTime AS DATE)),
    DATEADD(DAY, 7 - DATEPART(WEEKDAY, SessionDate + SessionTime),     CAST(SessionDate + SessionTime AS DATE)),
    Case when @ConsolidateSites = 1 then 0 else SiteNo end,
    Case when @ConsolidateSites = 1 then 'All' else CfgSites.Name end,
    GroupNo,
    GroupName,
    DeptNo,
    DeptName,
    SDeptNo,
    SDeptName,
    PluNo,
    PluDescription

order by WeekEnd

What you are saying isn't possible. We need to see the entire SQL statement to figure this one out, there's not enough information here. — DavidG, Mar 02 '15 at 09:33

score 2 · Accepted Answer · edited May 23 '17 at 11:59

There are two issues here, the first is that I suspect you are defining 8 weeks of data as having 8 different values for DATEPART(WEEK, in which case you can replicate the root cause of the issue by looking at what ISO would define as the first week of 2015:

SET DATEFIRST 2;
SELECT Date, Week = DATEPART(WEEK, Date)
FROM (VALUES 
        ('20141229'), ('20141230'), ('20141231'), ('20150101'),
        ('20150102'), ('20150103'), ('20150104')
    ) d (Date);

Which gives:

Date        Week
-----------------
2014-12-29  52
2014-12-30  53
2014-12-31  53
2015-01-01  1
2015-01-02  1
2015-01-03  1
2015-01-04  1

So although you only have 7 days, you have 3 different week numbers. The problem is that DATEPART(WEEK is quite a simplistic function, and will simply return the number of week boundaries passed since the first day of the year, a better function would be ISO_WEEK since this takes into account year boundaries nicely:

SET DATEFIRST 2;
SELECT Date, Week = DATEPART(ISO_WEEK, Date)
FROM (VALUES 
        ('20141229'), ('20141230'), ('20141231'), ('20150101'),
        ('20150102'), ('20150103'), ('20150104')
    ) d (Date);

Which gives:

Date        Week
-----------------
2014-12-29  1
2014-12-30  1
2014-12-31  1
2015-01-01  1
2015-01-02  1
2015-01-03  1
2015-01-04  1

The problem is, that this does not take into account that the week starts on Tuesday, since the ISO week runs Monday to Sunday, you could adapt your usage slightly to get the week number of the day before:

SET DATEFIRST 2;
SELECT Date, Week = DATEPART(ISO_WEEK, DATEADD(DAY, -1, Date))
FROM (VALUES 
        ('20141229'), ('20141230'), ('20141231'), ('20150101'),
        ('20150102'), ('20150103'), ('20150104')
    ) d (Date);

Which would give:

Date        Week
-----------------
2014-12-29  52
2014-12-30  1
2014-12-31  1
2015-01-01  1
2015-01-02  1
2015-01-03  1
2015-01-04  1

So Monday the 29th December is now recognized as the previous week. The problem is that there is no ISO_YEAR built in function, so you will need to define your own. This is a fairly trivial function, even so I almost never create scalar functions because they perform terribly, instead I use an inline table valued function, so for this I would use:

CREATE FUNCTION dbo.ISOYear (@Date DATETIME)
RETURNS TABLE
AS
RETURN
(   SELECT  IsoYear = DATEPART(YEAR, @Date) + 
                        CASE
                            -- Special cases: Jan 1-3 may belong to the previous year 
                            WHEN (DATEPART(MONTH, @Date) = 1 AND DATEPART(ISO_WEEK, @Date) > 50) THEN -1

                            -- Special case: Dec 29-31 may belong to the next year
                            WHEN (DATEPART(MONTH, @Date) = 12 AND DATEPART(ISO_WEEK, @Date) < 45) THEN 1
                            ELSE 0
                        END
);

Which just requires a subquery to be used, but the extra typing is worth it in terms of performance:

SET DATEFIRST 2;
SELECT Date, 
    Week = DATEPART(ISO_WEEK, DATEADD(DAY, -1, Date)),
    Year = (SELECT ISOYear FROM dbo.ISOYear(DATEADD(DAY, -1, Date)))
FROM (VALUES 
        ('20141229'), ('20141230'), ('20141231'), ('20150101'),
        ('20150102'), ('20150103'), ('20150104')
    ) d (Date);

Or you can use CROSS APPLY:

SET DATEFIRST 2;
SELECT Date, 
    Week = DATEPART(ISO_WEEK, DATEADD(DAY, -1, Date)),
    Year = y.ISOYear
FROM (VALUES 
        ('20141229'), ('20141230'), ('20141231'), ('20150101'),
        ('20150102'), ('20150103'), ('20150104')
    ) d (Date)
    CROSS APPLY dbo.ISOYear(d.Date) y;

Which gives:

Date        Week    Year
---------------------------
2014-12-29  52      2014
2014-12-30  1       2015
2014-12-31  1       2015
2015-01-01  1       2015
2015-01-02  1       2015
2015-01-03  1       2015
2015-01-04  1       2015

Even with this method, by simply getting a date 6 weeks ago you sill still end up with 7 weeks if the date you are using is not a Tuesday, because you will have 5 full weeks, and a part week at the start and a part week at the end, this is the second issue. So you need to make sure your start date is a Tuesday. The following will get you Tuesday of 7 weeks ago:

SELECT CAST(DATEADD(DAY, 1 - DATEPART(WEEKDAY, GETDATE()), DATEADD(WEEK, -6, GETDATE())) AS DATE);

The logic of this is explained better in this answer, the following is the part that will get the start of the week (based on your datefirst settings):

SELECT DATEADD(DAY, 1 - DATEPART(WEEKDAY, GETDATE()), GETDATE());

Then all I have done is substitute the second GETDATE() with DATEADD(WEEK, -6, GETDATE()) so that it is getting the start of the week 6 weeks ago, then there is just a cast to date to remove the time element from it.

This is beautifully defined. But would it not be easier to filter out the part-weeks? — Yokomoko, Mar 02 '15 at 10:00
Yes it would, this is what the second part addresses, by ensuring that your start date is a Tuesday you know that there will be no part weeks. — GarethD, Mar 02 '15 at 10:04
In that case.. Would this work? Select DatePart(yy,Date) as Year, Week = DATEPART(ISO_WEEK, DATEADD(DAY, -1, Date)) from TransactionDetail where Date between DATEADD(WEEK, -6, '2015/01/01') and '2015/01/01' group by DatePart(yy,Date), DATEPART(ISO_WEEK, DATEADD(DAY, -1, Date)) order by Year,Week So this changes GetDate() to a preset value. Seems to work provided there's information in each week! — Yokomoko, Mar 02 '15 at 10:18
`datepart(yy, Date)` will cause you issues over the year boundary, since the same week could have two different years, you would need to use the `ISOYear` function I have put in the question. — GarethD, Mar 02 '15 at 10:54

t-clausen.dk · Answer 2 · 2015-03-02T09:43:10.870

This will get you current week + 5 previous weeks starting tuesday:

WHERE dateadd(week, datediff(d, 0, getdate()-1)/7 - 4, 1) <= yourdatecolumn

This will show examples:

DECLARE @wks int = 6 -- Weeks To Show

SELECT 
  dateadd(week, datediff(d, 0, getdate()-1)/7 - 4, 1) tuesday5weeksago,
  dateadd(week, datediff(d, 0, getdate()-1)/7 - 5, 1) tuesday6weeksago,
  dateadd(week, datediff(d, 0, getdate()-1)/7 - 6, 1) tuesday7weeksago,
  dateadd(week, datediff(d, 0, getdate()-1)/7 - @wks + 1, 1) tuesdaydynamicweeksago

Result:

tuesday5weeksago  tuesday6weeksago  tuesday7weeksago  tuesdaydynamicweeksago
2015-01-27        2015-01-20        2015-01-13        2015-01-20

MSSQL - Getting last 6 weeks returns last 8 weeks

2 Answers2