• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 212
  • Last Modified:

How do I create this update statement?

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
JohnnyBCJ
Asked:
JohnnyBCJ
  • 3
  • 2
1 Solution
 
GuitarRichCommented:
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
 
GuitarRichCommented:
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
 
JohnnyBCJAuthor Commented:
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
 
GuitarRichCommented:
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
 
JohnnyBCJAuthor Commented:
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
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.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now