Where Clause Help - 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

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

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'

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources