CODEWITHSUNDEEP
sqlsql-serversql-server-2008
fledgling
fledgling

Reputation: 1051

Split data datetime wise in SQL Server

I have a requirement to group the data day-wise from 8:00 AM today to 7:59 AM the next day as 1 full day.

For example from the below table:

SoldDatetime           Qty
Dec 20,2015 12:05 AM    1
Dec 20,2015 1:05 AM     2
Dec 20,2015 7:05 AM     3
Dec 20,2015 8:05 AM     4
Dec 20,2015 10:05 AM    5
Dec 20,2015 11:05 PM    6
Dec 21,2015 12:05 AM    7
Dec 21,2015 1:05 AM     8
Dec 21,2015 7:05 AM     9
Dec 21,2015 8:05 AM     10
Dec 21,2015 10:05 AM    11
Dec 21,2015 11:05 PM    12
Dec 22,2015 12:05 AM    13
Dec 22,2015 1:05 AM     14
Dec 22,2015 7:05 AM     15
Dec 22,2015 8:05 AM     16
Dec 22,2015 10:05 AM    17
Dec 22,2015 11:05 PM    18
Dec 23,2015 12:05 AM    19
Dec 23,2015 1:05 AM     20
Dec 23,2015 7:05 AM     21
Dec 23,2015 8:05 AM     22
Dec 23,2015 10:05 AM    23
Dec 23,2015 11:05 PM    24
Dec 24,2015 12:05 AM    25
Dec 24,2015 1:05 AM     26
Dec 24,2015 7:05 AM     27
Dec 24,2015 8:05 AM     28
Dec 24,2015 10:05 AM    29
Dec 24,2015 11:05 PM    30
Dec 25,2015 12:05 AM    31
Dec 25,2015 1:05 AM     32
Dec 25,2015 7:05 AM     33
Dec 25,2015 8:05 AM     34
Dec 25,2015 10:05 AM    35
Dec 25,2015 11:05 PM    36

If I have to run a query to filter for date range Dec 21,2015 8:00 AM to Dec 25,2015 7:59 AM. I need the output in below format

SoldDateRange                               Qty
Dec 20,2015 8:00 AM - Dec 21,2015 7:59AM    39
Dec 21,2015 8:00 AM - Dec 22,2015 7:59AM    75
Dec 22,2015 8:00 AM - Dec 23,2015 7:59AM    111
Dec 23,2015 8:00 AM - Dec 24,2015 7:59AM    147
Dec 24,2015 8:00 AM - Dec 25,2015 7:59AM    183

Can someone help with the SQL query for this? Thanks in advance

Upvotes: 1

Views: 111

Answers (2)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520888

Here is a query which should give you the full output you mentioned in your original question.

SELECT CONCAT(CONVERT(VARCHAR, t.adjustedTime, 107), ' 8:00 AM',
              ' - ',
              CONVERT(VARCHAR, DATEADD(DAY, 1, t.adjustedTime), 107), ' 7:59AM'),
       t.Qty
FROM
(
    SELECT CAST(DATEADD(HOUR, -8, SoldDateTime) AS DATE) AS adjustedTime, SUM(Qty) AS Qty
    FROM yourTable
    GROUP BY CAST(DATEADD(HOUR, -8, SoldDateTime) AS DATE)
) t
ORDER BY t.adjustedTime

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269443

You can do this by subtracting 8 hours. The basic idea is:

select cast(dateadd(hour, -8, solddaterange) as date) as SoldDate, sum(qty)
from t
group by cast(dateadd(hour, -8, solddaterange) as date)
order by SoldDate;

EDIT:

You could use the same idea. For instance:

where cast(dateadd(hour, -8, solddaterange) as date) = '2016-01-01'

Upvotes: 0

Related Questions