question

aRookieBIdev avatar image
aRookieBIdev asked

SQL Query Help

Hi Please find below mytable structure and expected result. I have tried joining the same table with LastModified less than the other. But I still couldnt it in this order. Thanks in advance [1]: /storage/temp/1165-untitled.png
sql-server-2008t-sql
untitled.png (7.5 KiB)
10 |1200

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

1 Answer

·
Dave_Green avatar image
Dave_Green answered
Hello, I've had to make some guesses about keys etc. (it's helpful for future questions if you can post code to create the schema), but here goes: DECLARE @T TABLE ( RateCode CHAR(2) , Rate INT , LastModified DATETIME ) INSERT @T VALUES ( '01', 10, '2008-12-10 23:00' ), ( '01', 20, '2009-12-10 23:00' ), ( '01', 30, '2010-12-10 23:00' ) ;WITH LastModifiedDate ( RateCode, CurrentRate, PreviousModified ) AS ( SELECT a.RateCode , a.Rate CurrentRate , MAX(b.LastModified) AS PreviousModified FROM @T a LEFT JOIN @t b ON a.ratecode = b.ratecode AND a.LastModified > b.LastModified GROUP BY a.Rate , a.RateCode , a.LastModified ) SELECT LastModifiedDate.RateCode , LastModifiedDate.CurrentRate , z.Rate PreviousRate FROM LastModifiedDate LEFT JOIN @T z ON z.RateCode = LastModifiedDate.RateCode AND LastModifiedDate.PreviousModified = z.LastModified ORDER BY LastModifiedDate.CurrentRate DESC Basically, I'm calculating the latest modified date prior to the date of the current record, then using that to join back to the original table to get the rate, assuming that RateCode is your key.
10 |1200

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

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.