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
Name |All|Project1|Project2|Project3|Project4
Andre|12 |4 |8 |Null |Null
Frank|12 |Null |Null |7 |5
Select Name, sum(datediff(Minute, Starttime, Endtime)) from Table1
group by Name, sum(datediff(Minute, Starttime, Endtime))
Table2
UNION
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