SQL - pick current date from machine and compare with previous week

Question

For the below query

Sum(CASE WHEN dbo.sales.date BETWEEN '2016-07-17' AND '2016-07-23' THEN dbo.sales.sellinc END) AS ActualSales

Instead of hard coding the date. I would like to pick current date from machine and compare

Sum(CASE WHEN dbo.sales.date BETWEEN '**previous week (31 week 2016)**' AND '**Last year same week (31 week 2015)**' THEN dbo.sales.sellinc END) AS ActualSales

Week start from Sunday and ends Saturday. Any help please?

Samrat Alamgir · Answer 1 · 2016-09-05T17:52:30.490

2

I think this snippet might help you to get a range of one year from the previous week to last year.

Sum(CASE 
WHEN dbo.sales.date 
  BETWEEN DATEADD(YEAR, -1, DATEADD(WEEK, -1, GETDATE())) AND 
    DATEADD(WEEK, -1, GETDATE()) THEN dbo.sales.sellinc END) AS ActualSales

You can also modify the logic if you want to consider Friday as an exception. In that case, you have to use DATEPART(WEEKDAY, GETDATE()) function to get the week day which returns 1 for Sunday and 7 for Saturday. It would be prettier if you move the date generation logic to a variable and then use it to select your data.

edited Sep 05 '16 at 17:52

answered Sep 05 '16 at 17:37

Samrat Alamgir

355
2
13

Thanks@samrat-alamgir, What will be the code below scenario please? Scenario 1: ` sum(case when s.Date between '2016-07-17' and '2016-07-23' then s.SELLINC else 0 end) ActualSales` Scenario 2: `sum(case when s.Date between '2015-07-19' and '2015-07-25' then s.SELLINC else 0 end) LastYrVariance` Scenario 3: Picking dates between beginning of current year till today's date `sum(case when s.Date between '2016-01-01' and '2016-09-05' then s.SELLINC else 0 end) YrToDateActual` – user2331670 Sep 06 '16 at 08:16
Do you want the three columns I mean Actual sales, last year variance and Year to date at the same time? Can you describe about your date generation logic a bit more? – Samrat Alamgir Sep 06 '16 at 16:43

shawnt00 · Answer 2 · 2016-09-05T20:12:21.883

Others have already noted that your method of week numbering is going to impact your solution. Here's one that just looks for the Sunday of the current week and then subtracts 52 weeks. (You could also just deduct 364 days in the previous step and use dateadd only once.) So the correspondence with the previous year's week is essentially the nearest calendar date falling on Sunday that year.

with dates as (
    select
        dateadd(week, -52,
            dateadd(day,
                1 - datepart(weekday, getdate()), /* Sunday of current week */
                cast(getdate() as date)
            )
        ) as WeekStartY-1,
        dateadd(day, 1 - datepart(weekday, cast(getdate() as date)) as WeekStartY-0
)
select
    sum(case when cast(s."date" as date)
             between d.WeekStartY-0 and dateadd(day, 6, WeekStartY-0) then s.sellinc end
    ) as ActualSalesY-0,
    sum(case when cast(s."date" as date)
             between d.WeekStartY-1 and dateadd(day, 6, WeekStartY-1) then s.sellinc end
    ) as ActualSalesY-1,
from dbo.Sales s cross apply dates d;

This can occasionally produce an oddity where the weeks both start in the same year. It happens when the year ends on Sunday, or Monday in leap years, as with 2012. So Y-1 for December 30, 2012 was January 1, 2012. But consider that most of the days for that week actually fall in 2013 and the correspondence makes sense from that perspective.

Thanks@shawnt00, What will be the code below scenario please? Scenario 1: ` sum(case when s.Date between '2016-07-17' and '2016-07-23' then s.SELLINC else 0 end) ActualSales` Scenario 2: `sum(case when s.Date between '2015-07-19' and '2015-07-25' then s.SELLINC else 0 end) LastYrVariance` Scenario 3: Picking dates between beginning of current year till today's date `sum(case when s.Date between '2016-01-01' and '2016-09-05' then s.SELLINC else 0 end) YrToDateActual` Scenario 4:`sum(case when s.Date between '2015-01-01' AND '2015-09-05' then s.SELLINC else 0 end) LastYrToDateActual` — user2331670, Sep 06 '16 at 08:18
@user2331670 I don't know where the dates in July come from. One way to get January 1 of current year could be `dateadd(day, datepart(dayofyear, getdate()) - 1, getdate())`. I guarantee you could find that solution quite easily with a little searching. It would be best if you edited your question with all your requirements. Demonstrating independent effort and failed attempts is highly valued on this forum. At some point it would be most appropriate to create a new question entirely. — shawnt00, Sep 06 '16 at 13:44

score 0 · Answer 3 · edited May 23 '17 at 12:32

You can try using DATEADD:

SUM(CASE WHEN dbo.sales.date BETWEEN DATEADD(week, 30, '2016/01/01') AND
    DATEADD(week, 30, '2015/01/01')
         THEN dbo.sales.sellinc END) AS ActualSales

This will give you the one year range between the 30th week of 2015 and 2016, using the SQL Server week, whose first week begins on January 1st, regardless of the day. If you really want to use the ISO week, then it will be a lot more work.

SQL - pick current date from machine and compare with previous week

3 Answers3