ruedi ruedi - 5 months ago 13
SQL Question

Join two query results where one is created from column values

I have a table tables

Table1:

Name|Starttime|Endtime|Project_Number
Frank| 12:00| 16:00|Project1
Frank| 08:00| 16:00|Project2
Andre| 09:00| 16:00|Project4
Andre| 11:00| 16:00|Project5


I try to accomplish to get a table that shows the time worked all together and for each project like this:

Table2:

Name |All|Project1|Project2|Project3|Project4
Andre|12 |4 |8 |Null |Null
Frank|12 |Null |Null |7 |5


I can get the result for all

Select Name, sum(datediff(Minute, Starttime, Endtime)) from Table1
group by Name, sum(datediff(Minute, Starttime, Endtime))


and I can accomplish that for
Table2
as well (with a group over Project as well) but I just do not get the result I want. I already tried
UNION
but that just maps the tables.

Anyone who can help me get this done?

Answer

Do the datediff calculation in a derived table. (To keep the code pretty!)

Then use case expressions to do conditional aggregation:

select Name,
       sum(ts),
       sum(case when Project_Number = 'Project1' then ts end) as Project1,
       sum(case when Project_Number = 'Project2' then ts end) as Project2,
       sum(case when Project_Number = 'Project3' then ts end) as Project3,
       sum(case when Project_Number = 'Project4' then ts end) as Project4
from
(
    select Name, datediff(Minute, Starttime, Endtime) as ts, Project_Number
    from Table1
)
group by Name

You can also skip the derived table:

select Name,
       sum(datediff(Minute, Starttime, Endtime)),
       sum(case when Project_Number = 'Project1' then datediff(Minute, Starttime, Endtime) end) as Project1,
       sum(case when Project_Number = 'Project2' then datediff(Minute, Starttime, Endtime) end) as Project2,
       sum(case when Project_Number = 'Project3' then datediff(Minute, Starttime, Endtime) end) as Project3,
       sum(case when Project_Number = 'Project4' then datediff(Minute, Starttime, Endtime) end) as Project4
from Table1
group by Name