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
Table2UNIONAnyone 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
Source (Stackoverflow)