Tags
Complex SQL Challenges, Complex TSQL Challenge, Interview Qs.SQL SERVER Questions, Interview Questions on SQL, InterviewQuestions, InterviewQuestions for SQL, Learn complex SQL, Learn SQL, Learn T-SQL, PL/SQL Challenges, Puzzles, Queries for SQL Interview, SQL, SQL 2012, SQL Challenge, SQL Challenges, SQL pl/sql puzzles, SQL Puzzles, SQL SERVER Interview questions, SQL SERVER2005/2008, SQL Sudoku, SQLSERVER, T SQL Puzzles, T-SQL Challenge, Tough SQL Challenges, Tough SQL Puzzles, TSQL, TSQL Challenge, TSQL Challenges, TSQL Interview questions, TSQL Queries
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
Improving my SQL BI Skills 
;WITH cte AS
(
SELECT *, DATEPART(dd, dt) – ROW_NUMBER() OVER (PARTITION BY Rate ORDER BY dt) RN FROM [testGoldRateChange]
)
SELECT MIN(dt) StartDate, MAX(dt) EndDate, Rate FROM cte GROUP BY RN, Rate
ORDER BY StartDate
LikeLike
Great Reply ..
Try few more puzzles !
LikeLike