Using LAST_VALUE

  • Hi I basically want to return all of the ticketid's that have a reportdate that was changed

    ie...

    TICKETID, REPORTDATE

    a,17/04/15

    a,17/04/15

    a,19/02/14

    b,12/02/15

    b,12/02/15

    b,12/02/15

    c,01/01/15

    c,01/02/15

    should return

    a,17/04/15

    a,19/02/14

    c,01/01/15

    c,01/02/15

    Kinda like the PREVIOUS() function in srss... example was to highlight the field that was changed....

    =iif(Previous(Fields!TICKETID.Value) = Fields!TICKETID.Value and Previous(Fields!REPORTDATE.Value) <> Fields!REPORTDATE.Value, "Red","Black")

    Thanks heaps guys 🙂 Hope this makes sense!

  • preacha (4/16/2015)


    Hi I basically want to return all of the ticketid's that have a reportdate that was changed

    ie...

    TICKETID, REPORTDATE

    a,17/04/15

    a,17/04/15

    a,19/02/14

    b,12/02/15

    b,12/02/15

    b,12/02/15

    c,01/01/15

    c,01/02/15

    should return

    a,17/04/15

    a,19/02/14

    c,01/01/15

    c,01/02/15

    Kinda like the PREVIOUS() function in srss... example was to highlight the field that was changed....

    =iif(Previous(Fields!TICKETID.Value) = Fields!TICKETID.Value and Previous(Fields!REPORTDATE.Value) <> Fields!REPORTDATE.Value, "Red","Black")

    Thanks heaps guys 🙂 Hope this makes sense!

    Have you tried something along this lines?

    If you need only one row with previous and current values you can get rid of the NEXTDATE expression.

    WITH DATA AS (

    SELECT 'a' as TICKETID, '20150417' as REPORTDATE

    UNION ALL SELECT 'a', '20150417'

    UNION ALL SELECT 'a', '20140219'

    UNION ALL SELECT 'b', '20150212'

    UNION ALL SELECT 'b', '20150212'

    UNION ALL SELECT 'b', '20150212'

    UNION ALL SELECT 'c', '20150101'

    UNION ALL SELECT 'c', '20150201'

    ), COMPARISON AS (

    SELECT TICKETID, REPORTDATE,

    LAG(REPORTDATE) OVER(PARTITION BY TICKETID ORDER BY REPORTDATE DESC) AS PREVIOUSDATE,

    LAG(REPORTDATE) OVER(PARTITION BY TICKETID ORDER BY REPORTDATE) AS NEXTDATE

    FROM DATA

    )

    SELECT *

    FROM COMPARISON

    WHERE REPORTDATE <> PREVIOUSDATE OR

    REPORTDATE <> NEXTDATE

    ORDER BY TICKETID, REPORTDATE DESC;

  • I'm lazy and use FIRST_VALUE with ORDER BY x DESC instead of this extra long LAST-VALUE syntax.

    Of course only if I don't need both (FIRST and LAST) because it would have to sort it twice otherwise.

  • @topic:

    - you should never use FIRST_VALUE without "ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW", otherwise it will always spill out to TempDB -> slow

    - for this reason I prever to use FIRST_VALUE with the ROWS-Clause with descending ORDER above LAST_VALUE

    @preacha

    - Use

    LAG (AU.REPORTDATE) OVER (PARTITION BY AU.TICKETID ORDER BY AU.EAUDITTIMESTAMP) as PREV_RPTDATE,

    LEAD (AU.REPORTDATE) OVER (PARTITION BY AU.TICKETID ORDER BY AU.EAUDITTIMESTAMP) as NEXT_RPTDATE,

    - remove the LEAD / LAG for the TicketID.

    - change your outer WHERE to check for NULL / NOT NULL in prev / next_rptdate

    - ALWAYS use brackets, if you are combining AND and OR conditions in a WHERE - I have seen too much errors because someone (including me) used them in the wrong order (or added an additional condition) and broke the statement. Beside of this its always harder to understand without brackets.

    - Except you are using IN MEMORY Tables or binary / Case sensitive Collations, you could remove the UPPER()-calls (will save some CPU cycles)

    - Read the part about maybe-parameters in http://www.sqlservercentral.com/articles/T-SQL/121906/%5B/url%5D and try to avoid them (outer WHERE)

    God is real, unless declared integer.

  • I had the same story with AVG. It was returning an average of the column values between the first and the current row.

  • bhupendra.baraiya (4/16/2015)


    we can achieve the same output without adding the rows Cluse

    ...

    , LAST_VALUE(TotalDue) OVER ( PARTITION BY CustomerID ORDER BY CustomerID ) AS TD

    ...

    I like this observation (emphasis added). Is there a "gotcha" with this approach? Am I missing something?

  • The only thing I must disagree is with the first statement "Windowing functions are the best thing since sliced bread when it comes to T-SQL".. they only will be the best when Microsoft adds something to easily find the first or last value that is NOT NULL.

    I had to face a problem weeks ago when the first_value was returning me nulls when I knew that it shouldn't.. after think and think I realized that it was returnin null just for a simple reason.. null was the frist value on the list to pick.. so after making a little trick on order within over clause to leave nulls out of the pick it finally worked. After googling a while I noticed that oracle has an option to do this, my disappointment was when I realized that sql did not have it.

    Anyway I think so far windowed functions are very useful, but they still need work on them to add better features.

    A. Mauricio Repetto
    ML Engineer

  • EDIT: This is an answer to the question about using LAST_VALUE(TotalDue) OVER(PARTITION BY CustomerID, ORDER BY CustomerID)

    There are a couple. First, the performance could be bad. ROWS will generally perform better than the default.

    Also, the ORDER BY is not specific to how you want the rows to sort. When you order by CustomerID, which is the first row? Which is the last row? The results will be dependent on how the results are returned. If you specify an different ORDER BY for the query, that can change the results of FIRST_VALUE and LAST_VALUE. In this case, it is working because it just happens to return the rows in the order you want. If it didn't then this wouldn't work.

    I am not sure why there is so much push back about using the frame.

    Aunt Kathi Data Platform MVP
    Author of Expert T-SQL Window Functions
    Simple-Talk Editor

  • amd.repetto (6/3/2016)


    The only thing I must disagree is with the first statement "Windowing functions are the best thing since sliced bread when it comes to T-SQL".. they only will be the best when Microsoft adds something to easily find the first or last value that is NOT NULL.

    I had to face a problem weeks ago when the first_value was returning me nulls when I knew that it shouldn't.. after think and think I realized that it was returnin null just for a simple reason.. null was the frist value on the list to pick.. so after making a little trick on order within over clause to leave nulls out of the pick it finally worked. After googling a while I noticed that oracle has an option to do this, my disappointment was when I realized that sql did not have it.

    Anyway I think so far windowed functions are very useful, but they still need work on them to add better features.

    I agree. I was hoping to see new functionality in SQL Server 2016.

    Aunt Kathi Data Platform MVP
    Author of Expert T-SQL Window Functions
    Simple-Talk Editor

  • samot-dwarf (4/20/2015)


    I'm lazy and use FIRST_VALUE with ORDER BY x DESC instead of this extra long LAST-VALUE syntax.

    Of course only if I don't need both (FIRST and LAST) because it would have to sort it twice otherwise.

    Be sure to watch out for performance issues if you are working with a large dataset.

    Aunt Kathi Data Platform MVP
    Author of Expert T-SQL Window Functions
    Simple-Talk Editor

  • bhupendra.baraiya (4/16/2015)


    we can achieve the same output without adding the rows Cluse

    SELECT

    CustomerID

    , SalesOrderID

    , CAST(OrderDate AS DATE) AS OrderDate

    , TotalDue

    , LAST_VALUE(TotalDue) OVER ( PARTITION BY CustomerID ORDER BY CustomerID ) AS TD

    FROM

    Sales.SalesOrderHeader

    ORDER BY

    CustomerID

    SELECT

    CustomerID

    , SalesOrderID

    , CAST(OrderDate AS DATE) AS OrderDate

    , TotalDue

    , First_VALUE(TotalDue) OVER ( PARTITION BY CustomerID ORDER BY CustomerID ) AS TD

    FROM

    Sales.SalesOrderHeader

    ORDER BY

    CustomerID

    Don't count on this to always work! The results will change depending on the order the rows are returned. Since CustomerID is not unique, the rows could be returned in a different order and then the results will be different.

    Aunt Kathi Data Platform MVP
    Author of Expert T-SQL Window Functions
    Simple-Talk Editor

  • ... Furthermore without the ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW it would be slow because with the default RANGE-parameter it has to spill out to TempDB.

    God is real, unless declared integer.

  • Kathi Kellenberger (6/3/2016)


    EDIT: This is an answer to the question about using LAST_VALUE(TotalDue) OVER(PARTITION BY CustomerID, ORDER BY CustomerID)

    Also, the ORDER BY is not specific to how you want the rows to sort. When you order by CustomerID, which is the first row? Which is the last row? The results will be dependent on how the results are returned. If you specify an different ORDER BY for the query, that can change the results of FIRST_VALUE and LAST_VALUE. In this case, it is working because it just happens to return the rows in the order you want.

    Thanks for the reply. I knew I was missing something and that's why I asked. Seems obvious now. 🙂

Viewing 13 posts - 16 through 27 (of 27 total)

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