August 13, 2012 at 10:40 am
...or something like that. Here's some data:
CREATE TABLE Enrollm
(
Enrol_IDCHAR(10)NOT NULL,
ProVerCodeCHAR(15)NOT NULL,
Stude_IDCHAR(10)NOT NULL
)
;
INSERT INTO Enrollm
(
Enrol_ID,
ProVerCode,
Stude_ID
)
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')
;
CREATE TABLE Adm
(
Enrol_IDCHAR(10)NOT NULL,
StatCHAR(15)NOT NULL,
Stude_IDCHAR(10)NOT NULL
)
INSERT INTO Adm
(
Enrol_ID,
Stat,
Stude_ID
)
Values
('11111', 'Accept', '65468'),
('77777', 'Start', '54564'),
('22222', 'Accept', '56475'),
('66666', 'Accept', '87953'),
('33333', 'Start', '16654'),
('11111', 'Accept', '87944'),
('88888', 'Start', '56431')
;
I need to count distinct Stude_ID's for Stat by ProVerCode.
So something like this: (Please forgive the ugly SQL. I wasn't sure of a better way to accomplish the result set!)
SELECT
(
SELECT COUNT(DISTINCT Enrollm.Enrol_ID)
FROM Enrollm JOIN Adm ON Enrollm.Stude_ID = Adm.Stude_ID
WHERE Adm.Stat = 'Accept'
AND ProVerCode = 'AR-216'
)AR216_Accept,
(
SELECT COUNT(DISTINCT Enrollm.Enrol_ID)
FROM Enrollm JOIN Adm ON Enrollm.Stude_ID = Adm.Stude_ID
WHERE Adm.Stat = 'Start'
AND ProVerCode = 'AR-216'
)AR216_Start
Now for the fun part. I would like to pass the ProVerCode values as variables to the same query to keep from creating multiple queries. So the output would be something like this:
AR-216_Accept AR-216_Start PO-216_Accept PO-216_Start UN-216_Accept UN-216_Start
2 1 1 1 1 1
Is this possible? Please let me know if I can provide additional information.
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 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply