Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 211
  • Last Modified:

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.
0
susnewyork
Asked:
susnewyork
  • 5
  • 2
1 Solution
 
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
 
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
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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
 
YZlatCommented:
looks right to me
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

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