question

Karanjeet avatar image
Karanjeet asked

Overlapping date ranges

I have a table that maintains price of product over an year. I am struggling with the overlapping date ranges.It has multiple prices for a particular range and I need to pick the latest one and there has to be only one price per day. Attaching an example for the same.![alt text][1] Can someone help me with this puzzle. Thanks in advance [1]: /storage/temp/3445-example.jpg
date-range
example.jpg (43.1 KiB)
example.jpg (43.1 KiB)
2 comments
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Magnus Ahlkvist avatar image Magnus Ahlkvist commented ·
What version of SQL Server are you running?
0 Likes 0 ·
Karanjeet avatar image Karanjeet commented ·
Sql server 2012
0 Likes 0 ·

1 Answer

·
David Wimbush avatar image
David Wimbush answered
You could do it like this: select SequenceNo , Product , ValidFrom , ValidTo , DateMofified , Price from ( select SequenceNo , Product , ValidFrom , ValidTo , DateMofified , Price , row_number() over (partition by SequenceNo, Product, ValidFrom, ValidTo, Price order by DateModified desc) as RowNum from table_name ) x where RowNum = 1;
1 comment
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Karanjeet avatar image Karanjeet commented ·
Superb.. I have followed the same approach. Thanks alot for your suggestion.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.