?
Solved

Code to get COUNTS in seperate columns

Posted on 2005-03-31
2
Medium Priority
?
335 Views
Last Modified: 2008-02-26
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
Comment
Question by:WNottsC
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
2 Comments
 

Accepted Solution

by:
fuchsiasoft earned 250 total points
ID: 13688135
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
 

Expert Comment

by:fuchsiasoft
ID: 14552905
Thanks !!
Glad I could be of some help..
Regards
fuchsiasoft
0

Featured Post

Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Azure Functions is a solution for easily running small pieces of code, or "functions," in the cloud. This article shows how to create one of these functions to write directly to Azure Table Storage.
Recently, Microsoft released a best-practice guide for securing Active Directory. It's a whopping 300+ pages long. Those of us tasked with securing our company’s databases and systems would, ideally, have time to devote to learning the ins and outs…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

801 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question