CODEWITHSUNDEEP
sqlsql-serverdatetimegroup-by
user2634794
user2634794

Reputation: 63

SQL Server Group by date and by time of day

Using SQL Server and I have a table with a CreatedAt column that has a datetime datatype. I'm trying to write a query that will return the total records for each date but also split the date up into the time of day i.e Morning, Afternoon and Evening. So, for example, the following table

ID     createdAt
------------------------------
1      2017-10-17 21:31:52.160
2      2017-10-17 12:12:12.160
3      2017-10-17 09:09:09.160
4      2017-10-17 21:31:52.160
5      2017-10-16 21:31:52.160

would return the following results

DayCounter    SessionDay    Morning    Afternoon    Evening
4             2017-10-17    1          1            2
1             2017-10-16    0          0            1

I have the following two queries that group the data by day and then splits extracts the time of day from the date but I can't work out how to put them together.

SELECT 
    COUNT(createdAt) AS counter, 
    CONVERT(DATE, createdAt) AS sessionDay
FROM 
    MyTable
GROUP BY 
    CONVERT(DATE, createdAt)

and

SELECT 
    CASE 
       WHEN CAST(CONVERT(CHAR(2), createdAt, 108) AS INT) < 12 
          THEN 'Morning'
       WHEN CAST(CONVERT(CHAR(2), createdAt, 108) AS INT) < 17 
          THEN 'Afternoon'
       ELSE 'Evening' 
    END AS sessionPeriod, 
    createdAt 
FROM 
    MyTable

Upvotes: 2

Views: 1971

Answers (3)

Raging Bull
Raging Bull

Reputation: 18767

You are on the right track to your solution. Just have to combine your queries along with sum:

SELECT count(createdAt) AS counter, convert(date, createdAt) AS sessionDay
    ,sum(CASE WHEN CAST( CONVERT(CHAR(2), createdAt, 108) AS INT) <12 THEN 1 ELSE 0 end ) as 'Morninig'
    ,sum(case WHEN CAST( CONVERT(CHAR(2), createdAt, 108) AS INT) <17 AND CAST( CONVERT(CHAR(2), createdAt, 108) AS INT) >=12 THEN 1 ELSE 0 end) as 'Afternoon'
    ,sum(case when CAST( CONVERT(CHAR(2), createdAt, 108) AS INT) >=17 THEN 1 ELSE 0 end) as 'Evening'
FROM MyTable
GROUP BY convert(date, createdAt)

Result:

counter sessionDay  Morning     Afternoon   Evening
1       2017-10-16  0           0           1
4       2017-10-17  1           1           2

Result in SQL Fiddle

NB: use datepart() in sql server for a better query

Upvotes: 2

Mohamad TAGHLOBI
Mohamad TAGHLOBI

Reputation: 591

Please try to replace in your first query :

CONVERT(DATE, createdAt) AS sessionDay

by this one:

CONVERT(CHAR(10), createdAt, 103) AS sessionDay

and in the 2nd, to replace:

CAST(CONVERT(CHAR(2), createdAt, 108) AS INT) < 12

by 

CAST(CONVERT(CHAR(2), createdAt, 108) AS INT)  in 0 to 11

and 

WHEN CAST(CONVERT(CHAR(2), createdAt, 108) AS INT) < 17

by 

WHEN CAST(CONVERT(CHAR(2), createdAt, 108) AS INT) in 12 to 16

Hope this can help

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271181

You seem to want conditional aggregation, along with some clearer use of date/time functions:

select cast(createdAt as date),
       sum(case when datepart(hour, createdat) < 12 then 1 else 0 end) as Morning,
       sum(case when datepart(hour, createdat) >= 12 and datepart(hour, createdat) < 17 then 1 else 0 end) as Afternoon,
       sum(case when datepart(hour, createdat) >= 17 then 1 else 0 end) as Evening
from mytable
group by cast(createdAt as date)
order by cast(createdAt as date);

Upvotes: 9

Related Questions