Finding Exchanges

I had to do something similar to create a subscription history that includes exchanges in the same order and in another order. The way i did it was with a recursive CTE using the ret_parent_sli_no. I'ts been a while since i wrote it and i don't comment my code well but am happy to share and answer questions.

I'm sure it would be helpful, thanks!

here you go.

USE [impresario]
GO
/****** Object: StoredProcedure [dbo].[LWP_Get_Subscription_info_new] Script Date: 1/8/2019 1:26:50 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Mendy
-- Create date: 6/23/15
-- Description: 
-- =============================================
ALTER PROCEDURE [dbo].[LWP_Get_Subscription_info_new]
--@customer_no int,
@sessionkey varchar(64) = null,
@curentFY int = null,-- = 2016
@pastHistory int = null-- = 4
AS
SET NOCOUNT ON;

--/*
Declare @errmsg varchar(200), @customer_no int

-- validate parameters
If @sessionkey is null or Not exists (select * from t_web_session_session where sessionkey = @sessionkey)
Begin
select @errmsg = 'Invalid Session ID'
RAISERROR(@errmsg, 11, 2) WITH SETERROR
return -101
End

Select @customer_no = customer_no from t_web_session_session where sessionkey = @sessionkey

If @customer_no is null or Not exists (select * from t_customer where customer_no = @customer_no)
Begin
select @errmsg = 'Invalid Customer_no'
RAISERROR(@errmsg, 11, 2) WITH SETERROR
return -101
End

Exec WP_SET_WEB_CONTEXT @sessionkey = @sessionkey
--*/

-- if no fiscal year provided if it is past 6/30 the fiscal year is next year otherwise it is this year
set @curentFY = (coalesce(@curentFY,
case
when datepart(M,getdate()) > 6 then
datepart(YYYY,getdate()) + 1
else
datepart(YYYY,getdate())
end
))

select
distinct
ord.customer_no,
1 as Subscription_Order_No,
ord.order_no,
ord.order_dt,
cast(
case
when delivery.id = 1 then 'Postal Mail'
when delivery.id = -1 then 'Hold at Box Office'
when delivery.id in (3,4) then 'E-Ticket Print At Home'
else ''
end as varchar(30)) as DeliveryMethod,
--ord.appeal_no,
--apl.description as appeal_desc,
--apl.campaign_no,
--cmp.description as campaign_desc,
--ord.source_no,
--src.source_name,
ord.MOS as MOS,
mos.description as MOS_Desc,
cat.description as category,
ord.tot_contribution_amt,
ord.tot_fee_amt,
ord.tot_ticket_purch_amt + ord.tot_ticket_return_amt as tot_ticket_purch_amt,
li.li_seq_no as lineitem,
sli.sli_no as Sub_lineitem,
case when sli.due_amt = 0 then 'True' else 'False' end as comp,
--sli_d.detail_sli_no as Sub_lineitem_detail_no,
sli.sli_status,
sli_st.description as sli_status_desc,
sli.ret_parent_sli_no,
sli.price_type,
perf.season_no as season,
perf.season_name,
pkg.pkg_code,
pkg.description as pkg_desc,
perf.perf_code,
perf.perf_no,
prodExch.Exch_Start_Dt,
prodExch.Exch_End_Dt,
prodExch.Display_Seating,
Perf_Preview = case when exists(select * from TX_INV_TKW aa (nolock) where aa.inv_no = perf.perf_no and aa.tkw = 46) then 'True' else 'False' end,
perf.prod_name as perf_name,
coalesce(Full_Name,perf.prod_name,'') as full_perf_Name,
perf.perf_dt,
case
when perf.facility like 'Stage 2%' or perf.facility like 'Stage II%' then 'Stage II'
when perf.facility like 'Stage 1%' or perf.facility like 'Stage I%' then 'Stage I'
when perf.facility like '%Friedman%' then 'Friedman'
else perf.facility
end as facility_short, --perf.facility,
case
when perf.facility like 'Stage 2%' or perf.facility like 'Stage II%' then 'NY CITY CENTER Stage II'
when perf.facility like 'Stage 1%' or perf.facility like 'Stage I%' then 'NY CITY CENTER Stage I'
when perf.facility like '%Friedman%' then 'SAMUEL J. FRIEDMAN THEATRE'
else perf.facility
end as facility_Full, --perf.facility,
perf.fyear,
seat_sec.description as section,
seat.seat_row,
seat.seat_num,
seat.seat_no,
cast(null as int) as ticket_no,
cast(null as char(1)) as [checksum],
--pt.description as priceType,
--pt_cat.description as priceTypeCat,
--p_cat.description as PriceCat,
sli.due_amt as sli_due_amt,
--ord.tot_contribution_amt as order_cont_amt,
--sli_d.due_amt as sli_d_due_amt,
--li.tot_pur_amt,
li.num_seats_pur,--is subscription when perf_code like '^%'
li.tot_ret_amt,
isnull(li.num_seats_ret,0) as num_seats_ret
--sli.last_updated_by,
--sli.last_update_dt
into #work1
from
t_order ord (nolock)
left outer join tr_order_category cat (nolock) on cat.id = ord.class
left outer join t_lineitem li (nolock) on li.order_no = ord.order_no
left outer join t_appeal apl (nolock) on apl.appeal_no = ord.appeal_no
left outer join t_campaign cmp (nolock) on cmp.campaign_no = apl.campaign_no
left outer join TX_APPEAL_MEDIA_TYPE src (nolock) on src.source_no = ord.source_no
left outer Join tr_mos mos (nolock) on mos.id = ord.mos
left outer join LV_PERF_PROD_SEASON perf (nolock) on perf.perf_no = li.perf_no
left outer join LTR_Prod_Titles_Complete Perf_fullName on perf.prod_season_no = Perf_fullName.Prod_Season_No
left outer join ltr_prod_Exch_Open_Close prodExch (nolock) on perf.prod_season_no = prodExch.prod_season_no
left outer join t_pkg pkg (nolock) on li.pkg_no=pkg.pkg_no
left outer join t_sub_lineitem sli (nolock) on sli.li_seq_no = li.li_seq_no
left outer join TR_SLI_STATUS sli_st (nolock) on sli.sli_status = sli_st.id
left outer join tr_price_type pt (nolock) on sli.price_type = pt.id
left outer join TR_PRICE_TYPE_CATEGORY pt_cat (nolock) on pt_cat.id = pt.price_type_category
left outer join TR_ORDER_SHIP_METHOD delivery (nolock) on ord.delivery = delivery.id
--left outer join t_sli_detail sli_d (nolock) on sli_d.sli_no = sli.sli_no
--JOIN t_pmap pmap (nolock) on pmap.pmap_no = sli_d.pmap_no
--join TR_PRICE_CATEGORY p_cat (nolock) on p_cat.id = pmap.price_category
left outer join t_seat seat (nolock) on seat.seat_no = sli.seat_no --and isnull(sli.seat_no,0) > 0
left outer join tr_section seat_sec (nolock) on seat_sec.id = seat.section
where
customer_no = @customer_no
--ord.order_no = 488993
--and perf_code not like '^%'
and prod_name not like '*CH%'
and prod_name not like '!%'
and prod_name not like '%Passport Voucher%'
--perf.season_no in (8)
and li.primary_ind = 'Y'
and
(
ord.tot_ticket_purch_amt > 0
or
li.num_seats_pur > li.num_seats_ret
)
--and ord.tot_ticket_paid_amt > 0
--and ord.mos in (10,20)
--and not isnull(ord.tot_ticket_return_amt,0) = (-1 * ord.tot_ticket_purch_amt)
and perf.fyear > @curentFY - isnull(@pastHistory,1)

and sli.sli_status not in (7)
--and not (sli.sli_status in(8,7) and mos not in(10)) -- no returns and exchanges that arent in the original subscription order
--and cat.description not like '%flex%'
--order by
--ord.customer_no,
--ord.customer_no, season, mos, ord.order_no, li.li_seq_no, sli.sli_no--, sli_d.detail_sli_no

--select * from #work1

-- add subscription order_no each ticket belongs to
;with
orders(parent_order_no, child_order_no) as
(
select distinct
sli_parent_order.order_no as parent_order_no,
sli_child_order.order_no as child_order_no--,
--row_number() over (partition by sli_child_order.order_no order by sli_child_order.order_no) as sort
from
T_SUB_LINEITEM sli_child_order
join T_SUB_LINEITEM sli_parent_order on sli_child_order.ret_parent_sli_no = sli_parent_order.sli_no
where
-- focus on orders for specific customer
sli_child_order.order_no in (select distinct order_no from #work1) -- t_order where customer_no = @customer_no)
-- sub line item is a return
and sli_child_order.sli_status = 4 -- 4 = return
and sli_parent_order.sli_status = 8 -- 8 = VOO
--and
-- and this order does not have any sub line items that are VOO
--and sli_child_order.order_no not in (select order_no from T_SUB_LINEITEM a where a.sli_status = 8)
),
w1(parent_order_no, child_order_no, level) as
(
select distinct
parent_order_no,
child_order_no,
--sort,
0 as Level
from
orders
join t_order ord on orders.parent_order_no = ord.order_no
where
--child_order_no not in (select distinct parent_order_no from orders) -- no returns against this order
--and sort = 1
ord.mos = 10

--/*
UNION ALL

select
w1.parent_order_no,
orders.child_order_no,
--orders.sort,
Level + 1
from
orders
join w1 on w1.child_order_no = orders.parent_order_no
--and orders.sort = 1
--and w1.sort = 1
--*/
)
update a
set a.Subscription_Order_No = case when MOS <> 9 then coalesce(w1.parent_order_no,a.order_no) else 1 end
from #work1 a
left outer join w1 on a.order_no = w1.child_order_no

--/*

--select * from #work1

-- for the order date use either the greater of (order date / erliest ticket purchase trasnaction date)
;with order_dt(order_no, order_dt)
as
(
select order_no, max(order_dt) from(
(
select
t_order.order_no as order_no, max(t_order.order_dt) as order_dt
from
t_order
join #work1 on t_order.order_no = #work1.order_no
group by t_order.order_no
--where
--order_no = @order_no
union
select
T_TRANSACTION.order_no as order_no, min(T_TRANSACTION.trn_dt) as order_dt
from
T_TRANSACTION
join #work1 on T_TRANSACTION.order_no = #work1.order_no
--where
--order_no = @order_no
and trn_type = 32
group by T_TRANSACTION.order_no
)

) as aaaa
group by aaaa.order_no
)
update a
set a.order_dt = b.order_dt
from
#work1 a
join order_dt b on a.order_no = b.order_no

-- add ticket numbers from tx_sli_ticket

update a
set
a.ticket_no = b.ticket_no,
a.[checksum] = b.[checksum]
from
#work1 a
join (
select aa.sli_no, aa.ticket_no, aa.pkg_no, aa.perf_no, aa.seat_no, aa.[checksum],
row_number() over(partition by sli_no Order by reprint_no desc) rowNumber
from TX_SLI_TICKET (nolock) aa
join #work1 bb on aa.sli_no = bb.Sub_lineitem
)as b on a.Sub_lineitem = b.sli_no and rowNumber = 1

select
*
into #work2
from

(
select distinct
'sub' as rowType,
*
from
#work1 a
--join subCount b on b.order_no = a.order_no
where
MOS in (10) -- 10=Subscription
and sli_status not in(4) --4=Return

--select 'subscription exchanged history'

union all

select
'sub exch' as rowType,
*
from
#work1 a
where
mos = 5 --5=Exchanges
and sli_status not in (4,7,8)
/* sli_status
id description
4 Return
7 Void-Returned in this order
8 Void-Returned in other order
*/

union all

--select 'single ticket history'
select
'Add' as rowType,
*
from
#work1 a
where
mos = 9 -- 9=Single Tickets
and sli_status not in (4,7,8)
/* sli_status
id description
4 Return
7 Void-Returned in this order
8 Void-Returned in other order
*/
) a

declare @subscriptionHistory varchar(max)

set @subscriptionHistory = (
select --distinct
season.Season,
season.SeasonDescription,
SubscriptionHistory.SubscriptionType,
SubscriptionHistory.OrderDate,
SubscriptionHistory.Subscription_Order_No,
SubscriptionHistory.tot_contribution_amt,
SubscriptionHistory.tot_fee_amt,
SubscriptionHistory.tot_ticket_purch_amt,
SubscriptionHistory.DeliveryMethod,
SubscriptionHistory.PreferredDay,
SubscriptionHistory.NumSeats,
SubscriptionHistory.FixedOrFlex,
SubscriptionHistory.[Current],
--Performances.season,
--Performance.rowType,
Performance.perf_no,
performance.perf_code,
performance.Exch_Start_Dt,
performance.Exch_End_Dt,
performance.ExchOpen,
Performance.Display_Seating,
Performance.Perf_Preview,
Performance.Description,
Performance.Description_Short,
Performance.Date,
Performance.Venue,
Performance.venue_short,
seat.rowType,
seat.comp,
seat.price_type,
seat.sli_due_amt,
seat.section,
seat.seat_row,
seat.seat_num,
seat.seat_no,
seat.ticket_no,
seat.[checksum],
seat.sli_status,
seat.sli_status_desc,
seat.exchangeable,
seat.order_no,
seat.order_dt,
seat.DeliveryMethod--,
--Seat.tot_contribution_amt,
--Seat.tot_fee_amt,
--Seat.tot_ticket_purch_amt
/*,
AddOn.season,
AddOn.perf_name,
AddOn.perf_no,
AddOn.rowType,
AddOn.section,
AddOn.seat_row,
AddOn.seat_num,
AddOn.seat_no,
AddOn.ticket_no,
AddOn.[checksum],
AddOn.sli_status,
AddOn.sli_status_desc,
AddOn.exchangeable,
AddOn.order_no,
AddOn.order_dt,
AddOn.DeliveryMethod,
AddOn.tot_contribution_amt,
AddOn.tot_fee_amt,
AddOn.tot_ticket_purch_amt
*/

from
-- seasons
(
select distinct
season,
season_name as seasonDescription
from
#work2
) as season
-- Subscription rows
join (
select distinct
max(category) as SubscriptionType,
Subscription_Order_No,
season,
--max(season_name) as seasonDescription,
max(order_dt) as OrderDate,
--order_no,
max(DeliveryMethod) as DeliveryMethod,
max(tot_contribution_amt) as tot_contribution_amt,
max(tot_fee_amt) as tot_fee_amt,
max(tot_ticket_purch_amt) as tot_ticket_purch_amt,
--cast(convert(varchar,max(order_dt),101) as varchar) as OrderDate,
max(case
when pkg_desc like '%Tue%' then 'Tuesday'
when pkg_desc like '%Wed%' and pkg_desc like '%Eve%' then 'Wednesday Eve'
when pkg_desc like '%Wed%' and pkg_desc like '%Mat%' then 'Wednesday Mat'
when pkg_desc like '%Thu%' then 'Thursday'
when pkg_desc like '%Friday%' then 'Friday'
when pkg_desc like '%Sat%' and pkg_desc like '%Eve%' then 'Saturday Eve'
when pkg_desc like '%Sat%' and pkg_desc like '%Mat%' then 'Saturday Mat'
when pkg_desc like '%Sun%' and pkg_desc like '%Eve%' then 'Sunday Eve'
when pkg_desc like '%Sun%' and pkg_desc like '%Mat%' then 'Sunday Mat'
else ''
end) as PreferredDay,
max(num_seats_pur-num_seats_ret) as NumSeats,
max(case
when category like '%Flex%' or category like '%multi%' then 'Flex'
else 'Fixed'
end) as FixedOrFlex,
max(case
when fyear =
case
when datepart(M,getdate()) > 6 then
datepart(YYYY,getdate()) + 1
else
datepart(YYYY,getdate())
--datepart(YYYY,DATEADD(Y,1,datepart(YYYY,memb.init_dt)))
end
then 'True'
else 'False'
end) as [Current]

from
#work2
where
rowType in ('Sub', 'Add')--
group by season,Subscription_Order_No--order_no
) as SubscriptionHistory on SubscriptionHistory.season = season.season
-- performances
join (
--#results
select distinct
--customer_no,
season,
Subscription_Order_No,
order_no,
rowType,
--lineitem,
perf_no,
perf_code,
Perf_Preview,
Exch_Start_Dt,
Exch_End_Dt,
ExchOpen = case when
Exch_Start_Dt is not null
and Exch_End_Dt is not null
and DATEADD(day, DATEDIFF(day, 0, Exch_Start_Dt), 0) <= DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)
and DATEADD(day, DATEDIFF(day, 0, Exch_End_Dt), 0) > DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)
then 'True'
else 'False' end,
Display_Seating = case when DATEADD(day, DATEDIFF(day, 0, Display_Seating), 0) >= DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0) then 'True' else 'False' end,
perf_name as Description_Short,
full_perf_Name as Description,
perf_dt as Date,
facility_Full as Venue,
facility_short as venue_short
from #work2
)as Performance on SubscriptionHistory.Subscription_Order_No = Performance.Subscription_Order_No and SubscriptionHistory.season = Performance.season
-- seats - sub and sub exch
join
(
select distinct
Subscription_Order_No,
perf_no,
rowType,
comp,
price_type,
sli_due_amt,
section,
seat_row,
seat_num,
seat_no,
ticket_no,
[checksum],
sli_status,
sli_status_desc,
case
when perf_dt > dateadd(DD,1,getdate())
and rowType like 'sub%'
and sli_status in(1,2,3,5,6,9,10,11,12,14)
then 'True'
else 'False'
end as exchangeable,
order_no,
order_dt,
DeliveryMethod--,
--tot_contribution_amt,
--tot_fee_amt,
--tot_ticket_purch_amt

from
#work2
--where
--rowType in ('sub', 'sub exch')
) as Seat on Performance.Subscription_Order_No = Seat.Subscription_Order_No and Performance.perf_no = Seat.perf_no
/*
-- seats - additional tickets
left outer join
(
select distinct
season,
perf_name,
perf_no,
rowType,
section,
seat_row,
seat_num,
seat_no,
ticket_no,
[checksum],
sli_status,
sli_status_desc,
'False' as exchangeable,
order_no,
order_dt,
DeliveryMethod,
tot_contribution_amt,
tot_fee_amt,
tot_ticket_purch_amt

from
#work2
where
rowType in ('Add')
) as AddOn on SubscriptionHistory.season = AddOn.season
*/
order by SubscriptionHistory.season, Performance.Subscription_Order_No, Performance.Description,
-- sort performances
case
when Performance.rowType = 'sub' then 1
when Performance.rowType = 'sub exch' then 2
when Performance.rowType = 'Add' then 3
end,

-- sort seats in the same performance
case
when seat.rowType = 'sub' then 1
when seat.rowType = 'sub exch' then 2
when seat.rowType = 'Add' then 3
end,

seat.section, seat.seat_row, Seat.seat_num
for xml auto, ROOT ('SubscriptionHistory'), ELEMENTS XSINIL);

set @subscriptionHistory = coalesce(@subscriptionHistory,'')
select @subscriptionHistory
--*/

drop table #work1
--,#work2--,
-- #results,
--#performances,
--#subscriptionHistory

return

here you go.

USE [impresario]
GO
/****** Object: StoredProcedure [dbo].[LWP_Get_Subscription_info_new] Script Date: 1/8/2019 1:26:50 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Mendy
-- Create date: 6/23/15
-- Description: 
-- =============================================
ALTER PROCEDURE [dbo].[LWP_Get_Subscription_info_new]
--@customer_no int,
@sessionkey varchar(64) = null,
@curentFY int = null,-- = 2016
@pastHistory int = null-- = 4
AS
SET NOCOUNT ON;

--/*
Declare @errmsg varchar(200), @customer_no int

-- validate parameters
If @sessionkey is null or Not exists (select * from t_web_session_session where sessionkey = @sessionkey)
Begin
select @errmsg = 'Invalid Session ID'
RAISERROR(@errmsg, 11, 2) WITH SETERROR
return -101
End

Select @customer_no = customer_no from t_web_session_session where sessionkey = @sessionkey

If @customer_no is null or Not exists (select * from t_customer where customer_no = @customer_no)
Begin
select @errmsg = 'Invalid Customer_no'
RAISERROR(@errmsg, 11, 2) WITH SETERROR
return -101
End

Exec WP_SET_WEB_CONTEXT @sessionkey = @sessionkey
--*/

-- if no fiscal year provided if it is past 6/30 the fiscal year is next year otherwise it is this year
set @curentFY = (coalesce(@curentFY,
case
when datepart(M,getdate()) > 6 then
datepart(YYYY,getdate()) + 1
else
datepart(YYYY,getdate())
end
))

select
distinct
ord.customer_no,
1 as Subscription_Order_No,
ord.order_no,
ord.order_dt,
cast(
case
when delivery.id = 1 then 'Postal Mail'
when delivery.id = -1 then 'Hold at Box Office'
when delivery.id in (3,4) then 'E-Ticket Print At Home'
else ''
end as varchar(30)) as DeliveryMethod,
--ord.appeal_no,
--apl.description as appeal_desc,
--apl.campaign_no,
--cmp.description as campaign_desc,
--ord.source_no,
--src.source_name,
ord.MOS as MOS,
mos.description as MOS_Desc,
cat.description as category,
ord.tot_contribution_amt,
ord.tot_fee_amt,
ord.tot_ticket_purch_amt + ord.tot_ticket_return_amt as tot_ticket_purch_amt,
li.li_seq_no as lineitem,
sli.sli_no as Sub_lineitem,
case when sli.due_amt = 0 then 'True' else 'False' end as comp,
--sli_d.detail_sli_no as Sub_lineitem_detail_no,
sli.sli_status,
sli_st.description as sli_status_desc,
sli.ret_parent_sli_no,
sli.price_type,
perf.season_no as season,
perf.season_name,
pkg.pkg_code,
pkg.description as pkg_desc,
perf.perf_code,
perf.perf_no,
prodExch.Exch_Start_Dt,
prodExch.Exch_End_Dt,
prodExch.Display_Seating,
Perf_Preview = case when exists(select * from TX_INV_TKW aa (nolock) where aa.inv_no = perf.perf_no and aa.tkw = 46) then 'True' else 'False' end,
perf.prod_name as perf_name,
coalesce(Full_Name,perf.prod_name,'') as full_perf_Name,
perf.perf_dt,
case
when perf.facility like 'Stage 2%' or perf.facility like 'Stage II%' then 'Stage II'
when perf.facility like 'Stage 1%' or perf.facility like 'Stage I%' then 'Stage I'
when perf.facility like '%Friedman%' then 'Friedman'
else perf.facility
end as facility_short, --perf.facility,
case
when perf.facility like 'Stage 2%' or perf.facility like 'Stage II%' then 'NY CITY CENTER Stage II'
when perf.facility like 'Stage 1%' or perf.facility like 'Stage I%' then 'NY CITY CENTER Stage I'
when perf.facility like '%Friedman%' then 'SAMUEL J. FRIEDMAN THEATRE'
else perf.facility
end as facility_Full, --perf.facility,
perf.fyear,
seat_sec.description as section,
seat.seat_row,
seat.seat_num,
seat.seat_no,
cast(null as int) as ticket_no,
cast(null as char(1)) as [checksum],
--pt.description as priceType,
--pt_cat.description as priceTypeCat,
--p_cat.description as PriceCat,
sli.due_amt as sli_due_amt,
--ord.tot_contribution_amt as order_cont_amt,
--sli_d.due_amt as sli_d_due_amt,
--li.tot_pur_amt,
li.num_seats_pur,--is subscription when perf_code like '^%'
li.tot_ret_amt,
isnull(li.num_seats_ret,0) as num_seats_ret
--sli.last_updated_by,
--sli.last_update_dt
into #work1
from
t_order ord (nolock)
left outer join tr_order_category cat (nolock) on cat.id = ord.class
left outer join t_lineitem li (nolock) on li.order_no = ord.order_no
left outer join t_appeal apl (nolock) on apl.appeal_no = ord.appeal_no
left outer join t_campaign cmp (nolock) on cmp.campaign_no = apl.campaign_no
left outer join TX_APPEAL_MEDIA_TYPE src (nolock) on src.source_no = ord.source_no
left outer Join tr_mos mos (nolock) on mos.id = ord.mos
left outer join LV_PERF_PROD_SEASON perf (nolock) on perf.perf_no = li.perf_no
left outer join LTR_Prod_Titles_Complete Perf_fullName on perf.prod_season_no = Perf_fullName.Prod_Season_No
left outer join ltr_prod_Exch_Open_Close prodExch (nolock) on perf.prod_season_no = prodExch.prod_season_no
left outer join t_pkg pkg (nolock) on li.pkg_no=pkg.pkg_no
left outer join t_sub_lineitem sli (nolock) on sli.li_seq_no = li.li_seq_no
left outer join TR_SLI_STATUS sli_st (nolock) on sli.sli_status = sli_st.id
left outer join tr_price_type pt (nolock) on sli.price_type = pt.id
left outer join TR_PRICE_TYPE_CATEGORY pt_cat (nolock) on pt_cat.id = pt.price_type_category
left outer join TR_ORDER_SHIP_METHOD delivery (nolock) on ord.delivery = delivery.id
--left outer join t_sli_detail sli_d (nolock) on sli_d.sli_no = sli.sli_no
--JOIN t_pmap pmap (nolock) on pmap.pmap_no = sli_d.pmap_no
--join TR_PRICE_CATEGORY p_cat (nolock) on p_cat.id = pmap.price_category
left outer join t_seat seat (nolock) on seat.seat_no = sli.seat_no --and isnull(sli.seat_no,0) > 0
left outer join tr_section seat_sec (nolock) on seat_sec.id = seat.section
where
customer_no = @customer_no
--ord.order_no = 488993
--and perf_code not like '^%'
and prod_name not like '*CH%'
and prod_name not like '!%'
and prod_name not like '%Passport Voucher%'
--perf.season_no in (8)
and li.primary_ind = 'Y'
and
(
ord.tot_ticket_purch_amt > 0
or
li.num_seats_pur > li.num_seats_ret
)
--and ord.tot_ticket_paid_amt > 0
--and ord.mos in (10,20)
--and not isnull(ord.tot_ticket_return_amt,0) = (-1 * ord.tot_ticket_purch_amt)
and perf.fyear > @curentFY - isnull(@pastHistory,1)

and sli.sli_status not in (7)
--and not (sli.sli_status in(8,7) and mos not in(10)) -- no returns and exchanges that arent in the original subscription order
--and cat.description not like '%flex%'
--order by
--ord.customer_no,
--ord.customer_no, season, mos, ord.order_no, li.li_seq_no, sli.sli_no--, sli_d.detail_sli_no

--select * from #work1

-- add subscription order_no each ticket belongs to
;with
orders(parent_order_no, child_order_no) as
(
select distinct
sli_parent_order.order_no as parent_order_no,
sli_child_order.order_no as child_order_no--,
--row_number() over (partition by sli_child_order.order_no order by sli_child_order.order_no) as sort
from
T_SUB_LINEITEM sli_child_order
join T_SUB_LINEITEM sli_parent_order on sli_child_order.ret_parent_sli_no = sli_parent_order.sli_no
where
-- focus on orders for specific customer
sli_child_order.order_no in (select distinct order_no from #work1) -- t_order where customer_no = @customer_no)
-- sub line item is a return
and sli_child_order.sli_status = 4 -- 4 = return
and sli_parent_order.sli_status = 8 -- 8 = VOO
--and
-- and this order does not have any sub line items that are VOO
--and sli_child_order.order_no not in (select order_no from T_SUB_LINEITEM a where a.sli_status = 8)
),
w1(parent_order_no, child_order_no, level) as
(
select distinct
parent_order_no,
child_order_no,
--sort,
0 as Level
from
orders
join t_order ord on orders.parent_order_no = ord.order_no
where
--child_order_no not in (select distinct parent_order_no from orders) -- no returns against this order
--and sort = 1
ord.mos = 10

--/*
UNION ALL

select
w1.parent_order_no,
orders.child_order_no,
--orders.sort,
Level + 1
from
orders
join w1 on w1.child_order_no = orders.parent_order_no
--and orders.sort = 1
--and w1.sort = 1
--*/
)
update a
set a.Subscription_Order_No = case when MOS <> 9 then coalesce(w1.parent_order_no,a.order_no) else 1 end
from #work1 a
left outer join w1 on a.order_no = w1.child_order_no

--/*

--select * from #work1

-- for the order date use either the greater of (order date / erliest ticket purchase trasnaction date)
;with order_dt(order_no, order_dt)
as
(
select order_no, max(order_dt) from(
(
select
t_order.order_no as order_no, max(t_order.order_dt) as order_dt
from
t_order
join #work1 on t_order.order_no = #work1.order_no
group by t_order.order_no
--where
--order_no = @order_no
union
select
T_TRANSACTION.order_no as order_no, min(T_TRANSACTION.trn_dt) as order_dt
from
T_TRANSACTION
join #work1 on T_TRANSACTION.order_no = #work1.order_no
--where
--order_no = @order_no
and trn_type = 32
group by T_TRANSACTION.order_no
)

) as aaaa
group by aaaa.order_no
)
update a
set a.order_dt = b.order_dt
from
#work1 a
join order_dt b on a.order_no = b.order_no

-- add ticket numbers from tx_sli_ticket

update a
set
a.ticket_no = b.ticket_no,
a.[checksum] = b.[checksum]
from
#work1 a
join (
select aa.sli_no, aa.ticket_no, aa.pkg_no, aa.perf_no, aa.seat_no, aa.[checksum],
row_number() over(partition by sli_no Order by reprint_no desc) rowNumber
from TX_SLI_TICKET (nolock) aa
join #work1 bb on aa.sli_no = bb.Sub_lineitem
)as b on a.Sub_lineitem = b.sli_no and rowNumber = 1

select
*
into #work2
from

(
select distinct
'sub' as rowType,
*
from
#work1 a
--join subCount b on b.order_no = a.order_no
where
MOS in (10) -- 10=Subscription
and sli_status not in(4) --4=Return

--select 'subscription exchanged history'

union all

select
'sub exch' as rowType,
*
from
#work1 a
where
mos = 5 --5=Exchanges
and sli_status not in (4,7,8)
/* sli_status
id description
4 Return
7 Void-Returned in this order
8 Void-Returned in other order
*/

union all

--select 'single ticket history'
select
'Add' as rowType,
*
from
#work1 a
where
mos = 9 -- 9=Single Tickets
and sli_status not in (4,7,8)
/* sli_status
id description
4 Return
7 Void-Returned in this order
8 Void-Returned in other order
*/
) a

declare @subscriptionHistory varchar(max)

set @subscriptionHistory = (
select --distinct
season.Season,
season.SeasonDescription,
SubscriptionHistory.SubscriptionType,
SubscriptionHistory.OrderDate,
SubscriptionHistory.Subscription_Order_No,
SubscriptionHistory.tot_contribution_amt,
SubscriptionHistory.tot_fee_amt,
SubscriptionHistory.tot_ticket_purch_amt,
SubscriptionHistory.DeliveryMethod,
SubscriptionHistory.PreferredDay,
SubscriptionHistory.NumSeats,
SubscriptionHistory.FixedOrFlex,
SubscriptionHistory.[Current],
--Performances.season,
--Performance.rowType,
Performance.perf_no,
performance.perf_code,
performance.Exch_Start_Dt,
performance.Exch_End_Dt,
performance.ExchOpen,
Performance.Display_Seating,
Performance.Perf_Preview,
Performance.Description,
Performance.Description_Short,
Performance.Date,
Performance.Venue,
Performance.venue_short,
seat.rowType,
seat.comp,
seat.price_type,
seat.sli_due_amt,
seat.section,
seat.seat_row,
seat.seat_num,
seat.seat_no,
seat.ticket_no,
seat.[checksum],
seat.sli_status,
seat.sli_status_desc,
seat.exchangeable,
seat.order_no,
seat.order_dt,
seat.DeliveryMethod--,
--Seat.tot_contribution_amt,
--Seat.tot_fee_amt,
--Seat.tot_ticket_purch_amt
/*,
AddOn.season,
AddOn.perf_name,
AddOn.perf_no,
AddOn.rowType,
AddOn.section,
AddOn.seat_row,
AddOn.seat_num,
AddOn.seat_no,
AddOn.ticket_no,
AddOn.[checksum],
AddOn.sli_status,
AddOn.sli_status_desc,
AddOn.exchangeable,
AddOn.order_no,
AddOn.order_dt,
AddOn.DeliveryMethod,
AddOn.tot_contribution_amt,
AddOn.tot_fee_amt,
AddOn.tot_ticket_purch_amt
*/

from
-- seasons
(
select distinct
season,
season_name as seasonDescription
from
#work2
) as season
-- Subscription rows
join (
select distinct
max(category) as SubscriptionType,
Subscription_Order_No,
season,
--max(season_name) as seasonDescription,
max(order_dt) as OrderDate,
--order_no,
max(DeliveryMethod) as DeliveryMethod,
max(tot_contribution_amt) as tot_contribution_amt,
max(tot_fee_amt) as tot_fee_amt,
max(tot_ticket_purch_amt) as tot_ticket_purch_amt,
--cast(convert(varchar,max(order_dt),101) as varchar) as OrderDate,
max(case
when pkg_desc like '%Tue%' then 'Tuesday'
when pkg_desc like '%Wed%' and pkg_desc like '%Eve%' then 'Wednesday Eve'
when pkg_desc like '%Wed%' and pkg_desc like '%Mat%' then 'Wednesday Mat'
when pkg_desc like '%Thu%' then 'Thursday'
when pkg_desc like '%Friday%' then 'Friday'
when pkg_desc like '%Sat%' and pkg_desc like '%Eve%' then 'Saturday Eve'
when pkg_desc like '%Sat%' and pkg_desc like '%Mat%' then 'Saturday Mat'
when pkg_desc like '%Sun%' and pkg_desc like '%Eve%' then 'Sunday Eve'
when pkg_desc like '%Sun%' and pkg_desc like '%Mat%' then 'Sunday Mat'
else ''
end) as PreferredDay,
max(num_seats_pur-num_seats_ret) as NumSeats,
max(case
when category like '%Flex%' or category like '%multi%' then 'Flex'
else 'Fixed'
end) as FixedOrFlex,
max(case
when fyear =
case
when datepart(M,getdate()) > 6 then
datepart(YYYY,getdate()) + 1
else
datepart(YYYY,getdate())
--datepart(YYYY,DATEADD(Y,1,datepart(YYYY,memb.init_dt)))
end
then 'True'
else 'False'
end) as [Current]

from
#work2
where
rowType in ('Sub', 'Add')--
group by season,Subscription_Order_No--order_no
) as SubscriptionHistory on SubscriptionHistory.season = season.season
-- performances
join (
--#results
select distinct
--customer_no,
season,
Subscription_Order_No,
order_no,
rowType,
--lineitem,
perf_no,
perf_code,
Perf_Preview,
Exch_Start_Dt,
Exch_End_Dt,
ExchOpen = case when
Exch_Start_Dt is not null
and Exch_End_Dt is not null
and DATEADD(day, DATEDIFF(day, 0, Exch_Start_Dt), 0) <= DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)
and DATEADD(day, DATEDIFF(day, 0, Exch_End_Dt), 0) > DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)
then 'True'
else 'False' end,
Display_Seating = case when DATEADD(day, DATEDIFF(day, 0, Display_Seating), 0) >= DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0) then 'True' else 'False' end,
perf_name as Description_Short,
full_perf_Name as Description,
perf_dt as Date,
facility_Full as Venue,
facility_short as venue_short
from #work2
)as Performance on SubscriptionHistory.Subscription_Order_No = Performance.Subscription_Order_No and SubscriptionHistory.season = Performance.season
-- seats - sub and sub exch
join
(
select distinct
Subscription_Order_No,
perf_no,
rowType,
comp,
price_type,
sli_due_amt,
section,
seat_row,
seat_num,
seat_no,
ticket_no,
[checksum],
sli_status,
sli_status_desc,
case
when perf_dt > dateadd(DD,1,getdate())
and rowType like 'sub%'
and sli_status in(1,2,3,5,6,9,10,11,12,14)
then 'True'
else 'False'
end as exchangeable,
order_no,
order_dt,
DeliveryMethod--,
--tot_contribution_amt,
--tot_fee_amt,
--tot_ticket_purch_amt

from
#work2
--where
--rowType in ('sub', 'sub exch')
) as Seat on Performance.Subscription_Order_No = Seat.Subscription_Order_No and Performance.perf_no = Seat.perf_no
/*
-- seats - additional tickets
left outer join
(
select distinct
season,
perf_name,
perf_no,
rowType,
section,
seat_row,
seat_num,
seat_no,
ticket_no,
[checksum],
sli_status,
sli_status_desc,
'False' as exchangeable,
order_no,
order_dt,
DeliveryMethod,
tot_contribution_amt,
tot_fee_amt,
tot_ticket_purch_amt

from
#work2
where
rowType in ('Add')
) as AddOn on SubscriptionHistory.season = AddOn.season
*/
order by SubscriptionHistory.season, Performance.Subscription_Order_No, Performance.Description,
-- sort performances
case
when Performance.rowType = 'sub' then 1
when Performance.rowType = 'sub exch' then 2
when Performance.rowType = 'Add' then 3
end,

-- sort seats in the same performance
case
when seat.rowType = 'sub' then 1
when seat.rowType = 'sub exch' then 2
when seat.rowType = 'Add' then 3
end,

seat.section, seat.seat_row, Seat.seat_num
for xml auto, ROOT ('SubscriptionHistory'), ELEMENTS XSINIL);

set @subscriptionHistory = coalesce(@subscriptionHistory,'')
select @subscriptionHistory
--*/

drop table #work1
--,#work2--,
-- #results,
--#performances,
--#subscriptionHistory

return