Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 357
  • Last Modified:

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
0
WNottsC
Asked:
WNottsC
  • 2
1 Solution
 
fuchsiasoftCommented:
You could try using a stored proc. Get the 2 counts and store it in 2 variables and then show the data.
Eg:
CREATE PROCEDURE SP_ShowData
AS
BEGIN
      DECLARE @StageCOUNT0405 INT, @StageCOUNT0506 INT

      SET @StageCOUNT0405 = (SELECT COUNT(*) FROM TABLE WHERE CONDITION = VALUES)
      SET @StageCOUNT0506 = (SELECT COUNT(*) FROM TABLE WHERE CONDITION = DIFFERENT VALUES)

      SELECT @StageCOUNT0405  AS 0405Count, @StageCOUNT0506  AS 0506Count, Stage_Code, OTHER_FIELDS FROM TABLE WHERE CONDITION = COMMON VALUES

END
0
 
fuchsiasoftCommented:
Thanks !!
Glad I could be of some help..
Regards
fuchsiasoft
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now