Help updating Data - SQL Server Forums

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

Transact-SQL (2005)

Help updating Data

Author

Topic

bmahony993
Yak Posting Veteran

58 Posts

Posted - 2008-06-24 : 11:47:23

I have posted on this problem before, and got some wonderful help, but the problems keep growing!

In my table, I have projects,activities, credit amount, debit amount, starting balance.
Each project can have several activities associated with it. Each project has a starting balance. Each activity posts an expense to the starting balance of the project.
If the project has enough money to handle the charges made by the activities, all the activity expenses can be "posted". Then I want to re-adjust the balance and
check the second activity. If there is still enough money to handle the charge from the second activity, then the expenses can be posted and the balance
adjusted again. In this checking, I am creating a field called status, and flagging if the activity can clear or not, and a new balance field. The data I have can fall into
different examples, listed below.

First example:Only one activity for a project
project: 122400
activity: 0000
Cr_Amt: 2145.00
Dr_Amt: 0
Balance: 1190.00

In the above case, as the balance is less than the Cr_Amt, I want to set the Cr_Amt = Balance, and the new balance = 0 and have done that as follows:

Update r
set r.Post_Cr = r.Bal_2300,
r.new_Status = 'Can Clear',
r.new_Balance = 0
From Rev_Rec_Check r
WHERE r.project IN (Select project
from Rev_Rec_Check d
group by d.Project
having count(d.project) = 1)

That seems to work for me.

Second Example: More than one activity, with credits and debits
project: 145587
activity: 0000
Cr_Amt: 0
Dr_Amt: 2500
Balance: 1452

project: 145587
activity: 0110
Cr_Amt: 3953
Dr_Amt: 0
Balance: 1452

So, in this case, I need to add the Dr_Amt to the Balance and create a new balance (3952), and then check that the Balance can cover the Cr_Amt

UPDATE t
SET t.New_Balance=(t.Bal_2300 + b.postDrSum) - b.PostCRSum,
t.New_Status=(CASE WHEN (t.Bal_2300 -b.PostCRSum) >0 THEN 'Can Clear'
ELSE 'Still Check'
END)
FROM Rev_Rec_Check t CROSS APPLY
(SELECT SUM(post_Cr) as postCrSum, SUM(post_Dr) as postDrSum
FROM Rev_Rec_Check a
WHERE (a.project =t.project)
AND (a.activity<=t.activity)) b

This does seems to work, and gives me the correct status, and adjusts the new balance correctly, but I realize that doing that doesn't achieve what I want.
Ideally, in this example - where there is more than one project/activity, this is what I would like to see:
project: 145587
activity: 0000
Cr_Amt: 0
Dr_Amt: 2500
Balance: 1452
newBalance: 3952

project: 145587
activity: 0110
Cr_Amt: 3952
Dr_Amt: 0
Balance: 1452
newBalance: 0

I want the amount that gets posted (the Cr_Amt) to reflect what is left in the balance.

There could be cases where there are several activities for one project, so I want to be able to scroll through each activity and post what I can from what
they have left in their balance.

I don't know how to approach this. Am I making it more complicated than it actually is? Have I made any sense in trying to explain it?

Thanks in advance for any help!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-25 : 01:51:37

Do you mean you need Cr_Amt to become NewBalance of prev activity record?

bmahony993
Yak Posting Veteran

58 Posts

Posted - 2008-06-25 : 17:13:56

Thats correct - until the newBalance runs out!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-26 : 01:55:44

[code]UPDATE r
SET r.Cr_Amt =b.Bal_2300+c.postDrSum-c.postCrSum
FROM Rev_Rec_Check r
OUTER APPLY(SELECT TOP 1 Bal_2300
FROM Rev_Rec_Check
WHERE project =r.project
AND activity<r.activity
ORDER BY activity DESC) b
OUTER APPLY(SELECT SUM(post_Cr) as postCrSum, SUM(post_Dr) as postDrSum
FROM Rev_Rec_Check
WHERE (project =b.project)
AND (activity<=b.activity))c [/code]

bmahony993
Yak Posting Veteran

58 Posts

Posted - 2008-06-26 : 11:57:12

I get an error message when I try that - saying that project and activity are invalid columns. I think I must have a typo somewhere but for the life of me I can't find it. Try this test code and see if its the same

Create table #TestData(
project varchar(6),
activity varchar(4),
post_Cr decimal(12,4) default 0,
post_Dr decimal(12,4) default 0,
Bal_2300 decimal(12,4) default 0,
new_Status varchar(12),
new_Balance decimal(12,4) default 0
)

Insert into #TestData Values('127004','0000','2341','0','1911',NULL,NULL)
Insert into #TestData Values('127300','0500','160.70','0','-146663.15',NULL,NULL)
Insert into #TestData Values('127300','0600','0.37','0','-146663.15',NULL,NULL)
Insert into #TestData Values('127300','0100','8104.68','0','-146663.15',NULL,NULL)
Insert into #TestData Values('133515','0000','0','2520.91','3629',NULL,NULL)
Insert into #TestData Values('133515','0105','6150.36','0','3629',NULL,NULL)
Insert into #TestData Values('187200','0000','170.57','0','5487',NULL,NULL)
Insert into #TestData Values('187200','0010','2215.03','0','5487',NULL,NULL)
Insert into #TestData Values('187200','0100','1004.68','0','5487',NULL,NULL)
Insert into #TestData Values('187200','0110','3415.27','0','5487',NULL,NULL)

UPDATE r
SET r.Post_Cr = b.Bal_2300 + c.postDrSum - c.postCrSum
FROM #TestData r
OUTER APPLY(SELECT TOP 1 Bal_2300
FROM #TestData
WHERE (project = r.project) AND (activity < r.activity)
ORDER BY activity DESC
)b
OUTER APPLY(SELECT SUM(post_Cr) as postCrSum, SUM(post_Dr) as postDrSum
FROM #TestData
WHERE (project = b.project)AND (activity <= b.activity)
)c

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-26 : 12:32:00

quote:
Originally posted by bmahony993

I get an error message when I try that - saying that project and activity are invalid columns. I think I must have a typo somewhere but for the life of me I can't find it. Try this test code and see if its the same

Create table #TestData(
project varchar(6),
activity varchar(4),
post_Cr decimal(12,4) default 0,
post_Dr decimal(12,4) default 0,
Bal_2300 decimal(12,4) default 0,
new_Status varchar(12),
new_Balance decimal(12,4) default 0
)

Insert into #TestData Values('127004','0000','2341','0','1911',NULL,NULL)
Insert into #TestData Values('127300','0500','160.70','0','-146663.15',NULL,NULL)
Insert into #TestData Values('127300','0600','0.37','0','-146663.15',NULL,NULL)
Insert into #TestData Values('127300','0100','8104.68','0','-146663.15',NULL,NULL)
Insert into #TestData Values('133515','0000','0','2520.91','3629',NULL,NULL)
Insert into #TestData Values('133515','0105','6150.36','0','3629',NULL,NULL)
Insert into #TestData Values('187200','0000','170.57','0','5487',NULL,NULL)
Insert into #TestData Values('187200','0010','2215.03','0','5487',NULL,NULL)
Insert into #TestData Values('187200','0100','1004.68','0','5487',NULL,NULL)
Insert into #TestData Values('187200','0110','3415.27','0','5487',NULL,NULL)

UPDATE r
SET r.Post_Cr = b.Bal_2300 + c.postDrSum - c.postCrSum
FROM #TestData r
OUTER APPLY(SELECT TOP 1 Bal_2300
FROM #TestData
WHERE (project = r.project) AND (activity < r.activity)
ORDER BY activity DESC
)b
OUTER APPLY(SELECT SUM(post_Cr) as postCrSum, SUM(post_Dr) as postDrSum
FROM #TestData
WHERE (project = b.project)AND (activity <= b.activity)
)c

sorry it should be this:-

UPDATE r
SET r.Cr_Amt =b.Bal_2300+c.postDrSum-c.postCrSum
FROM Rev_Rec_Check r
OUTER APPLY(SELECT TOP 1 Bal_2300,project,activity
            FROM Rev_Rec_Check 
            WHERE project =r.project
            AND activity<r.activity
            ORDER BY activity DESC) b
OUTER APPLY(SELECT SUM(post_Cr) as postCrSum, SUM(post_Dr) as postDrSum
FROM Rev_Rec_Check 
WHERE (project =b.project)
AND (activity<=b.activity))c

bmahony993
Yak Posting Veteran

58 Posts

Posted - 2008-06-26 : 13:03:14

Thank you so much!! That works perfectly!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources