SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 First_Value/Last_Value Issue. Why?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

DavidChel
Constraint Violating Yak Guru

USA
474 Posts

Posted - 04/09/2013 :  15:41:54  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
6062 Posts

Posted - 04/09/2013 :  16:28:32  Show Profile  Reply with Quote
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

Edited by - TG on 04/09/2013 16:34:42
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.14 seconds. Powered By: Snitz Forums 2000