Untitled

mail@pastecode.io avatar
unknown
plain_text
2 years ago
1.9 kB
2
Indexable
Never
select
        distinct p.product_num as productNumber,
        dcsl.store_list_code as dealStoreListCode,
        fssl.store_list_code as slotStoreListCode,
        fg.save_tactic as saveTactic,
        ppp.zone_number as zone,
        z.zone_type_code as zoneTypeCode,
        ppp.calc_save_percent as savePercentAmount,
        ppp.calc_save_dollar as saveDollarAmount,
        d.deal_number as dealNumber,
        d.deal_year as dealYear,
        d.deal_start_date as dealStartDate,
        ppp.price_amount as basePromoPrice,
        ppp.snapshot_reg_price_amount as baseRegularPrice,
        p.special_buy_ind as isSpecialBuy 
    from
        promo_product_price ppp   
    join
        zones z 
            on ppp.zone_number = z.zone_number   
    join
        flighted_product fp 
            on ppp.flighted_product_id = fp.id   
    join
        flighted_group fg 
            on fp.flighted_group_id = fg.id   
    join
        flighted_group_media_channel fgmc 
            on fg.id = fgmc.flighted_group_id   
    join
        slot s 
            on fgmc.media_channel_reference_id = s.slot_id   
    join
        fp_deal_page dp 
            on s.page_id = dp.id   
    join
        deal d 
            on dp.deal_id = d.deal_id   
    join
        product p 
            on fp.product_id = p.id   
    left join
        deal_channel_store_list dcsl 
            on d.deal_id = dcsl.deal_id   
    left join
        fp_slot_store_list fssl 
            on s.slot_id = fssl.slot_id 
    where
        fp.status_code='FLT' 
        and fp.validated_ind=1 
        and ppp.price_type_code='Retail' 
        and p.product_num in (
            ?, ?
        ) 
        and (
            d.deal_start_date <= ? 
            and (
                d.deal_end_date is null 
                or d.deal_end_date >= ?
            ) 
        ) 
    order by
        p.product_num