Untitled

Declare @asOfDateTime as datetime = '2022-02-11'
Declare @storeNumber as varchar(10) = '0002'
Declare @productNumber as varchar(30) = '4082000'

----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 base_regular_price
, case
	when a1.zone_number is not null
	then a1.calc_save_dollar
	else ppp.calc_save_dollar
  end as total_regular_price
, case
	when a1.zone_number is not null
	then a1.calc_save_percent
	else ppp.calc_save_percent
  end as base_promo_price

, 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
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
and p.product_num = @productNumber
) 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
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 p.product_num = @productNumber
--and fpsl1.code is not null
) a on fpsls.store_list_code = a.store_list_code
where s.store_number = @storeNumber