left join within referential table

user_14837131 0
Viewed 52

I have a table Time

enter image description here

Second table SectorProject

enter image description here

Third table Rfsector

enter image description here

I developed the following query

Select sum(time) as time, Sector_L1, T.id_Project
from time T
left join SectorProject SP ON t.id_Project=T.id_Project
left join Rfsector RF on RF.Sector_L2=SP.Sector_L2
GROUP BY Sector_L1, T.id_Project

Result and expected result

enter image description here

I didn't understand the result, can someone explain me why I get this result and how to modify the query in ordr to get the expected result?

sql-server-2012 tsql sql-server sql
1 Answers

You can allocate time equally for a project using window functions:

select sum(time) as time, Sector_L1, T.id_Project,
       avg(time) over (partition by t.id_project) as imputed_time
from time T left join
     SectorProject SP 
     on t.id_Project = T.id_Project left join
     Rfsector RF
      on RF.Sector_L2 = SP.Sector_L2
group by Sector_L1, T.id_Project;
user_11440350
Related