Tags

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,


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

Hourss Dt
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

Hourss dt
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.
  • Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

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(('<X>'+REPLACE(LEFT(e.VALUE,len(e.VALUE)-1),',' ,'</X><X>')+'</X>') 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