quote:

---

Originally posted by ourtime545

I have 4 fields,

Name
Old value
New value
Operation Date

This are fields of audit table.
Now for example below in 'Sun Healthcare' new value is 'scheduled', And its changed to 'Occurred' after that. And also after that again changed to 'Pending'.
Then I want to display 'Occurred' because it is first changed value after 'Scheduled' as per operation date.

So basically for every NAME i want to display anything changed after 'scheduled'

For example see below:

NAME OLD_VAL NEW_VAL OPERATION_DT Row No.
Sun Healthcare Pending QC Rejected 8/16/2010 8
Sun Healthcare Occurred Pending 8/12/2010 7
Sun Healthcare Scheduled Occurred 11/11/2009 6
Sun Healthcare QC Approved Scheduled 10/7/2009 1
Sun Healthcare QC Pending QC Approved 10/7/2009 5
Sun Healthcare QC Rework QC Pending 10/6/2009 4
Sun Healthcare QC Pending QC Rework 10/6/2009 3
Sun Healthcare Pending QC Pending 10/1/2009 2
Sun Pharma Pending QC Rejected 8/4/2010 5
Sun Pharma Scheduled Pending 6/8/2010 4
Sun Pharma QC Approved Scheduled 10/13/2009 1
Sun Pharma QC Pending QC Approved 10/13/2009 3
Sun Pharma Pending QC Pending 10/9/2009 2


I tried this query by myself, but from that i can get row number of 'scheduled' as '1', but i want to display the immediate change value after 'scheduled' in the new value.


SELECT
OPT.NAME
,SAI.OLD_VAL
, SAI.NEW_VAL
, SAI.OPERATION_DT
,ROW_NUMBER() OVER(PARTITION BY OPT.NAME ORDER BY case WHEN CONVERT(VARCHAR(1000),NEW_VAL)='Scheduled' then 1 ELSE OPERATION_DT END ) 'Row Number'

FROM Reporting.dbo.AUDIT SAI
Inner Join Reporting.dbo.OPTY OPT ON OPT.ROW_ID=SAI.RECORD_ID
where SAI.BUSCOMP_NAME='Opportunity'
and SAI.FIELD_NAME='Status'
and opt.name in (' Sun Healthcare',' Sun Pharma')
ORDER BY OPT.NAME,SAI.OPERATION_DT desc


Can anyone please help. It will be greatly appreciated.

Thanks

---

SELECT t1.Name,t2.OLD_VAL,t2.NEW_VAL,t2.OPERATION_DT,t2.Row No
FROM Table t1
CROSS APPLY (SELECT TOP 1 *
             FROM table
             WHERE name = t1.Name
             AND OPERATION_DT > t1.OPERATION_DT
             ORDER BY OPERATION_DT)t2
WHERE t1.NEW_VAL='Scheduled'