April 13, 2015 at 11:43 pm
After seeing article about LAST_VALUE (http://www.sqlservercentral.com/Forums/Topic1676753-202-1.aspx?Update=1), I'm trying to possibly 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
(
SELECT AU.TICKETID,
TICKET.DESCRIPTION,
AU.JHG_RAILPRIORITY,
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,
AU.REPORTDATE,
AU.TARGETSTART,
AU.TARGETFINISH,
AU.EAUDITUSERNAME,
AU.EAUDITTIMESTAMP,
AU.EAUDITTYPE,
TICKET.JHG_WORKGROUP
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) + '%'
)
SELECT TICKETID,
DESCRIPTION,
JHG_RAILPRIORITY,
PREV_RPTDATE,
NEXT_RPTDATE,
REPORTDATE,
TARGETSTART,
TARGETFINISH,
EAUDITUSERNAME,
EAUDITTIMESTAMP,
EAUDITTYPE,
JHG_WORKGROUP
FROM TICKET_TBL
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 or if anyone has any other ideas.
Many thanks.
Viewing 0 posts
You must be logged in to reply to this topic. Login to reply