1 with _parameters as 2 (select to_date({% parameter p_start_date %}) as p_start_date 3 , {% parameter p_servername %} as p_servername) 4 5 -- generate list of display mac addresses from selected date's POP data 6 ,_pop_displays_list as ( 7 select distinct display_id 8 FROM "BSS_PRD"."POP_REPORT" pop 9 INNER JOIN _parameters p 10 on (to_date(pop.display_time_ts) = p.p_start_date)) 11 12 -- generate display (mac address) to server map from NetSuite 13 ,_netsuite_displaydetail as ( 14 SELECT du.display_unit_id 15 , du.display_unit_name 16 , du.playeriduniqueid 17 , du.server_name_id 18 , LOWER(du.mac_address) 19 , LOWER(du.uc_mac_address) 20 , LOWER(du.du_mac_address) 21 , ns.server_name 22 23 --, max(display_unit_id) over (partition by server_name_id) as du_per_server 24 --, iff(du_per_server=display_unit_id, 1, 0) as filter 25 FROM "NETSUITE"."DISPLAY_UNIT" du 26 LEFT JOIN "NETSUITE"."SERVER" ns 27 on du.server_name_id = ns.server_id 28 where du._fivetran_deleted = false 29 and lastreportdt is not null) 30 31 -- join display list (POP) to server mapping (NetSuite) to generate single display-per-server lookup table based on POP displays 32 ,_nsdu as ( 33 with _ndd as ( 34 SELECT playeriduniqueid as mac 35 , display_unit_id 36 , server_name_id 37 , server_name 38 , display_unit_name 39 , max(display_unit_id) over (partition by server_name_id) as du_per_server 40 --, iff(du_per_server=display_unit_id, 1, 0) as filter 41 from _netsuite_displaydetail ndd 42 inner join _pop_displays_list pdl on ndd.playeriduniqueid = pdl.display_id) 43 select *, iff(du_per_server=display_unit_id, 1, 0) as filter 44 from _ndd 45 inner join _parameters p 46 on p.p_servername = _ndd.server_name 47 where filter = 1) 48 49 -- pull pop data for a single display (POP) per server (NetSuite) 50 ,_pop_data as ( 51 SELECT ID, DISPLAY_ID, DISPLAY_TIME, DISPLAY_TIME_TS, DURATION, CREATED_AT, MODIFIED_AT, display 52 ,substring(split_part(DISPLAY, ',',1),POSITION(':',split_part(DISPLAY, ',',1))+1) as display_aspect_raw 53 ,substring(display_aspect_raw, 2 ,len(display_aspect_raw)) as display_aspect2 54 ,substring(display_aspect2, 1, len(display_aspect2)-1) as display_aspect 55 56 ,substring(split_part(DISPLAY, ',',2),POSITION(':',split_part(DISPLAY, ',',2))+1) as display_height 57 58 ,substring(split_part(DISPLAY, ',',3),POSITION(':',split_part(DISPLAY, ',',3))+1) as display_orientation_raw 59 ,substring(display_orientation_raw, 2 ,len(display_orientation_raw)) as display_orientation2 60 ,substring(display_orientation2, 1, len(display_orientation2)-1) as display_orientation 61 62 ,substring(split_part(DISPLAY, ',',4),POSITION(':',split_part(DISPLAY, ',',4))+1) as display_width_raw 63 ,substring(display_width_raw, 1, len(display_width_raw)-1) as display_width 64 65 ,widget 66 ,substring(split_part(WIDGET, ',', 1),POSITION(':',split_part(WIDGET,',',1))+1) as widget_dynamic_data_raw 67 ,substring(split_part(WIDGET, ',', 2),POSITION(':',split_part(WIDGET,',',2))+1) as widget_height 68 ,substring(split_part(WIDGET, ',', 3),POSITION(':',split_part(WIDGET,',',3))+1) as widget_layer 69 ,substring(split_part(WIDGET, ',', 4),POSITION(':',split_part(WIDGET,',',4))+1) as widget_meta_data_raw 70 ,substring(split_part(WIDGET, ',', 5),POSITION(':',split_part(WIDGET,',',5))+1) as widget_url_raw 71 ,substring(widget_url_raw, 2 , len(widget_url_raw)) as widget_url2 72 ,substring(widget_url2, 1, len(widget_url2)-1) as widget_url 73 ,substring(split_part(WIDGET, ',', 6),POSITION(':',split_part(WIDGET,',',6))+1) as widget_width 74 ,substring(split_part(WIDGET, ',', 7),POSITION(':',split_part(WIDGET,',',7))+1) as widget_x 75 ,substring(split_part(WIDGET, ',', 8),POSITION(':',split_part(WIDGET,',',8))+1) as widget_y_raw 76 ,substring(widget_y_raw, 1, len(widget_y_raw)-1) as widget_y 77 78 ,content 79 ,substring(split_part(CONTENT, ',', 1), POSITION(':',split_part(CONTENT,',',1))+1) as content_dynamic_data_raw 80 ,substring(split_part(CONTENT, ',', 2), POSITION(':',split_part(CONTENT,',',2))+1) as content_height 81 ,substring(split_part(CONTENT, ',', 3), POSITION(':',split_part(CONTENT,',',3))+1) as content_id 82 ,substring(split_part(CONTENT, ',', 4), POSITION(':',split_part(CONTENT,',',4))+1) as content_layer 83 ,substring(split_part(CONTENT, ',', 5), POSITION(':',split_part(CONTENT,',',5))+1) as content_meta_data_raw 84 ,substring(content_meta_data_raw, 2, len(content_meta_data_raw)) as content_meta_data2 85 ,substring(content_meta_data2, 1, len(content_meta_data2)-1) as content_meta_data 86 ,substring(split_part(CONTENT, ',', 6), POSITION(':',split_part(CONTENT,',',6))+1) as content_url_raw 87 ,substring(content_url_raw, 2, len(content_url_raw)) as content_url2 88 ,substring(content_url2, 1, len(content_url2)-1) as content_url 89 ,substring(split_part(CONTENT, ',', 7), POSITION(':',split_part(CONTENT,',',7))+1) as content_width 90 ,substring(split_part(CONTENT, ',', 8), POSITION(':',split_part(CONTENT,',',8))+1) as content_x 91 ,substring(split_part(CONTENT, ',', 9), POSITION(':',split_part(CONTENT,',',9))+1) as content_yraw 92 ,substring(content_yraw, 1, len(content_yraw)-1) as content_y 93 94 ,ad 95 ,substring(split_part(AD, ',', 1), POSITION(':',split_part(AD,',',1))+1) as ad_dynamic_data_raw 96 ,substring(split_part(AD, ',', 2), POSITION(':',split_part(AD,',',2))+1) as ad_height 97 ,substring(split_part(AD, ',', 3), POSITION(':',split_part(AD,',',3))+1) as ad_id 98 ,substring(split_part(AD, ',', 4), POSITION(':',split_part(AD,',',4))+1) as ad_layer 99 ,substring(split_part(AD, ',', 5), POSITION(':',split_part(AD,',',5))+1) as ad_meta_data_raw 100 ,substring(ad_meta_data_raw, 2, len(ad_meta_data_raw)) as ad_meta_data2 101 ,substring(ad_meta_data2, 1, len(ad_meta_data2)-1) as ad_meta_data 102 ,substring(split_part(AD, ',', 6), POSITION(':',split_part(AD,',',6))+1) as ad_provider_raw 103 ,substring(ad_provider_raw, 2, len(ad_provider_raw)) as ad_provider2 104 ,substring(ad_provider2, 1, len(ad_provider2)-1) as ad_provider 105 ,substring(split_part(AD, ',', 7), POSITION(':',split_part(AD,',',7))+1) as ad_url_raw 106 ,substring(ad_url_raw, 2, len(ad_url_raw)) as ad_url2 107 ,substring(ad_url2, 1, len(ad_url2)-1) as ad_url 108 ,substring(split_part(AD, ',', 8), POSITION(':',split_part(AD,',',8))+1) as ad_width 109 ,substring(split_part(AD, ',', 9), POSITION(':',split_part(AD,',',9))+1) as ad_x 110 ,substring(split_part(AD, ',', 10), POSITION(':',split_part(AD,',',10))+1) as ad_yraw 111 ,substring(ad_yraw, 1, len(ad_yraw)-1) as ad_y 112 113 ,sponsor 114 ,substring(split_part(SPONSOR, ',', 1), POSITION(':',split_part(SPONSOR,',',1))+1) as sponsor_dynamic_data_raw 115 ,substring(split_part(SPONSOR, ',', 2), POSITION(':',split_part(SPONSOR,',',2))+1) as sponsor_height 116 ,substring(split_part(SPONSOR, ',', 3), POSITION(':',split_part(SPONSOR,',',3))+1) as sponsor_id_raw 117 ,substring(split_part(SPONSOR, ',', 4), POSITION(':',split_part(SPONSOR,',',4))+1) as sponsor_layer_raw 118 ,substring(split_part(SPONSOR, ',', 5), POSITION(':',split_part(SPONSOR,',',5))+1) as sponsor_meta_data_raw 119 ,substring(split_part(SPONSOR, ',', 6), POSITION(':',split_part(SPONSOR,',',6))+1) as sponsor_provider_raw 120 ,substring(split_part(SPONSOR, ',', 7), POSITION(':',split_part(SPONSOR,',',7))+1) as sponsor_url_raw 121 ,substring(sponsor_url_raw, 2, len(sponsor_url_raw)) as sponsor_url2 122 ,substring(sponsor_url2, 1, len(sponsor_url2)-1) as sponsor_url 123 ,substring(split_part(SPONSOR, ',', 8), POSITION(':',split_part(SPONSOR,',',8))+1) as sponsor_width 124 ,substring(split_part(SPONSOR, ',', 9), POSITION(':',split_part(SPONSOR,',',9))+1) as sponsor_x 125 ,substring(split_part(SPONSOR, ',', 10), POSITION(':',split_part(SPONSOR,',',10))+1) as sponsor_yraw 126 ,substring(sponsor_yraw, 1, len(sponsor_yraw)-1) as sponsor_y 127 128 129 ,_sdc_extracted_at 130 ,_sdc_batched_at 131 ,_sdc_deleted_at 132 ,_nsdu.server_name 133 134 FROM "BSS_PRD"."POP_REPORT" as pop 135 INNER JOIN _parameters as p 136 --on (to_date(display_time) = p.p_start_date) 137 on (to_date(pop.display_time_ts) = p.p_start_date) 138 INNER JOIN _nsdu 139 on (_nsdu.mac = pop.display_id) 140 WHERE _SDC_DELETED_AT is null 141 and ad_provider = 'Direct') 142 143 ,_venueplan AS ( 144 select vp.* 145 , SUBSTR(vp.torno||'.', 1, POSITION('.', vp.torno||'.') - 1) truncatedtorno 146 from "SQL_SERVER_DBO"."VENUEPLAN" vp 147 inner join _parameters p 148 on vp.server_name = p.p_servername 149 where _fivetran_deleted = false ) 150 151 ,cp_flight_schedule AS (select * from pivotal_sfa_dbo.cp_flight_schedule where _fivetran_deleted = false ) 152 ,cp_venue_worklist AS (select * from pivotal_sfa_dbo.cp_venue_worklist where _fivetran_deleted = false ) 153 154select pop.id 155 ,pop.display_id 156 ,pop.display_time_ts 157 ,hour(display_time_ts) as hour 158 ,pop.duration 159 ,pop.created_at 160 ,pop.modified_at 161 ,pop.ad_url 162 ,pop.ad_provider 163 ,pop.content_url 164 ,pop.sponsor_url 165 ,vp.vpinstanceid 166 ,vp.venueplanid 167 ,vp.torno 168 ,vp.truncatedtorno 169 ,vp.flightno 170 ,vp.add_fill 171 ,vp.client 172 ,vp.copy_set_no 173 ,vp.priority 174 ,vp.sch_start_hr 175 ,vp.sch_end_hr 176 ,vp.sch_rotation 177 ,row_number() over (partition by hour, flightno, torno order by display_time_ts asc) as ro_no 178 ,case when vp.add_fill = 0 then 'P' 179 when vp.add_fill = 1 AND (ro_no <= sch_rotation) then 'P' 180 else 'F' end as pay_or_fill 181 ,vp.sic1 182 ,vw.vl_id 183 ,pop.server_name 184from _pop_data pop 185left join _venueplan vp 186 on pop.ad_id = vp.venueplanid 187left join cp_flight_schedule fs 188 on vp.flight_schedule_id = fs.cp_flight_schedule_id 189left join cp_venue_worklist vw 190 on fs.venue_worklist_id = vw.cp_venue_worklists_id 191order by display_time_ts