Ver Log SQL

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

      -- generate list of display mac addresses from selected date's POP data
        ,_pop_displays_list as (
        select distinct display_id
        FROM "BSS_PRD"."POP_REPORT" pop
        INNER JOIN _parameters p
          on (to_date(pop.display_time_ts) = p.p_start_date))

      -- generate display (mac address) to server map from NetSuite
      ,_netsuite_displaydetail as (
        SELECT du.display_unit_id
             , du.display_unit_name
             , du.server_name_id
             , du.mac_address
             , du.uc_mac_address
             , du.du_mac_address
             , ns.server_name
             , case when du.du_mac_address is not null then du.du_mac_address
                    when du.du_mac_address is null and du.mac_address is not null then du.mac_address
                    when du.du_mac_address is null and du.mac_address is null and du.uc_mac_address is not null then du.uc_mac_address
                    else null end as mac_case
             --, max(display_unit_id) over (partition by server_name_id) as du_per_server
             --, iff(du_per_server=display_unit_id, 1, 0) as filter
        FROM "NETSUITE"."DISPLAY_UNIT" du
        LEFT JOIN "NETSUITE"."SERVER" ns
          on du.server_name_id = ns.server_id
        where du._fivetran_deleted = false
          and lastreportdt is not null)

      -- join display list (POP) to server mapping (NetSuite) to generate single display-per-server lookup table based on POP displays
      ,_nsdu as (
        with _ndd as (
          SELECT mac_case as mac
               , display_unit_id
               , server_name_id
               , server_name
               , display_unit_name
               , max(display_unit_id) over (partition by server_name_id) as du_per_server
               --, iff(du_per_server=display_unit_id, 1, 0) as filter
          from _netsuite_displaydetail ndd
          inner join _pop_displays_list pdl on ndd.mac_case = pdl.display_id)
        select *, iff(du_per_server=display_unit_id, 1, 0) as filter
        from _ndd
        inner join _parameters p
          on p.p_servername = _ndd.server_name
        where filter = 1)

    -- pull pop data for a single display (POP) per server (NetSuite)
     ,_pop_data as (
    SELECT ID, DISPLAY_ID, DISPLAY_TIME, DISPLAY_TIME_TS, DURATION, CREATED_AT, MODIFIED_AT, display
      ,substring(split_part(DISPLAY, ',',1),POSITION(':',split_part(DISPLAY, ',',1))+1) as display_aspect_raw
      ,substring(display_aspect_raw, 2 ,len(display_aspect_raw)) as display_aspect2
      ,substring(display_aspect2, 1, len(display_aspect2)-1) as display_aspect

      ,substring(split_part(DISPLAY, ',',2),POSITION(':',split_part(DISPLAY, ',',2))+1) as display_height

      ,substring(split_part(DISPLAY, ',',3),POSITION(':',split_part(DISPLAY, ',',3))+1) as display_orientation_raw
      ,substring(display_orientation_raw, 2 ,len(display_orientation_raw)) as display_orientation2
      ,substring(display_orientation2, 1, len(display_orientation2)-1) as display_orientation

      ,substring(split_part(DISPLAY, ',',4),POSITION(':',split_part(DISPLAY, ',',4))+1) as display_width_raw
      ,substring(display_width_raw, 1, len(display_width_raw)-1) as display_width

      ,widget
      ,substring(split_part(WIDGET, ',', 1),POSITION(':',split_part(WIDGET,',',1))+1) as widget_dynamic_data_raw
      ,substring(split_part(WIDGET, ',', 2),POSITION(':',split_part(WIDGET,',',2))+1) as widget_height
      ,substring(split_part(WIDGET, ',', 3),POSITION(':',split_part(WIDGET,',',3))+1) as widget_layer
      ,substring(split_part(WIDGET, ',', 4),POSITION(':',split_part(WIDGET,',',4))+1) as widget_meta_data_raw
      ,substring(split_part(WIDGET, ',', 5),POSITION(':',split_part(WIDGET,',',5))+1) as widget_url_raw
      ,substring(widget_url_raw, 2 , len(widget_url_raw)) as widget_url2
      ,substring(widget_url2, 1, len(widget_url2)-1) as widget_url
      ,substring(split_part(WIDGET, ',', 6),POSITION(':',split_part(WIDGET,',',6))+1) as widget_width
      ,substring(split_part(WIDGET, ',', 7),POSITION(':',split_part(WIDGET,',',7))+1) as widget_x
      ,substring(split_part(WIDGET, ',', 8),POSITION(':',split_part(WIDGET,',',8))+1) as widget_y_raw
      ,substring(widget_y_raw, 1, len(widget_y_raw)-1) as widget_y

      ,content
      ,substring(split_part(CONTENT, ',', 1), POSITION(':',split_part(CONTENT,',',1))+1) as content_dynamic_data_raw
      ,substring(split_part(CONTENT, ',', 2), POSITION(':',split_part(CONTENT,',',2))+1) as content_height
      ,substring(split_part(CONTENT, ',', 3), POSITION(':',split_part(CONTENT,',',3))+1) as content_id
      ,substring(split_part(CONTENT, ',', 4), POSITION(':',split_part(CONTENT,',',4))+1) as content_layer
      ,substring(split_part(CONTENT, ',', 5), POSITION(':',split_part(CONTENT,',',5))+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
      ,substring(split_part(CONTENT, ',', 6), POSITION(':',split_part(CONTENT,',',6))+1) as content_url_raw
      ,substring(content_url_raw, 2, len(content_url_raw)) as content_url2
      ,substring(content_url2, 1, len(content_url2)-1) as content_url
      ,substring(split_part(CONTENT, ',', 7), POSITION(':',split_part(CONTENT,',',7))+1) as content_width
      ,substring(split_part(CONTENT, ',', 8), POSITION(':',split_part(CONTENT,',',8))+1) as content_x
      ,substring(split_part(CONTENT, ',', 9), POSITION(':',split_part(CONTENT,',',9))+1) as content_yraw
      ,substring(content_yraw, 1, len(content_yraw)-1) as content_y

      ,ad
      ,substring(split_part(AD, ',', 1), POSITION(':',split_part(AD,',',1))+1) as ad_dynamic_data_raw
      ,substring(split_part(AD, ',', 2), POSITION(':',split_part(AD,',',2))+1) as ad_height
      ,substring(split_part(AD, ',', 3), POSITION(':',split_part(AD,',',3))+1) as ad_id
      ,substring(split_part(AD, ',', 4), POSITION(':',split_part(AD,',',4))+1) as ad_layer
      ,substring(split_part(AD, ',', 5), POSITION(':',split_part(AD,',',5))+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
      ,substring(split_part(AD, ',', 6), POSITION(':',split_part(AD,',',6))+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
      ,substring(split_part(AD, ',', 7), POSITION(':',split_part(AD,',',7))+1) as ad_url_raw
      ,substring(ad_url_raw, 2, len(ad_url_raw)) as ad_url2
      ,substring(ad_url2, 1, len(ad_url2)-1) as ad_url
      ,substring(split_part(AD, ',', 8), POSITION(':',split_part(AD,',',8))+1) as ad_width
      ,substring(split_part(AD, ',', 9), POSITION(':',split_part(AD,',',9))+1) as ad_x
      ,substring(split_part(AD, ',', 10), POSITION(':',split_part(AD,',',10))+1) as ad_yraw
      ,substring(ad_yraw, 1, len(ad_yraw)-1) as ad_y

      ,sponsor
      ,substring(split_part(SPONSOR, ',', 1), POSITION(':',split_part(SPONSOR,',',1))+1) as sponsor_dynamic_data_raw
      ,substring(split_part(SPONSOR, ',', 2), POSITION(':',split_part(SPONSOR,',',2))+1) as sponsor_height
      ,substring(split_part(SPONSOR, ',', 3), POSITION(':',split_part(SPONSOR,',',3))+1) as sponsor_id_raw
      ,substring(split_part(SPONSOR, ',', 4), POSITION(':',split_part(SPONSOR,',',4))+1) as sponsor_layer_raw
      ,substring(split_part(SPONSOR, ',', 5), POSITION(':',split_part(SPONSOR,',',5))+1) as sponsor_meta_data_raw
      ,substring(split_part(SPONSOR, ',', 6), POSITION(':',split_part(SPONSOR,',',6))+1) as sponsor_provider_raw
      ,substring(split_part(SPONSOR, ',', 7), POSITION(':',split_part(SPONSOR,',',7))+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
      ,substring(split_part(SPONSOR, ',', 8), POSITION(':',split_part(SPONSOR,',',8))+1) as sponsor_width
      ,substring(split_part(SPONSOR, ',', 9), POSITION(':',split_part(SPONSOR,',',9))+1) as sponsor_x
      ,substring(split_part(SPONSOR, ',', 10), POSITION(':',split_part(SPONSOR,',',10))+1) as sponsor_yraw
      ,substring(sponsor_yraw, 1, len(sponsor_yraw)-1) as sponsor_y


      ,_sdc_extracted_at
      ,_sdc_batched_at
      ,_sdc_deleted_at
      ,_nsdu.server_name

      FROM "BSS_PRD"."POP_REPORT" as pop
      INNER JOIN _parameters as p
            --on (to_date(display_time) = p.p_start_date)
            on (to_date(pop.display_time_ts) = p.p_start_date)
      INNER JOIN _nsdu
            on (_nsdu.mac = pop.display_id)
      WHERE _SDC_DELETED_AT is null
      and ad_provider = 'Direct')

      ,_venueplan AS (
        select vp.*
        , SUBSTR(vp.torno||'.', 1, POSITION('.', vp.torno||'.') - 1) truncatedtorno
        from "SQL_SERVER_DBO"."VENUEPLAN" vp
        inner join _parameters p
          on vp.server_name = p.p_servername
        where _fivetran_deleted = false )

      ,cp_flight_schedule AS (select * from pivotal_sfa_dbo.cp_flight_schedule where _fivetran_deleted = false )
      ,cp_venue_worklist AS (select * from pivotal_sfa_dbo.cp_venue_worklist where _fivetran_deleted = false )

select pop.id
      ,pop.display_id
      ,pop.display_time_ts
      ,hour(display_time_ts) as hour
      ,pop.duration
      ,pop.created_at
      ,pop.modified_at
      ,pop.ad_url
      ,pop.ad_provider
      ,pop.content_url
      ,pop.sponsor_url
      ,vp.vpinstanceid
      ,vp.venueplanid
      ,vp.torno
      ,vp.truncatedtorno
      ,vp.flightno
      ,vp.add_fill
      ,vp.client
      ,vp.copy_set_no
      ,vp.priority
      ,vp.sch_start_hr
      ,vp.sch_end_hr
      ,vp.sch_rotation
      ,row_number() over (partition by hour, flightno, torno order by display_time_ts asc) as ro_no
      ,case when vp.add_fill = 0 then 'P'
            when vp.add_fill = 1 AND (ro_no <= sch_rotation) then 'P'
            else 'F' end as pay_or_fill
      ,vp.sic1
      ,vw.vl_id
      ,pop.server_name
from _pop_data pop
left join _venueplan vp
  on pop.ad_id = vp.venueplanid
left join cp_flight_schedule fs
  on vp.flight_schedule_id = fs.cp_flight_schedule_id
left join cp_venue_worklist vw
  on fs.venue_worklist_id = vw.cp_venue_worklists_id
order by display_time_ts