If the gold rate is changed then only we have to create a new row. 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 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

Try this code ; with table as ( Select '2018-01-18' DateColumn, 100 value union Select '2018-01-19' DateColumn, 100 value union Select '2018-01-20' DateColumn, 300 value union Select '2018-01-21' DateColumn, 400 value ), cte as ( Select *, ROW_NUMBER() over (Partition By Value Order By DateColumn) idx from table ) Select a.DateColumn FirstDate, case when b.DateColumn is null then a.DateColumn else b.DateColumn end SecondDate, a.value from cte a left join ( select * from cte where idx = 2) b on a.value = b.value Where a.idx = 1

Answer

Find the startdate and enddate for Gold Rate?

Enni

657

If the gold rate is changed then only we have to create a new row.

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

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

Answers (1)

Next Recommended Forum

Exporting from excel to SQL server

How to use of temp table in sql server

Forum Statistics

Please welcome our newest member .
users have contributed to threads and
In the past 24 hours, we have new threads, new posts, and new users.
In last week, the most popular thread is .

Upcoming Events

View all

Software Architecture Conference - 2025
Automated Sales Quote Approval Workflow for Faster Deal Closure: Automate Your Business Processes - Ep.9
Explore, Build, Deploy: Your AI Agent in Action