August 13, 2012 at 11:32 am
If you want to pass in a ProVerCode, then why don't you try something like this?
WITH cteEnrollm AS
(
SELECT Enrol_ID, ProVerCode, Stude_ID
FROM (
Values
('11111', 'AR-216', '65468'),
('77777', 'AR-216', '54564'),
('22222', 'AR-216', '56475'),
('66666', 'PO-216', '87953'),
('33333', 'PO-216', '16654'),
('11111', 'UN-216', '87944'),
('88888', 'UN-216', '56431') ) dt (Enrol_ID, ProVerCode, Stude_ID)
), cteAdm AS
(
SELECT Enrol_ID, Stat, Stude_ID
FROM (
Values
('11111', 'Accept', '65468'),
('77777', 'Start', '54564'),
('22222', 'Accept', '56475'),
('66666', 'Accept', '87953'),
('33333', 'Start', '16654'),
('11111', 'Accept', '87944'),
('88888', 'Start', '56431') )dt (Enrol_ID, Stat, Stude_ID)
), cteJoin AS
(
SELECT e.ProVerCode, a.Stat,
DistinctCount = COUNT(DISTINCT e.Enrol_ID)
FROM cteAdm a
JOIN cteEnrollm e
ON a.Stude_Id = e.Stude_ID
-- only allow Accept/Start - any other values would need to be handled in the next section
WHERE a.Stat IN ('Accept', 'Start')
GROUP BY e.ProVerCode, a.Stat
)
SELECT ProVerCode,
'AcceptCount' = MAX(CASE WHEN Stat = 'Accept' THEN DistinctCount ELSE NULL END),
'StartCount' = MAX(CASE WHEN Stat = 'Start' THEN DistinctCount ELSE NULL END)
FROM cteJoin
GROUP BY ProVerCode
This returns:
ProVerCode AcceptCount StartCount
---------- ----------- -----------
AR-216 2 1
PO-216 1 1
UN-216 1 1
If working with a specific ProVerCode, just add it to the final (outer) query:
WHERE ProVerCode = @ProVerCode
Edit: If you really want a result set like what you specified, then take a look at the Cross Tab and Pivot Table links in my signature. You need to understand Part 1, but you will need the dynamic part in Part 2.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
Viewing post 1 (of 2 total)
You must be logged in to reply to this topic. Login to reply