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
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.
I strongly recommend that you learn to use the code button on the formatting toolbar in the edit menu so that your code actually come out readable.
please explain select addressline1, city from person.address where isnumeric (postalcode)=1 and cast(cast(postalcode as int)as char )>'9000'?
Here's the formatted code that Dave_Green posted...
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
17 People are following this question.
