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?
 
PortletPaulConnect With a Mentor freelancerCommented:
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
 
PortletPaulfreelancerCommented:
What database is this for? MySQL? SQL Server? Oracle? ...
This information is vital so we propose the correct syntax
0
 
PortletPaulfreelancerCommented:
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
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

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

From novice to tech pro — start learning today.