SQL Server Group by date and by time of day

Question

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

Answer 1

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);

Answer 2

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