469,275 Members | 1,561 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,275 developers. It's quick & easy.

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_no open_date
1111 1/1/2000
2222 2/1/2002

Table Balance
account_no bal bal_date
1111 50 1/31/2000
1111 100 2/29/2000
1111 150 3/31/2000
1111 200 4/30/2000
1111 250 5/31/2000
1111 300 6/30/2000
1111 350 7/31/2000
1111 400 8/31/2000
2222 500 2/29/2002
2222 600 3/31/2002
2222 700 4/30/2002
2222 800 5/31/2002
2222 900 6/30/2002

Ideal Result Table
account_no open_date month0_bal month1_bal month2_bal month3_bal month4_bal
1111 1/1/2000 50 100 150 200 250
2222 2/1/2002 500 600 700 800 900

--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

Jul 23 '05 #1
2 1081
(ro******@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, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #2
Thanks Erland, I will look into it...

Jul 23 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

9 posts views Thread by netpurpose | last post: by
7 posts views Thread by Simon Bailey | last post: by
36 posts views Thread by Liam.M | last post: by
4 posts views Thread by Doris | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.