Calculating two columns based on two views

Question

Calculating two columns based on two views

jbon007

SSChasing Mays

Points: 602
More actions
December 29, 2010 at 4:33 am

#231508

Hi All,
I'm struggling with some code. I have two SQL statements both give me different answers.
SELECT COUNT(crn) AS NoDayCase,[DIS SPEC DESC], [DIS CONS DESC], [DIS_YEAR], [DIS_MONTH], [admtype], [SPELL LOS], [INTMAN]
FROM ColossusUser.inf.tbl_PS_IXP_ADMS_AND_DIS as DC
GROUP BY [DIS SPEC DESC], [DIS CONS DESC], [DIS_YEAR], [DIS_MONTH],[admtype],[SPELL LOS], [INTMAN]
HAVING (DIS_YEAR = N'2010') AND (DIS_MONTH = N'March') AND admtype IN('11','12','13') AND [SPELL LOS] <= 0 AND (INTMAN = 'DC')
SELECT COUNT(crn) AS NoElectiveAdmissions,[DIS SPEC DESC], [DIS CONS DESC], [DIS_YEAR], [DIS_MONTH], [admtype]
FROM ColossusUser.inf.tbl_PS_IXP_ADMS_AND_DIS as EA
GROUP BY [DIS SPEC DESC], [DIS CONS DESC], [DIS_YEAR], [DIS_MONTH],[admtype]
HAVING (DIS_YEAR = N'2010') AND (DIS_MONTH = N'March') AND admtype IN('11','12','13')
what am trying to do is to create a new view to show a column with NoElectiveAdmissions, NoDayCase, [DIS SPEC DESC],[DIS_YEAR], [DIS_MONTH] and [DIS CONS DESC]

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

Of course they give you different answers - you have a different column list. Please will you supply sample data (INSERT statements), table DDL (CREATE TABLE statements) and desired results so that we can help you.

Thanks

John

jbon007 SSChasing Mays Points: 602 More actions · Answer 2

sorry i wasn't clear enough.

from the two views i want the two results to be combined into one view.

i.e

NoDaycase noElective Dis_Spec Dis_Cons

16 23 ENT ConsA

19 38 Derm ConsC

basically combine the two views and link by Consultant and spec

WayneS SSC Guru Points: 95460 More actions · Answer 3

jbon007 (12/29/2010)
sorry i wasn't clear enough.
from the two views i want the two results to be combined into one view.
i.e
NoDaycase noElective Dis_Spec Dis_Cons
16 23 ENT ConsA
19 38 Derm ConsC
basically combine the two views and link by Consultant and spec

This code is joining on those fields, assuming that there is at least one record in each of the queries. If one can have zero records, with the other having >0, then you'll need to use a left or full join.

WITH CTE1 AS

(

SELECT COUNT(crn) AS NoDayCase,[DIS SPEC DESC], [DIS CONS DESC], [DIS_YEAR], [DIS_MONTH], [admtype], [SPELL LOS], [INTMAN]

FROM ColossusUser.inf.tbl_PS_IXP_ADMS_AND_DIS as DC

GROUP BY [DIS SPEC DESC], [DIS CONS DESC], [DIS_YEAR], [DIS_MONTH],[admtype],[SPELL LOS], [INTMAN]

HAVING (DIS_YEAR = N'2010') AND (DIS_MONTH = N'March') AND admtype IN('11','12','13') AND [SPELL LOS] <= 0 AND (INTMAN = 'DC')

), CTE2 AS

(

SELECT COUNT(crn) AS NoElectiveAdmissions,[DIS SPEC DESC], [DIS CONS DESC], [DIS_YEAR], [DIS_MONTH], [admtype]

FROM ColossusUser.inf.tbl_PS_IXP_ADMS_AND_DIS as EA

GROUP BY [DIS SPEC DESC], [DIS CONS DESC], [DIS_YEAR], [DIS_MONTH],[admtype]

HAVING (DIS_YEAR = N'2010') AND (DIS_MONTH = N'March') AND admtype IN('11','12','13')

)

SELECT CTE1.NoDayCase, CTE2.NoElectiveAdmissions, CTE1.[DIS SPEC DESC], CTE2.[DIS CONS DESC]

FROM CTE1

JOIN CTE2

ON CTE1.[DIS CONS DESC] = CTE2.[DIS CONS DESC]

AND CTE1.[DIS SPEC DESC] = CTE2.[DIS SPEC DESC]

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2