asked
Expand FromDate & ToDate columns into rows of
Hi there, Take the following sample data: WITH SampleData AS ( SELECT '8000213' AS EmployeeID, '2014-08-25 00:00:00.000' AS StartDate, '2014-08-31 00:00:00.000' AS EndDate, 28.5 AS HPW UNION ALL SELECT '8000213' AS EmployeeID, '2014-10-01 00:00:00.000' AS StartDate, NULL AS EndDate, 33 AS HPW UNION ALL SELECT '0003289' AS EmployeeID, '2014-04-25 00:00:00.0000' AS StartDate, '2014-04-30 00:00:00.000' AS EndDate, 36 AS HPW UNION ALL SELECT '0003289' AS EmployeeID, '2014-05-01 00:00:00.000' AS StartDate, NULL AS EndDate, 20 AS HPW ) SELECT * FROM SampleData How can we expand this data as follows (when there is no end date, assume current date): ![alt text][1] I suspect some kind of recursion / CTE / tally table will be required here but cant quite get my head round it. Thank You. Regards Jordon [1]: /storage/temp/1788-targetdata.png
Something like this (the first 90 rows is used to create a fast "tally table" function that can be reused) : CREATE FUNCTION [dbo].[fnNumsTable]( @pStartValue BIGINT= 1, @pEndValue BIGINT= 1000000, @pIncrement BIGINT= 1 ) RETURNS TABLE AS -- +---------------------------------------------------------------------------------------------------------------- -- ! O b j e c t : [DWH].[fnNumsTable] -- ! R e t u r n s : A table with number from startvalue to end value -- ! P a r a m e t e r s : Name DataType Description -- + ======================= ============== ================================================== -- ! @pStartValue BIGINT= 1, -- ! @pEndValue BIGINT= 1000000, -- ! @pIncrement BIGINT= 1 -- + --------------------------------------------------------------------------------------------------------------- -- ! O b j e c t i v e : Return a list of all numbers between startvalue to end value -- ! The table may be used to avoid cursors, generate a list of dates etc. -- + --------------------------------------------------------------------------------------------------------------- -- ! H i s t o r y : -- + --------------------------------------------------------------------------------------------------------------- -- ! Date Who What -- + ========== ===== ======================================================================== -- ! 2009-09-21 HAWI Created temp table for CDS to improve performance -- +---------------------------------------------------------------------------------------------------------------- --Select n from dbo.[fnNumsTable](1000,2000,1) RETURN( WITH BaseNum ( N ) AS ( SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 ), L1 ( N ) AS ( SELECT bn1.N FROM BaseNum bn1 CROSS JOIN BaseNum bn2 ), L2 ( N ) AS ( SELECT a1.N FROM L1 a1 CROSS JOIN L1 a2 ), L3 ( N ) AS ( SELECT TOP ((ABS(CASE WHEN @pStartValue < @pEndValue THEN @pEndValue ELSE @pStartValue END - CASE WHEN @pStartValue < @pEndValue THEN @pStartValue ELSE @pEndValue END))/ABS(@pIncrement)+ 1) a1.N FROM L2 a1 CROSS JOIN L2 a2 ), Tally ( N ) AS ( SELECT ROW_NUMBER() OVER (ORDER BY a1.N) FROM L3 a1 ) SELECT ((N - 1) * @pIncrement) + @pStartValue AS N FROM Tally ); GO WITH SampleData AS ( SELECT '8000213' AS EmployeeID, CONVERT(DATETIME,'2014-08-25 00:00:00.000') AS StartDate, CONVERT(DATETIME,'2014-08-31 00:00:00.000') AS EndDate, 28.5 AS HPW UNION ALL SELECT '8000213' AS EmployeeID, CONVERT(DATETIME,'2014-10-01 00:00:00.000') AS StartDate, CONVERT(DATETIME,NULL) AS EndDate, 33 AS HPW UNION ALL SELECT '0003289' AS EmployeeID, CONVERT(DATETIME,'2014-04-25 00:00:00.000') AS StartDate, CONVERT(DATETIME,'2014-04-30 00:00:00.000') AS EndDate, 36 AS HPW UNION ALL SELECT '0003289' AS EmployeeID, CONVERT(DATETIME,'2014-05-01 00:00:00.000') AS StartDate, CONVERT(DATETIME,NULL) AS EndDate, 20 AS HPW ) SELECT * FROM SampleData CROSS APPLY [SQLServiceTools2014].dbo.[fnNumsTable](1,1000,1) AS [fnt] CROSS APPLY (SELECT DATEADD(d,n,StartDate)) AS x(dag) WHERE x.[dag] >= [SampleData].[StartDate] AND x.[dag]]]]]
Impressive, that's almost perfect except the first day of each range is missing. i.e. 25th - 30th in the sample data is expanding as 26th - 30th (25th is missing) - how could one correct for this?
Tried that, didn't work :) doing DATEADD(DAY, NUMBER -1, StartDate) however worked a treat - thank you so much!
Jordan, converting those date ranges into individual dates becomes greatly simplified with a date dimension: SELECT SampleData.EmployeeID , dimDate.FullDate , SampleData.HPW FROM SampleData INNER JOIN dimDate ON dimDate.FullDate >= SampleData.StartDate AND dimDate.FullDate = SampleData.StartDate AND dimDate.FullDate
WITH SampleData AS ( SELECT '8000213' AS EmployeeID, convert(datetime, '2014-08-25 00:00:00.000') AS StartDate, '2014-08-31 00:00:00.000' AS EndDate, 28.5 AS HPW UNION ALL SELECT '8000213' AS EmployeeID, '2014-10-01 00:00:00.000' AS StartDate, NULL AS EndDate, 33 AS HPW UNION ALL SELECT '9003289' AS EmployeeID, '2014-04-25 00:00:00.000' AS StartDate, '2014-04-30 00:00:00.000' AS EndDate, 36 AS HPW UNION ALL SELECT '9003289' AS EmployeeID, '2014-05-01 00:00:00.000' AS StartDate, NULL AS EndDate, 20 AS HPW ), rCTE AS ( SELECT EmployeeID, [DATE] = StartDate, EndDate, HPW FROM SampleData UNION ALL SELECT EmployeeID, [DATE] = DATEADD(DAY, 1, [DATE]), EndDate, HPW FROM rCTE WHERE DATEADD(DAY, 1, [DATE]) ]]
17 People are following this question.
