WNottsC
asked on
Code to get COUNTS in seperate columns
I have written the following code to display a count of students for each stage code and 2 counts for the totals of two years. The problem i have is that the count of students for each stage code counts for 04/05 and 05/06 but in the same column which means it only displays the 04/05 data when displayed in a report and i need it to be able to display the count for both years so basically i need to columns!!.
Heres the stored procedure. I hope yo can help.
CREATE VIEW dbo.v_Admissions
AS
SELECT TOP 100 PERCENT COUNT(a.student_id) AS StudentCOUNT, a.stage_code, a.acad_period, b.ref1, e.StageCOUNT0405, c.Stage_Group, c.id,
D.StageCOUNT0506
FROM FRED.dbo.WILMA b INNER JOIN
FRED.dbo.BARNEY a ON a.aos_code = b.aos_code AND a.acad_period = b.acad_period AND a.aos_period = b.aos_period INNER JOIN
dbo.BAMBAM c ON c.stage_code = a.stage_code INNER JOIN
(SELECT COUNT(a.student_id) AS StageCOUNT0405, a.stage_code
FROM BETTY.FRED.dbo.BARNEY a INNER JOIN
BETTY.FRED.dbo.WILMA b ON a.aos_code = b.aos_code AND a.acad_period = b.acad_period AND
a.aos_period = b.aos_period INNER JOIN
BETTY.FRED.dbo.BAMBAM c ON c.stage_code = a.stage_code
WHERE (a.acad_period = '04/05') AND (a.update_dt < GetDate() - 365) AND (a.Stage_code = 'APP' OR
a.Stage_code = 'INT1' OR
a.Stage_code = 'ACC')
GROUP BY a.stage_code, a.acad_period, c.id, c.Stage_Group) e ON e.stage_code = a.stage_code INNER JOIN
(SELECT COUNT(a.student_id) AS StageCOUNT0506, a.stage_code
FROM BETTY.FRED.dbo.BARNEY a INNER JOIN
BETTY.FRED.dbo.WILMA b ON a.aos_code = b.aos_code AND a.acad_period = b.acad_period AND
a.aos_period = b.aos_period INNER JOIN
CISQL.WNC_CIS.dbo.BAMBAM c ON c.stage_code = a.stage_code
WHERE (a.acad_period = '05/06')
GROUP BY a.stage_code, a.acad_period, c.id, c.Stage_Group) D ON D.stage_code = a.stage_code
WHERE (a.acad_period = '05/06') OR
(a.acad_period = '04/05') AND (a.update_dt < GETDATE() - 365) AND (a.stage_code = 'APP' OR
a.stage_code = 'INT1' OR
a.stage_code = 'ACC')
GROUP BY a.stage_code, a.acad_period, b.ref1, c.id, e.StageCOUNT0405, c.Stage_Group, D.StageCOUNT0506
ORDER BY b.ref1, c.id
Thanks
Heres the stored procedure. I hope yo can help.
CREATE VIEW dbo.v_Admissions
AS
SELECT TOP 100 PERCENT COUNT(a.student_id) AS StudentCOUNT, a.stage_code, a.acad_period, b.ref1, e.StageCOUNT0405, c.Stage_Group, c.id,
D.StageCOUNT0506
FROM FRED.dbo.WILMA b INNER JOIN
FRED.dbo.BARNEY a ON a.aos_code = b.aos_code AND a.acad_period = b.acad_period AND a.aos_period = b.aos_period INNER JOIN
dbo.BAMBAM c ON c.stage_code = a.stage_code INNER JOIN
(SELECT COUNT(a.student_id) AS StageCOUNT0405, a.stage_code
FROM BETTY.FRED.dbo.BARNEY a INNER JOIN
BETTY.FRED.dbo.WILMA b ON a.aos_code = b.aos_code AND a.acad_period = b.acad_period AND
a.aos_period = b.aos_period INNER JOIN
BETTY.FRED.dbo.BAMBAM c ON c.stage_code = a.stage_code
WHERE (a.acad_period = '04/05') AND (a.update_dt < GetDate() - 365) AND (a.Stage_code = 'APP' OR
a.Stage_code = 'INT1' OR
a.Stage_code = 'ACC')
GROUP BY a.stage_code, a.acad_period, c.id, c.Stage_Group) e ON e.stage_code = a.stage_code INNER JOIN
(SELECT COUNT(a.student_id) AS StageCOUNT0506, a.stage_code
FROM BETTY.FRED.dbo.BARNEY a INNER JOIN
BETTY.FRED.dbo.WILMA b ON a.aos_code = b.aos_code AND a.acad_period = b.acad_period AND
a.aos_period = b.aos_period INNER JOIN
CISQL.WNC_CIS.dbo.BAMBAM c ON c.stage_code = a.stage_code
WHERE (a.acad_period = '05/06')
GROUP BY a.stage_code, a.acad_period, c.id, c.Stage_Group) D ON D.stage_code = a.stage_code
WHERE (a.acad_period = '05/06') OR
(a.acad_period = '04/05') AND (a.update_dt < GETDATE() - 365) AND (a.stage_code = 'APP' OR
a.stage_code = 'INT1' OR
a.stage_code = 'ACC')
GROUP BY a.stage_code, a.acad_period, b.ref1, c.id, e.StageCOUNT0405, c.Stage_Group, D.StageCOUNT0506
ORDER BY b.ref1, c.id
Thanks
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Glad I could be of some help..
Regards
fuchsiasoft