Tags

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


T-SQL Query | [SQL | Gold Rate Puzzle] – In the puzzle we have gold rate changing all the time. We have to find the start date, End Date and the gold rate at that duration. If the gold rate is changed then only we have create a new row. Please check the sample input and the expected output.

Sample Input

dt Rate
18-09-2014 27000
19-09-2014 27000
20-09-2014 31000
21-09-2014 31000
22-09-2014 31000
23-09-2014 32000
24-09-2014 31000
25-09-2014 32000
26-09-2014 27000

Expected Output

StartDate EndDate Rate
18-09-2014 19-09-2014 27000
20-09-2014 22-09-2014 31000
23-09-2014 23-09-2014 32000
24-09-2014 24-09-2014 31000
25-09-2014 25-09-2014 32000
26-09-2014 26-09-2014 27000

Rules/Restrictions

  • The solution should be should use “SELECT” statement or “CTE”.
  • Send your solution to pawankkmr@gmail.com
  • Do not post you solution in comments section

Script Use the below script to generate the source table and fill them up with the sample data.


--Create table

CREATE TABLE [dbo].[testGoldRateChange]
(
[dt] [datetime] NULL,
[Rate] [int] NULL
)
GO

--Insert Data
INSERT INTO [dbo].[testGoldRateChange](dt,Rate)
VALUES
('2014-09-18 06:25:19.897', 27000),
('2014-09-19 06:25:19.897', 27000),
('2014-09-20 06:25:19.897', 31000),
('2014-09-21 06:25:19.897', 31000),
('2014-09-22 06:25:19.897', 31000),
('2014-09-23 06:25:19.897', 32000),
('2014-09-24 06:25:19.897', 31000),
('2014-09-25 06:25:19.897', 32000),
('2014-09-26 06:25:19.897', 27000)

--Check data
SELECT dt,Rate FROM [dbo].[testGoldRateChange]

Update May 14 | Solutions



--


---------------------------------------
--Sol 1 | Pawan Kumar Khowal
---------------------------------------

; WITH CTE1 AS ( SELECT dt , Rate , ROW_NUMBER() OVER (ORDER BY dt) Rnk  FROM testGoldRateChange )
,CTE2 AS 
(
SELECT *,  CASE WHEN
              Rate = ( SELECT Rate from CTE1 c3 WHERE c3.rnk =  ( SELECT MAX(c1.rnk) FROM CTE1 c1 WHERE c1.rnk < c2.rnk ))  
              THEN 0 ELSE 1 END Identifier,
            SUM(CASE WHEN
              Rate = ( SELECT Rate from CTE1 c3 WHERE c3.rnk =  ( SELECT MAX(c1.rnk) FROM CTE1 c1 WHERE c1.rnk < c2.rnk ))  
              THEN 0 ELSE 1 END) OVER (ORDER BY rnk ) cols                   
                       FROM CTE1 c2
)
SELECT MIN(dt) StartDate , MAX(dt) EndDate , MAX(Rate) Rate FROM CTE2 GROUP BY cols


---------------------------------------
--Sol 2 | Pawan Kumar Khowal
---------------------------------------


; WITH CTE1 AS 
( 
       SELECT dt , Rate , ROW_NUMBER() OVER (ORDER BY dt) Rnk  FROM testGoldRateChange 
)
,CTE2 AS 
(
       SELECT *,  CASE WHEN Rate = lag(Rate) over(order by rnk) THEN 0 ELSE 1 END cols FROM CTE1 c2
)
,CTE3 AS
(
       SELECT *,  SUM(cols) over(order by rnk) Grouper FROM CTE2 c2
)
SELECT MIN(dt) StartDate , MAX(dt) EndDate , MAX(Rate) Rate FROM CTE3 GROUP BY Grouper




--

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

Keep Learning

http://MSBISkills.com