Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

How do I create this update statement?

Posted on 2009-04-28
5
Medium Priority
?
207 Views
Last Modified: 2012-05-06
I have 3 tables with the follow fields.

1.Clients
          SIN (Social Insurance Number), Primary Key.
          OFFICE

2. Disbursements
          SIN
          OFFICE
          CKDATE

3. tblARInvoices
          SIN
          OFFICE
          Invoice_Date

On the Clients table, the office field is currently null. This is the field I need to update.

It is possible that clients have multiple Disbursements and multiple AR Invoices.

I need to update the clients (office) field with the office value of whatever table has the most recent date (CKdate or Invoice_Date).

I would attempt to do this in 3 steps.
1. Figure out the script to get the latest office (according to CKDATE) from the Disbursements.
2. Figure out the script to get the latest office (according to Invoice_Date) from tblARInvoices.
3. Combine 1 + 2 in an update statement to solve the issue.

Any help would be greatly appreciated.

0
Comment
Question by:JohnnyBCJ
  • 3
  • 2
5 Comments
 
LVL 12

Expert Comment

by:GuitarRich
ID: 24250141
try this:
with officeCTE (SIN, Office, LookupDate)
as
(
select sin, office, ckdate
from OFFICE
union all
select sin, office, invoice_date
from INVOICE
)
update Clients 
    set Office = officeCTE.Office 
    from officeCTE
    where officeCTE.SIN = Clients.SIN
    and lookupdate = (select max(lookupdate) from officeCTE where officeCTE.SIN = Clients.SIN)

Open in new window

0
 
LVL 12

Accepted Solution

by:
GuitarRich earned 2000 total points
ID: 24250155
oops - didn't read the table names properly - try this:
with officeCTE (SIN, Office, LookupDate)
as
(
select sin, office, ckdate
from Disembursments
union all
select sin, office, invoice_date
from tblARInvoices
)
update Clients 
    set Office = officeCTE.Office 
    from officeCTE
    where officeCTE.SIN = Clients.SIN
    and lookupdate = (select max(lookupdate) from officeCTE where officeCTE.SIN = Clients.SIN)

Open in new window

0
 

Author Comment

by:JohnnyBCJ
ID: 24250313
Seems to me did a good job but I forgot to left out one important detail. Some Clients may only have a disbursement or an AR Invoice record but not both. I feel this is the reason why not all the records got updated?
0
 
LVL 12

Expert Comment

by:GuitarRich
ID: 24250361
that script should still pull out all the records, the union all wil pull all records from both tables. If a client record has not been updated check that there is definitely a record in one of the other 2 tables for that client. Also check that there are not any records that have a date but the office is null. This may be causing some records not to update. To get around that you could add this line to the bottom:
and officeCTE.Office is not null
0
 

Author Closing Comment

by:JohnnyBCJ
ID: 31575417
Thank you very much! I greatly appreciate it!
You are correct, there are client records that have no AR Invoice or Disbursement records. Great Job!!!
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There are some very powerful Dynamic Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a di…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…
Whether it be Exchange Server Crash Issues, Dirty Shutdown Errors or Failed to mount error, Stellar Phoenix Mailbox Exchange Recovery has always got your back. With the help of its easy to understand user interface and 3 simple steps recovery proced…

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question