CODEWITHSUNDEEP
sqlt-sql
ruedi
ruedi

Reputation: 5555

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?

Upvotes: 0

Views: 37

Answers (2)

MtwStark
MtwStark

Reputation: 4058

Use CTE

;with
t1 as (  -- your data table
    select * 
    from (
        values
        ('frank', cast('12:00' as time), cast('16:00' as time), 'Proj1'),
        ('frank', cast('08:00' as time), cast('16:00' as time), 'Proj2'),
        ('andre', cast('09:00' as time), cast('16:00' as time), 'Proj3'),
        ('andre', cast('11:00' as time), cast('16:00' as time), 'Proj4')
    ) x (name,startt,endt, prjn) 
),
t2 as (  -- precalc hours per project
    select name, prjn, datediff(hour, startt, endt) difft
    from t1 
),
t3 as (  -- precalc hours per name
    select name, SUM(difft) allt
    from t2
    group by name
),
t4 as (  -- table to pivot
    select t2.*, t3.allt
    from t2
    inner join t3 on t2.name = t3.name  
)
select *
from t4
pivot (sum(difft) for prjn in (Proj1, Proj2, Proj3, Proj4)) p
order by name

tadaaa

Upvotes: 0

jarlh
jarlh

Reputation: 44795

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

Upvotes: 2

Related Questions