April 13, 2015 at 9:56 pm
Comments posted to this topic are about the item Using LAST_VALUE
Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor
April 13, 2015 at 11:26 pm
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
(
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.
April 14, 2015 at 2:00 am
Thx for the article. Very interesting.
April 14, 2015 at 3:20 am
Hi,
You Can achieve the same Result by below statement for FIRST_VALUE and LAST_VALUE
------------------------------------------------FIRST_VALUE-------------------------------------------------------
SELECT
CustomerID
, SalesOrderID
, CAST(OrderDate AS DATE) AS OrderDate
, TotalDue
, FIRST_VALUE(TotalDue) OVER ( PARTITION BY CustomerID ORDER BY SalesOrderID ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) AS TD
FROM
Sales.SalesOrderHeader
ORDER BY
CustomerID
, SalesOrderID;
------------------------------------------------LAST_VALUE-------------------------------------------------------
SELECT
CustomerID
, SalesOrderID
, CAST(OrderDate AS DATE) AS OrderDate
, TotalDue
, LAST_VALUE(TotalDue) OVER ( PARTITION BY CustomerID ORDER BY SalesOrderID ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) AS TD
FROM
Sales.SalesOrderHeader
ORDER BY
CustomerID
, SalesOrderID;
April 14, 2015 at 4:01 am
Very interesting Post.
Basically, the "expected" behaviour for LAST_VALUE is achievable without ROWS clause, just specifying DESC in the ORDER BY clause of the FIRST_VALUE aggregate.
SELECT
CustomerID
, SalesOrderID
, CAST(OrderDate AS DATE) AS OrderDate
, TotalDue
, FIRST_VALUE(TotalDue) OVER ( PARTITION BY CustomerID ORDER BY SalesOrderID DESC) AS TD
FROM
Sales.SalesOrderHeader
ORDER BY
CustomerID
, SalesOrderID;
Anyone has considerations about what would be better between FIRST_VALUE with DESC or LAST_VALUE with ROWS? I would be interested in any experts consideration about it.
Thanks for the article.
Alberto Chiesa
April 14, 2015 at 4:42 am
Hi chiesa,
Your Suggestion will also work... But
As per Performance Tuning matter,
in your Solution.
First of all the Data will be Sorted in DESC Order and then It will Apply FIRST_VALUE from ORDERED Value..
So here data is very less.. But in ACTUAL world ther may be thousand and Lacks of records... So at that time your suggestion take more time to achieve as compare to
LAST_VALUE(Col3) OVER (Partition by Col1 order by anyCol2 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING )
Or simple
LAST_VALUE(Col3) OVER (Partition by Col1 order by anyCol2 ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING )
------------
Hope you will get an idea..
Share your Observation
April 14, 2015 at 7:55 am
I believe the following lines have been overlooked...
"...However, if you’re after the last row in the partition, using
the LAST_VALUE function with the default frame won’t give you what you want because
the last row in the default frame is the current row. So with the LAST_VALUE, you need to
be explicit about the window frame in order to get what you are after. And if you need
an element from the last row in the partition, the second delimiter in the frame should be
UNBOUNDED FOLLOWING."
Chapter 5 Grouping and Windowing - Pag. 180
Exam 70-641 Querying SQL Server 2012, Itzik Ben-Gan, Dejan Sarka, Ron Talamage
April 14, 2015 at 8:03 am
Very good article. The behavior of LAST_VALUE() would seem unintuitive without considering the frame. Thanks for highlighting this potential "gotcha".
I tried to apply the ROWS/RANGE clause to other windowing functions, but it is not implemented across the board. What is the rule for when the frame is available?
April 14, 2015 at 6:40 pm
ROWS and RANGE are only supported with Window Aggregates (for example, running totals) and FIRST_VALUE and LAST_VALUE.
Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor
April 14, 2015 at 11:27 pm
Any ideas guys?
April 16, 2015 at 12:00 am
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
April 16, 2015 at 12:04 am
*bump* 🙂
preacha (4/13/2015)
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
(
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.
April 16, 2015 at 12:08 am
This is Cool... 🙂
April 16, 2015 at 1:32 am
Preacha, probably you could spend some time giving more information about the source data and the desired result.
Telling "here is a query that doesn't work as I want it to", is pretty much useless, IMO.
Even if someone is wanting to help.
Bye.
April 16, 2015 at 10:33 am
LAG and LEAD functions support window partition, try adding the "PARTITION BY" to your LAG and LEAD functions to get the correct data,
i.e.
LAG (AU.REPORTDATE) OVER (PARTITION BY AU.TICKETID, ORDER BY AU.TICKETID, AU.EAUDITTIMESTAMP) as PREV_RPTDATE,
or
LAG (AU.REPORTDATE) OVER (PARTITION BY AU.REPORTDATE, ORDER BY AU.TICKETID, AU.EAUDITTIMESTAMP) as PREV_RPTDATE,
When you restrict those functions to the correct partition of data, the CTE will return the correct set...
More than this, it is difficult to help due to the above mentioned lack of info provided...
Viewing 15 posts - 1 through 15 (of 27 total)
You must be logged in to reply to this topic. Login to reply