I have a table that I'm storing when the value changes:
DECLARE @changedConcepts TABLE (id INT NOT NULL IDENTITY(1,1) PRIMARY KEY, form_id INT, question_id xml, concept_id INT, create_date DATETIME, cncpt_text varchar(150) , sub_id int, string_txt varchar(max))
I need to compare the concept_id for a question_id and if it changed, and create_date is newer on my form that was filled in than the create_date for the concept originally, then I need to use the old concept instead of the new. I'm tempted to use and exists and compare those and then set the value returned to the old instead of the new, but I think if I do that, it will get rid of that line instead of changing it. My other idea is to left join it into my query, but I'm not sure how to set it to the new value. How would I do this? This is my final query, and I commented out the left join idea since I'm not sure how to set the returned concept to the new one with this.
DECLARE @pnAnnotationCommitReport_ToBeInserted TABLE (progressnote_id INT,form_id INT,question_id INT,question_value [VARCHAR](max),associatedconcept_id INT, OI_create_date DATETIME, crte_date DATETIME)
@pnAnnotationCommitReport_ToBeInserted(progressnote_id,form_id,question_id,question_value,associatedconcept_id, OI_create_date, crte_date)
SELECT progressnote_id,
a.form_id,
fq.question_id,
questionvalue,
fq.concept_id,
a.create_date as OI_create_date,
@RUN_TO_DATE as crte_date
FROM (
SELECT form_id,
progressnote_id,
R.Q.value('@id', 'varchar(max)') AS questionid,
R.Q.value('@value', 'varchar(max)') AS questionvalue,
t.create_date
FROM
@tableNotes t
OUTER APPLY t.form_questions.nodes('/RESULT/QUESTIONS/QUESTION') AS R(Q)
WHERE
ISNUMERIC(R.Q.value('@id', 'varchar(max)')) <> 0
) a
INNER JOIN [CKOLTP_dev]..FORM_QUESTION fq ON
fq.form_id = a.form_id AND
fq.question_id = a.questionid
--left join @changedConcepts chg on --?? is this what I need
-- chg.form_id = fq.form_id AND
-- chg.question_id = fq.question_id
WHERE
(a.create_date > @LAST_RUN_DATE
AND
a.create_date <= @RUN_TO_DATE)
--AND exists ?? check date in changed one vs combined tables above and use new concept instead of old if concept changed since a.OI_create_date --not sure even what to search for on internet how-to
So basically, this is what the data looks like: @changedConcepts:
id form_id question_id concept_id create_date cncpt_text sub_id string_txt
1 500000 63 501000 2020-07-02 Source: 25 High Probability
2 500001 5 501000 2019-08-23 24/7 1 External
3 500001 27 503000 2020-04-03 Internal 5 Customer Service
And my former/normal @pnAnnotationCommitReport_ToBeInserted without changes based on the @changedConcepts:
progressnote_id form_id question_id questionvalue concept_id OI_create_date crte_date
250000 500300 3 501000 2020-04023 2021-03-03
250050 500000 63 501001 2020-04-23 2021-03-03
251000 500001 5 yes 503001 2020-04003 2021-03-03
What it needs to look like (put concept back to what it was before it was changed if oi_create_date > create_date for @changedConcepts table:
progressnote_id form_id question_id questionvalue concept_id OI_create_date crte_date
250000 500300 3 501000 2020-04023 2021-03-03
250050 500000 63 501000 2020-04-23 2021-03-03
251000 500001 5 yes 501000 2020-04003 2021-03-03
So my question is, how do I switch the concept data to the value in changedConcepts when it comes up in the new report?
Update 1: I'm tempted to add this instead of the commented out thoughts above:
--determine if concept changed and update in data to be inserted to old concept
SELECT *
from @pnAnnotationCommitReport_ToBeInserted pn
where exists(
select * from @changedConcepts chg
where
(pn.form_id = chg.form_id
and
pn.question_id = chg.question_id)
and
( if (pn.associatedconcept_id != chg.concept_id) then (pn.associatedconcept_id=chg.concept_id) ) --this is incorrect syntax..not sure how to do that
Update 2: I was looking at this update and I'm thinking of doing this instead of what I say in update...do you think this is the correct way to update the concept if it's changed...
UPDATE @pnAnnotationCommitReport_ToBeInserted
set associatedconcept_id= chg.concept_id
from @changedConcepts chg
left join @pnAnnotationCommitReport_ToBeInserted pn
on
chg.form_id = pn.form_id AND
chg.question_id = pn.question_id
where
pn.associatedconcept_id != chg.concept_id