June 16, 2017 at 9:14 am
I am trying to write a query that allows me to find the first recent occurrence where a column value changed. I have provided some data to explain the scenario.
CREATE TABLE #HS_SystemCustomerCRE(
[H_SystemCustomer_Seq_ID] [int] NOT NULL,
[RateDate] [datetime] NULL,
[Grade] [nvarchar](3) NULL,
)
INSERT INTO #HS_SystemCustomerCRE
(
[H_SystemCustomer_Seq_ID]
,[RateDate]
,[Grade]
)
VALUES (1, '2016-12-14 10:48:10.000' ,'R3')
,(1, '2016-12-08 10:48:10.000' ,'R1')
,(1, '2016-12-09 10:48:10.000' ,'D')
,(1, '2016-12-10 10:48:10.000' ,'R1')
,(1, '2016-12-11 10:48:10.000' ,'D1')
,(1, '2016-12-12 10:48:10.000' ,'D2')
,(1, '2016-12-13 10:48:10.000' ,'R1')
,(1, '2016-12-14 10:48:10.000' ,'R1')
,(1, '2016-12-15 10:48:10.000' ,'D2')
,(1, '2016-12-16 10:48:10.000' ,'R1')
,(1, '2016-12-17 10:48:10.000' ,'R1')
,(1, '2016-12-18 10:48:10.000' ,'D3')
,(1, '2016-12-19 10:48:10.000' ,'D4')
,(1, '2016-12-20 10:48:10.000' ,'D7')
,(1, '2016-12-21 10:48:10.000' ,'R3')
,(1, '2016-12-22 10:48:10.000' ,'R4')
,(1, '2016-12-23 10:48:10.000' ,'R7')
In this case I need to find the most recent occurrence, ratedate, when grade switched form an R value to a D value. In this case this would be on ratedate 2016-12-18 10:48:10.000
June 16, 2017 at 9:31 am
WITH NowandNext AS (
SELECT
H_SystemCustomer_Seq_ID
, RateDate
, Grade
, LAG(RateDate) OVER (ORDER BY RateDate) AS OldDate
, LAG(Grade) OVER (ORDER BY RateDate) AS OldGrade
FROM #HS_SystemCustomerCRE
)
SELECT TOP (1)
H_SystemCustomer_Seq_ID
, RateDate
, Grade
FROM NowAndNext
WHERE Grade LIKE 'D%' AND OldGrade LIKE 'R%'
ORDER BY RateDate DESC;
John
June 16, 2017 at 2:10 pm
Many thanks. Precisely what I needed
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply