T-SQL standard way to get current and previous rate situation
-
Hi all, can you help out. I would like to get this : ID Current Rate Previous Rate Current start date Previous start date x1234 50 95 01-09-2016 00:00 12-12-2016 00:00 From the following table : Select * from RATESID Rate Start_Date End_Date x1234 555 22-10-2014 00:00 11-05-2016 00:00 x1234 95 12-12-2016 00:00 31-05-2016 00:00 x1234 50 01-09-2016 00:00 31-12-1999 00:00 Whats the best approach here to get this in a standard way, knowing that some users may have up to 6 rates? Thanks in advance -
Are you really still using SQL Server 2000? That version hasn't been supported for over 4 years now. If you had a more recent version (2012+) you could easily achieve this using the LAG. If not, you're need to do a further JOIN to the table.
If you could confirm the SQL server you're using, that would be great, but if you are still using 2000, I would heavily recommend you upgrade, as using outdated software can (does) carry a lot of risks.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk -
Thanks.
I'm using SQL Server 2008
Just tried the LAG, but it retrieves the whole history
Don't know how I can only get a row per ID, as I'm only looking to get the current and previous rate.LAG (Rate, 1) OVER (PARTITION BY ID ORDER BY ID) as "Previous rate" -
LAG isn't a command available in 2008, it was first brought in in 2012, so the above should have presented you with an error.
You'll need to JOIN back to the same table for your environment, using an OUTER APPLY. So something like:
SELECT YTc.ID,
YTc.Rate AS CurrentRate,
YTp.Rate AS PreviousRate,
YTc.[Start_Date] AS CurrentStartDate,
YTp.[Start_Date] AS PreviousStartDate
FROM YourTable YTc
OUTER APPLY (SELECT TOP 1 *
FROM YourTable oa
WHERE oa.ID = YTc.ID
AND oa.End_Date <= YTc.[Start_Date]
ORDER BY EndDate DESC) YTp;The OUTER APPLY works like a LEFT JOIN. This means that if no prior Start Date is found, a row is still found. If you don't want this to apply, you would use CROSS APPLY (which works like a INNER JOIN).
P.s. It's always important to post in the forum that is relevant for the version of SQL server you're using. You've posted in the SQL Server 2000 forums, so people reading your post will assume that that is the version you're using. Functionality comes (and sometimes goes) with each version, so if you post in the correct forums, you'll get a solution that best suits your needs and Instance. 🙂
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk -
P.s. It's always important to post in the forum that is relevant for the version of SQL server you're using. You've posted in the SQL Server 2000 forums, so people reading your post will assume that that is the version you're using. Functionality comes (and sometimes goes) with each version, so if you post in the correct forums, you'll get a solution that best suits your needs and Instance.
Roger that
Thanks and YES 2012 - d_martins - Wednesday, September 20, 2017 3:54 AM
P.s. It's always important to post in the forum that is relevant for the version of SQL server you're using. You've posted in the SQL Server 2000 forums, so people reading your post will assume that that is the version you're using. Functionality comes (and sometimes goes) with each version, so if you post in the correct forums, you'll get a solution that best suits your needs and Instance.
Roger that
Thanks and YES 2012So you're using 2012? Then the LAG syntax would be:
LAG(Rate) OVER (PARTITION BY ID ORDER BY Start_date) AS PreviousRate
Note the ORDER BY caluse is different from what you attempted. You ordered by ID, but every row would have had the same ID within that LAG (as that's where your PARTITION is), so the value returned would have been Random.If you need to have a default value, then replace the first part with something like:
LAG(Rate,1,0)
Where 0 is the default value. Note it needs to be of the same data type as your column; so if your rating column is the int data type, you can't use a value such as 'N/A' (which is a varchar).Hope that helps.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk -
Still KO
The data presented earlier is not OKID Rate Start_Date End_Date x1234 555 22-10-2014 00:00 11-12-2016 00:00 x1234 95 12-12-2016 00:00 31-05-2016 00:00 x1234 50 01-06-2016 00:00 31-12-1999 00:00 If I do
Select Rate as CurrentRate, LAG(Rate) OVER (PARTITION BY ID ORDER BY Start_date) AS PreviousRate
FROM MY_TABLEThis retrieves all the rates
CurrenyRate PreviousRate 555 NULL 95 555 50 95 Don't know what am I doing wrong here.
-
Ahh, I see what you're saying now: you worded your post easlier as if to say that LAG was returning multiple results, which it is not. You mean you want to limit your result set to one row. 🙂
Use TOP 1, and ORDER BY (sorting by your End_date in descending order).
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk -
Try below query, i have created a temp table with some sample data. you may need to change the order by clause based on the data you want to see.
CREATE TABLE #t
(
id varchar(10)
,Rate decimal(15,2)
,Start_Date datetime
,end_date datetime
)
INSERT INTO #T VALUES
('x1234', 555, '10-22-2014 00:00', '05-11-2016 00:00')
,('x1234', 95, '12-12-2016 00:00', '05-31-2016 00:00')
,('x1234', 50, '09-01-2016 00:00', '12-31-1999 00:00')
,('x1235', 101, '10-22-2014 00:00', '05-11-2016 00:00')
,('x1235', 25, '12-12-2016 00:00', '05-31-2016 00:00')
,('x1235', 75, '09-01-2016 00:00', '12-31-1999 00:00');WITH Rates AS
(
SELECT *
,LAG(Rate) OVER (PARTITION BY ID ORDER BY Start_date) AS PreviousRate
,LAG(Start_Date) OVER (PARTITION BY ID ORDER BY Start_date) AS PreviousStart_Date
,Rank() OVER (PARTITION BY ID ORDER BY Start_Date DESC) GroupId
FROM #t a
)
SELECT *
FROM Rates
WHERE GroupId =1 - d_martins - Wednesday, September 20, 2017 2:04 AM
In the example, dates are overlapping or end date is less than start date.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply