Left Join Problems - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Left Join Problems

Author

Topic

DaveC11
Starting Member

43 Posts

Posted - 2008-02-05 : 05:37:08

I have the bewlow Sql statment that I'm trying to get working. I'm trying to join 6 tables that may or may not have information realted to them. I'm trying to show details all the details users from the users table that may or may not have made a placement within a timeframe. At the moment the query shows only consultants who have a made placement within a timeframe.

Thanks in advance Dave


Select 
u.userid ,
U.username as 'First Name', 
u.surname as 'Surname', 
Count(p.placementid) as 'Number of Deals', 
SUM(coalesce(P.PLACEMENTFEE,0)) AS 'Total Boarded',
cast(round(Avg(p.salary),2,1)as decimal(10,2)) as 'Avg Salary', 
cast(round (Avg(p.placementfee),2,1) as decimal(10,2)) as 'Avg Placement Fee', 
cast(round(Avg(p.commissionperc),2,1) as decimal(10,1)) as 'Avg Placement %',
T.Total_Boarded as 'Target'



from 

users u
left join usergroups ug on u.userid =ug.userid
left join groups g on ug.groupid = g.groupid
left join targets t on t.userid = u.userid
left join placementconsultants pc on pc.userid = u.userid
left join placements p on p.placementid = pc.placementid

where g.groupid = '6' and u.inactive = 'n' and t.date between '01/01/08' and '02/02/08' and p.createdon between '01/01/08' and '02/02/08'

group by u.userid, u.username, u.surname, t.total_boarded

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-05 : 05:41:47

change like this and try:-
Select
u.userid ,
U.username as 'First Name',
u.surname as 'Surname',
Count(p.placementid) as 'Number of Deals',
SUM(coalesce(P.PLACEMENTFEE,0)) AS 'Total Boarded',
cast(round(Avg(p.salary),2,1)as decimal(10,2)) as 'Avg Salary',
cast(round (Avg(p.placementfee),2,1) as decimal(10,2)) as 'Avg Placement Fee',
cast(round(Avg(p.commissionperc),2,1) as decimal(10,1)) as 'Avg Placement %',
T.Total_Boarded as 'Target'

from

users u
left join usergroups ug on u.userid =ug.userid
left join groups g on ug.groupid = g.groupid
left join targets t on t.userid = u.userid
left join placementconsultants pc on pc.userid = u.userid
left join placements p on p.placementid = pc.placementid

where (g.groupid = '6' or g.groupid is null)
and u.inactive = 'n'
and (t.date between '01/01/08' and '02/02/08' or t.date is null)
and (p.createdon between '01/01/08' and '02/02/08' or p.createdon is null)
group by u.userid, u.username, u.surname, t.total_boarded

DaveC11
Starting Member

43 Posts

Posted - 2008-02-05 : 05:46:45

Thanks that's solved the problem!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources