Comparing previous/current row values

  • 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.

  • Duplicate post. Replies here please: http://www.sqlservercentral.com/Forums/FindPost1676771.aspx

    -- Gianluca Sartori

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply