How to update a table based on previous process da

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)

How to update a table based on previous process da

Author

Topic

pras2007
Posting Yak Master

216 Posts

Posted - 2009-10-04 : 15:29:50

Hello All,

I have been thinking of the best way to solve my problem and I need help. The sample table below illustrates the final output. Based on certain criteria I will update the table accordingly…
First I will look at the processdate field, doing so I can determine the previous record populate into that table. The problem is that the processdate field is not in sequence meaning that I can have anywhere 1 – 6 months difference between pervious process dates. I need a way to figure the previous processdate programmatically.
Second, once I determine the previous process date I will compare the record, account_num, and INC fields to determine if it is a new entry to the table if so, I will update the following:

col_1_rev = col_1
col_2_rev = col_2
col_3_rev = col_3

If record, account_num, and INC fields is not a new entry I will do the following:

col_1_rev = 0
col_2_rev = 0
col_3_rev = 0

Does anyone know how to accomplish this task? Please advice.

Thanks.

pras2007
Posting Yak Master

216 Posts

Posted - 2009-10-04 : 15:31:39

The result table is below:

ProcessDate Record account_Num INC Col_1 Col_2 Col_3 Col_1_Rev Col_2_Rev Col_3_Rev
7/31/2007 273 03001S 0 950 50 1000 950 50 1000
8/31/2007 273 03001S 0 950 50 1000 0 0 0
9/30/2007 273 03001S 0.5 8000 0 8000 8000 0 8000
11/30/2007 273 03001S 1 200 100 300 200 100 300
2/28/2008 273 03001S 1 200 100 300 0 0 0

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-10-05 : 14:19:21

[code]UPDATE t
SET t.Col_1_Rev=CASE WHEN t1.Col1 IS NULL THEN t.Col_1 ELSE 0 END,
t.Col_2_Rev=CASE WHEN t1.Col2 IS NULL THEN t.Col_2 ELSE 0 END,
t.Col_3_Rev=CASE WHEN t1.Col3 IS NULL THEN t.Col_3 ELSE 0 END
FROM table t
OUTER APPLY (SELECT TOP 1 Col_1,Col_2,Col_3
FROM Table
WHERE record =t.record
AND account_num=t.account_num
AND INC=t.INC
AND ProcessDate < t.ProcessDate
ORDER BY ProcessDate DESC)t1
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources