Update table with result from a query

Hi,

I have a query that produces the most recent date that a document was signed within my database. I need to push that date through a stored procedure and into another table/column.

The query that produces the date is:

SELECT
    Name, MAX(maxxDate) AS MaxxDate
FROM (
    SELECT 
    ((p.last_name+', '+p.first_name+' '+p.middle_name)) Name
    , ((convert(varchar, convert(datetime, (SELECT max(v) 
       FROM (VALUES (paq.create_timestamp)
                            , (txp.init_tx_plan_signed)
                            , (txp.tx_plan_rev_signed_1)
                            , (txp.tx_plan_rev_signed_2)
                            , (txp.tx_plan_rev_signed_3)
                            , (txp.tx_plan_rev_signed_4)
                            , (txp.tx_plan_rev_signed_5)
                            , (txp.tx_plan_rev_signed_6)) AS value(v))), 101))) as [maxxDate]
     FROM   
     sftu_JOE_TX_PLAN_HX txp 
     FULL OUTER JOIN person p 
     ON txp.person_id=p.person_id
     LEFT OUTER JOIN patient_status ps 
     ON p.person_id=ps.person_id
     LEFT OUTER JOIN patient_documents pd 
     ON p.person_id=pd.person_id
     LEFT OUTER JOIN patient_provider pp 
     ON p.person_id=pp.person_id
     LEFT OUTER JOIN paq_signoff_history paq 
     ON pd.person_id=paq.person_id AND 
     pd.enc_id=paq.enc_id AND 
     pd.document_id=paq.item_id
     LEFT OUTER JOIN signatures s 
     ON pd.document_id=s.doc_id
     LEFT OUTER JOIN patient_encounter pe 
     ON pd.enc_id=pe.enc_id AND 
     pd.person_id=pe.person_id
     LEFT OUTER JOIN patient_status_mstr psm 
     ON ps.patient_status_id=psm.patient_status_id
     WHERE  
     paq.signoff_action='A' AND 
     pp.provider_id='ADB69024-4B7A-4B75-A9A6-BA2695632603' AND 
     psm.description='Active' AND 
     paq.create_timestamp>={ts '2012-03-20 00:00:01'} AND 
     (pd.template_file='bh_master_treatment' OR pd.template_file='sftubh_treatment_plan_review')
) AS derived
group by name
order by name

Open in new window


I would like to use an Update/Set to push the maxxDate to a variable.
LVL 1
susnewyorkAsked:
Who is Participating?
 
YZlatCommented:
looks right to me
0
 
YZlatCommented:
use INSERT INTO SELECT FROM
0
 
YZlatCommented:
INSERT INTO table1 ( column1, column2 )
SELECT
    Name, MAX(maxxDate) AS MaxxDate
FROM (
    SELECT
    ((p.last_name+', '+p.first_name+' '+p.middle_name)) Name
    , ((convert(varchar, convert(datetime, (SELECT max(v)
       FROM (VALUES (paq.create_timestamp)
                            , (txp.init_tx_plan_signed)
                            , (txp.tx_plan_rev_signed_1)
                            , (txp.tx_plan_rev_signed_2)
                            , (txp.tx_plan_rev_signed_3)
                            , (txp.tx_plan_rev_signed_4)
                            , (txp.tx_plan_rev_signed_5)
                            , (txp.tx_plan_rev_signed_6)) AS value(v))), 101))) as [maxxDate]
     FROM  
     sftu_JOE_TX_PLAN_HX txp
     FULL OUTER JOIN person p
     ON txp.person_id=p.person_id
     LEFT OUTER JOIN patient_status ps
     ON p.person_id=ps.person_id
     LEFT OUTER JOIN patient_documents pd
     ON p.person_id=pd.person_id
     LEFT OUTER JOIN patient_provider pp
     ON p.person_id=pp.person_id
     LEFT OUTER JOIN paq_signoff_history paq
     ON pd.person_id=paq.person_id AND
     pd.enc_id=paq.enc_id AND
     pd.document_id=paq.item_id
     LEFT OUTER JOIN signatures s
     ON pd.document_id=s.doc_id
     LEFT OUTER JOIN patient_encounter pe
     ON pd.enc_id=pe.enc_id AND
     pd.person_id=pe.person_id
     LEFT OUTER JOIN patient_status_mstr psm
     ON ps.patient_status_id=psm.patient_status_id
     WHERE  
     paq.signoff_action='A' AND
     pp.provider_id='ADB69024-4B7A-4B75-A9A6-BA2695632603' AND
     psm.description='Active' AND
     paq.create_timestamp>={ts '2012-03-20 00:00:01'} AND
     (pd.template_file='bh_master_treatment' OR pd.template_file='sftubh_treatment_plan_review')
) AS derived
group by name
order by name
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
YZlatCommented:
if you want to perform update, you can do something like

UPDATE  Table
SET  Table.col1 = other_table.col1,    
Table.col2 = other_table.col2
FROM Table INNER JOIN  other_table ON Table.id = other_table.id
0
 
susnewyorkAuthor Commented:
UPDATE  Table
SET  Table.col1 = other_table.col1,    
Table.col2 = other_table.col2
FROM Table INNER JOIN  other_table ON Table.id = other_table.id


How would I use my query as the condition of the update statement? I need to besure that the date produced from my query is the date that gets pushed into SharedDate.

Example:

Update table1
set SharedDate= MAXXDATE
where <insert my query here>
0
 
YZlatCommented:
somthing like this should work:

UPDATE Table1 
SET Table1.col1 = MAX(derived.maxxDate) 
FROM Table1 INNER JOIN
(SELECT 
    ((p.last_name+', '+p.first_name+' '+p.middle_name)) Name
    , ((convert(varchar, convert(datetime, (SELECT max(v) 
       FROM (VALUES (paq.create_timestamp)
                            , (txp.init_tx_plan_signed)
                            , (txp.tx_plan_rev_signed_1)
                            , (txp.tx_plan_rev_signed_2)
                            , (txp.tx_plan_rev_signed_3)
                            , (txp.tx_plan_rev_signed_4)
                            , (txp.tx_plan_rev_signed_5)
                            , (txp.tx_plan_rev_signed_6)) AS value(v))), 101))) as [maxxDate]
     FROM   
     sftu_JOE_TX_PLAN_HX txp 
     FULL OUTER JOIN person p 
     ON txp.person_id=p.person_id
     LEFT OUTER JOIN patient_status ps 
     ON p.person_id=ps.person_id
     LEFT OUTER JOIN patient_documents pd 
     ON p.person_id=pd.person_id
     LEFT OUTER JOIN patient_provider pp 
     ON p.person_id=pp.person_id
     LEFT OUTER JOIN paq_signoff_history paq 
     ON pd.person_id=paq.person_id AND 
     pd.enc_id=paq.enc_id AND 
     pd.document_id=paq.item_id
     LEFT OUTER JOIN signatures s 
     ON pd.document_id=s.doc_id
     LEFT OUTER JOIN patient_encounter pe 
     ON pd.enc_id=pe.enc_id AND 
     pd.person_id=pe.person_id
     LEFT OUTER JOIN patient_status_mstr psm 
     ON ps.patient_status_id=psm.patient_status_id
     WHERE  
     paq.signoff_action='A' AND 
     pp.provider_id='ADB69024-4B7A-4B75-A9A6-BA2695632603' AND 
     psm.description='Active' AND 
     paq.create_timestamp>={ts '2012-03-20 00:00:01'} AND 
     (pd.template_file='bh_master_treatment' OR pd.template_file='sftubh_treatment_plan_review')
) AS derived
ON Table1.TableID=derived.TableID

Open in new window

0
 
susnewyorkAuthor Commented:
Maybe I'm approaching this wrong. I am trying to use this query within a stored procedure. I would like to have the stored procedure assign a value that is produced from my query to a variable.

I thought the best way would be to use Update/Set, but now I'm not sure if that is true.

Do you think that setting a value produced by the query directly to the variable would work? How would I go about doing that?

This is the stored procedure as it stands now:

ALTER procedure [dbo].[JOE_Prev_TX_Plan]
(--@design bit,
@pid varchar(36),
@enc_id varchar(36),
@prev_TX_Plan varchar(8) output)
--@CurrentDate varchar (8) output)

as

UPDATE sftu_em_history_ 
SET sftu_em_history_.txp_date = derived.maxxDate 
FROM sftu_em_history_ INNER JOIN
(SELECT 
    p.person_id PID
    , ((convert(varchar, convert(datetime, (SELECT max(v) 
       FROM (VALUES (paq.create_timestamp)
                            , (txp.init_tx_plan_signed)
                            , (txp.tx_plan_rev_signed_1)
                            , (txp.tx_plan_rev_signed_2)
                            , (txp.tx_plan_rev_signed_3)
                            , (txp.tx_plan_rev_signed_4)
                            , (txp.tx_plan_rev_signed_5)
                            , (txp.tx_plan_rev_signed_6)) AS value(v))), 101))) as [maxxDate]
     FROM   
     sftu_JOE_TX_PLAN_HX txp 
     FULL OUTER JOIN person p 
     ON txp.person_id=p.person_id
     LEFT OUTER JOIN patient_status ps 
     ON p.person_id=ps.person_id
     LEFT OUTER JOIN patient_documents pd 
     ON p.person_id=pd.person_id
     LEFT OUTER JOIN patient_provider pp 
     ON p.person_id=pp.person_id
     LEFT OUTER JOIN paq_signoff_history paq 
     ON pd.person_id=paq.person_id AND 
     pd.enc_id=paq.enc_id AND 
     pd.document_id=paq.item_id
     LEFT OUTER JOIN signatures s 
     ON pd.document_id=s.doc_id
     LEFT OUTER JOIN patient_encounter pe 
     ON pd.enc_id=pe.enc_id AND 
     pd.person_id=pe.person_id
     LEFT OUTER JOIN patient_status_mstr psm 
     ON ps.patient_status_id=psm.patient_status_id
     WHERE  
     paq.signoff_action='A' AND 
     pp.provider_id='ADB69024-4B7A-4B75-A9A6-BA2695632603' AND 
     psm.description='Active' AND 
     paq.create_timestamp>={ts '2012-03-20 00:00:01'} AND 
     (pd.template_file='bh_master_treatment' OR pd.template_file='sftubh_treatment_plan_review')
     and p.person_id=@pid
     and pe.enc_id=@enc_id
) AS derived
ON sftu_em_history_.person_id=derived.PID

Open in new window

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.

All Courses

From novice to tech pro — start learning today.