jfitchett56
Programmer
Hello everyone,
I thought i was starting to understand when this this came along and blew my mind.
The task is simply to get two results sets one is to be sum of charges for all lineitems and the other is the chk_dtl.subt_ttl which is generally working. However some chk_sub_ttls are not matching my check line items. This one is particualarly scary, no idea how it can even happen.
The check has 412.91 worth of sales according to the line item query but the check sub_ttl in chk_dtl is only 34.99 bucks
The check has these line items on it: The overall task is to export the sales detail and check summary and reconsile the two. At bottom i have placed the actual queries i am using to try and accomplish this. At this point i would happily pay to have some correct those queries to solve this type of issue. Thank you. Might want to copy this out and turn off line wrap
==========================================================================
5285 1 83001 ULT CAIPIRHINA 1 12
5285 1 83006 ULT PAMA RINHA 1 12
5285 1 61004 GL TDB CHARD 1 9
5285 1 83006 ULT PAMA RINHA 1 12
5285 1 1002 SALAD AREA$24.99 1 24.99
5285 1 PROMO FOOD 1 -10
5285 1 83006 ULT PAMA RINHA 1 12
5285 2 83006 ULT PAMA RINHA 1 12
5285 2 1002 SALAD AREA$24.99 1 24.99
5285 2 83002 ULT STRAW RINHA 1 12
5285 3 83002 ULT STRAW RINHA 1 12
5285 3 1001 DINNER $44.99 1 44.99
5285 3 PROMO FOOD 1 -10
5285 3 83006 ULT PAMA RINHA 1 12
5285 4 1001 DINNER $44.99 1 44.99
5285 4 61004 GL TDB CHARD 1 9
5285 5 1001 DINNER $44.99 1 44.99
5285 5 PROMO FOOD 1 -10
5285 5 83006 ULT PAMA RINHA 1 12
5285 6 83001 ULT CAIPIRHINA 1 12
5285 6 1002 SALAD AREA$24.99 1 24.99
5285 7 1002 SALAD AREA$24.99 1 24.99
5285 8 1001 DINNER $44.99 1 44.99
5285 9 1001 DINNER $44.99 1 44.99
5285 10 PROMO FOOD 1 -10
5285 12 PROMO FOOD 1 -10
==================================================================
The total of which is obviously well above the 34.99 the chk_dtl has in it
chk_dtl for check 5284
-----------------------
chk_seq emp_seq order_type_seq id chk_num chk_open tbl_seq grp rvc_seq chk_open_date_time chk_open_date_time_raw chk_clsd_date_time tbl_open_date_time tbl_open_date_time_raw uws_seq standalone_chk_seq ob_ocs02_wait_for_auth ob_ocs03_summ_ttl_prntd ob_ocs04_auto_svc_enabled ob_ocs09_pickup_auth ob_ocs10_tms_created_chk ob_ocs11_suspended_chk ob_ocs12_auth_prntd ob_ccs02_ca_chk ob_ccs03_fast_trans_chk ob_ccs04_chk_added ob_ccs05_chk_cancelled ob_ccs10_ca_chk_batched ob_ccs11_chk_edited ob_ccs17_insuff_bev ob_high_priority training_status sub_ttl tax_ttl auto_svc_ttl other_svc_ttl pymnt_ttl amt_due_ttl chk_prntd_cnt pg_num line_find_line_num cov_cnt first_remote_ordr_dtl first_dtl_entry_last_rnd clsd_seat_map seat_chk_prntd num_dtl num_chk_info_lines num_mi_dtl last_svc_date_time_raw last_uws_seq chk_complete creation_mode ob_chk_delivery_xfer ob_ca_auth_on_chk ob_chk_inserted ob_set_begun_next_rnd ob_rsvd05 ob_rsvd06 ob_rsvd07 ob_items_on_hold gross_rcpts chgd_rcpts_ttl ob_chk_reopened autofire_time_raw autofire_time ob_bad_check rmt_order_dvc_mask job_seq ob_delayed_check team_seq ob_pending_auth_saved kds_order_id op_cov_cnt tray_totaled_seat_map void_chk_seq reason_seq ob_automatic_discounting pending_discount_ttl distribution_status remote_order_device_info_line1 remote_order_device_info_line2 remote_order_device_info_line3 remote_order_device_info_line4 xfer_request_tm xfer_request_emp_seq fiscalkey ob_refund_check_active ob_rush_order ob_using_tray_totals ob_incl_svc_enabled incl_svc_ttl last_dtl_prt_on_demand next_dtl_id ob_api_chk_print_through_pos last_tmed_seq ob_is_api_chk
59123 1687 1 NULL 5284 F 2322 1 3 2015-07-08 17:23:14.000 NULL 2015-07-08 19:41:41.000 2015-07-08 17:23:14.000 NULL NULL NULL F T F F F F F T F F F T F F F 0 [highlight #CE5C00]34.99[/highlight] 3.50 0.00 6.75 45.24 0.00 5 0 0 1 0 19 NULL NULL 36 16 1 NULL 35 C N F F F F F F F F 34.99 34.99 F NULL NULL F NULL 2 F NULL F 35:20150708194134398 NULL NULL NULL NULL F 0.00 20 NULL NULL NULL NULL NULL NULL NULL F F F F 0.00 35 52 F NULL F
==============================================================================================
The other tables involved:
Trans_dtl for chk_num = 5285
trans_seq type chk_seq rvc_seq chk_emp_seq chk_emp_shift_seq trans_emp_seq trans_emp_shift_seq job_seq cshr_ttl_seq cshr_ttl_shift_seq business_date ob_outside_business_date fixed_period_seq start_date_tm end_date_tm srv_period_seq training_status ob_closed_check_edit uws_seq uws_trans_num standalone_trans_seq round open_on_sys_uws_seq kds_trans_id creation_mode ob_chk_reopened ob_rsvd01 ob_rsvd02 ob_rsvd03 ob_rsvd04 team_seq team_version_seq cm_till_id rf_signal_strength battery_life
368331 S 59123 3 1687 167 1687 167 2 NULL NULL 2015-07-08 00:00:00.000 F 70 2015-07-08 17:23:14.000 2015-07-08 17:23:19.000 NULL 0 F 34 6786 NULL 0 NULL 34:20150708172314174 N F F F F F NULL NULL NULL NULL NULL
368356 S 59123 3 1687 167 1687 167 2 NULL NULL 2015-07-08 00:00:00.000 F 73 2015-07-08 18:00:53.000 2015-07-08 18:00:55.000 NULL 0 F 35 6277 NULL 0 NULL 35:20150708180052743 N F F F F F NULL NULL NULL NULL NULL
368476 S 59123 3 1687 167 1626 365 3 NULL NULL 2015-07-08 00:00:00.000 F 77 2015-07-08 19:02:49.000 2015-07-08 19:02:58.000 NULL 0 F 34 6817 NULL 0 NULL 34:20150708190248533 N F F F F F NULL NULL NULL NULL NULL
368497 S 59123 3 1687 167 1687 167 2 NULL NULL 2015-07-08 00:00:00.000 F 77 2015-07-08 19:08:08.000 2015-07-08 19:08:10.000 NULL 0 F 34 6824 NULL 0 NULL 34:20150708190807916 N F F F F F NULL NULL NULL NULL NULL
368536 S 59123 3 1687 167 1687 167 2 NULL NULL 2015-07-08 00:00:00.000 F 78 2015-07-08 19:22:12.000 2015-07-08 19:22:36.000 NULL 0 F 34 6829 NULL 0 NULL 34:20150708192211894 N F F F F F NULL NULL NULL NULL NULL
368541 X 59123 3 1687 167 1626 365 3 NULL NULL 2015-07-08 00:00:00.000 F 78 2015-07-08 19:24:32.000 2015-07-08 19:24:32.000 NULL 0 F 33 9629 NULL NULL NULL NULL N F F F F F NULL NULL NULL NULL NULL
368542 S 59123 3 1687 167 1626 365 3 NULL NULL 2015-07-08 00:00:00.000 F 78 2015-07-08 19:24:22.000 2015-07-08 19:24:32.000 NULL 0 F 33 9629 NULL 0 NULL 33:20150708192421978 N F F F F F NULL NULL NULL NULL NULL
368544 X 59123 3 1687 167 1626 365 3 NULL NULL 2015-07-08 00:00:00.000 F 78 2015-07-08 19:24:33.000 2015-07-08 19:24:33.000 NULL 0 F 33 9631 NULL NULL NULL NULL N F F F F F NULL NULL NULL NULL NULL
368545 S 59123 3 1687 167 1626 365 3 NULL NULL 2015-07-08 00:00:00.000 F 78 2015-07-08 19:24:22.000 2015-07-08 19:24:33.000 NULL 0 F 33 9631 NULL 0 NULL 33:20150708192421978 N F F F F F NULL NULL NULL NULL NULL
368547 X 59123 3 1687 167 1626 365 3 NULL NULL 2015-07-08 00:00:00.000 F 78 2015-07-08 19:24:35.000 2015-07-08 19:24:35.000 NULL 0 F 33 9633 NULL NULL NULL NULL N F F F F F NULL NULL NULL NULL NULL
368548 S 59123 3 1687 167 1626 365 3 NULL NULL 2015-07-08 00:00:00.000 F 78 2015-07-08 19:24:22.000 2015-07-08 19:24:35.000 NULL 0 F 33 9633 NULL 0 NULL 33:20150708192421978 N F F F F F NULL NULL NULL NULL NULL
368549 S 59123 3 1687 167 1626 365 3 NULL NULL 2015-07-08 00:00:00.000 F 78 2015-07-08 19:24:22.000 2015-07-08 19:24:37.000 NULL 0 F 33 9634 NULL 0 NULL 33:20150708192421978 N F F F F F NULL NULL NULL NULL NULL
368564 S 59123 3 1687 167 1687 167 2 NULL NULL 2015-07-08 00:00:00.000 F 79 2015-07-08 19:30:54.000 2015-07-08 19:31:05.000 NULL 0 F 35 6328 NULL 0 NULL 35:20150708193054425 N F F F F F NULL NULL NULL NULL NULL
368587 S 59123 3 1687 167 1687 167 2 NULL NULL 2015-07-08 00:00:00.000 F 79 2015-07-08 19:41:34.000 2015-07-08 19:41:41.000 NULL 0 F 35 6344 NULL 0 NULL 35:20150708194134398 N F F F F F NULL NULL NULL NULL NULL
==================================================
dtl where trans_seq was in those returned by trans_dtl above
trans_seq dtl_seq dtl_type ob_dtl06_ref_dtl_follows ob_dtl05_void_flag ob_error_correct ob_item_shared dtl_status reason_seq auth_emp seat date_time date_time_raw record_type dtl_index shared_numerator shared_denominator inclusive_tax_ttl chk_cnt chk_ttl rpt_cnt rpt_ttl comm_ttl autofire_date_time event_priority dtl_id autofire_time_raw external_seq external_type void_type dtl_name rpt_inclusive_tax_ttl active_taxes order_type_seq rpt_inclusive_tax_ttl_ex orig_rvc_seq suppressed_rpt_ttl origin_uws_seq
368331 1 M F F F F NULL NULL NULL 1 2015-07-08 17:23:19.000 NULL A -1 NULL NULL 0.00 1 44.99 1 44.99 0.00 NULL 0 17 NULL NULL NULL NULL DINNER $44.99 0.00 128 1 0.000000 3 44.99 34
368331 2 M F F F F NULL NULL NULL 1 2015-07-08 17:23:19.000 NULL A -1 NULL NULL 0.00 1 44.99 1 44.99 0.00 NULL 0 18 NULL NULL NULL NULL DINNER $44.99 0.00 128 1 0.000000 3 44.99 34
368331 3 M F F F F NULL NULL NULL 1 2015-07-08 17:23:19.000 NULL A -1 NULL NULL 0.00 1 44.99 1 44.99 0.00 NULL 0 19 NULL NULL NULL NULL DINNER $44.99 0.00 128 1 0.000000 3 44.99 34
368331 4 M F F F F NULL NULL NULL 1 2015-07-08 17:23:19.000 NULL A -1 NULL NULL 0.00 1 44.99 1 44.99 0.00 NULL 0 20 NULL NULL NULL NULL DINNER $44.99 0.00 128 1 0.000000 3 44.99 34
368476 1 D T F F F NULL NULL 1626 1 2015-07-08 19:02:58.000 NULL A -1 NULL NULL 0.00 1 -10.00 1 -10.00 0.00 NULL 0 21 NULL NULL NULL NULL PROMO FOOD 0.00 128 1 0.000000 3 -10.00 34
368476 2 R F F F F NULL NULL NULL 1 2015-07-08 19:02:58.000 NULL A -1 NULL NULL 0.00 0 0.00 0 0.00 0.00 NULL 0 22 NULL NULL NULL NULL NULL 0.00 0 1 0.000000 NULL 0.00 34
368476 3 D T F F F NULL NULL 1626 1 2015-07-08 19:02:58.000 NULL A -1 NULL NULL 0.00 1 -10.00 1 -10.00 0.00 NULL 0 23 NULL NULL NULL NULL PROMO FOOD 0.00 128 1 0.000000 3 -10.00 34
368476 4 R F F F F NULL NULL NULL 1 2015-07-08 19:02:58.000 NULL A -1 NULL NULL 0.00 0 0.00 0 0.00 0.00 NULL 0 24 NULL NULL NULL NULL NULL 0.00 0 1 0.000000 NULL 0.00 34
368476 5 D T F F F NULL NULL 1626 1 2015-07-08 19:02:58.000 NULL A -1 NULL NULL 0.00 1 -10.00 1 -10.00 0.00 NULL 0 25 NULL NULL NULL NULL PROMO FOOD 0.00 128 1 0.000000 3 -10.00 34
368476 6 R F F F F NULL NULL NULL 1 2015-07-08 19:02:58.000 NULL A -1 NULL NULL 0.00 0 0.00 0 0.00 0.00 NULL 0 26 NULL NULL NULL NULL NULL 0.00 0 1 0.000000 NULL 0.00 34
368476 7 D T F F F NULL NULL 1626 1 2015-07-08 19:02:58.000 NULL A -1 NULL NULL 0.00 1 -10.00 1 -10.00 0.00 NULL 0 27 NULL NULL NULL NULL PROMO FOOD 0.00 128 1 0.000000 3 -10.00 34
368476 8 R F F F F NULL NULL NULL 1 2015-07-08 19:02:58.000 NULL A -1 NULL NULL 0.00 0 0.00 0 0.00 0.00 NULL 0 28 NULL NULL NULL NULL NULL 0.00 0 1 0.000000 NULL 0.00 34
368536 1 M F F F F NULL NULL NULL 2 2015-07-08 17:23:19.000 NULL R 16 NULL NULL 0.00 1 44.99 0 0.00 0.00 NULL 0 17 NULL NULL NULL NULL DINNER $44.99 0.00 128 1 0.000000 3 0.00 34
368536 2 M F F F F NULL NULL NULL 3 2015-07-08 17:23:19.000 NULL R 17 NULL NULL 0.00 1 44.99 0 0.00 0.00 NULL 0 18 NULL NULL NULL NULL DINNER $44.99 0.00 128 1 0.000000 3 0.00 34
368536 3 M F F F F NULL NULL NULL 4 2015-07-08 17:23:19.000 NULL R 18 NULL NULL 0.00 1 44.99 0 0.00 0.00 NULL 0 19 NULL NULL NULL NULL DINNER $44.99 0.00 128 1 0.000000 3 0.00 34
368536 4 D T F F F NULL NULL 1626 2 2015-07-08 19:02:58.000 NULL R 20 NULL NULL 0.00 1 -10.00 0 0.00 0.00 NULL 0 21 NULL NULL NULL NULL PROMO FOOD 0.00 128 1 0.000000 3 0.00 34
368536 5 R F F F F NULL NULL NULL 2 2015-07-08 19:02:58.000 NULL R 21 NULL NULL 0.00 0 0.00 0 0.00 0.00 NULL 0 22 NULL NULL NULL NULL NULL 0.00 0 1 0.000000 NULL 0.00 34
368536 6 D T F F F NULL NULL 1626 3 2015-07-08 19:02:58.000 NULL R 22 NULL NULL 0.00 1 -10.00 0 0.00 0.00 NULL 0 23 NULL NULL NULL NULL PROMO FOOD 0.00 128 1 0.000000 3 0.00 34
368536 7 R F F F F NULL NULL NULL 3 2015-07-08 19:02:58.000 NULL R 23 NULL NULL 0.00 0 0.00 0 0.00 0.00 NULL 0 24 NULL NULL NULL NULL NULL 0.00 0 1 0.000000 NULL 0.00 34
368536 8 D T F F F NULL NULL 1626 4 2015-07-08 19:02:58.000 NULL R 24 NULL NULL 0.00 1 -10.00 0 0.00 0.00 NULL 0 25 NULL NULL NULL NULL PROMO FOOD 0.00 128 1 0.000000 3 0.00 34
368536 9 R F F F F NULL NULL NULL 4 2015-07-08 19:02:58.000 NULL R 25 NULL NULL 0.00 0 0.00 0 0.00 0.00 NULL 0 26 NULL NULL NULL NULL NULL 0.00 0 1 0.000000 NULL 0.00 34
368542 1 N F F F F NULL NULL NULL 0 2015-07-08 19:24:32.000 NULL D 21 NULL NULL 0.00 0 0.00 0 0.00 0.00 NULL 0 0 NULL NULL NULL NULL NULL 0.00 0 1 0.000000 NULL 0.00 NULL
368542 2 N F F F F NULL NULL NULL 0 2015-07-08 19:24:32.000 NULL D 20 NULL NULL 0.00 0 0.00 0 0.00 0.00 NULL 0 0 NULL NULL NULL NULL NULL 0.00 0 1 0.000000 NULL 0.00 NULL
368542 3 N F F F F NULL NULL NULL 0 2015-07-08 19:24:32.000 NULL D 16 NULL NULL 0.00 0 0.00 0 0.00 0.00 NULL 0 0 NULL NULL NULL NULL NULL 0.00 0 1 0.000000 NULL 0.00 NULL
368545 1 N F F F F NULL NULL NULL 0 2015-07-08 19:24:33.000 NULL D 20 NULL NULL 0.00 0 0.00 0 0.00 0.00 NULL 0 0 NULL NULL NULL NULL NULL 0.00 0 1 0.000000 NULL 0.00 NULL
368545 2 N F F F F NULL NULL NULL 0 2015-07-08 19:24:33.000 NULL D 19 NULL NULL 0.00 0 0.00 0 0.00 0.00 NULL 0 0 NULL NULL NULL NULL NULL 0.00 0 1 0.000000 NULL 0.00 NULL
368545 3 N F F F F NULL NULL NULL 0 2015-07-08 19:24:33.000 NULL D 16 NULL NULL 0.00 0 0.00 0 0.00 0.00 NULL 0 0 NULL NULL NULL NULL NULL 0.00 0 1 0.000000 NULL 0.00 NULL
368548 1 N F F F F NULL NULL NULL 0 2015-07-08 19:24:35.000 NULL D 19 NULL NULL 0.00 0 0.00 0 0.00 0.00 NULL 0 0 NULL NULL NULL NULL NULL 0.00 0 1 0.000000 NULL 0.00 NULL
368548 2 N F F F F NULL NULL NULL 0 2015-07-08 19:24:35.000 NULL D 18 NULL NULL 0.00 0 0.00 0 0.00 0.00 NULL 0 0 NULL NULL NULL NULL NULL 0.00 0 1 0.000000 NULL 0.00 NULL
368548 3 N F F F F NULL NULL NULL 0 2015-07-08 19:24:35.000 NULL D 16 NULL NULL 0.00 0 0.00 0 0.00 0.00 NULL 0 0 NULL NULL NULL NULL NULL 0.00 0 1 0.000000 NULL 0.00 NULL
368564 1 C F F F F NULL NULL NULL 1 2015-07-08 19:31:05.000 NULL A -1 NULL NULL 0.00 0 0.00 0 0.00 0.00 NULL 0 35 NULL NULL NULL NULL VISA 0.00 0 1 0.000000 3 0.00 35
368564 2 V F F F F NULL NULL NULL 1 2015-07-08 19:31:05.000 NULL A -1 NULL NULL 0.00 0 0.00 0 0.00 0.00 NULL 0 42 NULL NULL NULL NULL NULL 0.00 0 1 0.000000 3 0.00 35
368587 1 C F F F F NULL NULL NULL 1 2015-07-08 19:41:41.000 NULL A -1 NULL NULL 0.00 0 0.00 0 0.00 0.00 NULL 0 43 NULL NULL NULL NULL VISA 0.00 0 1 0.000000 3 0.00 35
368587 2 T T F F F NULL NULL NULL 1 2015-07-08 19:41:41.000 NULL A -1 NULL NULL 0.00 1 45.24 1 45.24 0.00 NULL 0 50 NULL NULL NULL NULL VISA 0.00 0 1 0.000000 3 0.00 35
368587 3 D F F F F NULL NULL NULL 1 2015-07-08 19:41:41.000 NULL S -1 NULL NULL 0.00 1 6.75 1 6.75 0.00 NULL 0 0 NULL NULL NULL NULL NULL 0.00 0 1 0.000000 NULL 0.00 35
368587 4 T F F F F NULL NULL NULL 0 2015-07-08 19:41:41.000 NULL S -1 NULL NULL 0.00 1 6.75 1 6.75 0.00 NULL 0 0 NULL NULL NULL NULL NULL 0.00 0 1 0.000000 NULL 0.00 35
368587 5 R F F F F NULL NULL NULL 1 2015-07-08 19:41:41.000 NULL A -1 NULL NULL 0.00 0 0.00 0 0.00 0.00 NULL 0 51 NULL NULL NULL NULL NULL 0.00 0 1 0.000000 NULL 0.00 35
================================================
Mi_dtl where trans_seq in same
trans_seq dtl_seq mi_seq ob_dtl04_rtn ob_dtl21_bev_aft_prompt crs ob_tax_1_active ob_tax_2_active ob_tax_3_active ob_tax_4_active ob_tax_5_active ob_tax_6_active ob_tax_7_active ob_tax_8_active ob_modified_by_no sls_itmzr_seq price_lvl type_def dsc_itmzr svc_itmzr print_grp surcharge_tax_ttl item_weight combo_meal_num combo_side_num vat_txbl_amt combo_grp_seq cndmnt_grp price_lvl_original refill_dtl_id refill_base_price print_class_seq dining_course_seq combo_size inclusive_tax_ttl_forgiven om_type om_number om_base_seq om_section_info_seq om_specialty_seq om_topping_seq om_topping_mi_seq om_modifier_seq parent_dtl_seq ob_is_condiment return_dtl_id prefix_type prefix_override_level maj_grp_seq fam_grp_seq obj_num source_slu_seq participant_id_01 participant_id_02 participant_id_03 participant_id_04 participant_id_05 participant_id_06 participant_id_07 participant_id_08 combo_meal_savings_ttl group_dtl_id prep_time_modified
368331 1 9436 F F NULL T F F F F F F F F 1 1 NULL 1 1 2 0.00 0.000 NULL NULL 0.00 NULL NULL 2 NULL 44.99 1 2 NULL 0.00 NULL NULL NULL NULL NULL NULL NULL NULL NULL F NULL NULL NULL 1 1 1001 366 NULL NULL NULL NULL NULL NULL NULL NULL 0.00 NULL NULL
368331 2 9436 F F NULL T F F F F F F F F 1 1 NULL 1 1 2 0.00 0.000 NULL NULL 0.00 NULL NULL 2 NULL 44.99 1 2 NULL 0.00 NULL NULL NULL NULL NULL NULL NULL NULL NULL F NULL NULL NULL 1 1 1001 366 NULL NULL NULL NULL NULL NULL NULL NULL 0.00 NULL NULL
368331 3 9436 F F NULL T F F F F F F F F 1 1 NULL 1 1 2 0.00 0.000 NULL NULL 0.00 NULL NULL 2 NULL 44.99 1 2 NULL 0.00 NULL NULL NULL NULL NULL NULL NULL NULL NULL F NULL NULL NULL 1 1 1001 366 NULL NULL NULL NULL NULL NULL NULL NULL 0.00 NULL NULL
368331 4 9436 F F NULL T F F F F F F F F 1 1 NULL 1 1 2 0.00 0.000 NULL NULL 0.00 NULL NULL 2 NULL 44.99 1 2 NULL 0.00 NULL NULL NULL NULL NULL NULL NULL NULL NULL F NULL NULL NULL 1 1 1001 366 NULL NULL NULL NULL NULL NULL NULL NULL 0.00 NULL NULL
368536 1 9436 F F NULL T F F F F F F F F 1 1 NULL 1 1 2 0.00 0.000 NULL NULL 0.00 NULL NULL 2 NULL 44.99 1 2 NULL 0.00 NULL NULL NULL NULL NULL NULL NULL NULL NULL F NULL NULL NULL 1 1 1001 366 NULL NULL NULL NULL NULL NULL NULL NULL 0.00 NULL NULL
368536 2 9436 F F NULL T F F F F F F F F 1 1 NULL 1 1 2 0.00 0.000 NULL NULL 0.00 NULL NULL 2 NULL 44.99 1 2 NULL 0.00 NULL NULL NULL NULL NULL NULL NULL NULL NULL F NULL NULL NULL 1 1 1001 366 NULL NULL NULL NULL NULL NULL NULL NULL 0.00 NULL NULL
368536 3 9436 F F NULL T F F F F F F F F 1 1 NULL 1 1 2 0.00 0.000 NULL NULL 0.00 NULL NULL 2 NULL 44.99 1 2 NULL 0.00 NULL NULL NULL NULL NULL NULL NULL NULL NULL F NULL NULL NULL 1 1 1001 366 NULL NULL NULL NULL NULL NULL NULL NULL 0.00 NULL NULL
===========================
And not related to this specific problem but the real task, these queries are what i am usign to try and retrieve detail and summary and expect the totals match up. I am willing to pay for completion of these two queries which probably suffer from at least a lack of split check support.
====================
CheckDetail: limited to check 5284
=====================
SELECT 'SID' AS OperatorID, 'BID' AS LocationID, micros.chk_dtl.chk_num as checkID, micros.dtl.dtl_seq as lineItem,
micros.mi_def.obj_num as ItemID, micros.dtl.dtl_name, isnull(shared_numerator,dtl.chk_cnt) AS Quantity,
dtl.chk_ttl AS SalesValue, CONVERT(CHAR(23), CONVERT(DATETIME, dtl.date_time, 101), 120) AS CheckItemTime,
micros.dtl.seat AS SeatID, ISNULL(mi_dtl.parent_dtl_seq, 0) AS ParentLineItem FROM micros.dtl
left outer JOIN micros.trans_dtl ON micros.dtl.trans_seq = micros.trans_dtl.trans_seq
LEFT OUTER JOIN micros.mi_dtl ON micros.dtl.trans_seq = micros.mi_dtl.trans_seq AND micros.dtl.dtl_seq = micros.mi_dtl.dtl_seq
INNER JOIN micros.chk_dtl ON micros.trans_dtl.chk_seq = micros.chk_dtl.chk_seq LEFT OUTER JOIN micros.mi_def ON
micros.mi_dtl.mi_seq = micros.mi_def.mi_seq
where isnull(shared_numerator,dtl.chk_cnt) <>0 and dtl.dtl_type in ('M','D') and dtl.dtl_id <>0
and chk_dtl.chk_num = 5284
==================
returns
==================
checkID lineItem ItemID dtl_name Quantity SalesValue CheckItemTime SeatID ParentLineItem
5284 1 1001 DINNER $44.99 1 44.99 2015-07-08 17:23:19 1 0
5284 2 1001 DINNER $44.99 1 44.99 2015-07-08 17:23:19 1 0
5284 3 1001 DINNER $44.99 1 44.99 2015-07-08 17:23:19 1 0
5284 4 1001 DINNER $44.99 1 44.99 2015-07-08 17:23:19 1 0
5284 1 NULL PROMO FOOD 1 -10.00 2015-07-08 19:02:58 1 0
5284 3 NULL PROMO FOOD 1 -10.00 2015-07-08 19:02:58 1 0
5284 5 NULL PROMO FOOD 1 -10.00 2015-07-08 19:02:58 1 0
5284 7 NULL PROMO FOOD 1 -10.00 2015-07-08 19:02:58 1 0
5284 1 1001 DINNER $44.99 1 44.99 2015-07-08 17:23:19 2 0
5284 2 1001 DINNER $44.99 1 44.99 2015-07-08 17:23:19 3 0
5284 3 1001 DINNER $44.99 1 44.99 2015-07-08 17:23:19 4 0
5284 4 NULL PROMO FOOD 1 -10.00 2015-07-08 19:02:58 2 0
5284 6 NULL PROMO FOOD 1 -10.00 2015-07-08 19:02:58 3 0
5284 8 NULL PROMO FOOD 1 -10.00 2015-07-08 19:02:58 4 0
-----------------------------
check query:
----------------------------
SELECT distinct CONVERT(CHAR(23), CONVERT(DATETIME, chk_dtl.chk_open_date_time, 101), 120) AS CheckDay,
chk_dtl.chk_num AS CheckID, CONVERT(CHAR(23),
CONVERT(DATETIME,chk_dtl.chk_open_date_time, 101), 120) AS TimeOpened,
CONVERT(CHAR(23), CONVERT(DATETIME, chk_dtl.chk_clsd_date_time, 101), 120) AS TimeClosed,
'N/A' AS InternalLocationID, 'N/A' AS TableID, 0 AS GuestCount, 0 AS EmployeeID, chk_dtl.sub_ttl AS SubTotal,
chk_dtl.tax_ttl AS tax, chk_dtl.other_svc_ttl AS OtherCharges,
chk_dtl.sub_ttl + chk_dtl.tax_ttl + chk_dtl.other_svc_ttl AS checkTotal
FROM micros.chk_dtl JOIN micros.trans_dtl ON chk_dtl.chk_seq = trans_dtl.chk_seq join micros.dtl
on dtl.trans_seq = trans_dtl.trans_seq where dtl.dtl_type = 'M' and dtl.chk_cnt <> 0 AND chk_dtl.chk_num = 5284
----------------------------------
returns
===================================
SELECT distinct CONVERT(CHAR(23), CONVERT(DATETIME, chk_dtl.chk_open_date_time, 101), 120) AS CheckDay,
chk_dtl.chk_num AS CheckID, CONVERT(CHAR(23),
CONVERT(DATETIME,chk_dtl.chk_open_date_time, 101), 120) AS TimeOpened,
CONVERT(CHAR(23), CONVERT(DATETIME, chk_dtl.chk_clsd_date_time, 101), 120) AS TimeClosed,
'N/A' AS InternalLocationID, 'N/A' AS TableID, 0 AS GuestCount, 0 AS EmployeeID, chk_dtl.sub_ttl AS SubTotal,
chk_dtl.tax_ttl AS tax, chk_dtl.other_svc_ttl AS OtherCharges,
chk_dtl.sub_ttl + chk_dtl.tax_ttl + chk_dtl.other_svc_ttl AS checkTotal
FROM micros.chk_dtl JOIN micros.trans_dtl ON chk_dtl.chk_seq = trans_dtl.chk_seq join micros.dtl
on dtl.trans_seq = trans_dtl.trans_seq where dtl.dtl_type = 'M' and dtl.chk_cnt <> 0 AND chk_dtl.chk_num = 5284
Thank you all.
I thought i was starting to understand when this this came along and blew my mind.
The task is simply to get two results sets one is to be sum of charges for all lineitems and the other is the chk_dtl.subt_ttl which is generally working. However some chk_sub_ttls are not matching my check line items. This one is particualarly scary, no idea how it can even happen.
The check has 412.91 worth of sales according to the line item query but the check sub_ttl in chk_dtl is only 34.99 bucks
The check has these line items on it: The overall task is to export the sales detail and check summary and reconsile the two. At bottom i have placed the actual queries i am using to try and accomplish this. At this point i would happily pay to have some correct those queries to solve this type of issue. Thank you. Might want to copy this out and turn off line wrap
==========================================================================
5285 1 83001 ULT CAIPIRHINA 1 12
5285 1 83006 ULT PAMA RINHA 1 12
5285 1 61004 GL TDB CHARD 1 9
5285 1 83006 ULT PAMA RINHA 1 12
5285 1 1002 SALAD AREA$24.99 1 24.99
5285 1 PROMO FOOD 1 -10
5285 1 83006 ULT PAMA RINHA 1 12
5285 2 83006 ULT PAMA RINHA 1 12
5285 2 1002 SALAD AREA$24.99 1 24.99
5285 2 83002 ULT STRAW RINHA 1 12
5285 3 83002 ULT STRAW RINHA 1 12
5285 3 1001 DINNER $44.99 1 44.99
5285 3 PROMO FOOD 1 -10
5285 3 83006 ULT PAMA RINHA 1 12
5285 4 1001 DINNER $44.99 1 44.99
5285 4 61004 GL TDB CHARD 1 9
5285 5 1001 DINNER $44.99 1 44.99
5285 5 PROMO FOOD 1 -10
5285 5 83006 ULT PAMA RINHA 1 12
5285 6 83001 ULT CAIPIRHINA 1 12
5285 6 1002 SALAD AREA$24.99 1 24.99
5285 7 1002 SALAD AREA$24.99 1 24.99
5285 8 1001 DINNER $44.99 1 44.99
5285 9 1001 DINNER $44.99 1 44.99
5285 10 PROMO FOOD 1 -10
5285 12 PROMO FOOD 1 -10
==================================================================
The total of which is obviously well above the 34.99 the chk_dtl has in it
chk_dtl for check 5284
-----------------------
chk_seq emp_seq order_type_seq id chk_num chk_open tbl_seq grp rvc_seq chk_open_date_time chk_open_date_time_raw chk_clsd_date_time tbl_open_date_time tbl_open_date_time_raw uws_seq standalone_chk_seq ob_ocs02_wait_for_auth ob_ocs03_summ_ttl_prntd ob_ocs04_auto_svc_enabled ob_ocs09_pickup_auth ob_ocs10_tms_created_chk ob_ocs11_suspended_chk ob_ocs12_auth_prntd ob_ccs02_ca_chk ob_ccs03_fast_trans_chk ob_ccs04_chk_added ob_ccs05_chk_cancelled ob_ccs10_ca_chk_batched ob_ccs11_chk_edited ob_ccs17_insuff_bev ob_high_priority training_status sub_ttl tax_ttl auto_svc_ttl other_svc_ttl pymnt_ttl amt_due_ttl chk_prntd_cnt pg_num line_find_line_num cov_cnt first_remote_ordr_dtl first_dtl_entry_last_rnd clsd_seat_map seat_chk_prntd num_dtl num_chk_info_lines num_mi_dtl last_svc_date_time_raw last_uws_seq chk_complete creation_mode ob_chk_delivery_xfer ob_ca_auth_on_chk ob_chk_inserted ob_set_begun_next_rnd ob_rsvd05 ob_rsvd06 ob_rsvd07 ob_items_on_hold gross_rcpts chgd_rcpts_ttl ob_chk_reopened autofire_time_raw autofire_time ob_bad_check rmt_order_dvc_mask job_seq ob_delayed_check team_seq ob_pending_auth_saved kds_order_id op_cov_cnt tray_totaled_seat_map void_chk_seq reason_seq ob_automatic_discounting pending_discount_ttl distribution_status remote_order_device_info_line1 remote_order_device_info_line2 remote_order_device_info_line3 remote_order_device_info_line4 xfer_request_tm xfer_request_emp_seq fiscalkey ob_refund_check_active ob_rush_order ob_using_tray_totals ob_incl_svc_enabled incl_svc_ttl last_dtl_prt_on_demand next_dtl_id ob_api_chk_print_through_pos last_tmed_seq ob_is_api_chk
59123 1687 1 NULL 5284 F 2322 1 3 2015-07-08 17:23:14.000 NULL 2015-07-08 19:41:41.000 2015-07-08 17:23:14.000 NULL NULL NULL F T F F F F F T F F F T F F F 0 [highlight #CE5C00]34.99[/highlight] 3.50 0.00 6.75 45.24 0.00 5 0 0 1 0 19 NULL NULL 36 16 1 NULL 35 C N F F F F F F F F 34.99 34.99 F NULL NULL F NULL 2 F NULL F 35:20150708194134398 NULL NULL NULL NULL F 0.00 20 NULL NULL NULL NULL NULL NULL NULL F F F F 0.00 35 52 F NULL F
==============================================================================================
The other tables involved:
Trans_dtl for chk_num = 5285
trans_seq type chk_seq rvc_seq chk_emp_seq chk_emp_shift_seq trans_emp_seq trans_emp_shift_seq job_seq cshr_ttl_seq cshr_ttl_shift_seq business_date ob_outside_business_date fixed_period_seq start_date_tm end_date_tm srv_period_seq training_status ob_closed_check_edit uws_seq uws_trans_num standalone_trans_seq round open_on_sys_uws_seq kds_trans_id creation_mode ob_chk_reopened ob_rsvd01 ob_rsvd02 ob_rsvd03 ob_rsvd04 team_seq team_version_seq cm_till_id rf_signal_strength battery_life
368331 S 59123 3 1687 167 1687 167 2 NULL NULL 2015-07-08 00:00:00.000 F 70 2015-07-08 17:23:14.000 2015-07-08 17:23:19.000 NULL 0 F 34 6786 NULL 0 NULL 34:20150708172314174 N F F F F F NULL NULL NULL NULL NULL
368356 S 59123 3 1687 167 1687 167 2 NULL NULL 2015-07-08 00:00:00.000 F 73 2015-07-08 18:00:53.000 2015-07-08 18:00:55.000 NULL 0 F 35 6277 NULL 0 NULL 35:20150708180052743 N F F F F F NULL NULL NULL NULL NULL
368476 S 59123 3 1687 167 1626 365 3 NULL NULL 2015-07-08 00:00:00.000 F 77 2015-07-08 19:02:49.000 2015-07-08 19:02:58.000 NULL 0 F 34 6817 NULL 0 NULL 34:20150708190248533 N F F F F F NULL NULL NULL NULL NULL
368497 S 59123 3 1687 167 1687 167 2 NULL NULL 2015-07-08 00:00:00.000 F 77 2015-07-08 19:08:08.000 2015-07-08 19:08:10.000 NULL 0 F 34 6824 NULL 0 NULL 34:20150708190807916 N F F F F F NULL NULL NULL NULL NULL
368536 S 59123 3 1687 167 1687 167 2 NULL NULL 2015-07-08 00:00:00.000 F 78 2015-07-08 19:22:12.000 2015-07-08 19:22:36.000 NULL 0 F 34 6829 NULL 0 NULL 34:20150708192211894 N F F F F F NULL NULL NULL NULL NULL
368541 X 59123 3 1687 167 1626 365 3 NULL NULL 2015-07-08 00:00:00.000 F 78 2015-07-08 19:24:32.000 2015-07-08 19:24:32.000 NULL 0 F 33 9629 NULL NULL NULL NULL N F F F F F NULL NULL NULL NULL NULL
368542 S 59123 3 1687 167 1626 365 3 NULL NULL 2015-07-08 00:00:00.000 F 78 2015-07-08 19:24:22.000 2015-07-08 19:24:32.000 NULL 0 F 33 9629 NULL 0 NULL 33:20150708192421978 N F F F F F NULL NULL NULL NULL NULL
368544 X 59123 3 1687 167 1626 365 3 NULL NULL 2015-07-08 00:00:00.000 F 78 2015-07-08 19:24:33.000 2015-07-08 19:24:33.000 NULL 0 F 33 9631 NULL NULL NULL NULL N F F F F F NULL NULL NULL NULL NULL
368545 S 59123 3 1687 167 1626 365 3 NULL NULL 2015-07-08 00:00:00.000 F 78 2015-07-08 19:24:22.000 2015-07-08 19:24:33.000 NULL 0 F 33 9631 NULL 0 NULL 33:20150708192421978 N F F F F F NULL NULL NULL NULL NULL
368547 X 59123 3 1687 167 1626 365 3 NULL NULL 2015-07-08 00:00:00.000 F 78 2015-07-08 19:24:35.000 2015-07-08 19:24:35.000 NULL 0 F 33 9633 NULL NULL NULL NULL N F F F F F NULL NULL NULL NULL NULL
368548 S 59123 3 1687 167 1626 365 3 NULL NULL 2015-07-08 00:00:00.000 F 78 2015-07-08 19:24:22.000 2015-07-08 19:24:35.000 NULL 0 F 33 9633 NULL 0 NULL 33:20150708192421978 N F F F F F NULL NULL NULL NULL NULL
368549 S 59123 3 1687 167 1626 365 3 NULL NULL 2015-07-08 00:00:00.000 F 78 2015-07-08 19:24:22.000 2015-07-08 19:24:37.000 NULL 0 F 33 9634 NULL 0 NULL 33:20150708192421978 N F F F F F NULL NULL NULL NULL NULL
368564 S 59123 3 1687 167 1687 167 2 NULL NULL 2015-07-08 00:00:00.000 F 79 2015-07-08 19:30:54.000 2015-07-08 19:31:05.000 NULL 0 F 35 6328 NULL 0 NULL 35:20150708193054425 N F F F F F NULL NULL NULL NULL NULL
368587 S 59123 3 1687 167 1687 167 2 NULL NULL 2015-07-08 00:00:00.000 F 79 2015-07-08 19:41:34.000 2015-07-08 19:41:41.000 NULL 0 F 35 6344 NULL 0 NULL 35:20150708194134398 N F F F F F NULL NULL NULL NULL NULL
==================================================
dtl where trans_seq was in those returned by trans_dtl above
trans_seq dtl_seq dtl_type ob_dtl06_ref_dtl_follows ob_dtl05_void_flag ob_error_correct ob_item_shared dtl_status reason_seq auth_emp seat date_time date_time_raw record_type dtl_index shared_numerator shared_denominator inclusive_tax_ttl chk_cnt chk_ttl rpt_cnt rpt_ttl comm_ttl autofire_date_time event_priority dtl_id autofire_time_raw external_seq external_type void_type dtl_name rpt_inclusive_tax_ttl active_taxes order_type_seq rpt_inclusive_tax_ttl_ex orig_rvc_seq suppressed_rpt_ttl origin_uws_seq
368331 1 M F F F F NULL NULL NULL 1 2015-07-08 17:23:19.000 NULL A -1 NULL NULL 0.00 1 44.99 1 44.99 0.00 NULL 0 17 NULL NULL NULL NULL DINNER $44.99 0.00 128 1 0.000000 3 44.99 34
368331 2 M F F F F NULL NULL NULL 1 2015-07-08 17:23:19.000 NULL A -1 NULL NULL 0.00 1 44.99 1 44.99 0.00 NULL 0 18 NULL NULL NULL NULL DINNER $44.99 0.00 128 1 0.000000 3 44.99 34
368331 3 M F F F F NULL NULL NULL 1 2015-07-08 17:23:19.000 NULL A -1 NULL NULL 0.00 1 44.99 1 44.99 0.00 NULL 0 19 NULL NULL NULL NULL DINNER $44.99 0.00 128 1 0.000000 3 44.99 34
368331 4 M F F F F NULL NULL NULL 1 2015-07-08 17:23:19.000 NULL A -1 NULL NULL 0.00 1 44.99 1 44.99 0.00 NULL 0 20 NULL NULL NULL NULL DINNER $44.99 0.00 128 1 0.000000 3 44.99 34
368476 1 D T F F F NULL NULL 1626 1 2015-07-08 19:02:58.000 NULL A -1 NULL NULL 0.00 1 -10.00 1 -10.00 0.00 NULL 0 21 NULL NULL NULL NULL PROMO FOOD 0.00 128 1 0.000000 3 -10.00 34
368476 2 R F F F F NULL NULL NULL 1 2015-07-08 19:02:58.000 NULL A -1 NULL NULL 0.00 0 0.00 0 0.00 0.00 NULL 0 22 NULL NULL NULL NULL NULL 0.00 0 1 0.000000 NULL 0.00 34
368476 3 D T F F F NULL NULL 1626 1 2015-07-08 19:02:58.000 NULL A -1 NULL NULL 0.00 1 -10.00 1 -10.00 0.00 NULL 0 23 NULL NULL NULL NULL PROMO FOOD 0.00 128 1 0.000000 3 -10.00 34
368476 4 R F F F F NULL NULL NULL 1 2015-07-08 19:02:58.000 NULL A -1 NULL NULL 0.00 0 0.00 0 0.00 0.00 NULL 0 24 NULL NULL NULL NULL NULL 0.00 0 1 0.000000 NULL 0.00 34
368476 5 D T F F F NULL NULL 1626 1 2015-07-08 19:02:58.000 NULL A -1 NULL NULL 0.00 1 -10.00 1 -10.00 0.00 NULL 0 25 NULL NULL NULL NULL PROMO FOOD 0.00 128 1 0.000000 3 -10.00 34
368476 6 R F F F F NULL NULL NULL 1 2015-07-08 19:02:58.000 NULL A -1 NULL NULL 0.00 0 0.00 0 0.00 0.00 NULL 0 26 NULL NULL NULL NULL NULL 0.00 0 1 0.000000 NULL 0.00 34
368476 7 D T F F F NULL NULL 1626 1 2015-07-08 19:02:58.000 NULL A -1 NULL NULL 0.00 1 -10.00 1 -10.00 0.00 NULL 0 27 NULL NULL NULL NULL PROMO FOOD 0.00 128 1 0.000000 3 -10.00 34
368476 8 R F F F F NULL NULL NULL 1 2015-07-08 19:02:58.000 NULL A -1 NULL NULL 0.00 0 0.00 0 0.00 0.00 NULL 0 28 NULL NULL NULL NULL NULL 0.00 0 1 0.000000 NULL 0.00 34
368536 1 M F F F F NULL NULL NULL 2 2015-07-08 17:23:19.000 NULL R 16 NULL NULL 0.00 1 44.99 0 0.00 0.00 NULL 0 17 NULL NULL NULL NULL DINNER $44.99 0.00 128 1 0.000000 3 0.00 34
368536 2 M F F F F NULL NULL NULL 3 2015-07-08 17:23:19.000 NULL R 17 NULL NULL 0.00 1 44.99 0 0.00 0.00 NULL 0 18 NULL NULL NULL NULL DINNER $44.99 0.00 128 1 0.000000 3 0.00 34
368536 3 M F F F F NULL NULL NULL 4 2015-07-08 17:23:19.000 NULL R 18 NULL NULL 0.00 1 44.99 0 0.00 0.00 NULL 0 19 NULL NULL NULL NULL DINNER $44.99 0.00 128 1 0.000000 3 0.00 34
368536 4 D T F F F NULL NULL 1626 2 2015-07-08 19:02:58.000 NULL R 20 NULL NULL 0.00 1 -10.00 0 0.00 0.00 NULL 0 21 NULL NULL NULL NULL PROMO FOOD 0.00 128 1 0.000000 3 0.00 34
368536 5 R F F F F NULL NULL NULL 2 2015-07-08 19:02:58.000 NULL R 21 NULL NULL 0.00 0 0.00 0 0.00 0.00 NULL 0 22 NULL NULL NULL NULL NULL 0.00 0 1 0.000000 NULL 0.00 34
368536 6 D T F F F NULL NULL 1626 3 2015-07-08 19:02:58.000 NULL R 22 NULL NULL 0.00 1 -10.00 0 0.00 0.00 NULL 0 23 NULL NULL NULL NULL PROMO FOOD 0.00 128 1 0.000000 3 0.00 34
368536 7 R F F F F NULL NULL NULL 3 2015-07-08 19:02:58.000 NULL R 23 NULL NULL 0.00 0 0.00 0 0.00 0.00 NULL 0 24 NULL NULL NULL NULL NULL 0.00 0 1 0.000000 NULL 0.00 34
368536 8 D T F F F NULL NULL 1626 4 2015-07-08 19:02:58.000 NULL R 24 NULL NULL 0.00 1 -10.00 0 0.00 0.00 NULL 0 25 NULL NULL NULL NULL PROMO FOOD 0.00 128 1 0.000000 3 0.00 34
368536 9 R F F F F NULL NULL NULL 4 2015-07-08 19:02:58.000 NULL R 25 NULL NULL 0.00 0 0.00 0 0.00 0.00 NULL 0 26 NULL NULL NULL NULL NULL 0.00 0 1 0.000000 NULL 0.00 34
368542 1 N F F F F NULL NULL NULL 0 2015-07-08 19:24:32.000 NULL D 21 NULL NULL 0.00 0 0.00 0 0.00 0.00 NULL 0 0 NULL NULL NULL NULL NULL 0.00 0 1 0.000000 NULL 0.00 NULL
368542 2 N F F F F NULL NULL NULL 0 2015-07-08 19:24:32.000 NULL D 20 NULL NULL 0.00 0 0.00 0 0.00 0.00 NULL 0 0 NULL NULL NULL NULL NULL 0.00 0 1 0.000000 NULL 0.00 NULL
368542 3 N F F F F NULL NULL NULL 0 2015-07-08 19:24:32.000 NULL D 16 NULL NULL 0.00 0 0.00 0 0.00 0.00 NULL 0 0 NULL NULL NULL NULL NULL 0.00 0 1 0.000000 NULL 0.00 NULL
368545 1 N F F F F NULL NULL NULL 0 2015-07-08 19:24:33.000 NULL D 20 NULL NULL 0.00 0 0.00 0 0.00 0.00 NULL 0 0 NULL NULL NULL NULL NULL 0.00 0 1 0.000000 NULL 0.00 NULL
368545 2 N F F F F NULL NULL NULL 0 2015-07-08 19:24:33.000 NULL D 19 NULL NULL 0.00 0 0.00 0 0.00 0.00 NULL 0 0 NULL NULL NULL NULL NULL 0.00 0 1 0.000000 NULL 0.00 NULL
368545 3 N F F F F NULL NULL NULL 0 2015-07-08 19:24:33.000 NULL D 16 NULL NULL 0.00 0 0.00 0 0.00 0.00 NULL 0 0 NULL NULL NULL NULL NULL 0.00 0 1 0.000000 NULL 0.00 NULL
368548 1 N F F F F NULL NULL NULL 0 2015-07-08 19:24:35.000 NULL D 19 NULL NULL 0.00 0 0.00 0 0.00 0.00 NULL 0 0 NULL NULL NULL NULL NULL 0.00 0 1 0.000000 NULL 0.00 NULL
368548 2 N F F F F NULL NULL NULL 0 2015-07-08 19:24:35.000 NULL D 18 NULL NULL 0.00 0 0.00 0 0.00 0.00 NULL 0 0 NULL NULL NULL NULL NULL 0.00 0 1 0.000000 NULL 0.00 NULL
368548 3 N F F F F NULL NULL NULL 0 2015-07-08 19:24:35.000 NULL D 16 NULL NULL 0.00 0 0.00 0 0.00 0.00 NULL 0 0 NULL NULL NULL NULL NULL 0.00 0 1 0.000000 NULL 0.00 NULL
368564 1 C F F F F NULL NULL NULL 1 2015-07-08 19:31:05.000 NULL A -1 NULL NULL 0.00 0 0.00 0 0.00 0.00 NULL 0 35 NULL NULL NULL NULL VISA 0.00 0 1 0.000000 3 0.00 35
368564 2 V F F F F NULL NULL NULL 1 2015-07-08 19:31:05.000 NULL A -1 NULL NULL 0.00 0 0.00 0 0.00 0.00 NULL 0 42 NULL NULL NULL NULL NULL 0.00 0 1 0.000000 3 0.00 35
368587 1 C F F F F NULL NULL NULL 1 2015-07-08 19:41:41.000 NULL A -1 NULL NULL 0.00 0 0.00 0 0.00 0.00 NULL 0 43 NULL NULL NULL NULL VISA 0.00 0 1 0.000000 3 0.00 35
368587 2 T T F F F NULL NULL NULL 1 2015-07-08 19:41:41.000 NULL A -1 NULL NULL 0.00 1 45.24 1 45.24 0.00 NULL 0 50 NULL NULL NULL NULL VISA 0.00 0 1 0.000000 3 0.00 35
368587 3 D F F F F NULL NULL NULL 1 2015-07-08 19:41:41.000 NULL S -1 NULL NULL 0.00 1 6.75 1 6.75 0.00 NULL 0 0 NULL NULL NULL NULL NULL 0.00 0 1 0.000000 NULL 0.00 35
368587 4 T F F F F NULL NULL NULL 0 2015-07-08 19:41:41.000 NULL S -1 NULL NULL 0.00 1 6.75 1 6.75 0.00 NULL 0 0 NULL NULL NULL NULL NULL 0.00 0 1 0.000000 NULL 0.00 35
368587 5 R F F F F NULL NULL NULL 1 2015-07-08 19:41:41.000 NULL A -1 NULL NULL 0.00 0 0.00 0 0.00 0.00 NULL 0 51 NULL NULL NULL NULL NULL 0.00 0 1 0.000000 NULL 0.00 35
================================================
Mi_dtl where trans_seq in same
trans_seq dtl_seq mi_seq ob_dtl04_rtn ob_dtl21_bev_aft_prompt crs ob_tax_1_active ob_tax_2_active ob_tax_3_active ob_tax_4_active ob_tax_5_active ob_tax_6_active ob_tax_7_active ob_tax_8_active ob_modified_by_no sls_itmzr_seq price_lvl type_def dsc_itmzr svc_itmzr print_grp surcharge_tax_ttl item_weight combo_meal_num combo_side_num vat_txbl_amt combo_grp_seq cndmnt_grp price_lvl_original refill_dtl_id refill_base_price print_class_seq dining_course_seq combo_size inclusive_tax_ttl_forgiven om_type om_number om_base_seq om_section_info_seq om_specialty_seq om_topping_seq om_topping_mi_seq om_modifier_seq parent_dtl_seq ob_is_condiment return_dtl_id prefix_type prefix_override_level maj_grp_seq fam_grp_seq obj_num source_slu_seq participant_id_01 participant_id_02 participant_id_03 participant_id_04 participant_id_05 participant_id_06 participant_id_07 participant_id_08 combo_meal_savings_ttl group_dtl_id prep_time_modified
368331 1 9436 F F NULL T F F F F F F F F 1 1 NULL 1 1 2 0.00 0.000 NULL NULL 0.00 NULL NULL 2 NULL 44.99 1 2 NULL 0.00 NULL NULL NULL NULL NULL NULL NULL NULL NULL F NULL NULL NULL 1 1 1001 366 NULL NULL NULL NULL NULL NULL NULL NULL 0.00 NULL NULL
368331 2 9436 F F NULL T F F F F F F F F 1 1 NULL 1 1 2 0.00 0.000 NULL NULL 0.00 NULL NULL 2 NULL 44.99 1 2 NULL 0.00 NULL NULL NULL NULL NULL NULL NULL NULL NULL F NULL NULL NULL 1 1 1001 366 NULL NULL NULL NULL NULL NULL NULL NULL 0.00 NULL NULL
368331 3 9436 F F NULL T F F F F F F F F 1 1 NULL 1 1 2 0.00 0.000 NULL NULL 0.00 NULL NULL 2 NULL 44.99 1 2 NULL 0.00 NULL NULL NULL NULL NULL NULL NULL NULL NULL F NULL NULL NULL 1 1 1001 366 NULL NULL NULL NULL NULL NULL NULL NULL 0.00 NULL NULL
368331 4 9436 F F NULL T F F F F F F F F 1 1 NULL 1 1 2 0.00 0.000 NULL NULL 0.00 NULL NULL 2 NULL 44.99 1 2 NULL 0.00 NULL NULL NULL NULL NULL NULL NULL NULL NULL F NULL NULL NULL 1 1 1001 366 NULL NULL NULL NULL NULL NULL NULL NULL 0.00 NULL NULL
368536 1 9436 F F NULL T F F F F F F F F 1 1 NULL 1 1 2 0.00 0.000 NULL NULL 0.00 NULL NULL 2 NULL 44.99 1 2 NULL 0.00 NULL NULL NULL NULL NULL NULL NULL NULL NULL F NULL NULL NULL 1 1 1001 366 NULL NULL NULL NULL NULL NULL NULL NULL 0.00 NULL NULL
368536 2 9436 F F NULL T F F F F F F F F 1 1 NULL 1 1 2 0.00 0.000 NULL NULL 0.00 NULL NULL 2 NULL 44.99 1 2 NULL 0.00 NULL NULL NULL NULL NULL NULL NULL NULL NULL F NULL NULL NULL 1 1 1001 366 NULL NULL NULL NULL NULL NULL NULL NULL 0.00 NULL NULL
368536 3 9436 F F NULL T F F F F F F F F 1 1 NULL 1 1 2 0.00 0.000 NULL NULL 0.00 NULL NULL 2 NULL 44.99 1 2 NULL 0.00 NULL NULL NULL NULL NULL NULL NULL NULL NULL F NULL NULL NULL 1 1 1001 366 NULL NULL NULL NULL NULL NULL NULL NULL 0.00 NULL NULL
===========================
And not related to this specific problem but the real task, these queries are what i am usign to try and retrieve detail and summary and expect the totals match up. I am willing to pay for completion of these two queries which probably suffer from at least a lack of split check support.
====================
CheckDetail: limited to check 5284
=====================
SELECT 'SID' AS OperatorID, 'BID' AS LocationID, micros.chk_dtl.chk_num as checkID, micros.dtl.dtl_seq as lineItem,
micros.mi_def.obj_num as ItemID, micros.dtl.dtl_name, isnull(shared_numerator,dtl.chk_cnt) AS Quantity,
dtl.chk_ttl AS SalesValue, CONVERT(CHAR(23), CONVERT(DATETIME, dtl.date_time, 101), 120) AS CheckItemTime,
micros.dtl.seat AS SeatID, ISNULL(mi_dtl.parent_dtl_seq, 0) AS ParentLineItem FROM micros.dtl
left outer JOIN micros.trans_dtl ON micros.dtl.trans_seq = micros.trans_dtl.trans_seq
LEFT OUTER JOIN micros.mi_dtl ON micros.dtl.trans_seq = micros.mi_dtl.trans_seq AND micros.dtl.dtl_seq = micros.mi_dtl.dtl_seq
INNER JOIN micros.chk_dtl ON micros.trans_dtl.chk_seq = micros.chk_dtl.chk_seq LEFT OUTER JOIN micros.mi_def ON
micros.mi_dtl.mi_seq = micros.mi_def.mi_seq
where isnull(shared_numerator,dtl.chk_cnt) <>0 and dtl.dtl_type in ('M','D') and dtl.dtl_id <>0
and chk_dtl.chk_num = 5284
==================
returns
==================
checkID lineItem ItemID dtl_name Quantity SalesValue CheckItemTime SeatID ParentLineItem
5284 1 1001 DINNER $44.99 1 44.99 2015-07-08 17:23:19 1 0
5284 2 1001 DINNER $44.99 1 44.99 2015-07-08 17:23:19 1 0
5284 3 1001 DINNER $44.99 1 44.99 2015-07-08 17:23:19 1 0
5284 4 1001 DINNER $44.99 1 44.99 2015-07-08 17:23:19 1 0
5284 1 NULL PROMO FOOD 1 -10.00 2015-07-08 19:02:58 1 0
5284 3 NULL PROMO FOOD 1 -10.00 2015-07-08 19:02:58 1 0
5284 5 NULL PROMO FOOD 1 -10.00 2015-07-08 19:02:58 1 0
5284 7 NULL PROMO FOOD 1 -10.00 2015-07-08 19:02:58 1 0
5284 1 1001 DINNER $44.99 1 44.99 2015-07-08 17:23:19 2 0
5284 2 1001 DINNER $44.99 1 44.99 2015-07-08 17:23:19 3 0
5284 3 1001 DINNER $44.99 1 44.99 2015-07-08 17:23:19 4 0
5284 4 NULL PROMO FOOD 1 -10.00 2015-07-08 19:02:58 2 0
5284 6 NULL PROMO FOOD 1 -10.00 2015-07-08 19:02:58 3 0
5284 8 NULL PROMO FOOD 1 -10.00 2015-07-08 19:02:58 4 0
-----------------------------
check query:
----------------------------
SELECT distinct CONVERT(CHAR(23), CONVERT(DATETIME, chk_dtl.chk_open_date_time, 101), 120) AS CheckDay,
chk_dtl.chk_num AS CheckID, CONVERT(CHAR(23),
CONVERT(DATETIME,chk_dtl.chk_open_date_time, 101), 120) AS TimeOpened,
CONVERT(CHAR(23), CONVERT(DATETIME, chk_dtl.chk_clsd_date_time, 101), 120) AS TimeClosed,
'N/A' AS InternalLocationID, 'N/A' AS TableID, 0 AS GuestCount, 0 AS EmployeeID, chk_dtl.sub_ttl AS SubTotal,
chk_dtl.tax_ttl AS tax, chk_dtl.other_svc_ttl AS OtherCharges,
chk_dtl.sub_ttl + chk_dtl.tax_ttl + chk_dtl.other_svc_ttl AS checkTotal
FROM micros.chk_dtl JOIN micros.trans_dtl ON chk_dtl.chk_seq = trans_dtl.chk_seq join micros.dtl
on dtl.trans_seq = trans_dtl.trans_seq where dtl.dtl_type = 'M' and dtl.chk_cnt <> 0 AND chk_dtl.chk_num = 5284
----------------------------------
returns
===================================
SELECT distinct CONVERT(CHAR(23), CONVERT(DATETIME, chk_dtl.chk_open_date_time, 101), 120) AS CheckDay,
chk_dtl.chk_num AS CheckID, CONVERT(CHAR(23),
CONVERT(DATETIME,chk_dtl.chk_open_date_time, 101), 120) AS TimeOpened,
CONVERT(CHAR(23), CONVERT(DATETIME, chk_dtl.chk_clsd_date_time, 101), 120) AS TimeClosed,
'N/A' AS InternalLocationID, 'N/A' AS TableID, 0 AS GuestCount, 0 AS EmployeeID, chk_dtl.sub_ttl AS SubTotal,
chk_dtl.tax_ttl AS tax, chk_dtl.other_svc_ttl AS OtherCharges,
chk_dtl.sub_ttl + chk_dtl.tax_ttl + chk_dtl.other_svc_ttl AS checkTotal
FROM micros.chk_dtl JOIN micros.trans_dtl ON chk_dtl.chk_seq = trans_dtl.chk_seq join micros.dtl
on dtl.trans_seq = trans_dtl.trans_seq where dtl.dtl_type = 'M' and dtl.chk_cnt <> 0 AND chk_dtl.chk_num = 5284
Thank you all.