Hi I'm trying to apply this to an SSRS report I am working on, which compares dates from previous rows. I previously just used LAG/LEAD but rows aren't showing as required. It's pretty much like trying to compare using the Previous() function in SSRS but I can't use this to filter out values when the parameter @ReportDateChanged is checked.
Below is my code. The area in bold is what I am trying to play around with.
At the moment it just shows some report dates that have been changed but not all....
WITH TICKET_TBL AS
LAG (AU.REPORTDATE) OVER (ORDER BY AU.TICKETID, AU.EAUDITTIMESTAMP) as PREV_RPTDATE,
LEAD (AU.REPORTDATE) OVER (ORDER BY AU.TICKETID, AU.EAUDITTIMESTAMP) as NEXT_RPTDATE,
LAG (AU.TICKETID) OVER (ORDER BY AU.TICKETID, AU.EAUDITTIMESTAMP) as PREV_DEFECT,
LEAD (AU.TICKETID) OVER (ORDER BY AU.TICKETID, AU.EAUDITTIMESTAMP) as NEXT_DEFECT,
FROM MAXIMO.AU_TICKET AU
LEFT OUTER JOIN MAXIMO.TICKET ON TICKET.TICKETID = AU.TICKETID
WHERE AU.SITEID = 'CRN' and AU.TICKETID like upper(@DefectNo) + '%' and AU.EAUDITUSERNAME like upper(@UserName) + '%'
WHERE @ReportDateChanged = 1 and PREV_DEFECT = NEXT_DEFECT and PREV_RPTDATE <> NEXT_RPTDATE
OR @ReportDateChanged = 0
ORDER BY TICKETID, EAUDITTIMESTAMP
Any help would be appreciated.