1

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
Dale K
  • 25,246
  • 15
  • 42
  • 71
Michele
  • 3,617
  • 12
  • 47
  • 81
  • Does `if (pn.associatedconcept_id != chg.concept_id) then (pn.associatedconcept_id=chg.concept_id)` mean that if the values are different you want to check that they are equal? – HABO Mar 03 '21 at 20:41
  • I have to agree with @HABO , this would just always evaluate to false – Anthony Hancock Mar 03 '21 at 20:49
  • My intent was that if they are not equal, to use the old one. How would I get it to do that? Would my Update2 cover that then? – Michele Mar 04 '21 at 12:44
  • Aside: [`IsNumeric()`](https://learn.microsoft.com/en-us/sql/t-sql/functions/isnumeric-transact-sql?view=sql-server-ver15) is notoriously [problematic](http://www.sqlservercentral.com/articles/ISNUMERIC()/71512/). – HABO Mar 04 '21 at 14:11
  • @HABO - I don't see where I'm using IsNumeric – Michele Mar 04 '21 at 17:09
  • `ISNUMERIC(R.Q.value('@id', 'varchar(max)')) <> 0`. Amazing what a browser with "Find in page" can find. – HABO Mar 04 '21 at 17:46

0 Answers0