Untitled

--Declare @asOfDateTime as datetime = '2022-05-01'

Declare @storeNumber as varchar(10) = '0453'

--Declare @productNumber as varchar(30) = '0072014'

Declare @dealNumber as nvarchar(10) = '0320' 

Declare @dealYear as nvarchar(4) = '2022'

--Declare @pageNumber as integer = 23;

--Declare @pageSlot as integer = 2;



----Save-stories by deal, page, slot

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, dp.number, s.slot_number

, 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

--and a.number = @pageNumber

--and a.slot_number = @pageSlot

--having count (*) > 1