Link to home
Start Free TrialLog in
Avatar of WNottsC
WNottsCFlag for Afghanistan

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
ASKER CERTIFIED SOLUTION
Avatar of fuchsiasoft
fuchsiasoft

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of fuchsiasoft
fuchsiasoft

Thanks !!
Glad I could be of some help..
Regards
fuchsiasoft