SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Where Clause Help		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

BDunlap1006
Starting Member

1 Posts
Posted - 12/31/2012 :  14:32:37  Show Profile  Reply with Quote
I have two select statements that execute the way I expected them to execute. Now what I need is to put the two together without InsuranceID column showing twice. How do I do this? What I want to see as the coulmns are: InsuranceID, July, August. Any help would be appreciated. Thanks!

SELECT InsuranceID, Round(Sum(Amount*-1),0) AS July
FROM BarCollectionTransactions
WHERE Type = 'R' and Month(BatchDateTime)= '07' and Year(BatchDateTime) = '2010'
GROUP BY InsuranceID
ORDER BY InsuranceID

SELECT InsuranceID, Round(Sum(Amount*-1),0) AS August
FROM BarCollectionTransactions
WHERE Type = 'R' and Month(BatchDateTime)= '08' and Year(BatchDateTime) = '2010'
GROUP BY InsuranceID
ORDER BY InsuranceID

shilpash
Yak Posting Veteran

72 Posts
Posted - 12/31/2012 :  14:50:29  Show Profile  Reply with Quote
WITH JulyAmt_cte
AS (SELECT InsuranceID
,ROUND(SUM(Amount * -1),0) AS July
FROM BarCollectionTransactions
WHERE Type = 'R'
AND MONTH(BatchDateTime) = '07'
AND YEAR(BatchDateTime) = '2010'
GROUP BY InsuranceID
),
AugustAmt_Cte
AS (SELECT InsuranceID
,ROUND(SUM(Amount * -1),0) AS August
FROM BarCollectionTransactions
WHERE Type = 'R'
AND MONTH(BatchDateTime) = '08'
AND YEAR(BatchDateTime) = '2010'
GROUP BY InsuranceID
)
SELECT InsuranceID
,July = JulyAmt_cte.July
,August = AugustAmt_Cte.August
FROM BarCollectionTransactions
LEFT JOIN JulyAmt_cte
ON BarCollectionTransactions.Insuranceid = JulyAmt_cte.InsuranceId
LEFT JOIN AugustAmt_Cte
ON BarCollectionTransactions.Insuranceid = AugustAmt_Cte.InsuranceId
Go to Top of Page

shilpash
Yak Posting Veteran

72 Posts
Posted - 12/31/2012 :  14:53:36  Show Profile  Reply with Quote
and u could add at the end of the code--
WHERE BarCollectionTransactions.TYPE = 'R'
Go to Top of Page

sodeep
Flowing Fount of Yak Knowledge

USA
7173 Posts
Posted - 01/01/2013 :  16:55:00  Show Profile  Reply with Quote
Make it simple

SELECT InsuranceID, 
Round(Sum(Case When Month(BatchDateTime)= '07' then (Amount * -1) Else 0 End),0) AS July,
Round(Sum(Case When Month(BatchDateTime)= '08' then (Amount * -1) Else 0 End),0) AS August
FROM BarCollectionTransactions
WHERE Type = 'R' and Month(BatchDateTime) in ('07','08') and Year(BatchDateTime) = '2010'
GROUP BY InsuranceID
ORDER BY InsuranceID
Edited by - sodeep on 01/01/2013 16:56:02
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000