How to simplify my endless query. Thanks.

[rong.guo]

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.acc ount
group by a.person_id, a.account

[Erland Sommarskog]

(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.acc ount
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****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[rong.guo]

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!

[Ross Presser]

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.acc ount
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.