Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 First_Value/Last_Value Issue. Why?

Author  Topic 

DavidChel
Constraint Violating Yak Guru

474 Posts

Posted - 2013-04-09 : 15:41:54
To simplify this question, I've created some sample code. Please consider the following which creates a temp table and seeds it with data.

IF  EXISTS (Select 1 from tempdb.sys.objects o where o.object_id = object_id('tempdb..#DateTest'))
BEGIN
DROP TABLE #DateTest
END

BEGIN
CREATE TABLE #DateTest(
[GeneralID] [int] NOT NULL,
[PeriodNumber] [smallint] NULL,
[FiscalYearStartDate] [date] NULL,
[MonthEndDate] [date] NULL,
[FiscalYear] [smallint] NULL
) ON [PRIMARY]
END
GO
INSERT INTO #DateTest ([FiscalYearStartDate], [MonthEndDate], [GeneralID], [PeriodNumber], [FiscalYear])
VALUES ('2012-12-25', '2013-01-25', 1, 1, 2013)
, ('2012-12-25', '2013-02-22', 1, 2, 2013)
, ('2012-12-25', '2013-03-25', 1, 3, 2013)
, ('2012-12-25', '2013-04-24', 1, 4, 2013)
, ('2012-12-25', '2013-05-24', 1, 5, 2013)
, ('2012-12-25', '2013-06-24', 1, 6, 2013)
, ('2012-12-25', '2013-07-25', 1, 7, 2013)
, ('2012-12-25', '2013-08-26', 1, 8, 2013)
, ('2012-12-25', '2013-09-24', 1, 9, 2013)
, ('2012-12-25', '2013-10-25', 1, 10, 2013)
, ('2012-12-25', '2013-11-21', 1, 11, 2013)
, ('2012-12-25', '2013-12-24', 1, 12, 2013)
, ('2013-01-01', '2013-01-25', 2, 1, 2013)
, ('2013-01-01', '2013-02-22', 2, 2, 2013)
, ('2013-01-01', '2013-03-25', 2, 3, 2013)
, ('2013-01-01', '2013-04-24', 2, 4, 2013)
, ('2013-01-01', '2013-05-24', 2, 5, 2013)
, ('2013-01-01', '2013-06-24', 2, 6, 2013)
, ('2013-01-01', '2013-07-25', 2, 7, 2013)
, ('2013-01-01', '2013-08-26', 2, 8, 2013)
, ('2013-01-01', '2013-09-24', 2, 9, 2013)
, ('2013-01-01', '2013-10-25', 2, 10, 2013)
, ('2013-01-01', '2013-11-21', 2, 11, 2013)
, ('2013-01-01', '2013-12-31', 2, 12, 2013)



I'm working in SQL Server 2012 and wanted to use the First_Value and Last_Value functionality. Consider the following query:

SELECT GeneralID
,PeriodNumber
,FiscalYearStartDate
,MonthEndDate
,LAST_VALUE (MonthEndDate) OVER (PARTITION BY GeneralID, FiscalYear
ORDER BY MonthEndDate ASC) LastValAscYearEndDate --Lists Each MonthEndDate
,FIRST_VALUE(MonthEndDate) OVER (PARTITION BY GeneralID, FiscalYear
ORDER BY MonthEndDate DESC) FirstValDescYearEndDate --Returns Last Month Ending Date for entire year.
, FiscalYear
FROM #DateTest
ORDER BY GeneralID, FiscalYearStartDate, PeriodNumber


It seems to me that both of those lines should have the same result, but they don't. The First_Value with Descending argument returns what I want, but the Last_Value with Ascending simply returns the value for MonthEndDate per record.

What am I missing here?

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2013-04-09 : 16:28:32
It has to do with the optional Rows/Range specification:

From Over in Books Online:
quote:

If ORDER BY is not specified entire partition is used for a window frame. This applies only to functions that do not require ORDER BY clause. If ROWS/RANGE is not specified but ORDER BY is specified, RANGE UNBOUNDED PRECEDING AND CURRENT ROW is used as default for window frame. This applies only to functions that have can accept optional ROWS/RANGE specification. For example, ranking functions cannot accept ROWS/RANGE, therefore this window frame is not applied even though ORDER BY is present and ROWS/RANGE is not.


If you add this to your OVER clause you'll get the results you expected:
ROWS between unbounded preceding and unbounded following

EDIT:
Be thoughtful between choosing ROWS vs RANGE:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=182542


Be One with the Optimizer
TG
Go to Top of Page
   

- Advertisement -