Does anyone have an order summery query that includes seat count.
Select * from t_order
does most of what I need however the num_seats column in my system is filled with all nulls.
Is this true of others? Has anyone got a query that fills in this data?
Did some looking and the Num_seats is clearly deprecated.
Is this as simple as
select tor.*
,ns.num_seats_pur
,ns.num_seats_ret
from T_ORDER as tor with (nolock)
left join (
select order_no
,sum(num_seats_pur) as num_seats_pur
,SUM(num_seats_ret) as num_seats_ret
from T_LINEITEM with (nolock)
group by order_no
) as ns
on tor.order_no = ns.order_no
Here is a more detailed prototype of a order summary.
select tor.order_no
,tor.appeal_no --Original Appeal No on the Order
,va.description as appeal_desc
,tor.source_no
,tamt.source_name as source_name
,tor.customer_no
,tor.solicitor
--,tor.cno_req_ind --Not Used
,tor.MOS
,tm.description as MOS_desc
-- ,tor.cancel_ind --Not Used
,tor.order_dt
,CAST(FLOOR(CAST(tor.order_dt AS FLOAT))as datetime) as order_dt_only
,batch_no
,coalesce(ns.num_seats_pur,0) as num_seats_pur
,coalesce(ns.num_seats_ret,0) as num_seats_ret
,class -- This is actually Order Category No
,toc.description as order_category_desc
,ISNULL(tor.accepting_rollovers, 'N') as aaccepting_rollovers
,tor.address_no
,coalesce(tor.address_no,fgpa.address_no) as presumed_address_no
-- ,act_no -- Obsolete as of V7.5
,tor.hold_until_dt
,CAST(FLOOR(CAST(tor.hold_until_dt AS FLOAT))as datetime) as hold_until_dt_only
,tor.tot_ticket_purch_amt
,tor.tot_ticket_return_amt
,tor.tot_fee_amt
,tor.tot_contribution_amt
,tor.tot_due_amt
,tor.tot_ticket_paid_amt
,tor.tot_fee_paid_amt
,tor.tot_contribution_paid_amt
,tor.tot_paid_amt
--,tor.transaction_no --Now obsolete
--,tor.habo_ind --Now obsolete
,tor.delivery
,tosm.description as delivery_desc
,tor.channel
,vsc.description as channel_desc
,tor.custom_1 --If there is a view to provide better data use
,tor.custom_2 --If there is a view to provide better data use
,tor.custom_3 --If there is a view to provide better data use
,tor.custom_4 --If there is a view to provide better data use
,tor.custom_5 --If there is a view to provide better data use
,tor.custom_6 --If there is a view to provide better data use
,tor.custom_7 --If there is a view to provide better data use
,tor.custom_8 --If there is a view to provide better data use
,tor.custom_9 --If there is a view to provide better data use
,tor.custom_0 --If there is a view to provide better data use
,tor.act_id -- May want to provide some things like
,tor.eaddress_no
,coalesce(tor.eaddress_no,fgpe.eaddress_no) as presumed_eaddress_no --Getting the eaddress set on the order or the best current email address for the order
,tor.initiator_no
--,'|' as '|', *
from dbo.T_ORDER as tor with (nolock)
-- This is secured by Channel and Appeal
join dbo.VS_APPEAL as va with (nolock)
on tor.appeal_no = va.appeal_no
join dbo.VRS_SALES_CHANNEL as vsc with (nolock) -- Something is wrong with Channel here at BAM that is hiding some records.
on tor.channel = vsc.id
-- Other needed details
left join dbo.TX_APPEAL_MEDIA_TYPE as tamt with (nolock)
on tor.source_no = tamt.source_no
left join dbo.TR_MOS as tm with (nolock)
on tor.MOS = tm.id
left join dbo.TR_ORDER_CATEGORY as toc with (nolock)
on tor.class = toc.id
left join dbo.TR_ORDER_SHIP_METHOD as tosm with (nolock)
on tor.delivery = tosm.id
-- Values Calculated on the Fly
left join dbo.FT_GET_PRIMARY_ADDRESS() as fgpa
on tor.customer_no = fgpa.customer_no
left join dbo.FT_GET_PRIMARY_EADDRESS() as fgpe
on tor.customer_no = fgpe.customer_no
from dbo.T_LINEITEM with (nolock)
join dbo.VRS_SALES_CHANNEL as vsc with (nolock)