469,600 Members | 2,393 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

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

How to simplify my endless query. Thanks.

Hello group!

I am having a problem with simplying my query...

I would like to get customers' balance info based on how many months
since they opened their accounts. The tricky part here is accounts
starting with '28' are treated differently than other accounts, they
are given 3 months grace period. In other words, for all other
accounts, their month0 balance is the balance of their open_month, and
month1 balance is the balance after the account is opened 1 month, and
so on. But accounts starting with '28' month0 balance would be the
balance after the account is opened 3 months, and month1 balance would
be the balance after the account is opened 4 months, and so on.

My query below works, but since some customers are more than 10 years
old (more than 120 months), my query is endless! Does anyone know a
better way to do the same job? Many thanks!

create table a
(person_id int
,account int
,open_date datetime)

insert into a values(1,200001,'11/15/2004')
insert into a values(2,280001,'8/20/2004')

create table b
(account int
,balance_date datetime
,balance money)

insert into b values(200001,'11/30/2004',700)
insert into b values(200001,'12/31/2004',800)
insert into b values(200001,'1/31/2005',900)
insert into b values(200001,'2/28/2005',1000)
insert into b values(280001,'8/30/2004',7000)
insert into b values(280001,'9/30/2004',8000)
insert into b values(280001,'10/31/2004',9000)
insert into b values(280001,'11/30/2004',10000)
insert into b values(280001,'12/31/2004',15000)
insert into b values(280001,'1/31/2005',20000)
insert into b values(280001,'2/28/2005',30000)

--Ideal output--

person_id acc_no month0_balance month1_balance month2_balance month3_balance
1 2000001 700 800 900 1000
2 2800001 10000 15000 20000 30000

select a.person_id
,a.account
,month0_balance=case
when a.account like '2%' and a.account not like '28%'
then
sum(case datediff(mm, a.open_date, balance_date) when 0
then b.balance else 0 end)
else sum(case datediff(mm, a.open_date, balance_date)
when 3 then b.balance else 0 end)
end
,month1_balance =case
when a.account like '2%' and a.account not like '28%'
then
sum(case datediff(mm, a.open_date, balance_date) when 1
then b.balance else 0 end)
else sum(case datediff(mm, a.open_date, balance_date)
when 4 then b.balance else 0 end)
end
from a as a
join b as b
on a.account=b.account
group by a.person_id, a.account

Jul 23 '05 #1
3 1335
(ro******@gmail.com) writes:
My query below works, but since some customers are more than 10 years
old (more than 120 months), my query is endless! Does anyone know a
better way to do the same job? Many thanks!


Hm, first I read endless as "query runs forever", but now I realize
that you want one column for each month.

That's a dynamic cross-tab, which you cannot to in SQL out-of-box,
since a query always produces a table with a well-defined set of
columns.

You could use dynamic SQL to create the beast; you can read about
dynamic SQL on my web site: http://www.sommarskog.se/dynamic_sql.html.
You may also consider the third-party tool RAC, which is very good
for this sort of things. (They say. I have never used it myself, but
it's a standard recommendation.) It's at http://www.rac4sql.net/.

Since I first misread your question, I looked at improving the query.
Since this is a simple join, good indexing is probably the best for
performance. But this maybe somewhat more effective:

select a.person_id, a.account,
month0_balance = SUM(CASE datediff(mm, a.open_date, balance_date)
WHEN CASE WHEN a.account LIKE '2%' AND
a.account NOT LIKE '28%'
THEN 0
ELSE 3
END THEN b.balance
ELSE 0
END),
month1_balance = SUM(CASE datediff(mm, a.open_date, balance_date)
WHEN CASE WHEN a.account LIKE '2%' AND
a.account NOT LIKE '28%'
THEN 1
ELSE 4
END THEN b.balance
ELSE 0
END)
from a as a
join b as b on a.account=b.account
group by a.person_id, a.account

(Egads! I have never had a CASE in the WHEN part of a CASE expression
before!)

--
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!
Sorry for the misleading word 'endless'... And you are right, I've
never had a CASE in the WHEN part of a CASE expression:-)) but it is so
exciting to learn! Thanks again!

Jul 23 '05 #3
On Fri, 18 Mar 2005 23:20:17 +0000 (UTC), Erland Sommarskog wrote:
(ro******@gmail.com) writes:
My query below works, but since some customers are more than 10 years
old (more than 120 months), my query is endless! Does anyone know a
better way to do the same job? Many thanks!


Hm, first I read endless as "query runs forever", but now I realize
that you want one column for each month.

That's a dynamic cross-tab, which you cannot to in SQL out-of-box,
since a query always produces a table with a well-defined set of
columns.


I would start with this (perhaps as a view) to collect the monthly balances
and do the 3 month correction for 28s:

select a.person_id, a.account,
sum(b.balance) AS MonthBalance,
'Month' + convert(varchar(),
datediff(mm, a.open_date, balance_date) -
case when a.account LIKE '2%' AND a.account NOT like '28%' then 0
else 3 END)
AS MonthNum
FROM a as a
JOIN b as b on a.account=b.account
GROUP BY a.person_id, a.account,
datediff(mm, a.open_date, balance_date) -
case when a.account LIKE '2%' AND a.account NOT like '28%' then 0
else 3 END
Then I'd put a crosstab tool on the client to consume this.
Jul 23 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by Herr Lukas | last post: by
30 posts views Thread by Skybuck Flying | last post: by
73 posts views Thread by Claudio Grondi | last post: by
3 posts views Thread by Bob Bedford | last post: by
5 posts views Thread by Jon Skeet [C# MVP] | last post: by
4 posts views Thread by guiromero | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.