N-Scan Attendance reports

Hi Meagan,

Good day.

We are reviewing our attendance report too.

but at last I found out, it could be a problem, as I don't know  what is the right records in the Tessitura.

In Tessitura, only one stored procedure is doing insert/update attendance info into database.

It is   LRP_NSCAN_TICKET_INFO. (line421-498 have the detail of insert/update)

Simple enough.

The problem is this stored procedure insert/update three different tables.

so we have three copies of data can be used.

they are

1, t_order_seat_hist (event_code=22)

2, Tx_perf_seat  (every seat_status is 13, and available_ind=0 will show green on the map) 

3, LT_Nscan_attendance (ticket_ok='Y')

70% of our two-years records match up with each other in these three tables.

I believe your system can have the same problem.

So before you create some reports, I think maybe you need to do this job too. check out the records.

Make sure data is clean. 

for the report itself, it is very simple, you just need to link  LT_Nscan_attendance to t_sub_lineitem with sli_no where ticket_ok='y'  ( if you regard the  LT_Nscan_attendance  is the right table.)

BTW, canned report is using t_order_seat_hist table.  on seast map, it is using tx_perf_seat table.

SELECT     TOP (100) a.ticket_no, a.customer_no, a.perf_no, a.pkg_no, a.seat_no, a.entrance, a.sli_no, a.user_id, a.status, a.error_text, a.update_dt, a.online_mode,
a.update_mode, a.ticket_ok, a.ticket_msg, a.ticket_updated, a.exit_mode, a.exit_logged, a.exit_dt, a.scan_str, a.perf_str, a.user_entrance, a.device_name,
b.li_seq_no, b.unseatable_code, b.fee_amt, b.fee_parent_sli_no, b.due_amt, b.paid_amt, b.price_type, b.cancel_ind, b.sr_ind, b.created_by, b.create_dt, b.create_loc,
b.last_updated_by, b.last_update_dt, b.comp_code, b.sli_status, b.zone_no, b.sli_status_code, b.batch_no, b.mir_lock, b.ret_parent_sli_no, b.order_no
FROM         LT_NSCAN_ATTENDANCE AS a LEFT OUTER JOIN
T_SUB_LINEITEM AS b ON a.sli_no = b.sli_no
WHERE     (a.ticket_ok = 'y')

have fun.

Ben

Hi Meagan,

Good day.

We are reviewing our attendance report too.

but at last I found out, it could be a problem, as I don't know  what is the right records in the Tessitura.

In Tessitura, only one stored procedure is doing insert/update attendance info into database.

It is   LRP_NSCAN_TICKET_INFO. (line421-498 have the detail of insert/update)

Simple enough.

The problem is this stored procedure insert/update three different tables.

so we have three copies of data can be used.

they are

1, t_order_seat_hist (event_code=22)

2, Tx_perf_seat  (every seat_status is 13, and available_ind=0 will show green on the map) 

3, LT_Nscan_attendance (ticket_ok='Y')

70% of our two-years records match up with each other in these three tables.

I believe your system can have the same problem.

So before you create some reports, I think maybe you need to do this job too. check out the records.

Make sure data is clean. 

for the report itself, it is very simple, you just need to link  LT_Nscan_attendance to t_sub_lineitem with sli_no where ticket_ok='y'  ( if you regard the  LT_Nscan_attendance  is the right table.)

BTW, canned report is using t_order_seat_hist table.  on seast map, it is using tx_perf_seat table.

SELECT     TOP (100) a.ticket_no, a.customer_no, a.perf_no, a.pkg_no, a.seat_no, a.entrance, a.sli_no, a.user_id, a.status, a.error_text, a.update_dt, a.online_mode,
a.update_mode, a.ticket_ok, a.ticket_msg, a.ticket_updated, a.exit_mode, a.exit_logged, a.exit_dt, a.scan_str, a.perf_str, a.user_entrance, a.device_name,
b.li_seq_no, b.unseatable_code, b.fee_amt, b.fee_parent_sli_no, b.due_amt, b.paid_amt, b.price_type, b.cancel_ind, b.sr_ind, b.created_by, b.create_dt, b.create_loc,
b.last_updated_by, b.last_update_dt, b.comp_code, b.sli_status, b.zone_no, b.sli_status_code, b.batch_no, b.mir_lock, b.ret_parent_sli_no, b.order_no
FROM         LT_NSCAN_ATTENDANCE AS a LEFT OUTER JOIN
T_SUB_LINEITEM AS b ON a.sli_no = b.sli_no
WHERE     (a.ticket_ok = 'y')

have fun.

Ben

Thanks. I'm going to pass that one along to our database guy.

Meagan