SQL Server Performance

Where Clause Help

Discussion in 'ALL SQL SERVER QUESTIONS' started by BDunlap, Dec 31, 2012.

  1. BDunlap New Member

    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
  2. Shehap MVP, MCTS, MCITP SQL Server

    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
  3. BDunlap New Member

    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.
  4. Sandeep Mittal New Member

    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
  5. BDunlap New Member

    Perfect Sandeep! Thank you so much!

Share This Page