I'm trying to write a SQL query that produces a table with summarized values for each year and month. I have a table that looks something like this: And another table that looks like this: I want to have a query that returns the sum for each month like this: Explanation of the output: Year and Month are obvious RegTime

Multiple SUM in one query with multiple where

I’m trying to write a SQL query that produces a table with summarized values for each year and month.

I have a table that looks something like this:

TABLENAME: TIME
id     cID     cDate         cTime 
 1     254     2019-10-11    5    
 2     259     2019-10-13    4    
 3     268     2020-01-17    6
 4     268     2020-01-18    9
 5     271     2020-01-21    4
 6     267     2020-02-19    8

And another table that looks like this:

TABLENAME: CASE
id     name     invoice      status
254    Acme     Yes          finish
259    Tex      NoFakt       finish
268    Rex C    Yes          *Null*
267    Hydro    *Null*       open
271    Corp     Yes          finish

I want to have a query that returns the sum for each month like this:

Year    Month    RegTime    Invoiced   ToBeInvoiced
2019    10       5          5          0
2019    11       0          0          0
2019    12       0          0          0
2020     1       19         4          15
2020     2       8          0          8

Explanation of the output:

Year and Month are obvious

RegTime should be the sum of all TIME.cTime WHERE CASE.invoice <> ‘NoFakt’

Invoiced should be the sum of all TIME.cTIME WHERE CASE.invoice = ‘Yes’ AND CASE.status = ‘finish’

ToBeInvoiced should be the sum of all TIME.cTIME WHERE CASE.invoice = ‘Yes’ AND CASE.status <> ‘finish’

I have tried this query, but with this i need to loop a predefined year and month value in my programming code instead of simply having a sql statement that are doing all the work. There´s got to be a simpler way than this…

select (select sum(cTIME) from TIME inner join CASE on TIME.cID = CASE.id WHERE CASE.invoice <> 'NoFakt' AND DATEPART(yy, cDate) = '2019' AND DATEPART(mm, cDate) = '10') AS RegTime,
(select sum(cTIME) from TIME inner join CASE on TIME.cID = CASE.id WHERE CASE.invoice = 'Yes' AND CASE.status = 'finish' AND DATEPART(yy, cDate) = '2019' AND DATEPART(mm, cDate) = '10') AS Invoiced,
(select sum(cTIME) from TIME inner join CASE on TIME.cID = CASE.id WHERE CASE.invoice = 'Yes' AND CASE.status <> 'finish' AND DATEPART(yy, cDate) = '2019' AND DATEPART(mm, cDate) = '10') AS ToBeInvoiced

Answer

Use conditional aggregation :

SELECT YEAR(T.cDate) AS YR, MONTH(T.cDate) AS Mnth,
       SUM(CASE WHEN C.invoice <> 'NoFakt' THEN C.cTIME ELSE 0 END) AS RegTime,
       SUM(CASE WHEN C.invoice = 'Yes' AND C.status = 'finish' THEN C.cTIME ELSE 0 END) AS Invoiced,
       SUM(CASE WHEN C.invoice = 'Yes' AND C.status <> 'finish' THEN C.cTIME ELSE 0 END) AS ToBeInvoiced
FROM TIME T INNER JOIN 
     CASE C
     ON T.cID = C.id
GROUP BY YEAR(T.cDate), MONTH(T.cDate);

Note: Do not use reserved keyword as Object Name.

Advertisement

Answer