SQL Pivot Table - Subqueries

Question

I have a program that is logging the time a user spends on certain aspects, some are identified as specific "time". I'm struggling to get multiple lines of Grouped query results into a single line for each month as a "summary".

My current query:

SELECT
    TotalMins  = SUM(Minutes)
    ,DateMonth = MONTH(Date)
    ,ID1
    ,PC
FROM User_Time_Log
WHERE
    (UserID = 1)
    AND (YEAR(Date) = 2018)
GROUP BY
    MONTH(Date)
    ,ID1
    ,PC1

Current results:

TotalMins   DateMonth   ID1         PC1
192         1           0           0
306         1           0           100
113         2           0           0
365         2           0           100
14          2           1           0
3           2           1           100
75          3           0           0
253         3           0           100
3           3           1           0
300         4           0           0
233         4           0           100
10          4           1           0
23          4           1           100
438         5           0           0
134         5           0           100
19          5           1           0
49          5           1           100
0           9           1           0
11          10          0           0
21          10          0           60
167         10          1           100

What I would like to do from this point is to create a table showing all 12 months, regardless of whether there is information within that month or not, and show the relative information within each row for that month. for example:

DateMonth       NonID1      TimeID1     TimePC1 (Round((PC1/100)*TotalMins)) TimePC1ID1
1               192             0               306             0
2               113             14              365             3
3               75              3               253             0
4               300             10              233             23              
5               438             19              134             49
6               0               0               0               0
7               0               0               0               0
8               0               0               0               0   
9               0               0               0               0
10              11              0               13              167
11              0               0               0               0
12              0               0               0               0

What's the most efficient way to do this?

Note: I have also created a table to give me 1-12 as rows that I can use to give me the months that I need to use, where information is not within the user_time_log.

Answer 1

Here's a simple way to do what you're looking for:

First, create your table of month values. I made a simple temp table with a single column.

CREATE TABLE #Dates (MonthNum INT)
INSERT INTO #Dates
(
    MonthNum
)
VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12)

Next, you can put your existing query into a CTE, then LEFT JOIN to your table of months. You'll want to put your columns into a SUM'd CASE statement, like so:

;WITH Aggregation AS
(
    SELECT
    TotalMins  = SUM(Minutes)
    ,DateMonth = MONTH(Date)
    ,ID1
    ,PC1
FROM #User_Time_Log
WHERE
    (UserID = 1)
    AND (YEAR(Date) = 2018)
GROUP BY
    MONTH(Date)
    ,ID1
    ,PC1
)
SELECT 
    d.MonthNum 
    ,NonID1 = SUM(CASE WHEN ID1 = 0 THEN TotalMins ELSE 0 END)
    ,TimeID1 = SUM(CASE WHEN ID1 = 1 THEN TotalMins ELSE 0 END)
    ,TimePC1 = SUM(CASE WHEN ID1 = 0 THEN ROUND((PC1/100)*TotalMins,0) ELSE 0 END) 
    ,TimePC1ID1 = SUM(CASE WHEN ID1 = 1 THEN ROUND((PC1/100)*TotalMins,0) ELSE 0 END) 
FROM #Dates d
LEFT JOIN Aggregation a ON d.MonthNum = a.DateMonth
GROUP BY d.MonthNum

Output would then look like this:

MonthNum    NonID1  TimeID1 TimePC1 TimePC1ID1
1           498     0       306     0
2           478     17      365     3
3           328     3       253     0
4           533     33      233     23
5           572     68      134     49
6           0       0       0       0
7           0       0       0       0
8           0       0       0       0
9           0       0       0       0
10          32      167     0       167
11          0       0       0       0
12          0       0       0       0

EDIT:

The ROUND() function call can be changed slightly to accomodate your need for decimal results. The first parameter of ROUND() is the expression you want to round, and the second is the number of decimal places to round to. Positive numbers indicate the number of places to the right of the decimal to round to. Negative numbers indicate the number of places to the left of the decimal to round to. So if you set it to 2, you'll get an answer rounded to the nearest hundredth.

But there's one more tweak we need. PC1 and TotalMins are both assumed to be INTs in my answer. So we have to give the SQL engine a little help so that it calculates the answer as a DECIMAL. By CAST()ing the INTs to DECIMALs, SQL will perform the arithmetic op as decimal math instead of integer math. You'd just have to change TimePC1 and TimePC1ID1 like so:

,TimePC1 = SUM(CASE WHEN ID1 = 0 THEN ROUND((CAST(PC1 AS DECIMAL)/100)*CAST(TotalMins AS DECIMAL),2) ELSE 0 END) 
,TimePC1ID1 = SUM(CASE WHEN ID1 = 1 THEN ROUND((CAST(PC1 AS DECIMAL)/100)*CAST(TotalMins AS DECIMAL),2) ELSE 0 END) 

Then the output looks like this:

MonthNum    NonID1  TimeID1 TimePC1     TimePC1ID1
1           498     0       306.000000  0.000000
2           478     17      365.000000  3.000000
3           328     3       253.000000  0.000000
4           533     33      233.000000  23.000000
5           572     68      134.000000  49.000000
6           0       0       0.000000    0.000000
7           0       0       0.000000    0.000000
8           0       0       0.000000    0.000000
9           0       0       0.000000    0.000000
10          32      167     12.600000   167.000000
11          0       0       0.000000    0.000000
12          0       0       0.000000    0.000000