Grouping by DateTime field

Question

I have run into an interesting issue, and could certainly use some assistance. I have a query that pulls "events" that take place over the course of a day. The goal is to group the events by day with a count of how many events took place that day.

The issue I have run into is that the datetime field has different times for every event making grouping it very difficult. Note that there must be 2 fields for "TheDate"- one that will be displayed to the user (the NVARCHAR convert) and one that is used to do a date range search. This query will be stored in a view.

The query, that doesn't perform as I want and hasn't taken that into account, looks something like this:

SELECT TheDate, Username, CONVERT(NVARCHAR(10), TheDate, 120) 'Date',  
        (SUM(CASE WHEN itemID = 1 THEN 1 ELSE 0 END)) AS Item1,
        (SUM(CASE WHEN itemID = 2 THEN 1 ELSE 0 END)) AS Item2,
        (SUM(CASE WHEN itemID = 3 THEN 1 ELSE 0 END)) AS Item3,
        (SUM(CASE WHEN itemID = 4 THEN 1 ELSE 0 END)) AS Item4,
        (SUM(CASE WHEN itemID = 5 THEN 1 ELSE 0 END)) AS Item5
FROM         Table1 AS a INNER JOIN
                  Table2 AS tab2 ON tab2.Table1ID = a.Table1ID LEFT OUTER JOIN
                  Table3 AS tab3 ON tab3.Table2ID = tab2.Table2ID
WHERE someOtherID = 3 AND UserID = 40
GROUP BY CONVERT(NVARCHAR(10), TheDate, 120), TheDate, Username
ORDER BY CONVERT(NVARCHAR(10), TheDate, 120)

UNION 

    SELECT TheDate, Username, 'GrandTotal',  
        (SUM(CASE WHEN itemID = 1 THEN 1 ELSE 0 END)) AS Item1,
        (SUM(CASE WHEN itemID = 2 THEN 1 ELSE 0 END)) AS Item2,
        (SUM(CASE WHEN itemID = 3 THEN 1 ELSE 0 END)) AS Item3,
        (SUM(CASE WHEN itemID = 4 THEN 1 ELSE 0 END)) AS Item4,
        (SUM(CASE WHEN itemID = 5 THEN 1 ELSE 0 END)) AS Item5
FROM         Table1 AS a INNER JOIN
                  Table2 AS tab2 ON tab2.Table1ID = a.Table1ID LEFT OUTER JOIN
                  Table3 AS tab3 ON tab3.Table2ID = tab2.Table2ID
WHERE someOtherID = 3 AND UserID = 40
GROUP BY TheDate, Username

The groupings are all messed up in this, and for obvious reasons. I want the results to by grouped by day, and instead there are 10-20 rows per day because of the hour/minute/seconds listed on the datetime field TheDate.

Trying to fix this somehow, I attempted something similar to the following:

SELECT * FROM(
SELECT UserName, DATEADD(DAY, DATEDIFF(day, 0, TheDate), 0) AS TheDate,
       CONVERT(NVARCHAR(10), TheDate, 120) 'Date',  
        (SUM(CASE WHEN itemID = 1 THEN 1 ELSE 0 END)) AS Item1,
        (SUM(CASE WHEN itemID = 2 THEN 1 ELSE 0 END)) AS Item2,
        (SUM(CASE WHEN itemID = 3 THEN 1 ELSE 0 END)) AS Item3,
        (SUM(CASE WHEN itemID = 4 THEN 1 ELSE 0 END)) AS Item4,
        (SUM(CASE WHEN itemID = 5 THEN 1 ELSE 0 END)) AS Item5
FROM         Table1 AS a INNER JOIN
                  Table2 AS tab2 ON tab2.Table1ID = a.Table1ID LEFT OUTER JOIN
                  Table3 AS tab3 ON tab3.Table2ID = tab2.Table2ID
WHERE someOtherID = 3 AND UserID = 40
GROUP BY CONVERT(NVARCHAR(10), TheDate, 120), TheDate, UserName
ORDER BY CONVERT(NVARCHAR(10), TheDate, 120)

UNION 

    SELECT Username, DATEADD(DAY, DATEDIFF(day, 0, TheDate), 0) AS TheDate, 
        'GrandTotal',  
        (SUM(CASE WHEN itemID = 1 THEN 1 ELSE 0 END)) AS Item1,
        (SUM(CASE WHEN itemID = 2 THEN 1 ELSE 0 END)) AS Item2,
        (SUM(CASE WHEN itemID = 3 THEN 1 ELSE 0 END)) AS Item3,
        (SUM(CASE WHEN itemID = 4 THEN 1 ELSE 0 END)) AS Item4,
        (SUM(CASE WHEN itemID = 5 THEN 1 ELSE 0 END)) AS Item5
FROM         Table1 AS a INNER JOIN
                  Table2 AS tab2 ON tab2.Table1ID = a.Table1ID LEFT OUTER JOIN
                  Table3 AS tab3 ON tab3.Table2ID = tab2.Table2ID
WHERE someOtherID = 3 AND UserID = 40
GROUP BY UserName, TheDate) AS X
GROUP BY X.TheDate, X.Date, X.UserName, X.Item1, X.Item2, X.Item3, X.Item4, X.Item5

My goal there was to remove the times from the datetime. Visibly, in the results, it worked. The datetime results now all look like

2013-12-24 00:00:00.000

Unfortunately, technically, it didn't do a thing. I still have 10-20 rows per day, the same as when I had the times visible.

I would like my results to look like this (remember, this is going into a view, so I won't be visibly selected Username and TheDate for the user to see. Those are for where clause purposes only):

Date        Item2   Item2   Item3   Item4   Item5
2013-05-31  1       30      0       0       129
2013-06-03  0       90      0       15      78
2013-06-04  0       50      0       1       124
2013-06-05  0       100     0       17      58
2013-06-06  0       24      0       0       105
2013-06-07  0       50      0       1       83
2013-06-10  0       45      2       42      64
2013-06-11  3       43      0       14      90
2013-06-12  2       44      0       36      88
2013-06-13  1       34      0       15      92
GRAND TOTAL 7       510     2       141     911

Does anyone have a suggestion on the best way to fix this issue, and allow me to group each set of rows by day, as opposed to multiple rows per day due to different times in the datetimes?

Thank you!

Answer 1

You are using SQL Server 2008, so use cast(xxx as date) -- a much simpler syntax. And put it both in the select and group by clauses:

SELECT * FROM(
SELECT UserName, cast(TheDate as date) AS TheDate,
       CONVERT(NVARCHAR(10), TheDate, 120) 'Date',  
        (SUM(CASE WHEN itemID = 1 THEN 1 ELSE 0 END)) AS Item1,
        (SUM(CASE WHEN itemID = 2 THEN 1 ELSE 0 END)) AS Item2,
        (SUM(CASE WHEN itemID = 3 THEN 1 ELSE 0 END)) AS Item3,
        (SUM(CASE WHEN itemID = 4 THEN 1 ELSE 0 END)) AS Item4,
        (SUM(CASE WHEN itemID = 5 THEN 1 ELSE 0 END)) AS Item5
FROM         Table1 AS a INNER JOIN
                  Table2 AS tab2 ON tab2.Table1ID = a.Table1ID LEFT OUTER JOIN
                  Table3 AS tab3 ON tab3.Table2ID = tab2.Table2ID
WHERE someOtherID = 3 AND UserID = 40
GROUP BY CONVERT(NVARCHAR(10), TheDate, 120), cast(TheDate as date), UserName
ORDER BY CONVERT(NVARCHAR(10), TheDate, 120)

UNION 

    SELECT Username, cast(TheDate as date) AS TheDate, 
        'GrandTotal',  
        (SUM(CASE WHEN itemID = 1 THEN 1 ELSE 0 END)) AS Item1,
        (SUM(CASE WHEN itemID = 2 THEN 1 ELSE 0 END)) AS Item2,
        (SUM(CASE WHEN itemID = 3 THEN 1 ELSE 0 END)) AS Item3,
        (SUM(CASE WHEN itemID = 4 THEN 1 ELSE 0 END)) AS Item4,
        (SUM(CASE WHEN itemID = 5 THEN 1 ELSE 0 END)) AS Item5
FROM         Table1 AS a INNER JOIN
                  Table2 AS tab2 ON tab2.Table1ID = a.Table1ID LEFT OUTER JOIN
                  Table3 AS tab3 ON tab3.Table2ID = tab2.Table2ID
WHERE someOtherID = 3 AND UserID = 40
GROUP BY UserName, cast(TheDate as date)) AS X
GROUP BY X.TheDate, X.Date, X.UserName, X.Item1, X.Item2, X.Item3, X.Item4, X.Item5

The problem with your query is that you have the construct:

select <expression> as TheDate
. . .
group by TheDate

where TheDate is also a column in the table. The group by uses the column from the table, not the computed value. In fact, SQL Server does allow the use of column aliases in the group by clause.