{"id":538,"date":"2011-08-18T15:12:00","date_gmt":"2011-08-18T15:12:00","guid":{"rendered":"\/blogs\/bobb\/post\/Window-clause-range-specification-and-missing-values.aspx"},"modified":"2013-01-04T00:02:35","modified_gmt":"2013-01-04T08:02:35","slug":"window-clause-range-specification-and-missing-values","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/bobb\/window-clause-range-specification-and-missing-values\/","title":{"rendered":"Window clause, range specification, and missing values"},"content":{"rendered":"<p>\nIn the <a href=\"http:\/\/3.209.169.194\/blogs\/bobb\/remember-lag-function-in-sql-server-denali-uses-rows\/\" class=\"broken_link\">previous post<\/a>, I insinuated that allowing LAG\/LEAD to be sensitive to value RANGEs might help with series of data with missing values. It won&#39;t do that unless there&#39;s some logic that allows the offset that LAG\/LEAD uses to be calculated on a row-by-row basis (i.e. LAG\/LEAD uses an expression based on the window values). That&#39;s a little too much to expect; notice I&#39;ve&nbsp;updated the original post to back off a bit&nbsp;on that one. I&#39;ll need my densified data for that.\n<\/p>\n<p>\nHowever, a common scenario that would be helped by an expansion of use of RANGE in the window specification is rolling totals\/rolling balances. Here&#39;s an example.\n<\/p>\n<p>\nSuppose I wanted to calculate a 3-month rolling average (or rolling total) of sales on a per-employee basis. Looking at an employee who doesn&#39;t make at least one sale every month, I get a skewed total\/average. The totals don&#39;t reflect the last three <strong>calendar months<\/strong>, but instead they reflect the last three months <strong>in which the employee had a nonzero sales total<\/strong>. Skews the figures upward a little&#8230;.\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; COUNT(*) OVER (PARTITION BY Employee ORDER BY [Year], [Month] ROWS 2 PRECEDING) AS NumberOfMonths,<br \/>\n&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; SUM(EmployeeTotal) OVER (PARTITION BY Employee ORDER BY [Year], [Month] ROWS 2 PRECEDING) AS ThreeMonthTotal,<br \/>\n&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; AVG(EmployeeTotal) OVER (PARTITION BY Employee ORDER BY [Year], [Month] ROWS 2 PRECEDING) AS ThreeMonthAverage<br \/>\nFROM EmployeeSalesByMonth<br \/>\nWHERE Employee = 272<br \/>\nORDER BY Employee, [Year], [Month];\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; SalesThisMonth Number&nbsp;&nbsp; ThreeMonthTotal ThreeMonthAverage<br \/>\n272&nbsp;&nbsp;&nbsp;&nbsp; 2005&nbsp;&nbsp;&nbsp; 8&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 20544.7015&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 20544.7015&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 20544.7015<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; 2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 22584.6955&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 11292.3477&nbsp;&nbsp; <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;3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 28926.2465&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 9642.0821<br \/>\n272&nbsp;&nbsp;&nbsp;&nbsp; 2006&nbsp;&nbsp;&nbsp; 2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 61206.4782&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 69588.0232&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 23196.0077<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;&nbsp; 3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 85855.7752&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 28618.5917<br \/>\n272&nbsp;&nbsp;&nbsp;&nbsp; 2006&nbsp;&nbsp;&nbsp; 4&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 33406.7043&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 112920.9285&nbsp;&nbsp;&nbsp;&nbsp; 37640.3095\n<\/p>\n<p>\nSuppose I had a way to specify a time interval and use RANGE instead of ROWS. Something like: &quot;RANGE BETWEEN &#39;2 MONTHS&#39; PRECEEDING AND CURRENT ROW&quot; where &#39;2 MONTHS&#39; represents a time-interval data type. Then I&#39;d get the &quot;non-skewed&quot; answer. Currently SQL Server Denali doesn&#39;t even allow that form the RANGE specification (RANGE BETWEEN &lt;unsigned value specification&gt; PRECEDING) with <strong>any<\/strong> data type currently. Instead, I can use the densified data (see <a href=\"http:\/\/3.209.169.194\/blogs\/bobb\/remember-lag-function-in-sql-server-denali-uses-rows\/\" class=\"broken_link\">previous post<\/a>) with the rows window and get that &quot;non-skewed&quot; answer.\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; COUNT(*) OVER (PARTITION BY Employee ORDER BY [Year], [Month] ROWS 2 PRECEDING) AS NumberOfMonths,<br \/>\n&nbsp;SUM(EmployeeTotal) OVER (PARTITION BY Employee ORDER BY [Year], [Month] ROWS 2 PRECEDING) AS ThreeMonthTotal,<br \/>\n&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; AVG(EmployeeTotal) OVER (PARTITION BY Employee ORDER BY [Year], [Month] ROWS 2 PRECEDING) AS ThreeMonthAverage<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>\n&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; SalesThisMonth Number&nbsp;&nbsp; ThreeMonthTotal ThreeMonthAverage&nbsp;&nbsp; <br \/>\n272&nbsp;&nbsp;&nbsp;&nbsp; 2005&nbsp;&nbsp;&nbsp; 7&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0.00&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&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<br \/>\n272&nbsp;&nbsp;&nbsp;&nbsp; 2005&nbsp;&nbsp;&nbsp; 8&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 20544.7015&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 20544.7015&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 10272.3507<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;&nbsp; 3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 22584.6955&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 7528.2318<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;3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 22584.6955&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 7528.2318<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;3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 8381.545&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2793.8483\n<\/p>\n<p>\nNotice, however, that there&#39;s another subtle change in the answer. In choosing to start the time series exactly on 2005-07 (rather than the month when each employee had his\/her first sale), the averages for the first couple of months for this employee are a little low. We can account for this, if we want, by basing the #allmonths table on each employee&#39;s hire date, for example. Yet another instance of &quot;always pay attention to what you&#39;re actually measuring&quot;.\n<\/p>\n<p>\n@bobbeauch<br \/>\n&nbsp;<\/p>\n","protected":false},"excerpt":{"rendered":"<p>In the previous post, I insinuated that allowing LAG\/LEAD to be sensitive to value RANGEs might help with series of data with missing values. It won&#39;t do that unless there&#39;s some logic that allows the offset that LAG\/LEAD uses to be calculated on a row-by-row basis (i.e. LAG\/LEAD uses an expression based on the window [&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-538","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>Window clause, range specification, and missing values - 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\/window-clause-range-specification-and-missing-values\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Window clause, range specification, and missing values - Bob Beauchemin\" \/>\n<meta property=\"og:description\" content=\"In the previous post, I insinuated that allowing LAG\/LEAD to be sensitive to value RANGEs might help with series of data with missing values. It won&#039;t do that unless there&#039;s some logic that allows the offset that LAG\/LEAD uses to be calculated on a row-by-row basis (i.e. LAG\/LEAD uses an expression based on the window [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/bobb\/window-clause-range-specification-and-missing-values\/\" \/>\n<meta property=\"og:site_name\" content=\"Bob Beauchemin\" \/>\n<meta property=\"article:published_time\" content=\"2011-08-18T15:12: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=\"5 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\/window-clause-range-specification-and-missing-values\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/window-clause-range-specification-and-missing-values\/\",\"name\":\"Window clause, range specification, and missing values - Bob Beauchemin\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/#website\"},\"datePublished\":\"2011-08-18T15:12: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\/window-clause-range-specification-and-missing-values\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/bobb\/window-clause-range-specification-and-missing-values\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/window-clause-range-specification-and-missing-values\/#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\":\"Window clause, range specification, and missing values\"}]},{\"@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":"Window clause, range specification, and missing values - 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\/window-clause-range-specification-and-missing-values\/","og_locale":"en_US","og_type":"article","og_title":"Window clause, range specification, and missing values - Bob Beauchemin","og_description":"In the previous post, I insinuated that allowing LAG\/LEAD to be sensitive to value RANGEs might help with series of data with missing values. It won&#39;t do that unless there&#39;s some logic that allows the offset that LAG\/LEAD uses to be calculated on a row-by-row basis (i.e. LAG\/LEAD uses an expression based on the window [&hellip;]","og_url":"https:\/\/www.sqlskills.com\/blogs\/bobb\/window-clause-range-specification-and-missing-values\/","og_site_name":"Bob Beauchemin","article_published_time":"2011-08-18T15:12: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":"5 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/window-clause-range-specification-and-missing-values\/","url":"https:\/\/www.sqlskills.com\/blogs\/bobb\/window-clause-range-specification-and-missing-values\/","name":"Window clause, range specification, and missing values - Bob Beauchemin","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/#website"},"datePublished":"2011-08-18T15:12: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\/window-clause-range-specification-and-missing-values\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/bobb\/window-clause-range-specification-and-missing-values\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/window-clause-range-specification-and-missing-values\/#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":"Window clause, range specification, and missing values"}]},{"@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\/538","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=538"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/posts\/538\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/media?parent=538"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/categories?post=538"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/tags?post=538"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}