survey85load.sql

use stage
go


insert into mip_portal.dbo.response
select null as org_site_id, null as provider_id
      ,ps.provider_survey_id
      ,case when piv.surveyQuestionId in (10178,10179,10180,10181,10182) and piv.response = 1 then 'True'
            when piv.surveyQuestionId in (10178,10179,10180,10181,10182) and piv.response = 0 then 'False'
            when piv.surveyQuestionId in (10193) and piv.response in (1,2) then 'True'
            when piv.surveyQuestionId in (10193) and piv.response in (3) then 'False'
            when piv.surveyQuestionId in (10194) and piv.response in (1) then 'True'
            when piv.surveyQuestionId in (10194) and piv.response in (2) then 'False'
            when piv.surveyQuestionId in (10194) and piv.response = '' then 'False'
            when piv.surveyQuestionId in (10195) and piv.response in (1) then 'True'
            when piv.surveyQuestionId in (10195) and piv.response in (2) then 'False'
            when piv.surveyQuestionId in (10195) and piv.response = '' then 'False'
            when piv.surveyQuestionId in (10210, 10211, 10212, 10213, 10214, 10215, 10216, 10238, 10239, 10240, 10241, 10242, 10243) and piv.response = 1 then 'True'
            when piv.surveyQuestionId in (10210, 10211, 10212, 10213, 10214, 10215, 10216, 10238, 10239, 10240, 10241, 10242, 10243) and piv.response = 0 then 'False'
            else piv.response
       end as response_answer
      ,case when piv.surveyQuestionId in (10178,10179,10180,10181,10182) and piv.response = 1 then 'True'
            when piv.surveyQuestionId in (10178,10179,10180,10181,10182) and piv.response = 0 then 'False'
            when piv.surveyQuestionId in (10193) and piv.response in (1,2) then 'True'
            when piv.surveyQuestionId in (10193) and piv.response in (3) then 'False'
            when piv.surveyQuestionId in (10194) and piv.response in (1) then 'True'
            when piv.surveyQuestionId in (10194) and piv.response in (2) then 'False'
            when piv.surveyQuestionId in (10194) and piv.response = '' then 'False'
            when piv.surveyQuestionId in (10195) and piv.response in (1) then 'True'
            when piv.surveyQuestionId in (10195) and piv.response in (2) then 'False'
            when piv.surveyQuestionId in (10195) and piv.response = '' then 'False'
            when piv.surveyQuestionId in (10210, 10211, 10212, 10213, 10214, 10215, 10216, 10238, 10239, 10240, 10241, 10242, 10243) and piv.response = 1 then 'True'
            when piv.surveyQuestionId in (10210, 10211, 10212, 10213, 10214, 10215, 10216, 10238, 10239, 10240, 10241, 10242, 10243) and piv.response = 0 then 'False'
            else piv.response
       end as response_full_answer
      ,GETDATE(), 'nghia.tran', GETDATE(), 'nghia.tran'
      ,piv.surveyQuestionId, null
from (
select *, REPLACE(answer,'q','') as surveyQuestionId
from (select provider_id, q10173, q10175, q10176, q10178, q10179, q10180, q10181, q10182, q10184, q10185, q10187, q10188
            ,q10189, q10190, q10193, q10194, q10195, q10197, q10198, q10199, q10200, q10201, q10202, q10210, q10211
            ,q10212, q10213, q10214, q10215, q10216, q10238, q10239, q10240, q10241, q10242, q10243
      from d_survey85) as main
unpivot (response for answer in (q10173, q10175, q10176, q10178, q10179, q10180, q10181, q10182, q10184, q10185, q10187
        ,q10188, q10189, q10190, q10193, q10194, q10195, q10197, q10198, q10199, q10200, q10201, q10202, q10210, q10211
        ,q10212, q10213, q10214, q10215, q10216, q10238, q10239, q10240, q10241, q10242, q10243)) as asdf) as piv
inner join mip_portal.dbo.provider_survey as ps on ps.provider_id = piv.provider_id
where ps.survey_id = 85