SELECT
R.RespondentID,
R.InstanceID,
R.AnswersChangedDate,
[5579D479-7ED7-4CEE-98CD-C125D2C7E37E],
[78746ACD-89AD-45E2-BD81-E1F71DBB5429_1], [78746ACD-89AD-45E2-BD81-E1F71DBB5429_2], [78746ACD-89AD-45E2-BD81-E1F71DBB5429_3], [78746ACD-89AD-45E2-BD81-E1F71DBB5429_4], [78746ACD-89AD-45E2-BD81-E1F71DBB5429_5], [78746ACD-89AD-45E2-BD81-E1F71DBB5429_6], [78746ACD-89AD-45E2-BD81-E1F71DBB5429_7], [78746ACD-89AD-45E2-BD81-E1F71DBB5429_8], [78746ACD-89AD-45E2-BD81-E1F71DBB5429_9], [78746ACD-89AD-45E2-BD81-E1F71DBB5429_10], [78746ACD-89AD-45E2-BD81-E1F71DBB5429_11], [78746ACD-89AD-45E2-BD81-E1F71DBB5429_99], [78746ACD-89AD-45E2-BD81-E1F71DBB5429_nans],
[0BD838A2-5B74-4E9A-A928-8A568242DAF9],
[C23D5CA2-4A8E-4665-9FFD-576FD166B998],
[A195490A-CE7E-4124-A560-5EEC7925A494],
[3445E1EE-C5A1-4279-8F82-A540D0F5208C],
[B5A6C6A7-AE8D-4339-92F0-1367CCF1AA26],
[C1619F7A-08E3-41CF-B04B-070907C93607],
[DC5332F5-E315-4C62-9D25-D39DE9A54A89],
[D7C93906-AE82-4A47-AC72-9BD114FA73B3],
[1108F4BC-D9E3-4D3E-80BC-B825EB7E9C80],
[4293DA93-68EC-4F69-AEDF-161C59FB8576],
[7A603BD0-6782-4182-8BFE-AE6EEDBE01A0],
[1BB55309-3412-4703-985D-B3E0D0E92644],
[6D1152DD-4225-48AF-B143-F1573371822F_1], [6D1152DD-4225-48AF-B143-F1573371822F_2], [6D1152DD-4225-48AF-B143-F1573371822F_3], [6D1152DD-4225-48AF-B143-F1573371822F_4], [6D1152DD-4225-48AF-B143-F1573371822F_5], [6D1152DD-4225-48AF-B143-F1573371822F_6], [6D1152DD-4225-48AF-B143-F1573371822F_7], [6D1152DD-4225-48AF-B143-F1573371822F_8], [6D1152DD-4225-48AF-B143-F1573371822F_nans],
[4C155F81-4458-4A93-B46D-EB0E79BAB500],
[AC9C726F-0820-413C-A40F-067B5EC72ED8],
[C0F8EAE9-30AA-48AC-A7EE-0248AA91528B],
[4CA003B7-B248-45CC-9CA6-79DDE68C8DD8],
[0FB4E6DE-1E64-4E97-8D0B-FA2577A2C995],
[7057B10B-EFC9-4511-B1F3-BEC3899D8E10],
[A3DDD6D7-597E-43D6-BBE4-8D80781B31C5],
[EA8EA60B-EB8E-483A-B1B6-861A500DCC0F],
[16DE4FFB-BEAE-4125-AEDF-5450FBACEDA8],
[1D15B69B-CD54-4612-8429-A9896D199FF3],
[913B71B6-F84A-4490-9376-59B231626389],
[D0C8E8C1-77C5-4C65-91F8-A36C89E88DCD],
[aaaB71B6-F84A-4490-9376-59B231626389],
[bbbB71B6-F84A-4490-9376-59B231626389],
R.InstanceId AS [00000000-0000-0000-0000-000000000001],
R.TakeStartTime AS [00000000-0000-0000-0000-000000000002],
R.TakeFinishTime AS [00000000-0000-0000-0000-000000000003],
CASE WHEN R.Properties & 4 <> 0 THEN 1 ELSE 0 END AS [00000000-0000-0000-0000-000000000004_4], CASE WHEN R.Properties & 64 <> 0 THEN 1 ELSE 0 END AS [00000000-0000-0000-0000-000000000004_64], CASE WHEN R.Properties & 256 <> 0 THEN 1 ELSE 0 END AS [00000000-0000-0000-0000-000000000004_256], CASE WHEN R.Properties & 2 <> 0 THEN 1 ELSE 0 END AS [00000000-0000-0000-0000-000000000004_2], CASE WHEN R.Properties & 512 <> 0 THEN 1 ELSE 0 END AS [00000000-0000-0000-0000-000000000004_512], CASE WHEN R.Properties & 16 <> 0 THEN 1 ELSE 0 END AS [00000000-0000-0000-0000-000000000004_16], CASE WHEN R.Properties & 1 <> 0 THEN 1 ELSE 0 END AS [00000000-0000-0000-0000-000000000004_1], CASE WHEN R.Properties & 32 <> 0 THEN 1 ELSE 0 END AS [00000000-0000-0000-0000-000000000004_32], CASE WHEN R.Properties & 8 <> 0 THEN 1 ELSE 0 END AS [00000000-0000-0000-0000-000000000004_8], CASE WHEN R.Properties = 0 THEN 0 ELSE 1 END AS [00000000-0000-0000-0000-000000000004_nans] ,
R.Email AS [00000000-0000-0000-0000-000000000005]
FROM
(
SELECT R.RespondentId,
R.InstanceID,
R.AnswersChangedDate,
MAX(CASE WHEN NA.ResponseId='5579D479-7ED7-4CEE-98CD-C125D2C7E37E' THEN NA.Value ELSE NULL END) AS [5579D479-7ED7-4CEE-98CD-C125D2C7E37E],
MAX(CASE WHEN NA.ResponseId='78746ACD-89AD-45E2-BD81-E1F71DBB5429' AND NA.value=1 THEN 1 ELSE 0 END) AS [78746ACD-89AD-45E2-BD81-E1F71DBB5429_1], MAX(CASE WHEN NA.ResponseId='78746ACD-89AD-45E2-BD81-E1F71DBB5429' AND NA.value=2 THEN 1 ELSE 0 END) AS [78746ACD-89AD-45E2-BD81-E1F71DBB5429_2], MAX(CASE WHEN NA.ResponseId='78746ACD-89AD-45E2-BD81-E1F71DBB5429' AND NA.value=3 THEN 1 ELSE 0 END) AS [78746ACD-89AD-45E2-BD81-E1F71DBB5429_3], MAX(CASE WHEN NA.ResponseId='78746ACD-89AD-45E2-BD81-E1F71DBB5429' AND NA.value=4 THEN 1 ELSE 0 END) AS [78746ACD-89AD-45E2-BD81-E1F71DBB5429_4], MAX(CASE WHEN NA.ResponseId='78746ACD-89AD-45E2-BD81-E1F71DBB5429' AND NA.value=5 THEN 1 ELSE 0 END) AS [78746ACD-89AD-45E2-BD81-E1F71DBB5429_5], MAX(CASE WHEN NA.ResponseId='78746ACD-89AD-45E2-BD81-E1F71DBB5429' AND NA.value=6 THEN 1 ELSE 0 END) AS [78746ACD-89AD-45E2-BD81-E1F71DBB5429_6], MAX(CASE WHEN NA.ResponseId='78746ACD-89AD-45E2-BD81-E1F71DBB5429' AND NA.value=7 THEN 1 ELSE 0 END) AS [78746ACD-89AD-45E2-BD81-E1F71DBB5429_7], MAX(CASE WHEN NA.ResponseId='78746ACD-89AD-45E2-BD81-E1F71DBB5429' AND NA.value=8 THEN 1 ELSE 0 END) AS [78746ACD-89AD-45E2-BD81-E1F71DBB5429_8], MAX(CASE WHEN NA.ResponseId='78746ACD-89AD-45E2-BD81-E1F71DBB5429' AND NA.value=9 THEN 1 ELSE 0 END) AS [78746ACD-89AD-45E2-BD81-E1F71DBB5429_9], MAX(CASE WHEN NA.ResponseId='78746ACD-89AD-45E2-BD81-E1F71DBB5429' AND NA.value=10 THEN 1 ELSE 0 END) AS [78746ACD-89AD-45E2-BD81-E1F71DBB5429_10], MAX(CASE WHEN NA.ResponseId='78746ACD-89AD-45E2-BD81-E1F71DBB5429' AND NA.value=11 THEN 1 ELSE 0 END) AS [78746ACD-89AD-45E2-BD81-E1F71DBB5429_11], MAX(CASE WHEN NA.ResponseId='78746ACD-89AD-45E2-BD81-E1F71DBB5429' AND NA.value=99 THEN 1 ELSE 0 END) AS [78746ACD-89AD-45E2-BD81-E1F71DBB5429_99], MAX(CASE WHEN NA.ResponseId='78746ACD-89AD-45E2-BD81-E1F71DBB5429' THEN 1 ELSE 0 END) AS [78746ACD-89AD-45E2-BD81-E1F71DBB5429_nans] ,
MAX(CASE WHEN NA.ResponseId='C23D5CA2-4A8E-4665-9FFD-576FD166B998' THEN NA.Value ELSE NULL END) AS [C23D5CA2-4A8E-4665-9FFD-576FD166B998],
MAX(CASE WHEN NA.ResponseId='A195490A-CE7E-4124-A560-5EEC7925A494' THEN NA.Value ELSE NULL END) AS [A195490A-CE7E-4124-A560-5EEC7925A494],
MAX(CASE WHEN NA.ResponseId='3445E1EE-C5A1-4279-8F82-A540D0F5208C' THEN NA.Value ELSE NULL END) AS [3445E1EE-C5A1-4279-8F82-A540D0F5208C],
MAX(CASE WHEN NA.ResponseId='B5A6C6A7-AE8D-4339-92F0-1367CCF1AA26' THEN NA.Value ELSE NULL END) AS [B5A6C6A7-AE8D-4339-92F0-1367CCF1AA26],
MAX(CASE WHEN NA.ResponseId='C1619F7A-08E3-41CF-B04B-070907C93607' THEN NA.Value ELSE NULL END) AS [C1619F7A-08E3-41CF-B04B-070907C93607],
MAX(CASE WHEN NA.ResponseId='DC5332F5-E315-4C62-9D25-D39DE9A54A89' THEN NA.Value ELSE NULL END) AS [DC5332F5-E315-4C62-9D25-D39DE9A54A89],
MAX(CASE WHEN NA.ResponseId='D7C93906-AE82-4A47-AC72-9BD114FA73B3' THEN NA.Value ELSE NULL END) AS [D7C93906-AE82-4A47-AC72-9BD114FA73B3],
MAX(CASE WHEN NA.ResponseId='1108F4BC-D9E3-4D3E-80BC-B825EB7E9C80' THEN NA.Value ELSE NULL END) AS [1108F4BC-D9E3-4D3E-80BC-B825EB7E9C80],
MAX(CASE WHEN NA.ResponseId='4293DA93-68EC-4F69-AEDF-161C59FB8576' THEN NA.Value ELSE NULL END) AS [4293DA93-68EC-4F69-AEDF-161C59FB8576],
MAX(CASE WHEN NA.ResponseId='7A603BD0-6782-4182-8BFE-AE6EEDBE01A0' THEN NA.Value ELSE NULL END) AS [7A603BD0-6782-4182-8BFE-AE6EEDBE01A0],
MAX(CASE WHEN NA.ResponseId='1BB55309-3412-4703-985D-B3E0D0E92644' THEN NA.Value ELSE NULL END) AS [1BB55309-3412-4703-985D-B3E0D0E92644],
MAX(CASE WHEN NA.ResponseId='6D1152DD-4225-48AF-B143-F1573371822F' AND NA.value=1 THEN 1 ELSE 0 END) AS [6D1152DD-4225-48AF-B143-F1573371822F_1], MAX(CASE WHEN NA.ResponseId='6D1152DD-4225-48AF-B143-F1573371822F' AND NA.value=2 THEN 1 ELSE 0 END) AS [6D1152DD-4225-48AF-B143-F1573371822F_2], MAX(CASE WHEN NA.ResponseId='6D1152DD-4225-48AF-B143-F1573371822F' AND NA.value=3 THEN 1 ELSE 0 END) AS [6D1152DD-4225-48AF-B143-F1573371822F_3], MAX(CASE WHEN NA.ResponseId='6D1152DD-4225-48AF-B143-F1573371822F' AND NA.value=4 THEN 1 ELSE 0 END) AS [6D1152DD-4225-48AF-B143-F1573371822F_4], MAX(CASE WHEN NA.ResponseId='6D1152DD-4225-48AF-B143-F1573371822F' AND NA.value=5 THEN 1 ELSE 0 END) AS [6D1152DD-4225-48AF-B143-F1573371822F_5], MAX(CASE WHEN NA.ResponseId='6D1152DD-4225-48AF-B143-F1573371822F' AND NA.value=6 THEN 1 ELSE 0 END) AS [6D1152DD-4225-48AF-B143-F1573371822F_6], MAX(CASE WHEN NA.ResponseId='6D1152DD-4225-48AF-B143-F1573371822F' AND NA.value=7 THEN 1 ELSE 0 END) AS [6D1152DD-4225-48AF-B143-F1573371822F_7], MAX(CASE WHEN NA.ResponseId='6D1152DD-4225-48AF-B143-F1573371822F' AND NA.value=8 THEN 1 ELSE 0 END) AS [6D1152DD-4225-48AF-B143-F1573371822F_8], MAX(CASE WHEN NA.ResponseId='6D1152DD-4225-48AF-B143-F1573371822F' THEN 1 ELSE 0 END) AS [6D1152DD-4225-48AF-B143-F1573371822F_nans] ,
MAX(CASE WHEN NA.ResponseId='AC9C726F-0820-413C-A40F-067B5EC72ED8' THEN NA.Value ELSE NULL END) AS [AC9C726F-0820-413C-A40F-067B5EC72ED8],
MAX(CASE WHEN NA.ResponseId='C0F8EAE9-30AA-48AC-A7EE-0248AA91528B' THEN NA.Value ELSE NULL END) AS [C0F8EAE9-30AA-48AC-A7EE-0248AA91528B],
MAX(CASE WHEN NA.ResponseId='4CA003B7-B248-45CC-9CA6-79DDE68C8DD8' THEN NA.Value ELSE NULL END) AS [4CA003B7-B248-45CC-9CA6-79DDE68C8DD8],
MAX(CASE WHEN NA.ResponseId='0FB4E6DE-1E64-4E97-8D0B-FA2577A2C995' THEN NA.Value ELSE NULL END) AS [0FB4E6DE-1E64-4E97-8D0B-FA2577A2C995],
MAX(CASE WHEN NA.ResponseId='7057B10B-EFC9-4511-B1F3-BEC3899D8E10' THEN NA.Value ELSE NULL END) AS [7057B10B-EFC9-4511-B1F3-BEC3899D8E10],
MAX(CASE WHEN NA.ResponseId='1D15B69B-CD54-4612-8429-A9896D199FF3' THEN NA.Value ELSE NULL END) AS [1D15B69B-CD54-4612-8429-A9896D199FF3],
MAX(CASE WHEN NA.ResponseId='913B71B6-F84A-4490-9376-59B231626389' THEN NA.Value ELSE NULL END) AS [913B71B6-F84A-4490-9376-59B231626389],
MAX(CASE WHEN NA.ResponseId='aaaB71B6-F84A-4490-9376-59B231626389' THEN NA.Value ELSE NULL END) AS [aaaB71B6-F84A-4490-9376-59B231626389],
MAX(CASE WHEN NA.ResponseId='bbbB71B6-F84A-4490-9376-59B231626389' THEN NA.Value ELSE NULL END) AS [bbbB71B6-F84A-4490-9376-59B231626389]
FROM @AnsweredRespondents R
LEFT OUTER JOIN (
SELECT
ResponseId, RespondentId, InstanceID, Value
FROM NumberAnswers (NOLOCK)
WHERE InstanceId IN (21301)
AND ResponseId IN ('5579D479-7ED7-4CEE-98CD-C125D2C7E37E','78746ACD-89AD-45E2-BD81-E1F71DBB5429','C23D5CA2-4A8E-4665-9FFD-576FD166B998','A195490A-CE7E-4124-A560-5EEC7925A494','3445E1EE-C5A1-4279-8F82-A540D0F5208C','B5A6C6A7-AE8D-4339-92F0-1367CCF1AA26','C1619F7A-08E3-41CF-B04B-070907C93607','DC5332F5-E315-4C62-9D25-D39DE9A54A89','D7C93906-AE82-4A47-AC72-9BD114FA73B3','1108F4BC-D9E3-4D3E-80BC-B825EB7E9C80','4293DA93-68EC-4F69-AEDF-161C59FB8576','7A603BD0-6782-4182-8BFE-AE6EEDBE01A0','1BB55309-3412-4703-985D-B3E0D0E92644','6D1152DD-4225-48AF-B143-F1573371822F','AC9C726F-0820-413C-A40F-067B5EC72ED8','C0F8EAE9-30AA-48AC-A7EE-0248AA91528B','4CA003B7-B248-45CC-9CA6-79DDE68C8DD8','0FB4E6DE-1E64-4E97-8D0B-FA2577A2C995','7057B10B-EFC9-4511-B1F3-BEC3899D8E10','1D15B69B-CD54-4612-8429-A9896D199FF3','913B71B6-F84A-4490-9376-59B231626389','aaaB71B6-F84A-4490-9376-59B231626389','bbbB71B6-F84A-4490-9376-59B231626389','00000000-0000-0000-0000-000000000001','00000000-0000-0000-0000-000000000002','00000000-0000-0000-0000-000000000003','00000000-0000-0000-0000-000000000004')
) NA ON R.RespondentId = NA.RespondentId
GROUP BY R.RespondentId, R.InstanceID, R.AnswersChangedDate
) NACH
INNER JOIN
(
SELECT R.RespondentId,
R.InstanceID,
R.AnswersChangedDate,
MAX(CASE WHEN TA.ResponseId='0BD838A2-5B74-4E9A-A928-8A568242DAF9' AND NOT TA.Value IS NULL THEN '' ELSE NULL END) AS [0BD838A2-5B74-4E9A-A928-8A568242DAF9],
MAX(CASE WHEN TA.ResponseId='4C155F81-4458-4A93-B46D-EB0E79BAB500' AND NOT TA.Value IS NULL THEN '' ELSE NULL END) AS [4C155F81-4458-4A93-B46D-EB0E79BAB500],
MAX(CASE WHEN TA.ResponseId='A3DDD6D7-597E-43D6-BBE4-8D80781B31C5' AND NOT TA.Value IS NULL THEN '' ELSE NULL END) AS [A3DDD6D7-597E-43D6-BBE4-8D80781B31C5],
MAX(CASE WHEN TA.ResponseId='EA8EA60B-EB8E-483A-B1B6-861A500DCC0F' AND NOT TA.Value IS NULL THEN '' ELSE NULL END) AS [EA8EA60B-EB8E-483A-B1B6-861A500DCC0F],
MAX(CASE WHEN TA.ResponseId='16DE4FFB-BEAE-4125-AEDF-5450FBACEDA8' AND NOT TA.Value IS NULL THEN '' ELSE NULL END) AS [16DE4FFB-BEAE-4125-AEDF-5450FBACEDA8],
MAX(CASE WHEN TA.ResponseId='D0C8E8C1-77C5-4C65-91F8-A36C89E88DCD' AND NOT TA.Value IS NULL THEN '' ELSE NULL END) AS [D0C8E8C1-77C5-4C65-91F8-A36C89E88DCD]
FROM @AnsweredRespondents R
LEFT OUTER JOIN (
SELECT
ResponseId, RespondentId, InstanceID, Value
FROM TextAnswers (NOLOCK)
WHERE InstanceId IN (21301)
AND ResponseId IN ('0BD838A2-5B74-4E9A-A928-8A568242DAF9','4C155F81-4458-4A93-B46D-EB0E79BAB500','A3DDD6D7-597E-43D6-BBE4-8D80781B31C5','EA8EA60B-EB8E-483A-B1B6-861A500DCC0F','16DE4FFB-BEAE-4125-AEDF-5450FBACEDA8','D0C8E8C1-77C5-4C65-91F8-A36C89E88DCD','00000000-0000-0000-0000-000000000005')
) TA ON R.RespondentId = TA.RespondentId
GROUP BY R.RespondentId, R.InstanceID, R.AnswersChangedDate
) TACH

ON NACH.RespondentId = TACH.RespondentId

INNER JOIN @AnsweredRespondents R ON TACH.RespondentId = R.RespondentId

WHERE R.InstanceId IN (21301) AND ((((R.InstanceId
=21301) ) and (([1D15B69B-CD54-4612-8429-A9896D199FF3]=1) or ([1D15B69B-CD54-4612-8429-A9896D199FF3]=2) or ([1D15B69B-CD54-4612-8429-A9896D199FF3]=3) or ([1D15B69B-CD54-4612-8429-A9896D199FF3]=4) or ([1D15B69B-CD54-4612-8429-A9896D199FF3]=5) or ([1D15B69B-CD54-4612-8429-A9896D199FF3]=6))))