SQL Server 2005

How can I write a procedure to update the DealStatus as show in the "Change to" column in the attached file? The rule is the DealStatus for APC & DRG record need to be the same as the CTR record if they're in the same Year. But what hard is on the last record (purple highlighted), the APC trend on 1/1/13 should also be changed to FIN (or whatever the DealStatus that is showing on the record right above it, the sort is Par_id, TypeOfContract, ActEffDt acending) since it occurs before the next OPEN CTR record. I need to perform this update on the table that have many tax ids about 20,000 records.






Test.doc
HNA071252Asked:
Who is Participating?
 
dougaugCommented:
See if this works for you:

 
update YourTable
   set DealStatus = (select case when t1.Trend_type = 'APC' and t2.DealStatus = 'OPEN' then 'FIN'
                                 else DealStatus
                            end
                       from YourTable t2
                      where t2.Trend_type = 'CTR' and t1.Year = t2.Year) 
  from YourTable t1
 where Trend_type in ('APC', 'DRG') and DealStatus = 'OPEN'

Open in new window

update YourTable
   set DealStatus = (select case when t1.Trend_type = 'APC' and t2.DealStatus = 'OPEN' then 'FIN'
                                 else DealStatus
                            end
                       from YourTable t2
                      where t2.Trend_type = 'CTR' and t1.Year = t2.Year) 
  from YourTable t1
 where Trend_type in ('APC', 'DRG') and DealStatus = 'OPEN'

Open in new window

0
 
HNA071252Author Commented:
No, it's not. You cannot hard code the criteria of 'FIN' or 'OPEN' because there're other values... The rule is DealStatus for APC & DRG record need to be the same as the DealStatus on the CTR record if they're in the same Year.
0
 
HNA071252Author Commented:
I think I would need 3 update queries:

1. Update DealStatus on APC records (yellow & green) to be the same as the DealStatus on CTR record if they're in the same year.

2. Update DealStatus on DRG record (yellow & green) to be the same as the DealStatus on CTR record if they're in the same year.


3. Update the APC/DRG record (purple) to have the same DealStatus that is showing on the record right above it. The rule here is:
        a. Sort the table by Par_id, TypeOfContract, ActEffDt ascending
        b. Find the first CTR record that have OPEN DealStatus (which is #12)
        c. Check the record right above the record found in (b)  (which is #11), if it's APC or DRG and in the same year (meaning if #11 is APC or DRG and in the same year as #12), then set the DealStatus to be the same as the DealStatus of the record right above it (which is #10)
0
 
HNA071252Author Commented:
Please see the attach to support what I've just explained.
DealStatus-update.xls
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.