Where Clause Help | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Where Clause Help

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
You can try Case When commands , it is usually helpful for such cases as shown below , SELECT InsuranceID,case when Month(BatchDateTime)= ’08’ and Year(BatchDateTime) = ‘2010’ then Round(Sum(Amount*-1),0) end AS August ,
case when Month(BatchDateTime)= ’07’ and Year(BatchDateTime) = ‘2010’ then Round(Sum(Amount*-1),0) end AS July
WHERE Type = ‘R’ and
GROUP BY InsuranceID
ORDER BY InsuranceID Please try it and let me know your feedback
Shehap, I gave it a try and it did not work. I got this error:
Column ‘BarCollectionTransactions.BatchDateTime’ is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. I don’t really want to GROUP BY BatchDateTime. I tried putting Month(BatchDateTIme) and Year(BatchDateTime) in the GROUP BY but that just returned a lot of unnecessary "InsuranceID-NULL-NULL" rows.
Code:
SELECT    InsuranceID
    , Round(Sum((CASE WHEN Month(BatchDateTime) = 7 THEN Amount ELSE 0 END)*-1),0) AS July
    , Round(Sum((CASE WHEN Month(BatchDateTime) = 8 THEN Amount ELSE 0 END)*-1),0) AS August
FROM    BarCollectionTransactions
WHERE    Type = 'R'
AND    Month(BatchDateTime) IN (7, 8)
AND    Year(BatchDateTime) = 2010
GROUP BY InsuranceID
ORDER BY InsuranceID

Perfect Sandeep! Thank you so much!
]]>

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |