schedule SQL

with _parameters as
    (select to_date({% parameter p_start_date %}) as p_start_date
    , {% parameter p_servername %} as p_servername)

SELECT id, server, display_time, SECOND(display_time) display_second, duration, sic1, replace,
content,
   --split_part(CONTENT, ',', 1) as "splitcontent1",
   substring(split_part(CONTENT, ',', 1),POSITION(':',split_part(CONTENT, ',', 1))+1) as content_filename_raw,
   substring(content_filename_raw, 2, len(content_filename_raw)) as content_filename2,
   substring(content_filename2, 1, len(content_filename2)-1) as content_filename,
   --split_part(CONTENT, ',', 2) as "splitcontent2",
   substring(split_part(CONTENT, ',', 2),POSITION(':',split_part(CONTENT, ',', 2))+1) as content_id,
   --split_part(CONTENT, ',', 3) as "splitcontent3",
   substring(split_part(CONTENT, ',', 3),POSITION(':',split_part(CONTENT, ',', 3))+1) as content_meta_data_raw,
   substring(content_meta_data_raw, 2, len(content_meta_data_raw)) as content_meta_data2,
   substring(content_meta_data2, 1, len(content_meta_data2)-1) as content_meta_data,
   --split_part(CONTENT, ',', 4) as "splitcontent4",
   substring(split_part(CONTENT, ',', 4),POSITION(':',split_part(CONTENT, ',', 4))+1) as content_provider_raw,
   substring(content_provider_raw, 2, len(content_provider_raw)) as content_provider2,
   substring(content_provider2, 1, len(content_provider2)-1) as content_provider,
   --split_part(CONTENT, ',', 5) as "content_url",
   substring(split_part(CONTENT, ',', 5),POSITION(':',split_part(CONTENT, ',', 5))+1) as content_url_raw,
   substring(content_url_raw, 2, len(content_url_raw)) as content_url2,
   substring(content_url2, 1, len(content_url2)-2) as content_url,
 ad,
   --split_part(AD, ',', 1) as "splitpart1",
   substring(split_part(AD, ',', 1),POSITION(':', split_part(AD, ',', 1))+1) as ad_filename_raw,
   substring(ad_filename_raw, 2, len(ad_filename_raw)) as ad_filename2,
   substring(ad_filename2, 1, len(ad_filename2)-1) as ad_filename,
   --split_part(AD, ',', 2) as "splitpart2",
   substring(split_part(AD, ',', 2),POSITION(':', split_part(AD, ',', 2))+1) as ad_id,
   --split_part(AD, ',', 3) as "splitpart3",
   substring(split_part(AD, ',', 3),POSITION(':', split_part(AD, ',', 3))+1) as ad_meta_data_raw,
   substring(ad_meta_data_raw, 2, len(ad_meta_data_raw)) as ad_meta_data2,
   substring(ad_meta_data2, 1, len(ad_meta_data2)-1) as ad_meta_data,
   --split_part(AD, ',', 4) as "splitpart4",
   substring(split_part(AD, ',', 4),POSITION(':', split_part(AD, ',', 4))+1) as ad_provider_raw,
   substring(ad_provider_raw, 2, len(ad_provider_raw)) as ad_provider2,
   substring(ad_provider2, 1, len(ad_provider2)-1) as ad_provider,
   --split_part(AD, ',', 5) as "splitpart5"
   substring(split_part(AD, ',', 5),POSITION(':', split_part(AD, ',', 5))+1) as ad_url_raw,
   substring(ad_url_raw, 2, len(ad_url_raw)) as ad_url2,
   substring(ad_url2, 1, len(ad_url2)-2) as ad_url,
 sponsor,
  substring(split_part(SPONSOR, ',',1),POSITION(':', split_part(SPONSOR, ',',1))+1) as sponsor_filename_raw,
  substring(sponsor_filename_raw, 2, len(sponsor_filename_raw)) as sponsor_filename2,
  substring(sponsor_filename2, 1, len(sponsor_filename2)-1)as sponsor_filename,

  substring(split_part(SPONSOR, ',',2),POSITION(':', split_part(SPONSOR, ',',2))+1) as sponsor_id_raw,
  substring(sponsor_id_raw, 2, len(sponsor_id_raw)) as sponsor_id2,
  substring(sponsor_id2, 1, len(sponsor_id2)-1)as sponsor_id,

  substring(split_part(SPONSOR, ',',3),POSITION(':', split_part(SPONSOR, ',',3))+1) as sponsor_meta_data_raw,
  substring(sponsor_meta_data_raw, 2, len(sponsor_meta_data_raw)) as sponsor_meta_data2,
  substring(sponsor_meta_data2, 1, len(sponsor_meta_data2)-1) as sponsor_meta_data,

  substring(split_part(SPONSOR, ',',4),POSITION(':', split_part(SPONSOR, ',',4))+1) as sponsor_provider_raw,
  substring(sponsor_provider_raw, 2, len(sponsor_provider_raw)) as sponsor_provider2,
  substring(sponsor_provider2, 1, len(sponsor_provider2)-1) as sponsor_provider,

  substring(split_part(SPONSOR, ',',5),POSITION(':', split_part(SPONSOR, ',',5))+1) as sponsor_url_raw,
  substring(sponsor_url_raw, 2, len(sponsor_url_raw)) as sponsor_url2,
  substring(sponsor_url2, 1, len(sponsor_url2)-1) as sponsor_url,


created_at, modified_at, _sdc_extracted_at, _sdc_batched_at
FROM "BSS_PRD"."SCHEDULE_ARCHIVE" sa
inner join _parameters p
  on to_date(sa.display_time) = p.p_start_date
  and sa.server = p.p_servername
WHERE _SDC_DELETED_AT is null
and ad_provider = 'Direct'