Data extrapolation on the fly
In some cases we need to generate the data based on a starting date, to an end data (today-getdate()). This process is called data extrapolation based on limited information (starting date). Here I am trying to achieve the same without storing data in temp./staging tables.
First I am creating a temp. table wih 2 rows for Demo and now I need to generate rest of the rows starting from given date to today's date.
Next step is to make use of CTE to generate sequence of rows and finally making use of CROSS JOIN to join the sequence with real data and get the desired output.
CREATE TABLE #TEMP (
MYNAME VARCHAR(10),
STARTDATE DATETIME)
GO
INSERT INTO #TEMP
VALUES ('Mohit',
'2008-09-15')
INSERT INTO #TEMP
VALUES ('Nayyar',
'2008-09-22')
GO
WITH FIRST2(SEQ)
AS (SELECT 1
UNION ALL
SELECT 1),
FIRST4(SEQ)
AS (SELECT 1
FROM FIRST2 X
CROSS JOIN FIRST2 Y),
FIRST16(SEQ)
AS (SELECT 1
FROM FIRST4 X
CROSS JOIN FIRST4 Y),
FIRST256(SEQ)
AS (SELECT 1
FROM FIRST16 X
CROSS JOIN FIRST16 Y),
FIRST65536(SEQ)
AS (SELECT 1
FROM FIRST256 X
CROSS JOIN FIRST256 Y),
SEQROWS
AS (SELECT ROW_NUMBER()
OVER(ORDER BY SEQ) AS SEQNUMBER
FROM FIRST65536 WITH (NOEXPAND))
SELECT MYNAME,
STARTDATE,
DATEADD(DAY,N.SEQNUMBER - 1,STARTDATE) AS EXTRAPOLATION
FROM #TEMP
CROSS JOIN SEQROWS N
WHERE N.SEQNUMBER <= DATEDIFF(DAY,STARTDATE,GETDATE())
ORDER BY MYNAME,
STARTDATE,
3
GO
DROP TABLE #TEMP
GO
- Mohit Nayyar