Please help, problem with update query.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Please help, problem with update query.

Author

Topic

jamesrah
Starting Member

13 Posts

Posted - 2010-05-17 : 05:13:25

Hi,

I hope someone can help?

I am writing an sp and have 3 tables:

--Table shows the last 4 weeks - perenddate (4 different dates), tncynumber (customer ID), Dateinbalance (the last date the customer was in credit), [bal-val] (is the amount of debt per customer)

create table #temptable
(tncynumber int,
perenddate datetime null,
Dateinbalance datetime null,
[bal-val] int)

--Table shows period counts for which is defined in the sp by choosing 1 of the 4 week end dates. Shows the number/count of customers in debt less than three months, threetosixmonth, etc for say the end of week 1 date (perenddate )or week end date 2 (perenddate) or week end 3 (perenddate ) or week end date 4 (perenddate). The week chosen is defined in sp and works okay.
Create table #periodscount
(periodsnumber varchar(25) null,
lessthreemonthsmonth int null,
threetosixmonth int null,
sixtoninemonth int null,
ninetotwelvemonth int null,
twelvetotwentyfourmonth int null,
twentyfourtotirtysixmonth int null,
overthirtysixmonth int null)

--Table field age_of_debt content is filled with the following and does not change:
Less than 3 months
3 to 6 months
6 to 9 months
9 to 12 months
12 to 24 months
24 to 36 months
Over 36 months

period_end_date is tmp.perenddate which is one of the 4 week ending dates (defined by the sp using an input field from buisness objects), so will all be the same date. number_of_tenancies is the count for each period on the #periodscount table. amount_of_debt is the [bal-val] on the #temptable.

create table graph
(age_of_debt varchar(30),
period_end_date datetime,
number_of_tenancies int,
amount_of_debt int)

I would like to update the graph table field ‘amount_of_debt’ with the [bal-val] in the #temptable. However as I am joining on the ‘period_end_date’ in the temptable and the perendate on the graph table is the same for all ‘age_of_debts’ (example – lessthreemonthsmonth, threetosixmonth, sixtoninemonth, etc) the update is updating all 7 rows. How can I update just one row (lessthreemonthsmonth), then the next row (threetosixmonth), etc.

Update graph
set amount_of_debt =
(select sum(tmp.[bal-val])
from #temptable as tmp join graph
On tmp.perenddate = period_end_date
join #periodscount
on graph.number_of_tenancies = #periodscount.lessthreemonthsmonth
where tmp.dateinbalance > dateadd(m,-3, tmp.perenddate)
Group by graph.age_of_debt)

Your time and help is much appreciated..

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-17 : 12:36:32

can you restate your requirement in below format giving some sample data and desired output?

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

jamesrah
Starting Member

13 Posts

Posted - 2010-05-19 : 04:57:03

Thanks for the reply!

I have managed to resolve the issue by using temp tables for each period and using them to update the over all graph table.

Cheers,
James

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources