Need a sales report

Hi Brendan,

Good day.

I have a quick script to create a table of sales-everything.

then what you need to do is picking up the items you want to have.

====

---pick up other info link to t_sub_lineitem table
SELECT     TOP (1000) a.description, b.sli_no, b.fee_amt, b.due_amt, b.paid_amt, b.pkg_no, b.perf_no,
b.sli_status, b.seat_no, b.create_dt, c.prod_season_no,
b.batch_no, b.order_no, b.price_type
INTO            [#LT_SUB_LINEITEM_NEW]
FROM         TR_PRICE_TYPE AS a INNER JOIN
T_SUB_LINEITEM AS b ON a.id = b.price_type INNER JOIN
T_PERF AS c ON b.perf_no = c.perf_no

--pick up other info link to t_sli_detail table
SELECT     A.description AS PRICE_TYPE_DESCRIPTION, A.sli_no, A.fee_amt, A.pkg_no, A.perf_no, A.sli_status,
A.seat_no, A.create_dt AS sub_lineitem_create_dt, A.prod_season_no, A.batch_no, A.order_no,
A.price_type, B.detail_sli_no, B.pmap_no, B.due_amt, B.paid_amt, B.campaign_no,
B.create_dt AS sli_Detail_create_dt, C.gl_hold_no, E.id AS SEASON_id,
E.description AS SEASON_description, F.customer_no, F.order_dt,
D.perf_status, C.price_category
INTO            [#LT_SLI_DETAIL_NEW]
FROM        [#LT_SUB_LINEITEM_NEW] AS A INNER JOIN
T_SLI_DETAIL AS B ON A.sli_no = B.sli_no INNER JOIN
T_PMAP AS C ON B.pmap_no = C.pmap_no INNER JOIN
T_PERF AS D ON A.perf_no = D.perf_no INNER JOIN
TR_SEASON AS E ON D.season = E.id INNER JOIN
T_ORDER AS F ON A.order_no = F.order_no

---count the items you are interested in.
select * from #LT_SLI_DETAIL_NEW

drop table #LT_SUB_LINEITEM_NEW
drop table #LT_SLI_DETAIL_NEW

====

you can copy and paste into a sql script window to check out whether it has all the fields.

have fun

Ben

Hi Brendan,

Good day.

I have a quick script to create a table of sales-everything.

then what you need to do is picking up the items you want to have.

====

---pick up other info link to t_sub_lineitem table
SELECT     TOP (1000) a.description, b.sli_no, b.fee_amt, b.due_amt, b.paid_amt, b.pkg_no, b.perf_no,
b.sli_status, b.seat_no, b.create_dt, c.prod_season_no,
b.batch_no, b.order_no, b.price_type
INTO            [#LT_SUB_LINEITEM_NEW]
FROM         TR_PRICE_TYPE AS a INNER JOIN
T_SUB_LINEITEM AS b ON a.id = b.price_type INNER JOIN
T_PERF AS c ON b.perf_no = c.perf_no

--pick up other info link to t_sli_detail table
SELECT     A.description AS PRICE_TYPE_DESCRIPTION, A.sli_no, A.fee_amt, A.pkg_no, A.perf_no, A.sli_status,
A.seat_no, A.create_dt AS sub_lineitem_create_dt, A.prod_season_no, A.batch_no, A.order_no,
A.price_type, B.detail_sli_no, B.pmap_no, B.due_amt, B.paid_amt, B.campaign_no,
B.create_dt AS sli_Detail_create_dt, C.gl_hold_no, E.id AS SEASON_id,
E.description AS SEASON_description, F.customer_no, F.order_dt,
D.perf_status, C.price_category
INTO            [#LT_SLI_DETAIL_NEW]
FROM        [#LT_SUB_LINEITEM_NEW] AS A INNER JOIN
T_SLI_DETAIL AS B ON A.sli_no = B.sli_no INNER JOIN
T_PMAP AS C ON B.pmap_no = C.pmap_no INNER JOIN
T_PERF AS D ON A.perf_no = D.perf_no INNER JOIN
TR_SEASON AS E ON D.season = E.id INNER JOIN
T_ORDER AS F ON A.order_no = F.order_no

---count the items you are interested in.
select * from #LT_SLI_DETAIL_NEW

drop table #LT_SUB_LINEITEM_NEW
drop table #LT_SLI_DETAIL_NEW

====

you can copy and paste into a sql script window to check out whether it has all the fields.

have fun

Ben