Query help please - 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)

Query help please

Author

Topic

haroon2k9
Constraint Violating Yak Guru

328 Posts

Posted - 2010-12-10 : 03:01:41

hi,

TableA

Pkid (INT,pRIMARY KEY ) userid(INT)

pkidA userid

1 100

2 100

3 100

4 200

5 200

TableB

pKIDB (int,primary key) ,pkidA(fORIGN KEY,INT) Hours (Decimal (9,2) ) CaseType id(int)
pkidb pkida hours casetypeid

1 1 10 1

2 2 2 2

Casetypetbl

casetypeid(Int,primarykey),casetypename (varchar(50))

casetypeid casetypename

1 typea

2 typpeb

3 typec

4 typed

5 typee

Output expected (if parameter passed 100 (Tablea - userid) )
totalhrs typea typeb typec typed typee

12 10 2 0 0 0

am i clear? please reply

i mean if parameter passed as 100

we need to get the pkidA where userid =100 in Tablea.. (for this case 1,2,3)

then we need to sum the hrs where pkida in(1,2,3) in tableb..now see the output please

clear now?

haroon2k9
Constraint Violating Yak Guru

328 Posts

Posted - 2010-12-10 : 03:36:45

Am i not clear?..please response

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-12-10 : 05:23:04

Try:

Declare @TableA Table
(
PkidA Int Primary Key,
userid INT
)

Declare @TableB Table
(
pKIDB Int Primary Key,
pKIDA Int,
Hours Decimal (9,2),
CaseTypeID INT
)

Declare @Casetypetbl Table
(
casetypeid Int primary key,
casetypename varchar(50)
)

--totalhrs typea typeb typec typed typee

--1--2 10 2 0 0 0

Insert Into @TableA
Select 1, 100 union
select 2, 100 union
select 3, 100 union
select 4, 200 union
select 5, 200

Insert into @TableB
Select 1,1,10,1 union
Select 2,2,2,2 union
Select 3,4,4,4

Insert into @Casetypetbl
Select 1,'typea' union
Select 2, 'typpeb' union
select 3, 'typec' union
select 4, 'typed' union
select 5, 'typee'

Select a.userid,
sum(b.Hours) as TotalHrs,
Max(Case when c.casetypeid =1 then B.Hours else 0 end ) as typea,
Max(Case when c.casetypeid =2 then B.Hours else 0 end ) as typeb,
Max(Case when c.casetypeid =3 then B.Hours else 0 end ) as typec,
Max(Case when c.casetypeid =4 then B.Hours else 0 end ) as typed,
Max(Case when c.casetypeid =5 then B.Hours else 0 end ) as typee
from @TableA A
inner join @TableB B on B.pKIDA = A.pKIDA
inner join @Casetypetbl C on B.CaseTypeID = c.CaseTypeID
group by A.userid

Regards,
Bohra

I am here to learn from Masters and help new bees in learning.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources