A Query Help!

I am trying to use table 'Account' and table 'Balance' to get the ideal
result table listed below, but my query is too cumbersome. If an
account opened 10 years ago, I would have 12*10=120 rows in my query.
Is there any simplied way to do this, like a do-loop thing? Thanks a
million!

Table Account
account_noopen_date
11111/1/2000
22222/1/2002

Table Balance
account_nobalbal_date
1111501/31/2000
11111002/29/2000
11111503/31/2000
11112004/30/2000
11112505/31/2000
11113006/30/2000
11113507/31/2000
11114008/31/2000
22225002/29/2002
22226003/31/2002
22227004/30/2002
22228005/31/2002
22229006/30/2002

Ideal Result Table
account_noopen_datemonth0_balmonth1_balmonth2_balmonth3_balmonth4_bal
11111/1/200050100150200250
22222/1/2002500600700800900

--My Query--
SELECT a.account_no
,a.open_date
,sum(case datediff(mm, a.open_date, b.bal_date) when 0 then b.bal else
0 end) as 'month0_bal'
,sum(case datediff(mm, a.open_date, b.bal_date) when 1 then b.bal else
0 end) as 'month1_bal'
,sum(case datediff(mm, a.open_date, b.bal_date) when 2 then b.bal else
0 end) as 'month2_bal'
,sum(case datediff(mm, a.open_date, b.bal_date) when 3 then b.bal else
0 end) as 'month3_bal'
,sum(case datediff(mm, a.open_date, b.bal_date) when 4 then b.bal else
0 end) as 'month4_bal'
FROM account as a
LEFT OUTER JOIN balance as b
on a.account_no=b.account_no
GROUP BY a.account_no, a.open_date
ORDER BY a.account_no, a.open_date(rong.guo@.gmail.com) writes:
> I am trying to use table 'Account' and table 'Balance' to get the ideal
> result table listed below, but my query is too cumbersome. If an
> account opened 10 years ago, I would have 12*10=120 rows in my query.
> Is there any simplied way to do this, like a do-loop thing? Thanks a
> million!

That's indeed how cross-tab queries usually looks like.

You may be interested in checking out RAC, which is a third-party tool
which is good for dynamic crosstabs. Check out http://www.rac4sql.net.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks Erland, I will look into it...

a query

hello all,

im trying to build a query...

the target of this query is to show the sales man, and the balance of the salesman of credits of a single plan.

the data is stored in the table as following,

RowID,SalesManID,PlanID,Credit,TransactionType

the balanace of of a salesman of a single plan is

the (sum(credit) where Transactiontype = 0 ) - (sum(credit) where transaction = 1 )

ofcourse for every single plan and sales man...

please if u need any question to have more detials just ask

and thank u for ur careTry this:

SELECT PlanID, SalesManID,
SUM(CASE WHEN Transactiontype=0 THEN credit WHEN Transactiontype=1 THEN -credit ELSE 0.0 END) as Balance
From YourTable
Group By PlanID,SalesManID

|||thank you

its a great way

but mmm

it seems that it is not working

as if all of them has transaction type 0, but in the database there is 0 and 1

so is there anything i can do|||Did you get the minus sign in from of the second condition?

SELECT PlanID, SalesManID,
SUM(CASE WHEN Transactiontype=0 THEN credit WHEN Transactiontype=1 THEN-credit ELSE 0.0 END) as Balance
From YourTable
Group By PlanID,SalesManID

If it still doesn't work then try this as a diagnostic. Just run it in query analyzer to see what is going on. You should just see the unaggregated values with some have positive values corresponding to transactiontype=0 and negative for transactiontype=1.
SELECT PlanID, SalesManID,Transactiontype,
CASE WHEN Transactiontype=0 THEN credit WHEN Transactiontype=1 THEN -credit ELSE 0.0 END as Balance
From YourTable
</code>