Adam H Adam H - 2 months ago 7
SQL Question

SQL How to query a record by date where the date is stored as a start date with a repeated count and interval of occurance?

I have a table that stores values by date. The issue is there has to be an established value for each month of the year. The table isn't setup to store each individual month's value, it stored the start date then how many times it's repeated and the interval it is repeated. Example: ID 2 below starts on 2014-04-01 and is repeated 12 times every 3 months. I need to query to find all values that fall within the month of the whatever getdate() returns. ID's 1, 2 & 3 would need to be returned since they have dates which fall on 2016-01-01.

I attempted a case when but that didn't work. It worked when (REPEAT=1 AND INTERVAL=1) and (REPEAT>1 AND INTERVAL=1) but it didn't work when INTERVAL>1.

This is part of a SQL Serve Agent job so if each of the 3 scenarios need to be done separately I could do that. I just can't figure out how to handle when INTERVAL>1. Any help would be greatly appreciated. This is on SQL Server Management 2008.

ID |DATE |REPEAT|INTERVAL|VALUE
1 |2016-01-01 | 1| 1| 10
2 |2014-01-01 | 12| 3| 15
3 |2015-01-01 | 13| 1| 20
4 |2014-04-01 | 12| 6| 100

Answer

I think it is this, what you are looking for:

--Create a function, which will return a list of ints between a start and end with a given step-width.

CREATE FUNCTION RunningNumbers(@start INT,@end INT,@step INT)
RETURNS TABLE AS

--Create a List of up to 1.000.000.000 rows on the fly
--This is limited by start and end parameter
RETURN
 WITH x AS(SELECT 1 AS N FROM(VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) AS tbl(N))--10^1
,N3 AS (SELECT 1 AS N FROM x CROSS JOIN x AS N2 CROSS JOIN x N3) --10^3
,Tally AS(SELECT TOP(@end-@start +1) ROW_NUMBER() OVER(ORDER BY(SELECT NULL)) + @start -1 AS Nr FROM N3 
          CROSS JOIN N3 N6 CROSS JOIN N3 AS N9)
SELECT Nr*@step AS Nr
FROM Tally;
GO

--Your test table as mock-up

DECLARE @YourTbl TABLE(ID INT,[DATE] DATE,[REPEAT] INT,[INTERVAL] INT,[VALUE] INT);
INSERT INTO @YourTbl VALUES
 (1,{d'2016-01-01'},1,1,10)
,(2,{d'2014-01-01'},12,3,15)
,(3,{d'2015-01-01'},13,1,20)
,(4,{d'2014-04-01'},12,6,100);

--Try all your test-dates here. The last from your comment returns 2 and 4, not 1 and 4...

DECLARE @SearchDate DATE={d'2016-10-01'};

--This is the query

WITH RangeList AS
(
    SELECT tbl.*
          ,DATEADD(MONTH,Months.Nr,tbl.[DATE]) AS IntervalPoint
    FROM @YourTbl AS tbl
    CROSS APPLY dbo.RunningNumbers(0,tbl.[REPEAT]-1,tbl.[INTERVAL]) AS Months
)
SELECT *
FROM RangeList
WHERE IntervalPoint=@SearchDate
GO

--Clean-Up

DROP FUNCTION RunningNumbers;
Comments