1with _parameters as 2 (select to_date({% parameter p_start_date %}) as p_start_date 3 , {% parameter p_servername %} as p_servername) 4 5SELECT id, server, display_time, SECOND(display_time) display_second, duration, sic1, replace, 6content, 7 --split_part(CONTENT, ',', 1) as "splitcontent1", 8 substring(split_part(CONTENT, ',', 1),POSITION(':',split_part(CONTENT, ',', 1))+1) as content_filename_raw, 9 substring(content_filename_raw, 2, len(content_filename_raw)) as content_filename2, 10 substring(content_filename2, 1, len(content_filename2)-1) as content_filename, 11 --split_part(CONTENT, ',', 2) as "splitcontent2", 12 substring(split_part(CONTENT, ',', 2),POSITION(':',split_part(CONTENT, ',', 2))+1) as content_id, 13 --split_part(CONTENT, ',', 3) as "splitcontent3", 14 substring(split_part(CONTENT, ',', 3),POSITION(':',split_part(CONTENT, ',', 3))+1) as content_meta_data_raw, 15 substring(content_meta_data_raw, 2, len(content_meta_data_raw)) as content_meta_data2, 16 substring(content_meta_data2, 1, len(content_meta_data2)-1) as content_meta_data, 17 --split_part(CONTENT, ',', 4) as "splitcontent4", 18 substring(split_part(CONTENT, ',', 4),POSITION(':',split_part(CONTENT, ',', 4))+1) as content_provider_raw, 19 substring(content_provider_raw, 2, len(content_provider_raw)) as content_provider2, 20 substring(content_provider2, 1, len(content_provider2)-1) as content_provider, 21 --split_part(CONTENT, ',', 5) as "content_url", 22 substring(split_part(CONTENT, ',', 5),POSITION(':',split_part(CONTENT, ',', 5))+1) as content_url_raw, 23 substring(content_url_raw, 2, len(content_url_raw)) as content_url2, 24 substring(content_url2, 1, len(content_url2)-2) as content_url, 25 ad, 26 --split_part(AD, ',', 1) as "splitpart1", 27 substring(split_part(AD, ',', 1),POSITION(':', split_part(AD, ',', 1))+1) as ad_filename_raw, 28 substring(ad_filename_raw, 2, len(ad_filename_raw)) as ad_filename2, 29 substring(ad_filename2, 1, len(ad_filename2)-1) as ad_filename, 30 --split_part(AD, ',', 2) as "splitpart2", 31 substring(split_part(AD, ',', 2),POSITION(':', split_part(AD, ',', 2))+1) as ad_id, 32 --split_part(AD, ',', 3) as "splitpart3", 33 substring(split_part(AD, ',', 3),POSITION(':', split_part(AD, ',', 3))+1) as ad_meta_data_raw, 34 substring(ad_meta_data_raw, 2, len(ad_meta_data_raw)) as ad_meta_data2, 35 substring(ad_meta_data2, 1, len(ad_meta_data2)-1) as ad_meta_data, 36 --split_part(AD, ',', 4) as "splitpart4", 37 substring(split_part(AD, ',', 4),POSITION(':', split_part(AD, ',', 4))+1) as ad_provider_raw, 38 substring(ad_provider_raw, 2, len(ad_provider_raw)) as ad_provider2, 39 substring(ad_provider2, 1, len(ad_provider2)-1) as ad_provider, 40 --split_part(AD, ',', 5) as "splitpart5" 41 substring(split_part(AD, ',', 5),POSITION(':', split_part(AD, ',', 5))+1) as ad_url_raw, 42 substring(ad_url_raw, 2, len(ad_url_raw)) as ad_url2, 43 substring(ad_url2, 1, len(ad_url2)-2) as ad_url, 44 sponsor, 45 substring(split_part(SPONSOR, ',',1),POSITION(':', split_part(SPONSOR, ',',1))+1) as sponsor_filename_raw, 46 substring(sponsor_filename_raw, 2, len(sponsor_filename_raw)) as sponsor_filename2, 47 substring(sponsor_filename2, 1, len(sponsor_filename2)-1)as sponsor_filename, 48 49 substring(split_part(SPONSOR, ',',2),POSITION(':', split_part(SPONSOR, ',',2))+1) as sponsor_id_raw, 50 substring(sponsor_id_raw, 2, len(sponsor_id_raw)) as sponsor_id2, 51 substring(sponsor_id2, 1, len(sponsor_id2)-1)as sponsor_id, 52 53 substring(split_part(SPONSOR, ',',3),POSITION(':', split_part(SPONSOR, ',',3))+1) as sponsor_meta_data_raw, 54 substring(sponsor_meta_data_raw, 2, len(sponsor_meta_data_raw)) as sponsor_meta_data2, 55 substring(sponsor_meta_data2, 1, len(sponsor_meta_data2)-1) as sponsor_meta_data, 56 57 substring(split_part(SPONSOR, ',',4),POSITION(':', split_part(SPONSOR, ',',4))+1) as sponsor_provider_raw, 58 substring(sponsor_provider_raw, 2, len(sponsor_provider_raw)) as sponsor_provider2, 59 substring(sponsor_provider2, 1, len(sponsor_provider2)-1) as sponsor_provider, 60 61 substring(split_part(SPONSOR, ',',5),POSITION(':', split_part(SPONSOR, ',',5))+1) as sponsor_url_raw, 62 substring(sponsor_url_raw, 2, len(sponsor_url_raw)) as sponsor_url2, 63 substring(sponsor_url2, 1, len(sponsor_url2)-1) as sponsor_url, 64 65 66created_at, modified_at, _sdc_extracted_at, _sdc_batched_at 67FROM "BSS_PRD"."SCHEDULE_ARCHIVE" sa 68inner join _parameters p 69 on to_date(sa.display_time) = p.p_start_date 70 and sa.server = p.p_servername 71WHERE _SDC_DELETED_AT is null 72and ad_provider = 'Direct'