{"id":539,"date":"2011-08-17T16:52:00","date_gmt":"2011-08-17T16:52:00","guid":{"rendered":"\/blogs\/bobb\/post\/Remember-LAG-function-in-SQL-Server-Denali-uses-rows.aspx"},"modified":"2013-01-04T00:02:35","modified_gmt":"2013-01-04T08:02:35","slug":"remember-lag-function-in-sql-server-denali-uses-rows","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/bobb\/remember-lag-function-in-sql-server-denali-uses-rows\/","title":{"rendered":"Remember: LAG function in SQL Server Denali uses rows"},"content":{"rendered":"<p>\nYou&#39;ve heard my rant before &quot;measure what you think you are measuring&quot;. If not, <a href=\"http:\/\/3.209.169.194\/blogs\/bobb\/what-exactly-does-percentile_cont-do-anyhow\/\" class=\"broken_link\">follow the link<\/a>. Here&#39;s an example using the LAG function, new in SQL Server Denali, to measure sales trends. We&#39;ll start with a view from AdventureWorksDW2008R2 (the double cast is for clarity).\n<\/p>\n<p>\nCREATE VIEW dbo.EmployeeSalesByMonth<br \/>\nAS<br \/>\nSELECT&nbsp; EmployeeKey as [Employee],<br \/>\n&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; DATEPART(yyyy, CAST(CAST(OrderDateKey as varchar(8)) as DATE)) AS [Year] ,<br \/>\n&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; DATEPART(mm, CAST(CAST(OrderDateKey as varchar(8)) as DATE)) AS [Month] ,<br \/>\n&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; SUM(SalesAmount) AS [EmployeeTotal]<br \/>\nFROM&nbsp;&nbsp;&nbsp; dbo.FactResellerSales<br \/>\nWHERE&nbsp;&nbsp;&nbsp; EmployeeKey IS NOT NULL<br \/>\nGROUP BY EmployeeKey,<br \/>\n&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; DATEPART(yyyy, CAST(CAST(OrderDateKey as varchar(8)) as DATE)),<br \/>\n&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; DATEPART(mm, CAST(CAST(OrderDateKey as varchar(8)) as DATE))<br \/>\nGO\n<\/p>\n<p>\nLet&#39;s use LAG to get sales for this month, previous month, and three months ago. I&#39;m choosing a specific employee, just leave the WHERE clause out to get everyone.\n<\/p>\n<p>\nSELECT&nbsp; Employee,<br \/>\n&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; [Year] ,<br \/>\n&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; [Month] ,<br \/>\n&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; EmployeeTotal AS SalesThisMonth,<br \/>\n&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; LAG(EmployeeTotal, 1, 0.00) OVER (PARTITION BY Employee ORDER BY [Year], [Month]) AS SalesLastMonth ,<br \/>\n&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; LAG(EmployeeTotal, 3, 0.00) OVER (PARTITION BY Employee&nbsp;ORDER BY [Year], [Month]) AS SalesThreeMonthsAgo<br \/>\nFROM dbo.EmployeeSalesByMonth<br \/>\nWHERE Employee = 272<br \/>\nORDER BY Employee, [Year], [Month];<br \/>\nGO\n<\/p>\n<p>\nThe LAG function works perfectly. Or does it&#8230;? Let&#39;s look at the first few rows.\n<\/p>\n<p>\n&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; This Month&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Last Month&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Three Months Ago<br \/>\n272&nbsp;&nbsp;&nbsp;&nbsp; 2005&nbsp;&nbsp;&nbsp; 8&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 20544.7015&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0.00&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;0.00<br \/>\n272&nbsp;&nbsp;&nbsp;&nbsp; 2005&nbsp;&nbsp;&nbsp; 9&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2039.994&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 20544.7015&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;0.00<br \/>\n272&nbsp;&nbsp;&nbsp;&nbsp; 2005&nbsp;&nbsp;&nbsp; 11&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 6341.551&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2039.994&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0.00<br \/>\n272&nbsp;&nbsp;&nbsp;&nbsp; 2006&nbsp;&nbsp;&nbsp; 2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 61206.4782&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 6341.551&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 20544.7015<br \/>\n272&nbsp;&nbsp;&nbsp;&nbsp; 2006&nbsp;&nbsp;&nbsp; 3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 18307.746&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 61206.4782&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;2039.994\n<\/p>\n<p>\nAlthough this sounded like a good idea, and the function is working as advertised, the answer isn&#39;t what&nbsp;we want. Look at the 2005-11 line, for example. Last month was 2005-10, but there was no sales that month, so we get 2005-9 sales. Three months ago (2005-08) there were sales, but we get zero. What gives?\n<\/p>\n<p>\nWe&#39;re only&nbsp;producing rows for&nbsp;months where there were sales for employee 272. Probably not what you&#39;d want. Although we think we&#39;re counting months with this LAG function, we&#39;re actually counting ROWS. SQL Server BOL implementation of LAG states that the offset (first parameter) is &quot;The number of rows back from the current row from which to obtain a value.&quot;\n<\/p>\n<p>\nSo, can we fix things to get &quot;reasonable&quot; values? Because LAG uses rows, we&#39;d need to come up with some blank (zero total) rows for months&nbsp;where there are no sales. Some databases have a special data densification syntax, like &quot;partition-by joins&quot; to fill in the gaps.&nbsp;Using Itzik Ben-Gan&#39;s dbo.GetNums TVF, we can make a table of allMonths and all Employees between a date range. (Note: this code is a quick hack and pretty fragile, but we will end up with the right answer. It also assumes you have dbo.GetNums in tempdb, see the <a href=\"http:\/\/www.sqlmag.com\/article\/sql-server\/virtual-auxiliary-table-of-numbers\">SQLMag article<\/a>&nbsp;for that function).\n<\/p>\n<p>\nDECLARE <br \/>\n&nbsp;@startdt AS DATE = &#39;20050701&#39;,&nbsp; &#8212; first date we care about<br \/>\n&nbsp;@enddt&nbsp;&nbsp; AS DATE = &#39;20080731&#39;&nbsp;&nbsp; &#8212; last date we care about<br \/>\n&nbsp;<br \/>\nSELECT DATEADD(month, n-1, @startdt) AS dt, EmployeeKey AS Employee <br \/>\nINTO #allMonths<br \/>\nFROM tempdb.dbo.GetNums(DATEDIFF(month, @startdt, @enddt) + 1) AS Nums<br \/>\nCROSS JOIN <br \/>\n(<br \/>\nSELECT DISTINCT EmployeeKey <br \/>\nFROM dbo.FactResellerSales <br \/>\n) AS A;&nbsp; <br \/>\nGO\n<\/p>\n<p>\nWith this table in hand, we can use an OUTER APPLY to &quot;add in&quot; the months with no sales, and our sales trend query becomes:\n<\/p>\n<p>\nWITH SalesByMonth AS<br \/>\n(<br \/>\nSELECT Employee,<br \/>\n&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; DATEPART(yyyy,dt) as Year,<br \/>\n&nbsp;&nbsp;&nbsp; DATEPART(MONTH,dt) as Month,<br \/>\n&nbsp;&nbsp;&nbsp; ISNULL(EmployeeTotal, 0) as EmployeeTotal<br \/>\nFROM #allMonths m<br \/>\nOUTER APPLY (<br \/>\nSELECT&nbsp; EmployeeTotal<br \/>\nFROM&nbsp;&nbsp;&nbsp; EmployeeSalesByMonth e <br \/>\nWHERE m.Employee = e.Employee&nbsp;&nbsp; <br \/>\n&nbsp;AND&nbsp;&nbsp;&nbsp; DATEPART(yyyy,dt) = [Year]<br \/>\n&nbsp;AND&nbsp;&nbsp;&nbsp; DATEPART(mm,dt) = [Month]<br \/>\n) AS t<br \/>\n)<br \/>\nSELECT&nbsp; Employee,<br \/>\n&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; [Year] ,<br \/>\n&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; [Month] ,<br \/>\n&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; EmployeeTotal AS SalesThisMonth,<br \/>\n&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; LAG(EmployeeTotal, 1, 0.00) OVER (PARTITION BY Employee&nbsp;ORDER BY [Year], [Month]) AS SalesLastMonth ,<br \/>\n&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; LAG(EmployeeTotal, 3, 0.00) OVER (PARTITION BY Employee&nbsp;ORDER BY [Year], [Month]) AS SalesThreeMonthsAgo<br \/>\nFROM SalesByMonth<br \/>\nWHERE Employee = 272<br \/>\nORDER BY Employee,<br \/>\n&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; [Year],<br \/>\n&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; [Month];<br \/>\nGO\n<\/p>\n<p>\nA few corresponding rows&#8230;the sales figures now agree with the column headings\n<\/p>\n<p>\n&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; This Month&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Last Month&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Three Months Ago<br \/>\n272&nbsp;&nbsp;&nbsp;&nbsp; 2005&nbsp;&nbsp;&nbsp; 8&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 20544.7015&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0.00&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;0.00<br \/>\n272&nbsp;&nbsp;&nbsp;&nbsp; 2005&nbsp;&nbsp;&nbsp; 9&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2039.994&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 20544.7015&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;0.00<br \/>\n272&nbsp;&nbsp;&nbsp;&nbsp; 2005&nbsp;&nbsp;&nbsp; 10&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;0.00&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;2039.994&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;0.00<br \/>\n272&nbsp;&nbsp;&nbsp;&nbsp; 2005&nbsp;&nbsp;&nbsp; 11&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;6341.551&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;0.00&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;20544.7015<br \/>\n272&nbsp;&nbsp;&nbsp;&nbsp; 2005&nbsp;&nbsp;&nbsp; 12&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;0.00&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;6341.551&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2039.994<br \/>\n272&nbsp;&nbsp;&nbsp;&nbsp; 2006&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0.00&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;0.00&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;0.00<br \/>\n272&nbsp;&nbsp;&nbsp;&nbsp; 2006&nbsp;&nbsp;&nbsp; 2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 61206.4782&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;0.00&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 6341.551<br \/>\n272&nbsp;&nbsp;&nbsp;&nbsp; 2006&nbsp;&nbsp;&nbsp; 3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 18307.746&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 61206.4782&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0.00\n<\/p>\n<p>\nSo remember&#8230;although the default window for the OVER clause really is RANGE UNBOUNDED PRECEDING AND CURRENT ROW, the LAG function only counts ROWs, not RANGEs.\n<\/p>\n<p>\n@bobbeauch&nbsp;&nbsp;<\/p>\n","protected":false},"excerpt":{"rendered":"<p>You&#39;ve heard my rant before &quot;measure what you think you are measuring&quot;. If not, follow the link. Here&#39;s an example using the LAG function, new in SQL Server Denali, to measure sales trends. We&#39;ll start with a view from AdventureWorksDW2008R2 (the double cast is for clarity). CREATE VIEW dbo.EmployeeSalesByMonth AS SELECT&nbsp; EmployeeKey as [Employee], &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; [&hellip;]<\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[31,40],"tags":[],"class_list":["post-539","post","type-post","status-publish","format-standard","hentry","category-sql-server-2012","category-transact-sql"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v21.9.1 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>Remember: LAG function in SQL Server Denali uses rows - Bob Beauchemin<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.sqlskills.com\/blogs\/bobb\/remember-lag-function-in-sql-server-denali-uses-rows\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Remember: LAG function in SQL Server Denali uses rows - Bob Beauchemin\" \/>\n<meta property=\"og:description\" content=\"You&#039;ve heard my rant before &quot;measure what you think you are measuring&quot;. If not, follow the link. Here&#039;s an example using the LAG function, new in SQL Server Denali, to measure sales trends. We&#039;ll start with a view from AdventureWorksDW2008R2 (the double cast is for clarity). CREATE VIEW dbo.EmployeeSalesByMonth AS SELECT&nbsp; EmployeeKey as [Employee], &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/bobb\/remember-lag-function-in-sql-server-denali-uses-rows\/\" \/>\n<meta property=\"og:site_name\" content=\"Bob Beauchemin\" \/>\n<meta property=\"article:published_time\" content=\"2011-08-17T16:52:00+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2013-01-04T08:02:35+00:00\" \/>\n<meta name=\"author\" content=\"Bob Beauchemin\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Bob Beauchemin\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"7 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/remember-lag-function-in-sql-server-denali-uses-rows\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/remember-lag-function-in-sql-server-denali-uses-rows\/\",\"name\":\"Remember: LAG function in SQL Server Denali uses rows - Bob Beauchemin\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/#website\"},\"datePublished\":\"2011-08-17T16:52:00+00:00\",\"dateModified\":\"2013-01-04T08:02:35+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/#\/schema\/person\/62bfa986c5b5d28fcffd8b4fc409c73e\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/remember-lag-function-in-sql-server-denali-uses-rows\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/bobb\/remember-lag-function-in-sql-server-denali-uses-rows\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/remember-lag-function-in-sql-server-denali-uses-rows\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"SQL Server 2012\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/category\/sql-server-2012\/\"},{\"@type\":\"ListItem\",\"position\":3,\"name\":\"Remember: LAG function in SQL Server Denali uses rows\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/#website\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/\",\"name\":\"Bob Beauchemin\",\"description\":\"SQL Server Blog\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/#\/schema\/person\/62bfa986c5b5d28fcffd8b4fc409c73e\",\"name\":\"Bob Beauchemin\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/6f80e6cc667410857fa6a21931dc528b8092f4d112bf7a8ff7c267674d44ee37?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/6f80e6cc667410857fa6a21931dc528b8092f4d112bf7a8ff7c267674d44ee37?s=96&d=mm&r=g\",\"caption\":\"Bob Beauchemin\"},\"sameAs\":[\"http:\/www.sqlskills.com\/blogs\/bobb\/\"],\"url\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/author\/bobb\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Remember: LAG function in SQL Server Denali uses rows - Bob Beauchemin","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.sqlskills.com\/blogs\/bobb\/remember-lag-function-in-sql-server-denali-uses-rows\/","og_locale":"en_US","og_type":"article","og_title":"Remember: LAG function in SQL Server Denali uses rows - Bob Beauchemin","og_description":"You&#39;ve heard my rant before &quot;measure what you think you are measuring&quot;. If not, follow the link. Here&#39;s an example using the LAG function, new in SQL Server Denali, to measure sales trends. We&#39;ll start with a view from AdventureWorksDW2008R2 (the double cast is for clarity). CREATE VIEW dbo.EmployeeSalesByMonth AS SELECT&nbsp; EmployeeKey as [Employee], &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; [&hellip;]","og_url":"https:\/\/www.sqlskills.com\/blogs\/bobb\/remember-lag-function-in-sql-server-denali-uses-rows\/","og_site_name":"Bob Beauchemin","article_published_time":"2011-08-17T16:52:00+00:00","article_modified_time":"2013-01-04T08:02:35+00:00","author":"Bob Beauchemin","twitter_misc":{"Written by":"Bob Beauchemin","Est. reading time":"7 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/remember-lag-function-in-sql-server-denali-uses-rows\/","url":"https:\/\/www.sqlskills.com\/blogs\/bobb\/remember-lag-function-in-sql-server-denali-uses-rows\/","name":"Remember: LAG function in SQL Server Denali uses rows - Bob Beauchemin","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/#website"},"datePublished":"2011-08-17T16:52:00+00:00","dateModified":"2013-01-04T08:02:35+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/#\/schema\/person\/62bfa986c5b5d28fcffd8b4fc409c73e"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/remember-lag-function-in-sql-server-denali-uses-rows\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/bobb\/remember-lag-function-in-sql-server-denali-uses-rows\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/remember-lag-function-in-sql-server-denali-uses-rows\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/bobb\/"},{"@type":"ListItem","position":2,"name":"SQL Server 2012","item":"https:\/\/www.sqlskills.com\/blogs\/bobb\/category\/sql-server-2012\/"},{"@type":"ListItem","position":3,"name":"Remember: LAG function in SQL Server Denali uses rows"}]},{"@type":"WebSite","@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/#website","url":"https:\/\/www.sqlskills.com\/blogs\/bobb\/","name":"Bob Beauchemin","description":"SQL Server Blog","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.sqlskills.com\/blogs\/bobb\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/#\/schema\/person\/62bfa986c5b5d28fcffd8b4fc409c73e","name":"Bob Beauchemin","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/6f80e6cc667410857fa6a21931dc528b8092f4d112bf7a8ff7c267674d44ee37?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/6f80e6cc667410857fa6a21931dc528b8092f4d112bf7a8ff7c267674d44ee37?s=96&d=mm&r=g","caption":"Bob Beauchemin"},"sameAs":["http:\/www.sqlskills.com\/blogs\/bobb\/"],"url":"https:\/\/www.sqlskills.com\/blogs\/bobb\/author\/bobb\/"}]}},"_links":{"self":[{"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/posts\/539","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/users\/7"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/comments?post=539"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/posts\/539\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/media?parent=539"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/categories?post=539"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/tags?post=539"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}