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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.