SQL - using calculated dates in WHERE clause

Question

I have a table with future values where I only want the last full week.

Meaning af want to find the last Sunday and have a WHERE clause where date is between 'last Sunday - 7' and 'last Sunday'.

Meaning I have transactions until '31-10-2018' and want the period between '2018-10-28' and '2018-10-22' (both days included). But every week new data will be added continuously so very soon I have data until '2018-11-30' and so on.

I have tried a query like:

SELECT *
FROM PlannedCounts
WHERE [Date] BETWEEN DATEADD(d,-6,MAX([Date])) and MAX([Date])

Note: I'm aware that this will only give me last day ('2018-10-31' and not last Sunday).

How can I include calculated dates in my where claus and also conditioning the target date to be last Sunday?

My table have the following columns:

Date
Location
Type
Material
Value
Manager

Picture of my dataset and expected result

Please provide the result set you are looking for. The question is not clear. — Gordon Linoff, Sep 06 '18 at 13:52

Salman A · Accepted Answer · 2018-09-06T15:00:33.493

For a given date 2018-10-31 you need to determine the day of week (1 ... 7) so that you can calculate the previous Sunday. Unfortunately it is not straight forward (see discussion):

CREATE TABLE #PlannedCounts(Date Date);
INSERT INTO #PlannedCounts(Date) VALUES
    ('2018-10-31'),
    ('2018-10-30'),
    ('2018-10-29'),
    ('2018-10-28'),
    ('2018-10-27'),
    ('2018-10-26'),
    ('2018-10-25'),
    ('2018-10-24'),
    ('2018-10-23'),
    ('2018-10-22'),
    ('2018-10-21'),
    ('2018-10-20'),
    ('2018-10-19'),
    ('2018-10-18'),
    ('2018-10-17'),
    ('2018-10-16'),
    ('2018-10-15'),
    ('2018-10-14'),
    ('2018-10-13'),
    ('2018-10-12');

DECLARE @LastSunday AS DATE;
DECLARE @PrevSunday AS DATE;

SELECT
    @LastSunday = DATEADD(DAY, -(@@DATEFIRST + DATEPART(dw, MAX(Date)) - 1) % 7 - 0, MAX(Date)),
    @PrevSunday = DATEADD(DAY, -(@@DATEFIRST + DATEPART(dw, MAX(Date)) - 1) % 7 - 6, MAX(Date))
FROM #PlannedCounts

-- @LastSunday = 2018-10-28
-- @PrevSunday = 2018-10-22

SELECT *
FROM #PlannedCounts
WHERE Date BETWEEN @PrevSunday AND @LastSunday

Note: it is possible to replace user variables with queries.

score 0 · Answer 2 · answered Sep 06 '18 at 13:58

You need a case statement in where clause -

SELECT *
FROM PlannedCounts
WHERE [Date] >= CASE WHEN DAYNAME(MAX([Date])) = 'Monday' THEN 
                          DATEADD(d,-1,MAX([Date]))
                     WHEN DAYNAME(MAX([Date])) = 'Tuesday' THEN 
                          DATEADD(d,-2,MAX([Date]))
                     WHEN DAYNAME(MAX([Date])) = 'Wednesday' THEN 
                          DATEADD(d,-3,MAX([Date]))
                     WHEN DAYNAME(MAX([Date])) = 'Thursday' THEN 
                          DATEADD(d,-4,MAX([Date]))
                     WHEN DAYNAME(MAX([Date])) = 'Friday' THEN 
                          DATEADD(d,-5,MAX([Date]))
                     WHEN DAYNAME(MAX([Date])) = 'Saturday' THEN 
                          DATEADD(d,-6,MAX([Date]))
                 ELSE MAX([Date] END
AND [Date] <= CASE WHEN DAYNAME(MAX([Date])) = 'Monday' THEN 
                          DATEADD(d,-7,MAX([Date]))
                     WHEN DAYNAME(MAX([Date])) = 'Tuesday' THEN 
                          DATEADD(d,-8,MAX([Date]))
                     WHEN DAYNAME(MAX([Date])) = 'Wednesday' THEN 
                          DATEADD(d,-9,MAX([Date]))
                     WHEN DAYNAME(MAX([Date])) = 'Thursday' THEN 
                          DATEADD(d,-10,MAX([Date]))
                     WHEN DAYNAME(MAX([Date])) = 'Friday' THEN 
                          DATEADD(d,-11,MAX([Date]))
                     WHEN DAYNAME(MAX([Date])) = 'Saturday' THEN 
                          DATEADD(d,-12,MAX([Date]))
                 ELSE DATEADD(d,-6,MAX([Date])) END

Also thank you for taking the time to answer my question. It is highly valued. — DickTaid81, Sep 06 '18 at 15:12

SQL - using calculated dates in WHERE clause

2 Answers2