brodskyb brodskyb - 1 month ago 8
SQL Question

SQL - Select values from a table based on dates using incrementing dates

I have a SQL table of dates (

MM/DD
format), targets, and levels, as such:

Date Target Level
10/2 1000 1
10/4 2000 1
10/7 2000 2


I want to use those dates as tiers, or checkpoints, for when to use the respective targets and levels. So, anything on or after those dates (until the next date) would use that target/level. Anything before the first date just uses the values from the first date.

I want to select a range of dates (a 5 week range of dates, with the start date and end date of the range being determined by the current day: 3 weeks back from today, to 2 weeks forward from today) and fill in the targets and levels accordingly, as such:

Date Target Level
10/1 1000 1
10/2 1000 1
10/3 1000 1
10/4 2000 1
10/5 2000 1
10/6 2000 1
10/7 2000 2
10/8 2000 2
...
11/5 2000 2


How do I go about:


  1. Selecting the range of dates (as efficiently as possible)

  2. Filling in the range of dates with the respective target/level from the appropriate date in my table?



Thank you.

Answer

I don't know why you store dates as MM/DD but you need some convention into right datatype. This could do a trick:

;WITH YourTable AS (
SELECT *
FROM (VALUES
('10/2', 1000, 1),
('10/4', 2000, 1),
('10/7', 2000, 2)
) as t([Date], [Target],  [Level])
), dates_cte AS ( --this CTE is generating dates you need
    SELECT DATEADD(week,-3,GETDATE()) as d --3 weeks back
    UNION ALL
    SELECT dateadd(day,1,d)
    FROM dates_cte
    WHERE d < DATEADD(week,2,GETDATE()) --2 weeks forward
)

SELECT  REPLACE(CONVERT(nvarchar(5),d,101),'/0','/') as [Date],
        COALESCE(t.[Target],t1.[Target]) [Target],
        COALESCE(t.[Level],t1.[Level]) [Level]
FROM dates_cte dc
OUTER APPLY ( --Here we got PREVIOUS values
    SELECT TOP 1 *
    FROM YourTable
    WHERE CONVERT(datetime,REPLACE([Date],'/','/0')+'/2016',101) <= dc.d
    ORDER BY CONVERT(datetime,REPLACE([Date],'/','/0')+'/2016',101) DESC
    ) t
OUTER APPLY ( --Here we got NEXT values and use them if there is no PREV
    SELECT TOP 1 *
    FROM YourTable
    WHERE CONVERT(datetime,REPLACE([Date],'/','/0')+'/2016',101) >= dc.d
    ORDER BY CONVERT(datetime,REPLACE([Date],'/','/0')+'/2016',101) ASC
    ) t1

Output:

Date    Target  Level
10/5    2000    1
10/6    2000    1
10/7    2000    2
10/8    2000    2
10/9    2000    2
10/10   2000    2
10/11   2000    2
10/12   2000    2
...
11/9    2000    2

EDIT

With Categories:

;WITH YourTable AS (
SELECT *
FROM (VALUES
('10/2', 1000, 1, 'A'),
('10/4', 3000, 1, 'B'),
('10/7', 2000, 2, 'A')
) as t([Date], [Target], [Level], [Category])
), dates_cte AS (
    SELECT DATEADD(week,-3,GETDATE()) as d
    UNION ALL
    SELECT dateadd(day,1,d)
    FROM dates_cte
    WHERE d < DATEADD(week,2,GETDATE())
)

SELECT  REPLACE(CONVERT(nvarchar(5),d,101),'/0','/') as [Date],
        COALESCE(t.[Target],t1.[Target]) [Target],
        COALESCE(t.[Level],t1.[Level]) [Level],
        c.Category
FROM dates_cte dc
CROSS JOIN (
    SELECT DISTINCT Category
    FROM YourTable
) c
OUTER APPLY (
    SELECT TOP 1 *
    FROM YourTable
    WHERE CONVERT(datetime,REPLACE([Date],'/','/0')+'/2016',101) <= dc.d
        AND c.Category = Category
    ORDER BY CONVERT(datetime,REPLACE([Date],'/','/0')+'/2016',101) DESC
    ) t
OUTER APPLY (
    SELECT TOP 1 *
    FROM YourTable
    WHERE CONVERT(datetime,REPLACE([Date],'/','/0')+'/2016',101) >= dc.d
        AND c.Category = Category
    ORDER BY CONVERT(datetime,REPLACE([Date],'/','/0')+'/2016',101) ASC
    ) t1
ORDER BY c.Category, d
Comments