SQL Puzzle | Fill the Date Gaps Puzzle (No tally or numbers table)

This puzzle is really simple. We have to write the SQL query to fill the gaps between dates. The catch is we cannot use tally table or a number table here !

Please check out the sample input and the expected output below-

Sample Input

9.1833 2016-06-29 00:00:00.000
5.6500 2016-06-30 00:00:00.000
6.5833 2016-07-01 00:00:00.000
7.4000 2016-07-05 00:00:00.000

Expected Output

9.1833 2016-06-29 00:00:00.000
5.6500 2016-06-30 00:00:00.000
6.5833 2016-07-01 00:00:00.000
0.0000 2016-07-02 00:00:00.000
0.0000 2016-07-03 00:00:00.000
0.0000 2016-07-04 00:00:00.000
7.4000 2016-07-05 00:00:00.000

Rules/Restrictions

• The solution should be should use “SELECT” statement or a CTE.

Script | use below script to create table & insert some sample data

 ``` -- CREATE TABLE [dbo].[test] ( [Hourss] [varchar](10) NULL, [Dt] [datetime] NULL ) ON [PRIMARY] GO INSERT INTO [dbo].[test](Hourss,Dt) VALUES (9.1833, '2016-06-29 00:00:00.000'), (5.6500, '2016-06-30 00:00:00.000'), (6.5833, '2016-07-01 00:00:00.000'), (7.4000, '2016-07-05 00:00:00.000') GO -- ```

Solution 1

 ``` -- ;WITH CTE AS ( SELECT * , REPLICATE( Hourss + ',' , r ) VALUE FROM ( SELECT *, DATEDIFF(d, ISNULL(LAG(Dt) OVER (ORDER BY dt),dt) ,dt )r,ISNULL(LAG(Dt) OVER (ORDER BY dt),dt) lastdt FROM test ) r WHERE r.r > 1 ) ,CTE1 AS ( SELECT CAST((''+REPLACE(LEFT(e.VALUE,len(e.VALUE)-1),',' ,'')+'') as xml) as xmlcol FROM CTE e ) SELECT * FROM ( SELECT CASE WHEN rnk = 0 THEN CAST(splitdata AS DECIMAL(10,4)) ELSE 0.0000 END Hourss , DATEADD(d, (rnk*-1), dt) dt FROM CTE1 S CROSS APPLY ( SELECT ProjectData.D.value('.', 'varchar(50)') as splitdata, ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) -1 rnk FROM s.xmlcol.nodes('X') as ProjectData(D) ) d INNER JOIN test t ON d.splitdata = t.Hourss UNION ALL SELECT Hourss,Dt FROM ( SELECT *, DATEDIFF(d, ISNULL(LAG(Dt) OVER (ORDER BY dt),dt) ,dt )r,ISNULL(LAG(Dt) OVER (ORDER BY dt),dt) lastdt FROM test ) r WHERE r.r <= 1 )y ORDER BY dt -- ```

Add a comment if you have any other solution in mind. I would love to learn it. We all need to learn.

Enjoy !!! Keep Learning

Pawan Khowal

Http://MSBISkills.com