Untitled

Declare @dealYear as varchar(10) = '2014'
Declare @dealNumber as varchar(10) = '0306'
 
select deal_year as dealYear, deal_number as dealNumber, deal_start_date as dealStartDate, deal_end_date as dealEndDate, dcsl.media_channel as channel
, deal_week as dealWeek, deal_kind_cd as dealKindCode, fmt.description as mediaType
, isnull(fde.early_print_date,deal_print_date) as printDate, dcsl.store_list_code as storeList, dp.number as pageNumber, dp.mte as pageSize, dp.width, dp.height
, dp.number_of_slot as numberOfSlots, dp.theme as pageTheme, mpp.description as mediaProgram
, case
    when s.alt_slot_version_number <> 0
    then concat(cast(s.slot_number as varchar(10)), '_', cast(s.alt_slot_version_number as varchar(10)))
    else cast(s.slot_number as varchar(10))
  end as slotNumber
, alt.slot_number as parentSlot
, s.mte_value as slotSize, s.width_value as width, s.height_value as height, s.x_position_value as xPositionValue
, s.y_position_value as yPositionValue, mps.description as mediaProgram, fpp.description as priority, iif(s.product_flag = 1,'N','Y') as nonProductSlot
, fcp.description as creativePosition, fct.description as creativeTreatment, fpssl.store_list_code
from deal d with (nolock)
inner join deal_channel_store_list dcsl with (nolock) on d.deal_id = dcsl.deal_id and dcsl.media_channel = 'FLYER'
inner join fp_media fm with (nolock) on d.deal_id = fm.deal_id
inner join fp_media_type fmt with (nolock) on fm.media_type_code = fmt.code
inner join fp_deal_page dp with (nolock) on d.deal_id = dp.deal_id
inner join slot s with (nolock) on dp.id = s.page_id
left join slot alt with (nolock) on s.national_slot_id = alt.slot_id and alt.alt_slot_version_number = 0
left join flyer_date_exception fde with (nolock) on d.deal_id = fde.deal_id
left join fp_media_program mpp with (nolock) on dp.media_program_code = mpp.code
left join fp_media_program mps with (nolock) on s.media_program_code = mps.code
left join fp_priority fpp with (nolock) on s.priority_code = fpp.code
left join fp_creative_position fcp with (nolock) on s.creative_position_code = fcp.code
left join fp_creative_treatment fct with (nolock) on s.creative_treatment_code = fct.code
left join fp_slot_store_list fpssl with (nolock) on s.slot_id = fpssl.slot_id
 
where deal_year = @dealYear
and deal_number = @dealNumber
order by dp.number, s.slot_number, s.alt_slot_version_number