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