Untitled
unknown
plain_text
2 years ago
8.6 kB
1
Indexable
Never
--Declare @asOfDateTime as datetime = '2022-05-01'
Declare @storeNumber as varchar(10) = '0453'
Declare @productNumber as varchar(30) = '0072014'
Declare @dealNumber as nvarchar(10) = '0318'
Declare @dealYear as nvarchar(4) = '2022'
----Retail Promo Prices by Product, Store and asOfDateTime
select a.*, s.store_number
from fp_store_list_store fpsls with (nolock)
inner join store s with (nolock) on fpsls.store_id = s.store_id
inner join
(select p.product_num, d.deal_year, d.deal_number, d.deal_kind_cd, d.deal_start_date, d.deal_end_date
, case
when a1.zone_number is not null
then a1.zone_number
else ppp.zone_number
end as zone_number
, case
when a1.zone_number is not null
then a1.save_tactic
else fg.save_tactic
end as save_tactic
, case
when a1.zone_number is not null
then a1.calc_save_dollar
else ppp.calc_save_dollar
end as calc_save_dollar
, case
when a1.zone_number is not null
then a1.calc_save_percent
else ppp.calc_save_percent
end as calc_save_percent
, case
when isnull(a1.base_regular_price,ppp.snapshot_reg_price_amount) = isnull(a1.base_promo_price,ppp.price_amount)
and s.media_program_code <> 'LOY' and p.special_buy_ind = 'N'
then 'GREAT VALUE'
when isnull(a1.save_tactic,fg.save_tactic) = 'PERCENT'
and isnull(a1.calc_save_percent,ppp.calc_save_percent) > '10'
then 'SAVE'
when isnull(a1.save_tactic,fg.save_tactic) = 'DOLLAR'
and isnull(a1.calc_save_dollar,ppp.calc_save_dollar) > '10'
then 'SAVE'
when isnull(a1.save_tactic,fg.save_tactic) = 'PERCENT'
and isnull(a1.base_regular_price,ppp.snapshot_reg_price_amount) <> isnull(a1.base_promo_price,ppp.price_amount)
and s.media_program_code <> 'LOY' and p.special_buy_ind = 'N'
and isnull(a1.calc_save_percent,ppp.calc_save_percent) < '10'
then 'NOW'
when isnull(a1.save_tactic,fg.save_tactic) = 'DOLLAR'
and isnull(a1.base_regular_price,ppp.snapshot_reg_price_amount) <> isnull(a1.base_promo_price,ppp.price_amount)
and s.media_program_code <> 'LOY' and p.special_buy_ind = 'N'
and isnull(a1.calc_save_dollar,ppp.calc_save_dollar) < '10'
then 'NOW'
when p.special_buy_ind = 'Y'
then 'SPECIAL BUY'
end as save_story_english
, case
when isnull(a1.base_regular_price,ppp.snapshot_reg_price_amount) = isnull(a1.base_promo_price,ppp.price_amount)
and s.media_program_code <> 'LOY' and p.special_buy_ind = 'N'
then 'PRIX AVANTAGEUX'
when isnull(a1.save_tactic,fg.save_tactic) = 'PERCENT'
and isnull(a1.calc_save_percent,ppp.calc_save_percent) > '10'
then 'RABAIS'
when isnull(a1.save_tactic,fg.save_tactic) = 'DOLLAR'
and isnull(a1.calc_save_dollar,ppp.calc_save_dollar) > '10'
then 'RABAIS'
when isnull(a1.save_tactic,fg.save_tactic) = 'PERCENT'
and isnull(a1.base_regular_price,ppp.snapshot_reg_price_amount) <> isnull(a1.base_promo_price,ppp.price_amount)
and s.media_program_code <> 'LOY' and p.special_buy_ind = 'N'
and isnull(a1.calc_save_percent,ppp.calc_save_percent) < '10'
then 'MAINTENANT'
when isnull(a1.save_tactic,fg.save_tactic) = 'DOLLAR'
and isnull(a1.base_regular_price,ppp.snapshot_reg_price_amount) <> isnull(a1.base_promo_price,ppp.price_amount)
and s.media_program_code <> 'LOY' and p.special_buy_ind = 'N'
and isnull(a1.calc_save_dollar,ppp.calc_save_dollar) < '10'
then 'MAINTENANT'
when p.special_buy_ind = 'Y'
then 'ACHAT SPÉCIAL'
end as save_story_french
, case
when fpsl1.code is not null
then fpsl1.code
else fpsl2.code
end as store_list_code
from deal d with (nolock)
inner join flighted_group fg with (nolock) on d.deal_id = fg.pre_flight_deal_id
inner join flighted_product fp with (nolock) on fg.id = fp.flighted_group_id
inner join product p with (nolock) on fp.product_id = p.id
inner join promo_product_price ppp with (nolock) on fp.id = ppp.flighted_product_id
inner join
(select z.zone_id, zone_number, zone_type_code, zone_end_date, product_id
from zones z with (nolock)
inner join zone_product zp1 with (nolock) on z.zone_id = zp1.zone_id
) zp on ppp.zone_number = zp.zone_number and fp.product_id = zp.product_id
inner join zone_store zs with (nolock) on zp.zone_id = zs.zone_id
inner join store s1 with (nolock) on zs.store_id = s1.store_id
left join promo_price_eco_fee pef with (nolock) on ppp.product_price_id = pef.promo_price_in_zone_id
left join
(select product_id, retail_casting_amount, effective_date
, LEAD((DATEADD(day, -1, effective_date)), 1, '9999-12-31') OVER (PARTITION BY product_fee_id order by update_date) as end_date
from product_fee with (nolock)
) pfh on pfh.product_id = p.id and d.deal_start_date between pfh.effective_date and pfh.end_date
left join
(select p.product_num, deal_year, deal_number, deal_kind_cd, d.deal_start_date, d.deal_end_date
, ppp.zone_number, ppp.snapshot_reg_price_amount as base_regular_price
--, (ppp.snapshot_reg_price_amount+isnull(pfh.retail_casting_amount,0)+isnull(pef.eco_fee_amount,0)) as total_regular_price
, ppp.price_amount as base_promo_price
--, ppp.price_effective_date, pef.eco_fee_amount, pfh.retail_casting_amount
--,(ppp.price_amount+isnull(pfh.retail_casting_amount,0)+isnull(pef.eco_fee_amount,0)) as total_promo_price
, s1.store_number, fg.save_tactic, ppp.calc_save_percent, ppp.calc_save_dollar
from deal d with (nolock)
inner join flighted_group fg with (nolock) on d.deal_id = fg.pre_flight_deal_id
inner join flighted_product fp with (nolock) on fg.id = fp.flighted_group_id
inner join product p with (nolock) on fp.product_id = p.id
inner join promo_product_price ppp with (nolock) on fp.id = ppp.flighted_product_id
inner join
(select z.zone_id, zone_number, zone_type_code, zone_end_date, product_id
from zones z with (nolock)
inner join zone_product zp1 with (nolock) on z.zone_id = zp1.zone_id
) zp on ppp.zone_number = zp.zone_number and fp.product_id = zp.product_id
inner join zone_store zs with (nolock) on zp.zone_id = zs.zone_id
inner join store s1 with (nolock) on zs.store_id = s1.store_id
--left join promo_price_eco_fee pef with (nolock) on ppp.product_price_id = pef.promo_price_in_zone_id
--left join
--(select product_id, retail_casting_amount, effective_date
--, LEAD((DATEADD(day, -1, effective_date)), 1, '9999-12-31') OVER (PARTITION BY product_fee_id order by update_date) as end_date
--from product_fee with (nolock)
--) pfh on pfh.product_id = p.id and d.deal_start_date between pfh.effective_date and pfh.end_date
--where @asOfDateTime between d.deal_start_date and d.deal_end_date
where d.deal_year = @dealYear
and d.deal_number = @dealNumber
and p.product_num = @productNumber
and s1.store_type_code in ('RETAIL','PARTY_CITY','ONLINE')
and s1.store_status_code in ('ACTIVE','PRE_ACTIVE')
and s1.store_number = @storeNumber
and zp.zone_type_code = 'O'
and zp.zone_end_date > convert(date,getdate())
and fp.status_code = 'FLT'
and ppp.price_type_code = 'Retail'
and validated_ind = 1
) a1 on d.deal_year = a1.deal_year and d.deal_number = a1.deal_number and p.product_num = a1.product_num and s1.store_number = a1.store_number
left join flighted_group_media_channel fgmc with (nolock) on fg.id = fgmc.flighted_group_id
left join slot s with (nolock) on fgmc.media_channel_reference_id = s.slot_id
left join fp_deal_page dp with (nolock) on s.page_id = dp.id and d.deal_id = dp.deal_id
left join fp_slot_store_list fpssl with (nolock) on fpssl.slot_id = s.slot_id
left join fp_store_list fpsl1 ON fpssl.store_list_code = fpsl1.code
--left join fp_media m with (nolock) on d.deal_id = m.deal_id
--left join fp_store_list fpsl2 on m.store_list_code = fpsl2.code
left join deal_channel_store_list dcsl with (nolock) on d.deal_id = dcsl.deal_id
left join fp_store_list fpsl2 ON dcsl.store_list_code = fpsl2.code
--where d.deal_end_date >= convert(date,getdate())
--where @asOfDateTime between d.deal_start_date and d.deal_end_date
where d.deal_year = @dealYear
and d.deal_number = @dealNumber
and p.product_num = @productNumber
and s1.store_type_code in ('RETAIL','PARTY_CITY','ONLINE')
and s1.store_status_code in ('ACTIVE','PRE_ACTIVE')
and s1.store_number = @storeNumber
and zp.zone_type_code = 'N'
and zp.zone_end_date > convert(date,getdate())
and fp.status_code = 'FLT'
and ppp.price_type_code = 'Retail'
and validated_ind = 1
--and fpsl1.code is not null
) a on fpsls.store_list_code = a.store_list_code
where s.store_number = @storeNumber
--group by product_num --a.deal_year, a.deal_number, a.deal_kind_cd
--having count (*) > 1