Total Select Subquery Results - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Total Select Subquery Results

Author

Topic

scottybhoy
Starting Member

1 Post

Posted - 2009-09-22 : 11:31:15

Hi

I am trying to calculate the results of two Sub-Queries (if they are called something completely different, please let me know as I am relatively new to SQL).

Basically, as you will see from my code below, I want to be able to display the total value of [STCV] - [STPV] + [STPAV] AS [STV] as well as the original alias values in the same query results.

Any ideas would be greatly appreciated as I have been banging my head off of a brick wall for the last two weeks!

SELECT 
pa_Branch.br_ID AS [Branch ID],
pa_Branch.br_desc AS [Branch Name],
pa_Healthboard.hb_desc AS [Healthboard Name],
pa_Organisation.nh_homename AS [Organisation Name],
pa_Sites.si_desc AS [Site Name],
pa_EyeTest.ey_id AS [Test ID],
pa_EyeTest.ey_testdate AS [Test Date],
pa_optician.op_FirstName + ',' + pa_optician.op_LastName + '/' + pa_Dispenser.di_FirstName + ',' + pa_Dispenser.di_LastName AS [Test Team],
pa_Patients.pa_LastName + ',' + pa_Patients.pa_FirstName AS [Patient Name]

	,(SELECT     
	 ISNULL(SUM(pa_STClaimCode.cc_value),0)
	 FROM
     pa_STClaim
	 INNER JOIN pa_STClaimCode ON pa_STClaim.sc_ccid = pa_STClaimCode.cc_id
	 WHERE
	 (pa_STClaim.sc_eyid = pa_EyeTest.ey_id) AND (pa_STClaim.disabled = 0)) AS [STCV]

	                                                                             
	,(SELECT																												  
	  ISNULL (SUM(pa_STPayment.sp_payment),0)																					  		
	  FROM																													  	
	  pa_STClaim																												 
	  INNER JOIN pa_STPayment ON pa_STClaim.sc_id = pa_STPayment.sp_scid														
	  WHERE																													
	  (pa_STPayment.sp_scid = pa_STClaim.sc_id) AND (pa_STClaim.sc_eyid = pa_EyeTest.ey_id)) AS [STPV]	
				

	 ,(SELECT ISNULL(SUM(pa_STPaymentAdjustment.sj_payment),0)
	   FROM 
	   pa_STPaymentAdjustment
       WHERE
	   (pa_STPaymentAdjustment.disabled = 0) AND (pa_STPaymentAdjustment.sj_eyid = pa_EyeTest.ey_id)) AS [STPAV]

FROM
pa_EyeTest
INNER JOIN pa_sites ON pa_EyeTest.ey_siid = pa_Sites.si_id
INNER JOIN pa_patients ON pa_EyeTest.ey_paid = pa_patients.pa_id
INNER JOIN pa_organisation ON pa_sites.si_nhid = pa_Organisation.nh_id
INNER JOIN pa_Healthboard ON pa_Organisation.nh_HealthboardID = pa_Healthboard.hb_id
INNER JOIN pa_Branch ON pa_Organisation.nh_branchID = pa_Branch.br_id
INNER JOIN pa_organisationType ON pa_Organisation.nh_organisationType = pa_OrganisationType.ot_id
INNER JOIN pa_Optician ON pa_EyeTest.ey_optician = pa_Optician.op_id
INNER JOIN pa_Dispenser ON pa_EyeTest.ey_dispenser = pa_Dispenser.di_id
WHERE
(pa_EyeTest.disabled = 0 AND pa_EyeTest.ey_testdate between '2001-01-01' and '2011-01-01' AND pa_OrganisationType.ot_BusinessType = 1)
GROUP BY
pa_Branch.br_ID,
pa_Branch.br_desc,
pa_Healthboard.hb_desc,
pa_Organisation.nh_homename,
pa_Sites.si_desc,
pa_EyeTest.ey_id,
pa_EyeTest.ey_testdate,
pa_optician.op_FirstName + ',' + pa_optician.op_LastName + '/' + pa_Dispenser.di_FirstName + ',' + pa_Dispenser.di_LastName ,
pa_Patients.pa_LastName + ',' + pa_Patients.pa_FirstName
ORDER BY
[Branch Name],
[Test ID],
[Healthboard Name],
[Organisation Name],
[Site Name],
[Patient Name]

JCirocco
Constraint Violating Yak Guru

392 Posts

Posted - 2009-09-22 : 11:46:28

Have you tried to use derived tables? (I apologize for not putting your table and column names, I am sure the REAL experts here will do that but I struggle with my own sometimes)

Table Sales_All generates a column [Total Sales] and Sales_Won generates [Total Won]. These can then be used however you need to calculate (in my case) [Sales Other]

SELECT Sales_All.Account_Manager_Id,
Sales_All.[Total Sales],
Sales_Won.[Total Won],
Sales_All.[Total Sales] - Sales_Won.[Total Won] AS [Sales Other]

FROM
--- Total
(SELECT Account_Manager_Id,
SUM(C_Sales) AS [Total Leads]
FROM ED_Sales_
WHERE (C_Lead_Type_Id = 0x0000000000000002)

GROUP BY Account_Manager_Id)
AS Sales_All

--- Won
LEFT OUTER JOIN
(SELECT Account_Manager_Id,
C_Account_Manager_Branch_Id,
SUM(C_Sales) AS [Total Won]
FROM ED_Sales_ AS Sales_2
WHERE (C_Pipeline_Stage_Id = 'Won')
AND (C_Lead_Type_Id = 0x0000000000000002)

GROUP BY Account_Manager_Id)
AS Sales_Won

ON Sales_All.Account_Manager_Id = Sales_Won.Account_Manager_Id

John

"The smoke monster is just the Others doing barbecue"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources