Tags

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


SQL Puzzle | The Next Date Puzzle

In this puzzle you have to change the effective date column to the first day of the month. The catch is if the last (termination date + 1 ) or (termination date + 0 ) THEN effective date should be current effective date + 1 day. Please check out the sample input values and sample expected output below.

Sample Input

Effective Date Termination Date Emissions Category Service Type
2007-10-21 00:00:00.000 2010-12-30 00:00:00.000 Stats Fuel Oil
2010-12-31 00:00:00.000 2011-12-30 00:00:00.000 Stats Fuel Oil
2011-12-31 00:00:00.000 2013-12-30 00:00:00.000 Stats Fuel Oil
2013-12-31 00:00:00.000 2014-12-30 00:00:00.000 Stats Fuel Oil
2014-12-31 00:00:00.000 2015-12-30 00:00:00.000 Stats Fuel Oil
2015-12-31 00:00:00.000 NULL Stats Fuel Oil

Expected Output

Effective Date Termination Date Emissions Category Service Type
2007-10-01 00:00:00.000 2010-12-01 00:00:00.000 Stats Fuel Oil
2011-01-01 00:00:00.000 2011-12-01 00:00:00.000 Stats Fuel Oil
2012-01-01 00:00:00.000 2013-12-01 00:00:00.000 Stats Fuel Oil
2014-01-01 00:00:00.000 2014-12-01 00:00:00.000 Stats Fuel Oil
2015-01-01 00:00:00.000 2015-12-01 00:00:00.000 Stats Fuel Oil
2016-01-01 00:00:00.000 NULL Stats Fuel Oil

Script

Use below script to create table and insert sample data into it.

--

CREATE TABLE ManageData
(
     [Effective Date] DATETIME
    ,[Termination Date] DATETIME
    ,[Emissions Category] VARCHAR(1000) 
    ,[Service Type] VARCHAR(1000)   
)
GO
 
INSERT INTO ManageData VALUES
('10/21/2007',  '12/30/2010',   'Stats',   'Fuel Oil' ), 
('12/31/2010',  '12/30/2011',   'Stats',   'Fuel Oil' ), 
('12/31/2011',  '12/30/2013',   'Stats',   'Fuel Oil' ), 
('12/31/2013',  '12/30/2014',   'Stats',   'Fuel Oil' ), 
('12/31/2014',  '12/30/2015',   'Stats',   'Fuel Oil' ), 
('12/31/2015',  NULL,           'Stats',   'Fuel Oil' )
GO
 			 
--

Rules/Restrictions

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

Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

Solution 1


--

SELECT 
	CASE WHEN [Effective Date] = ISNULL( DATEADD(Day,1,LAG(CASE WHEN [Termination Date] = '1900-01-01 00:00:00.000' THEN NULL ELSE [Termination Date] END) 
						OVER (ORDER BY CASE WHEN [Termination Date] = '1900-01-01 00:00:00.000' THEN NULL ELSE [Termination Date] END)) , [Effective Date] )
			  THEN DATEADD(month, DATEDIFF(month, 0, DATEADD(Day,1,[Effective Date])), 0)
			  END [Effective Date]
	,DATEADD(MONTH, DATEDIFF(MONTH, 0, DATEADD(Day,1, CASE WHEN [Termination Date] = '1900-01-01 00:00:00.000' THEN NULL ELSE [Termination Date] END)), 0) 
	[Termination Date]
	,[Emissions Category]
	,[Service Type]	
FROM ManageData
ORDER BY [Effective Date]


--

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