CASE statement with between dates

[awaria]

SQL Server 2000, SP3

Need help with a query.
Querying [GL Line Item TRX table], want to group trx amts by month in columns. Fields in table are trxdate, trxamt, gl account number.

Desired Results:

Acct Number | Jan10 Bal | Feb10 Bal | . . .
100-999 $50 $250

Was attempting to get monthly column balances by using CASE, but not having success with syntax.

Appreciate any assistance. My feelings won't be hurt if recommendation is a completely different strategy.

Thanks,

Andrew

 

[PWise]

Select [Acct Number],
Sum Case when month(trxdate)=1 then trxamt else 0 end jan,
Sum Case when month(trxdate)=2 then trxamt else 0 end feb,
...
Sum Case when month(trxdate)=12 then trxamt else 0 end dec
From Tablename
where trxdate between @StartDate and @endDate
Group by [Acct Number],year(trxdate)

 

[markros]

Small correction to the above query: I believe () are required (although haven't tried without):

Select [Acct Number],
Sum (Case when month(trxdate)=1 then trxamt else 0 end) as [Jan10 Bal],
Sum (Case when month(trxdate)=2 then trxamt else 0 end) as  [Feb10 Bal],etc.
Sum(Case when month(trxdate)=12 then trxamt else 0 end) as  [Dec10 Bal] From Tablename
where trxdate between @StartDate and @endDate
Group by [Acct Number],year(trxdate)

PluralSight Learning Library

 

[awaria]

Thank you both for sharing your expertise and how quickly you responded.

Statement works great!!!

Thanks again,

Andrew