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
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Where Clause Help

Author  Topic 

BDunlap1006
Starting Member

1 Post

Posted - 2012-12-31 : 14:32:37
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
Posting Yak Master

103 Posts

Posted - 2012-12-31 : 14:50:29
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
Posting Yak Master

103 Posts

Posted - 2012-12-31 : 14:53:36
and u could add at the end of the code--
WHERE BarCollectionTransactions.TYPE = 'R'
Go to Top of Page

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2013-01-01 : 16:55:00
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
Go to Top of Page
   

- Advertisement -