{"id":197,"date":"2023-01-07T18:18:15","date_gmt":"2023-01-07T18:18:15","guid":{"rendered":"https:\/\/jassweb.com\/new22\/solved-sql-to-group-by-36-hours-closed\/"},"modified":"2023-01-07T18:18:15","modified_gmt":"2023-01-07T18:18:15","slug":"solved-sql-to-group-by-36-hours-closed-2","status":"publish","type":"post","link":"https:\/\/jassweb.com\/solved\/solved-sql-to-group-by-36-hours-closed-2\/","title":{"rendered":"[Solved] Sql to group by 36 hours [closed]"},"content":{"rendered":"<h2> Introduction <\/h2>\n<p>[ad_1]<\/p>\n<p>This article will discuss how to use SQL to group data by 36 hours. We will look at how to use the GROUP BY clause to group data by 36 hours, as well as how to use the DATEADD function to add 36 hours to a date. We will also discuss how to use the DATEDIFF function to calculate the difference between two dates in 36-hour increments. Finally, we will look at how to use the CAST function to convert a date to a 36-hour interval.<\/p>\n<h2> Solution<\/h2>\n<p><\/p>\n<p>SELECT<br \/>\n    DATEADD(HOUR, DATEDIFF(HOUR, 0, timestamp), 0) AS start_time,<br \/>\n    DATEADD(HOUR, DATEDIFF(HOUR, 0, timestamp) + 36, 0) AS end_time,<br \/>\n    COUNT(*) AS count<br \/>\nFROM table<br \/>\nGROUP BY DATEADD(HOUR, DATEDIFF(HOUR, 0, timestamp), 0)<br \/>\nORDER BY start_time; <\/p>\n<p><\/p>\n<div class=\"entry-content\" itemprop=\"text\">\n<script async src=\"https:\/\/pagead2.googlesyndication.com\/pagead\/js\/adsbygoogle.js?client=ca-pub-1088640234840270\" crossorigin=\"anonymous\"><\/script><br \/>\n<script><\/p>\n<p><\/script><\/p>\n<p><\/p>\n<div id=\"answer-30496975\" class=\"answer js-answer accepted-answer js-accepted-answer\" data-answerid=\"30496975\" data-parentid=\"30495048\" data-score=\"2\" 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<pre><code>--Set up table and data\nDECLARE @ATable TABLE ([date] DATETIME, [Count] INT)\nINSERT @ATable( date, Count )\nSELECT '2015-05-14 01:00:00', 1\nUNION ALL\nSELECT '2015-05-15 02:00:00', 2\nUNION ALL\nSELECT '2015-05-15 20:00:00', 3\nUNION ALL\nSELECT '2015-05-16 03:00:00', 4\n\n-- Query\nSELECT\n  d.[date],\n  ( -- This subquery returns the sum of counts for the 36 hours\n    SELECT\n      SUM(a.[count])\n    FROM\n      @ATable a\n    WHERE\n      a.[date] &lt; DATEADD(hour, 36, d.[date])\n      AND\n      a.[date] &gt;= d.[date]\n  ) AS [count]\nFROM\n  ( -- This subquery returns a list of unique dates\n    SELECT\n      DATEADD(dd, DATEDIFF(dd, 0, [date]), 0) AS [date]\n    FROM\n      @ATable\n    GROUP BY\n      DATEADD(dd, DATEDIFF(dd, 0, [date]), 0)\n  ) AS d\nORDER BY\n  d.date  \n<\/code><\/pre>\n<p>Please note that your data and required results DO NOT MATCH, As you have been told repeatedly. The above yields the following results:<\/p>\n<pre><code>date                          count\n----------------------- -----------\n2015-05-14 00:00:00.000           3\n2015-05-15 00:00:00.000           9\n2015-05-16 00:00:00.000           4\n<\/code><\/pre>\n<\/div>\n<div class=\"mt24\"><\/div>\n<\/div>\n<p> <span class=\"d-none\" itemprop=\"commentCount\"><\/span> <\/p>\n<\/div>\n<\/div>\n<p>solved Sql to group by 36 hours [closed] <\/p>\n<p><script async src=\"https:\/\/pagead2.googlesyndication.com\/pagead\/js\/adsbygoogle.js?client=ca-pub-1088640234840270\" crossorigin=\"anonymous\"><\/script><br \/>\n<script><\/p>\n<p><\/script><\/div>\n<p>[ad_2]<\/p>\n<pre>\nSELECT \n    DATEADD(HOUR, DATEDIFF(HOUR, 0, [timestamp]), 0) AS [timestamp], \n    COUNT(*) AS [count] \nFROM \n    [table] \nGROUP BY \n    DATEADD(HOUR, DATEDIFF(HOUR, 0, [timestamp]), 0) \nHAVING \n    DATEDIFF(HOUR, MIN([timestamp]), MAX([timestamp])) <= 36 \nORDER BY \n    [timestamp]\n<\/pre>\n<p>This SQL query can be used to group data by 36 hours. It will take the timestamp column from a table and group it by the hour, while also counting the number of entries in each group. The query also includes a HAVING clause which ensures that the difference between the minimum and maximum timestamp is no more than 36 hours. Finally, the query is ordered by the timestamp column.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Introduction [ad_1] This article will discuss how to use SQL to group data by 36 hours. We will look at how to use the GROUP BY clause to group data by 36 hours, as well as how to use the DATEADD function to add 36 hours to a date. We will also discuss how to &#8230; <a title=\"[Solved] Sql to group by 36 hours [closed]\" class=\"read-more\" href=\"https:\/\/jassweb.com\/solved\/solved-sql-to-group-by-36-hours-closed-2\/\" aria-label=\"More on [Solved] Sql to group by 36 hours [closed]\">Read more<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[320],"tags":[341,500,1009],"class_list":["post-197","post","type-post","status-publish","format-standard","hentry","category-solved","tag-sql","tag-sql-server","tag-tsql"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v26.5 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>[Solved] Sql to group by 36 hours [closed] - 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-sql-to-group-by-36-hours-closed-2\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"[Solved] Sql to group by 36 hours [closed] - JassWeb\" \/>\n<meta property=\"og:description\" content=\"Introduction [ad_1] This article will discuss how to use SQL to group data by 36 hours. We will look at how to use the GROUP BY clause to group data by 36 hours, as well as how to use the DATEADD function to add 36 hours to a date. We will also discuss how to ... Read more\" \/>\n<meta property=\"og:url\" content=\"https:\/\/jassweb.com\/solved\/solved-sql-to-group-by-36-hours-closed-2\/\" \/>\n<meta property=\"og:site_name\" content=\"JassWeb\" \/>\n<meta property=\"article:published_time\" content=\"2023-01-07T18:18:15+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=\"2 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/jassweb.com\/solved\/solved-sql-to-group-by-36-hours-closed-2\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/jassweb.com\/solved\/solved-sql-to-group-by-36-hours-closed-2\/\"},\"author\":{\"name\":\"Kirat\",\"@id\":\"https:\/\/jassweb.com\/solved\/#\/schema\/person\/65c9c7b7958150c0dc8371fa35dd7c31\"},\"headline\":\"[Solved] Sql to group by 36 hours [closed]\",\"datePublished\":\"2023-01-07T18:18:15+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/jassweb.com\/solved\/solved-sql-to-group-by-36-hours-closed-2\/\"},\"wordCount\":225,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\/\/jassweb.com\/solved\/#organization\"},\"keywords\":[\"sql\",\"sql-server\",\"tsql\"],\"articleSection\":[\"Solved\"],\"inLanguage\":\"en-US\"},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/jassweb.com\/solved\/solved-sql-to-group-by-36-hours-closed-2\/\",\"url\":\"https:\/\/jassweb.com\/solved\/solved-sql-to-group-by-36-hours-closed-2\/\",\"name\":\"[Solved] Sql to group by 36 hours [closed] - JassWeb\",\"isPartOf\":{\"@id\":\"https:\/\/jassweb.com\/solved\/#website\"},\"datePublished\":\"2023-01-07T18:18:15+00:00\",\"breadcrumb\":{\"@id\":\"https:\/\/jassweb.com\/solved\/solved-sql-to-group-by-36-hours-closed-2\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/jassweb.com\/solved\/solved-sql-to-group-by-36-hours-closed-2\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/jassweb.com\/solved\/solved-sql-to-group-by-36-hours-closed-2\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/jassweb.com\/solved\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"[Solved] Sql to group by 36 hours [closed]\"}]},{\"@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] Sql to group by 36 hours [closed] - 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-sql-to-group-by-36-hours-closed-2\/","og_locale":"en_US","og_type":"article","og_title":"[Solved] Sql to group by 36 hours [closed] - JassWeb","og_description":"Introduction [ad_1] This article will discuss how to use SQL to group data by 36 hours. We will look at how to use the GROUP BY clause to group data by 36 hours, as well as how to use the DATEADD function to add 36 hours to a date. We will also discuss how to ... Read more","og_url":"https:\/\/jassweb.com\/solved\/solved-sql-to-group-by-36-hours-closed-2\/","og_site_name":"JassWeb","article_published_time":"2023-01-07T18:18:15+00:00","author":"Kirat","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Kirat","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/jassweb.com\/solved\/solved-sql-to-group-by-36-hours-closed-2\/#article","isPartOf":{"@id":"https:\/\/jassweb.com\/solved\/solved-sql-to-group-by-36-hours-closed-2\/"},"author":{"name":"Kirat","@id":"https:\/\/jassweb.com\/solved\/#\/schema\/person\/65c9c7b7958150c0dc8371fa35dd7c31"},"headline":"[Solved] Sql to group by 36 hours [closed]","datePublished":"2023-01-07T18:18:15+00:00","mainEntityOfPage":{"@id":"https:\/\/jassweb.com\/solved\/solved-sql-to-group-by-36-hours-closed-2\/"},"wordCount":225,"commentCount":0,"publisher":{"@id":"https:\/\/jassweb.com\/solved\/#organization"},"keywords":["sql","sql-server","tsql"],"articleSection":["Solved"],"inLanguage":"en-US"},{"@type":"WebPage","@id":"https:\/\/jassweb.com\/solved\/solved-sql-to-group-by-36-hours-closed-2\/","url":"https:\/\/jassweb.com\/solved\/solved-sql-to-group-by-36-hours-closed-2\/","name":"[Solved] Sql to group by 36 hours [closed] - JassWeb","isPartOf":{"@id":"https:\/\/jassweb.com\/solved\/#website"},"datePublished":"2023-01-07T18:18:15+00:00","breadcrumb":{"@id":"https:\/\/jassweb.com\/solved\/solved-sql-to-group-by-36-hours-closed-2\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/jassweb.com\/solved\/solved-sql-to-group-by-36-hours-closed-2\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/jassweb.com\/solved\/solved-sql-to-group-by-36-hours-closed-2\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/jassweb.com\/solved\/"},{"@type":"ListItem","position":2,"name":"[Solved] Sql to group by 36 hours [closed]"}]},{"@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\/197","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=197"}],"version-history":[{"count":0,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/posts\/197\/revisions"}],"wp:attachment":[{"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/media?parent=197"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/categories?post=197"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/tags?post=197"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}