Conformity Export

select 
	convert(char,convert(date,(DATEADD(SS,SessionStartTime/1000,'1/1/1970')at time zone 'UTC' at time zone 'Central Standard Time (Mexico)')),120)SessionDate,
	convert(char,convert(datetime,(DATEADD(SS,SessionStartTime/1000,'1/1/1970')at time zone 'UTC' at time zone 'Central Standard Time (Mexico)')),120)SessionStartTime,
	convert(char,convert(datetime,SurveyFinishReceivedOn at time zone 'UTC' at time zone 'Central Standard Time (Mexico)'),120)SurveyFinishReceivedOn,
	SessionStatusLogId,A.SessionUid,
	CASE
	when SurveyStatusId = '-2' then 'Discarded' 
	when SurveyStatusId = '-1' then 'Canceled' 
	when SurveyStatusId = '0' then 'InComplete'
	when SurveyStatusId = '1' then 'Complete'
	end as SurveyStatus,
	ProgramIds,ProgramItemIds,TotalSurvey TotalQuestions,TotalScene 'TotalScene(SurveyTable)',TotalSceneImages 'TotalSceneImages(SurveyTable)',
	ADFPipelineRunID,IsActualFacingStatusApplicable,ActualFacingStatus,ActualFacingTimestamp,ActualFacingExportStatus,
	convert(char,convert(datetime,ActualFacingExportTimestamp at time zone 'UTC' at time zone 'Central Standard Time (Mexico)'),120)ActualFacingExportTimestamp_UTC,
	IsSceneApplicable,SceneStatus,SceneTimestamp,SceneExportStatus,
	convert(char,convert(datetime,SceneExportTimestamp at time zone 'UTC' at time zone 'Central Standard Time (Mexico)'),120)SceneExportTimestamp_UTC,
	IsProgramConformitySetup,
	convert(char,convert(datetime,ConformityTimestamp at time zone 'UTC' at time zone 'Central Standard Time (Mexico)'),120)ConformityTimestamp_UTC,
	ConformityExportStatus,
	convert(char,convert(datetime,ConformityExportTimestamp at time zone 'UTC' at time zone 'Central Standard Time (Mexico)'),120)ConformityExportTimestamp_UTC,
	IsQuestionSetup,QuestionTimestamp,
	convert(char,convert(datetime,QuestionExportTimestamp at time zone 'UTC' at time zone 'Central Standard Time (Mexico)'),120)QuestionExportTimestamp_UTC,
	SessionStatusTimestamp,SessionExportStatus,
	convert(char,convert(datetime,SessionExportTimestamp at time zone 'UTC' at time zone 'Central Standard Time (Mexico)'),120)SessionExportTimestamp_UTC,A.createdon
	from SessionStatusLog A with (nolock)
	full outer join Survey B with (nolock) on A.SessionUid=B.SessionUid
	where A.SessionUid IN (
	'78E2FC36-F8D8-40EB-A097-E86A13593A4F','AD523D6D-10D0-459D-9979-42278586842B','6ED6291A-1122-49ED-BA32-9D894F826A4C','45D9086C-C8E3-4C50-AAAB-80F59BC0A64E','80C71E51-E61F-4570-8D49-16CAF51B42B8','BEADDEAA-B136-4397-A166-52E40EDA8D45','1BC6A98B-CE9A-4E33-94C3-8AEC41BDF156','CCA7681A-249B-4185-BD60-F945E5115D3A','05DD3274-209D-4AFB-AAA2-ADC5BF49EA73','22220AFC-873D-42BB-93AB-C15A5BA06E75','90D416C4-E232-46DB-B1B2-13E04F746505','EA44DEA2-166D-4687-937A-042625E16CA7','B8CF08F9-A310-44A4-BC93-AC59E58D93E5','18C590C5-9522-47CE-9E52-240302C6C18B','7E698C84-3646-452C-97B6-F1728466E37E','196FE232-3C0C-439C-AEFB-1BBB5C58F560','129F9A0A-7F28-4300-A84D-023693A1F9F2','1974AAC8-AB3E-43DF-8AD0-79CFA6978676','C9E04690-DB88-4F09-B95D-C701985F9CAB','053CD1A3-3160-44EC-B898-0F40825F69A2','761B1E8B-AD61-40AB-AE73-B46B67B9AA56','20A9689C-F9E4-4A98-A9D2-6C2244DB050C','8311AAC0-5F2B-41CE-9C83-FF0CE2419722','216E1AEA-FDB5-4000-8876-C7DF1CCCF608','9A482591-B1A7-4D08-B3CC-99D6086F4B14','9F109F49-420E-4360-B0CB-891009CC2E2D','AB77A311-2882-47BB-9C60-B2CEAF1B580A','AE29554B-99C5-4173-863E-B9F50874CFAE','247B3D5A-5545-4D02-8840-F66AB112B5AA','BE6B3552-5979-4462-A6F4-85399911C6A8','26094075-458B-44DD-9586-9D15238EA881','DB071727-E89B-40E7-B695-FCC670211DEB','26BDD961-3A9F-4853-BA18-BCABDBC930F5','F1F76AC9-254C-4627-A379-9A67585354E6','27A93621-E358-4B80-98A7-E2B0AF6DD013','794F68E9-1A3C-4739-A563-EAC0A83615B2','28139364-6337-446A-82EC-735A99D69A98','7FA73AA8-3A81-44FD-8307-E8345BF03E40','2AD2A377-20B5-494C-BEC8-ADBB0ADF853E','85115DA1-4833-4028-97D3-6F8E2B35F3EB','2C352410-FD6F-439B-9041-404F08EEB65C','956E0265-9320-4159-96B5-E7FDCBD7D563','2DC9986A-68D7-4C7D-BD80-1E820295F7D0','9BA96B49-DBBD-4492-BAB1-63C230B963DF','2F465F50-282B-4AC0-9901-28AB90C3AA4A','A264F2BE-546B-4391-A422-5741C9704C8C','2FDDF4F5-B099-4178-903C-FCA530F0D032','AD3371F7-4DB9-44B6-A7A5-546C13192C8B','33A42462-F876-4EED-BA27-3165E92B4171','B4F5D64A-E371-4D17-8D9E-C4032EB3F7EB','37F6C10F-1AB4-47B8-BF98-0A428D065E8E','BC746A46-11E6-4388-8820-7FD3C450AFE2','3856DD35-628C-4041-BE57-25D46F119DF7','C02C0A00-FDF2-412A-998E-4810AC034280','3AA2B9EB-F2D2-40C3-A2EE-7D5C2AEEA4B7','D979CBAA-7AE5-42E7-B719-28B57873FEEC','3AE79083-C279-406A-BA01-87C2EB94E6A7','E05F7087-16BD-48A2-838B-A4FE45C9EA09','3B60C6E6-D6E2-4739-9706-21DF3E53339C','EFBC5918-499E-43B4-8CB3-3DA53500E8B9','416A6B40-F73C-4A18-82BB-533CBD487C4C','030E5D85-9391-42F7-A889-B741C801EEF3','44097462-EAD0-42D9-BE58-F3895A12E422','023E1520-4563-4A33-96A8-9825D4DACB85','45132312-7C9D-4D7F-9EFC-5B8F327D6439','7AB3E8F3-46B6-4251-A2CB-7540837A0F02','05825A6B-F76D-48A5-9A3B-3CA8E35ACD74','7EDC042D-983C-4EB9-B3FF-C9A47FA73BFC','4622AA60-88A5-401A-AFBE-47952823AF4E','125B6672-9DBC-470B-AAA0-BC4C00AB71DC','7FAF096B-9A8B-4A5D-9D2E-D1784383464C','48362D85-932A-48A6-AA74-48E2F9FD4B63','84E85026-5989-4A3C-A6BA-06F68E5B20CB','48FB5CD9-0F9C-4942-B12B-B3B9372DF3AE','8B31D9F6-316D-4EA8-9CED-B1984B8458CC','492504DC-5728-4E95-8795-1A1771F1FE68','942DD635-D63A-49CA-8237-02E6AB3A1351','4A4B4F82-971C-4C3B-9AFC-A3371C3A7845','96A1EF11-F763-4001-8E13-B511ECF14436','4B96D92B-4891-41E1-BE19-E13224189184','9B8E90D1-8FFC-4FB8-9563-C30F4CEB4D86','4B991C9D-9DF0-466E-B43B-CAC565A9BA52','9CD778C2-2A5B-476D-BAEE-E6551A95AB7C','4E91C25F-D59C-426A-9D47-23BF866828F1','A056CB40-2630-43B8-A989-0914997A086F','4FF5CD4E-202D-4E6E-B3BF-3DE2E330FAE8','A6C28216-94BD-4C6F-99B3-7D35A7545E6A','50FD88A6-1B6A-4280-8DF4-E4A484C3CB8B','AC12610D-7DFE-42B0-B9B0-B78858C77740','52B19E7E-02E7-4C15-B06E-9A777220DF59','02F7C3D3-0C40-440E-954B-9942B1FC8090','5BCD2DEB-923D-4BD8-BF86-3785FD7CF96D','AE5992C8-B93D-42EA-B2A7-891C910D2B3F','5C3FE181-743D-4F12-96F9-745D4C9BC3DD','B74BE8BA-BA2A-417F-B79E-AB2B19B2BA71','6178067D-EBF8-441D-8688-DEAB628A5405','BC2A7B23-3A7C-4A23-9016-AD03B8B045AF','62D5B1D7-BD45-4E28-A459-92A1D67ACF39','BD2E7ABC-E90E-4939-AF6D-51D30A3480F3','630A6E41-32A8-4378-94F8-A897814D6698','0E6F5850-8D2B-44EE-B2E7-C0C75A11CB25','CB844888-2F0A-47AA-B8DB-455B150838D5','C708794B-1597-4F9E-8CA7-90BF15559128','D021DF4F-88FE-4393-AE9F-9597FEF94550','0E713B43-E210-4BB7-9372-56252CC02069','D78BEB79-C070-49DD-8041-57EC58805E37','63116A12-8BA4-477F-947D-5EFE538EE797','D34432D5-20F9-43DA-800B-3EEA99F9E933','64D11537-AD1A-460A-A198-316A88449D24','D8B707E7-62A9-49D9-8C17-72A07D0E1EC0','65E15CCE-C8D3-4C78-AD5A-52A62EDC2834','DA6DE4AC-BF91-4348-8C65-FE5755D415B1','66834611-6FA1-4440-BECA-A7407FF8933E','DD063788-AD9C-48C6-BD64-E4ED6CB759AC','69BAF8F6-0D68-4AA6-B47D-F86C18AE0807','E416D160-9C40-47E9-8A21-518671C28809','6A323949-7A90-40B7-A7CA-77BBE82DD89A','EB9C8A98-12CD-4122-86F6-89B087043284','6BAF228E-452E-42B1-B19C-59B612D48508','F0DA5B7D-091A-4838-B0A0-312DD0BCF939','F6BFFF16-2D2A-4002-BE11-2AF9F80031AF','F60070B4-E080-4E12-99DB-743FF32B450D','FD3F182E-AB84-497D-9250-0B85AF7CBA61','74ED28C0-BDC4-4EB5-A9AA-B8567209DFDA','00E37D2A-D07D-4B8E-845C-DD95B327D6F9','717224D0-E3B7-4E74-A7E1-9D5AE1F2A878'
	)
	--and convert(char,convert(datetime,ConformityExportTimestamp at time zone 'UTC' at time zone 'Central Standard Time (Mexico)'),120)>= '2023-10-06'
	/*and (SceneExportTimestamp IS NOT NULL or SessionExportTimestamp IS NOT NULL or ConformityExportTimestamp IS NOT NULL or QuestionExportTimestamp
	IS NOT NULL or ActualFacingExportTimestamp IS NOT NULL)*/
	and ConformityExportTimestamp IS NOT NULL
	order by SessionUid,SessionStatusLogId

--------------------------------------------------