November 26, 2011 at 4:27 pm
I have 2 tables with below table structures
Create table Client
(ClientID Int,
clientName Nvarchar(100))
Create Table Category
(categoryID int,
CategoryName Nvarchar(100))
Create table ClientMemberShip
(ClientMemberShipID Int,
ClientID Int,
PopulationTypeID Int,
CategoryTypeID int)
Insert into Client Values(1,'ClientOne')
Insert into Client Values(2,'ClientTwo')
Insert into Client Values(3,'ClientThree')
Insert into Category values(1,'CategoryOne')
Insert into Category Values(2,'CategoryTwo')
Insert into Category values(3,'CategoryThree')
Insert into ClientMembership values(11,1,1,1)
Insert into ClientMembership values(12,1,1,2)
Insert into ClientMembership values(13,1,2,1)
Insert into ClientMemberShip values(14,2,1,1)
Insert into ClientMemberShip values(15,2,1,2)
I would like to create a stored Procedure which would return the results like below....
If i pass the client ID as Input parameter output would as below....
ClientID / PopulationType / CategoryOne / CategoryTwo / CategoryThree
1 / 1 / Yes / Yes / No
1 / 2 / Yes / No / No
2 / 1 / Yes / Yes / No
November 26, 2011 at 9:51 pm
Could someone please help me with the Stored Procedure...
November 26, 2011 at 10:49 pm
Here is the query to give you the results you want.
I assume you can write the stored procedure to pass in a parameter to a WHERE clause.
;with cte as (
select c1.ClientID,c2.PopulationTypeID
,sum(case when CategoryTypeID = 1 then 1 else 0 end) as CategoryOneTotal
,sum(case when CategoryTypeID = 2 then 1 else 0 end) as CategoryTwoTotal
,sum(case when CategoryTypeID = 3 then 1 else 0 end) as CategoryThreeTotal
from Client c1
join ClientMembership c2 on c2.clientID = c1.ClientID
group by c1.ClientID, c2.PopulationTypeID
)
select ClientID,PopulationTypeID
,case when CategoryOneTotal > 0 then 'Yes' else 'No' end as CategoryOne
,case when CategoryTwoTotal > 0 then 'Yes' else 'No' end as CategoryTwo
,case when CategoryThreeTotal > 0 then 'Yes' else 'No' end as CategoryThree
from cte
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
November 27, 2011 at 10:11 am
thank you so much it worked fine
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply