Complicated Table Select - 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)

Complicated Table Select

Author

Topic

tmcgee09
Starting Member

3 Posts

Posted - 2010-01-11 : 13:47:27

Hi all, I'm new to this forum, but I need some help quickly on how to generate the result set described below.

I have 3 tables needed in the query (these are simplified):

USERS:
USER_ID | NAME
USER001 user1
USER002 user2

MONTHS:
MONTH_ID | NAME | HRS_IN_MONTH
MONTH001 JAN-09 176
MONTH002 FEB-09 168

PROJECTS
USER_ID | PROJECT_ID | MONTH_ID | HRS
USER001 PRJ001 MONTH001 50
USER001 PRJ001 MONTH002 45
USER001 PRJ002 MONTH001 40
USER002 PRJ001 MONTH001 30

So, what i need is to select a result set that looks like below. Each row should be for one user, and I need a column for MONTH001 and MONTH002 which displays the the hours in that month minus the total hours that user worked on projects. So for MONTH001 and USER001 above, that column would display 176 - 90 = 86


RESULT_SET
USER_NAME | JAN-09 | FEB-09
user1          41       143
user2          146      168

This is the sql i have so far:

select u.name,m.hrs_in_month - sum(case when m.name='JAN-09' then p.hrs else 0 end),m.hrs_in_month - sum(case when m.name='FEB-09' then p.hrs else 0 end)

from projects p inner join users u on u.user_id=p.user_id inner join months m on m.month_id = p.month_id

where (p.month_id='MONTH001' or p.month_id='MONTH002')
group by au.last_name,m.hrs_in_month
order by au.last_name;

....but this is giving me 2 rows for each user...any help i can get would be great! Thank you

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-01-11 : 14:06:09

Your sample data and expected result dont match, yes??
Anyways..try this..

select a.[NAME], max(case when b.[NAME] = 'JAN-09' then b.HRS_IN_MONTH else 0 end) - sum(case when b.[NAME] = 'JAN-09' then c.HRS else 0 end)
,max(case when b.[NAME] = 'FEB-09' then b.HRS_IN_MONTH else 0 end) - sum(case when b.[NAME] = 'FEB-09' then c.HRS else 0 end)
from USERS a
inner join (select USER_ID,MONTH_ID,SUM(HRS) as HRS from PROJECTS group by USER_ID,MONTH_ID) c on a.USER_ID = c.USER_ID
inner join MONTHS b on c.MONTH_ID = b.MONTH_ID
group by a.[NAME]

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-01-11 : 14:07:45

Sample data

declare @USERS table (USERID varchar(20), [NAME] varchar(20))
insert @USERS
select 'USER001', 'user1'
union all select 'USER002', 'user2'

declare @MONTHS table (MONTH_ID varchar(20), [NAME] VARCHAR(20),HRS_IN_MONTH INT)
insert @MONTHS
select 'MONTH001', 'JAN-09', 176
union all select 'MONTH002', 'FEB-09', 168

declare @PROJECTS table(USERID varchar(20),PROJECT_ID varchar(20),MONTH_ID varchar(20), HRS int)
insert @PROJECTS
select 'USER001', 'PRJ001', 'MONTH001', 50
union all select 'USER001', 'PRJ001', 'MONTH002', 45
union all select 'USER001', 'PRJ002', 'MONTH001', 40
union all select 'USER002', 'PRJ001', 'MONTH001', 30

Query

select a.[NAME], max(case when b.[NAME] = 'JAN-09' then b.HRS_IN_MONTH else 0 end) - sum(case when b.[NAME] = 'JAN-09' then c.HRS else 0 end)
,max(case when b.[NAME] = 'FEB-09' then b.HRS_IN_MONTH else 0 end) - sum(case when b.[NAME] = 'FEB-09' then c.HRS else 0 end)
from @USERS a
inner join (select USERID,MONTH_ID,SUM(HRS) as HRS from @PROJECTS group by USERID,MONTH_ID) c on a.USERID = c.USERID
inner join @MONTHS b on c.MONTH_ID = b.MONTH_ID
group by a.[NAME]

Result

NAME                 JAN-09      FEB-09
-------------------- ----------- -----------
user1                86          123
user2                146         0

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-01-11 : 14:23:10

Not bad Vijay.
Next problem will be that the month and year as column name is hard coded...

No, you're never too old to Yak'n'Roll if you're too young to die.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-01-11 : 14:24:30

Well...OP has done the same in the SQL that he has tried..so I hope he doesn't complain

tmcgee09
Starting Member

3 Posts

Posted - 2010-01-11 : 14:40:43

Hey thanks a lot vijay. The only problem is that if there are no records for the month, i still need to return the hrs in month. So in the sample data, the bottom right cell needs to be '168', instead of the '0' your sql returned. Any ideas on how to accomplish that?

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-01-11 : 14:50:48

I thought it doesn't make sense to have a row if there is no record for that user for that month...

Anyways..This is not a great solution...but try this

select a.[NAME], (SELECT HRS_IN_MONTH from MONTHS where [NAME] = 'JAN-09') - sum(case when c.MONTH_ID = 'MONTH001' then c.HRS else 0 end) as [JAN-09]
,(SELECT HRS_IN_MONTH from MONTHS where [NAME] = 'FEB-09') - sum(case when c.MONTH_ID = 'MONTH002' then c.HRS else 0 end) as [FEB-09]
from USERS a
inner join (select USERID,MONTH_ID,SUM(HRS) as HRS from PROJECTS group by USERID,MONTH_ID) c on a.USERID = c.USERID
group by a.[NAME]

tmcgee09
Starting Member

3 Posts

Posted - 2010-01-11 : 14:59:47

I think that will work, thank you!!!

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-01-11 : 15:05:01

Np. You're welcome.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources