{"id":6560,"date":"2022-09-04T00:07:29","date_gmt":"2022-09-03T18:37:29","guid":{"rendered":"https:\/\/jassweb.com\/solved\/solved-why-the-following-sql-server-query-returns-12-months-data-and-not-14-months-data\/"},"modified":"2022-09-04T00:07:29","modified_gmt":"2022-09-03T18:37:29","slug":"solved-why-the-following-sql-server-query-returns-12-months-data-and-not-14-months-data","status":"publish","type":"post","link":"https:\/\/jassweb.com\/solved\/solved-why-the-following-sql-server-query-returns-12-months-data-and-not-14-months-data\/","title":{"rendered":"[Solved] Why the following SQL Server query returns 12 months data and not 14 months data"},"content":{"rendered":"<p> [ad_1]<br \/>\n<\/p>\n<div id=\"answer-32082104\" class=\"answer js-answer accepted-answer js-accepted-answer\" data-answerid=\"32082104\" data-parentid=\"32038826\" data-score=\"1\" data-position-on-page=\"1\" data-highest-scored=\"1\" data-question-has-accepted-highest-score=\"1\" itemprop=\"acceptedAnswer\" itemscope itemtype=\"https:\/\/schema.org\/Answer\">\n<div class=\"post-layout\">\n<div class=\"votecell post-layout--left\"><\/div>\n<div class=\"answercell post-layout--right\">\n<div class=\"s-prose js-post-body\" itemprop=\"text\">\n<p>You need to group by both month and year parts of ad.xDate. There are only twelve months of the year and you should be seeing the earliest two (14 &#8211; 2) month of your results with totals that are too large because they actually represent a combination of two calendar months.<\/p>\n<p>It worked in your original version because there are more than 14 days in any month. If you tried to extend that old query beyond 31 days (or 28, 29, 30 for some months) then you find the same problem all over again.<\/p>\n<pre><code>...\nSELECT\n    'playing' As activity,\n    min(ad.xDate) As xDate, \n    Isnull(sum(t.TimePerDay), 0) As TimePerDay\nFROM AllDates As ad Left Outer Join @test As t On ad.xDate = t.date\nGROUP BY Year(ad.xDate), Month(ad.xDate) \/* &lt;--- change here *\/\nORDER BY xDate\n<\/code><\/pre>\n<\/p><\/div>\n<div class=\"mt24\"><\/div>\n<\/div>\n<p>            <span class=\"d-none\" itemprop=\"commentCount\">2<\/span> <\/p><\/div>\n<\/div>\n<p>[ad_2]<\/p>\n<p>solved Why the following SQL Server query returns 12 months data and not 14 months data <\/p>\n","protected":false},"excerpt":{"rendered":"<p>[ad_1] You need to group by both month and year parts of ad.xDate. There are only twelve months of the year and you should be seeing the earliest two (14 &#8211; 2) month of your results with totals that are too large because they actually represent a combination of two calendar months. It worked in &#8230; <a title=\"[Solved] Why the following SQL Server query returns 12 months data and not 14 months data\" class=\"read-more\" href=\"https:\/\/jassweb.com\/solved\/solved-why-the-following-sql-server-query-returns-12-months-data-and-not-14-months-data\/\" aria-label=\"More on [Solved] Why the following SQL Server query returns 12 months data and not 14 months data\">Read more<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[320],"tags":[341,500,501],"class_list":["post-6560","post","type-post","status-publish","format-standard","hentry","category-solved","tag-sql","tag-sql-server","tag-sql-server-2008"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v26.5 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>[Solved] Why the following SQL Server query returns 12 months data and not 14 months data - JassWeb<\/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:\/\/jassweb.com\/solved\/solved-why-the-following-sql-server-query-returns-12-months-data-and-not-14-months-data\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"[Solved] Why the following SQL Server query returns 12 months data and not 14 months data - JassWeb\" \/>\n<meta property=\"og:description\" content=\"[ad_1] You need to group by both month and year parts of ad.xDate. There are only twelve months of the year and you should be seeing the earliest two (14 &#8211; 2) month of your results with totals that are too large because they actually represent a combination of two calendar months. It worked in ... Read more\" \/>\n<meta property=\"og:url\" content=\"https:\/\/jassweb.com\/solved\/solved-why-the-following-sql-server-query-returns-12-months-data-and-not-14-months-data\/\" \/>\n<meta property=\"og:site_name\" content=\"JassWeb\" \/>\n<meta property=\"article:published_time\" content=\"2022-09-03T18:37:29+00:00\" \/>\n<meta name=\"author\" content=\"Kirat\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Kirat\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"1 minute\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/jassweb.com\/solved\/solved-why-the-following-sql-server-query-returns-12-months-data-and-not-14-months-data\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/jassweb.com\/solved\/solved-why-the-following-sql-server-query-returns-12-months-data-and-not-14-months-data\/\"},\"author\":{\"name\":\"Kirat\",\"@id\":\"https:\/\/jassweb.com\/solved\/#\/schema\/person\/65c9c7b7958150c0dc8371fa35dd7c31\"},\"headline\":\"[Solved] Why the following SQL Server query returns 12 months data and not 14 months data\",\"datePublished\":\"2022-09-03T18:37:29+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/jassweb.com\/solved\/solved-why-the-following-sql-server-query-returns-12-months-data-and-not-14-months-data\/\"},\"wordCount\":118,\"publisher\":{\"@id\":\"https:\/\/jassweb.com\/solved\/#organization\"},\"keywords\":[\"sql\",\"sql-server\",\"sql-server-2008\"],\"articleSection\":[\"Solved\"],\"inLanguage\":\"en-US\"},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/jassweb.com\/solved\/solved-why-the-following-sql-server-query-returns-12-months-data-and-not-14-months-data\/\",\"url\":\"https:\/\/jassweb.com\/solved\/solved-why-the-following-sql-server-query-returns-12-months-data-and-not-14-months-data\/\",\"name\":\"[Solved] Why the following SQL Server query returns 12 months data and not 14 months data - JassWeb\",\"isPartOf\":{\"@id\":\"https:\/\/jassweb.com\/solved\/#website\"},\"datePublished\":\"2022-09-03T18:37:29+00:00\",\"breadcrumb\":{\"@id\":\"https:\/\/jassweb.com\/solved\/solved-why-the-following-sql-server-query-returns-12-months-data-and-not-14-months-data\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/jassweb.com\/solved\/solved-why-the-following-sql-server-query-returns-12-months-data-and-not-14-months-data\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/jassweb.com\/solved\/solved-why-the-following-sql-server-query-returns-12-months-data-and-not-14-months-data\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/jassweb.com\/solved\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"[Solved] Why the following SQL Server query returns 12 months data and not 14 months data\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/jassweb.com\/solved\/#website\",\"url\":\"https:\/\/jassweb.com\/solved\/\",\"name\":\"JassWeb\",\"description\":\"Build High-quality Websites\",\"publisher\":{\"@id\":\"https:\/\/jassweb.com\/solved\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/jassweb.com\/solved\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/jassweb.com\/solved\/#organization\",\"name\":\"Jass Web\",\"url\":\"https:\/\/jassweb.com\/solved\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/jassweb.com\/solved\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/jassweb.com\/wp-content\/uploads\/2021\/02\/jass-website-logo-1.png\",\"contentUrl\":\"https:\/\/jassweb.com\/wp-content\/uploads\/2021\/02\/jass-website-logo-1.png\",\"width\":693,\"height\":132,\"caption\":\"Jass Web\"},\"image\":{\"@id\":\"https:\/\/jassweb.com\/solved\/#\/schema\/logo\/image\/\"}},{\"@type\":\"Person\",\"@id\":\"https:\/\/jassweb.com\/solved\/#\/schema\/person\/65c9c7b7958150c0dc8371fa35dd7c31\",\"name\":\"Kirat\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/jassweb.com\/solved\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/jassweb.com\/solved\/wp-content\/litespeed\/avatar\/1261af3c9451399fa1336d28b98ea3bb.jpg?ver=1776403586\",\"contentUrl\":\"https:\/\/jassweb.com\/solved\/wp-content\/litespeed\/avatar\/1261af3c9451399fa1336d28b98ea3bb.jpg?ver=1776403586\",\"caption\":\"Kirat\"},\"sameAs\":[\"http:\/\/jassweb.com\"],\"url\":\"https:\/\/jassweb.com\/solved\/author\/jaspritsinghghumangmail-com\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"[Solved] Why the following SQL Server query returns 12 months data and not 14 months data - JassWeb","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:\/\/jassweb.com\/solved\/solved-why-the-following-sql-server-query-returns-12-months-data-and-not-14-months-data\/","og_locale":"en_US","og_type":"article","og_title":"[Solved] Why the following SQL Server query returns 12 months data and not 14 months data - JassWeb","og_description":"[ad_1] You need to group by both month and year parts of ad.xDate. There are only twelve months of the year and you should be seeing the earliest two (14 &#8211; 2) month of your results with totals that are too large because they actually represent a combination of two calendar months. It worked in ... Read more","og_url":"https:\/\/jassweb.com\/solved\/solved-why-the-following-sql-server-query-returns-12-months-data-and-not-14-months-data\/","og_site_name":"JassWeb","article_published_time":"2022-09-03T18:37:29+00:00","author":"Kirat","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Kirat","Est. reading time":"1 minute"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/jassweb.com\/solved\/solved-why-the-following-sql-server-query-returns-12-months-data-and-not-14-months-data\/#article","isPartOf":{"@id":"https:\/\/jassweb.com\/solved\/solved-why-the-following-sql-server-query-returns-12-months-data-and-not-14-months-data\/"},"author":{"name":"Kirat","@id":"https:\/\/jassweb.com\/solved\/#\/schema\/person\/65c9c7b7958150c0dc8371fa35dd7c31"},"headline":"[Solved] Why the following SQL Server query returns 12 months data and not 14 months data","datePublished":"2022-09-03T18:37:29+00:00","mainEntityOfPage":{"@id":"https:\/\/jassweb.com\/solved\/solved-why-the-following-sql-server-query-returns-12-months-data-and-not-14-months-data\/"},"wordCount":118,"publisher":{"@id":"https:\/\/jassweb.com\/solved\/#organization"},"keywords":["sql","sql-server","sql-server-2008"],"articleSection":["Solved"],"inLanguage":"en-US"},{"@type":"WebPage","@id":"https:\/\/jassweb.com\/solved\/solved-why-the-following-sql-server-query-returns-12-months-data-and-not-14-months-data\/","url":"https:\/\/jassweb.com\/solved\/solved-why-the-following-sql-server-query-returns-12-months-data-and-not-14-months-data\/","name":"[Solved] Why the following SQL Server query returns 12 months data and not 14 months data - JassWeb","isPartOf":{"@id":"https:\/\/jassweb.com\/solved\/#website"},"datePublished":"2022-09-03T18:37:29+00:00","breadcrumb":{"@id":"https:\/\/jassweb.com\/solved\/solved-why-the-following-sql-server-query-returns-12-months-data-and-not-14-months-data\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/jassweb.com\/solved\/solved-why-the-following-sql-server-query-returns-12-months-data-and-not-14-months-data\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/jassweb.com\/solved\/solved-why-the-following-sql-server-query-returns-12-months-data-and-not-14-months-data\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/jassweb.com\/solved\/"},{"@type":"ListItem","position":2,"name":"[Solved] Why the following SQL Server query returns 12 months data and not 14 months data"}]},{"@type":"WebSite","@id":"https:\/\/jassweb.com\/solved\/#website","url":"https:\/\/jassweb.com\/solved\/","name":"JassWeb","description":"Build High-quality Websites","publisher":{"@id":"https:\/\/jassweb.com\/solved\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/jassweb.com\/solved\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/jassweb.com\/solved\/#organization","name":"Jass Web","url":"https:\/\/jassweb.com\/solved\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/jassweb.com\/solved\/#\/schema\/logo\/image\/","url":"https:\/\/jassweb.com\/wp-content\/uploads\/2021\/02\/jass-website-logo-1.png","contentUrl":"https:\/\/jassweb.com\/wp-content\/uploads\/2021\/02\/jass-website-logo-1.png","width":693,"height":132,"caption":"Jass Web"},"image":{"@id":"https:\/\/jassweb.com\/solved\/#\/schema\/logo\/image\/"}},{"@type":"Person","@id":"https:\/\/jassweb.com\/solved\/#\/schema\/person\/65c9c7b7958150c0dc8371fa35dd7c31","name":"Kirat","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/jassweb.com\/solved\/#\/schema\/person\/image\/","url":"https:\/\/jassweb.com\/solved\/wp-content\/litespeed\/avatar\/1261af3c9451399fa1336d28b98ea3bb.jpg?ver=1776403586","contentUrl":"https:\/\/jassweb.com\/solved\/wp-content\/litespeed\/avatar\/1261af3c9451399fa1336d28b98ea3bb.jpg?ver=1776403586","caption":"Kirat"},"sameAs":["http:\/\/jassweb.com"],"url":"https:\/\/jassweb.com\/solved\/author\/jaspritsinghghumangmail-com\/"}]}},"_links":{"self":[{"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/posts\/6560","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/comments?post=6560"}],"version-history":[{"count":0,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/posts\/6560\/revisions"}],"wp:attachment":[{"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/media?parent=6560"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/categories?post=6560"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/tags?post=6560"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}