Getting MAX Date from a table--that needs joined to another table

I'm trying to update a SQL table with the max immunization date for every person in the table.  Here is my table I've made:

person_id                    last_name               first_name                  rendering_provider_id                immunization_date

Immunization_date is the column I want to update.  The location of that date is in the following table;

Imm_order_vaccines.create_timestamp

Unfortunately, that table doesn't have a person_id column to tie it up with my existing table.  Instead it's primary key is a column called order_num, which I then tie with another immunization table named Imm_nor as it does have a person_id column.  So I tied those tables together and tried running an update using MAX for fun (I knew this would update every column with the same date but was just making sure the joins would work).  Here is that code;


UPDATE FM_Influenza_Reporting 
SET immunization_date = (
select MAX(imm_order_vaccines.create_timestamp) from imm_order_vaccines
JOIN imm_nor ON imm_nor.order_num = imm_order_vaccines.order_num
JOIN appointments ON appointments.enc_id = imm_nor.enc_id
JOIN FM_Influenza_Reporting ON FM_Influenza_Reporting.person_id = imm_nor.person_id
where imm_nor.person_id = FM_Influenza_Reporting.person_id and imm_order_vaccines.vaccine_desc like '%Influenza%' )

Open in new window



The problem as listed above is--it updates the column with the max date from any person (so it's the same value all the way down).  I know I need to do an INNER JOIN statement with a select max clause, something like this;

INNER JOIN (select EXAMPLETABLE.person_id, MAX(EXAMPLETABLE.create_timestamp) AS MAXDATE from EXAMPLETABLE
			group by EXAMPLETABLE.person_id) m
			ON pe.create_timestamp = m.MAXDATE and m.person_id = e.person_id

Open in new window


....the issue is, the MAXDATE join there doesn't have a person_id to tie it nicely--and that's where I'm stuck.  So to reiterate;

imm_order_vaccines.create_timestamp = the date I'm trying to update a table with based on person_id
imm_nor.person_id = the table/column that houses the person_id I can link up with
imm_nor.order_num = imm_order_vaccines.order_num is the link I can make between those two tables

So how do I get that max date, by person_id and update my existing table?  Hope that makes sense and thanks for any help.
CMCITDAsked:
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.

PortletPaulEE Topic AdvisorCommented:
What database is this for? MySQL? SQL Server? Oracle? ...
This information is vital so we propose the correct syntax
0
PortletPaulEE Topic AdvisorCommented:
Without knowing which database you use I will offer an approach that should be viable for MS SQL Server:
WITH
      cte AS (
                  SELECT
                        imm_nor.person_id
                      , MAX(imm_order_vaccines.create_timestamp) max_flu_date
                  FROM imm_order_vaccines
                  JOIN imm_nor ON imm_nor.order_num = imm_order_vaccines.order_num
                  JOIN appointments ON appointments.enc_id = imm_nor.enc_id
                  JOIN FM_Influenza_Reporting ON FM_Influenza_Reporting.person_id = imm_nor.person_id
                  WHERE imm_nor.person_id = FM_Influenza_Reporting.person_id
                  AND imm_order_vaccines.vaccine_desc LIKE '%Influenza%'
                  GROUP BY
                        imm_nor.person_id
            )
UPDATE cte 
SET immunization_date = max_flu_date

Open in new window

Note that -as yet- MySQL doesn't support "WITH", and that the above suggestion probably would not work in Oracle , so if it does not work it is very likely t be due to the database needing a different syntax.
0
CMCITDAuthor Commented:
Sorry was gone for weekend.

It's SQL Server 2008 R2.

When trying to run--I'm getting an Invalid column name 'immunization_date'.  I ran the select statement separate and the data appears correct, just need help with the update syntax.  Thanks so much!
0
Amazon Web Services

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

PortletPaulEE Topic AdvisorCommented:
Sorry. Please try this. but make sure you use a select clause to verify before you apply the update

WITH
      cte AS (
            SELECT 
                  fmir.person_id
                , fmir.immunization_date
                , g.max_flu_date
            FROM FM_Influenza_Reporting AS fmir
            INNER JOIN (
                      SELECT
                            imm_nor.person_id
                          , MAX(imm_order_vaccines.create_timestamp) max_flu_date
                      FROM imm_order_vaccines
                      JOIN imm_nor ON imm_nor.order_num = imm_order_vaccines.order_num
                      JOIN appointments ON appointments.enc_id = imm_nor.enc_id
                      JOIN FM_Influenza_Reporting ON FM_Influenza_Reporting.person_id = imm_nor.person_id
                      WHERE imm_nor.person_id = FM_Influenza_Reporting.person_id
                      AND imm_order_vaccines.vaccine_desc LIKE '%Influenza%'
                      GROUP BY
                            imm_nor.person_id
                        ) AS g on fmir.person_id = g.person_id
            )
select * 
from cte 
;

Open in new window

If this appears correct then proceed.

WITH
      cte AS (
            SELECT 
                  fmir.person_id
                , fmir.immunization_date
                , g.max_flu_date
            FROM FM_Influenza_Reporting AS fmir
            INNER JOIN (
                      SELECT
                            imm_nor.person_id
                          , MAX(imm_order_vaccines.create_timestamp) max_flu_date
                      FROM imm_order_vaccines
                      JOIN imm_nor ON imm_nor.order_num = imm_order_vaccines.order_num
                      JOIN appointments ON appointments.enc_id = imm_nor.enc_id
                      JOIN FM_Influenza_Reporting ON FM_Influenza_Reporting.person_id = imm_nor.person_id
                      WHERE imm_nor.person_id = FM_Influenza_Reporting.person_id
                      AND imm_order_vaccines.vaccine_desc LIKE '%Influenza%'
                      GROUP BY
                            imm_nor.person_id
                        ) AS g on fmir.person_id = g.person_id
            )
UPDATE cte 
SET immunization_date = max_flu_date
;

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
CMCITDAuthor Commented:
Nailed it, thank you so much!
0
CMCITDAuthor Commented:
As always, you rock Paul
0
PortletPaulEE Topic AdvisorCommented:
You are too kind! (I should have nailed it the first time... grrr)
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
SQL

From novice to tech pro — start learning today.