jmercado27
Programmer
I've been trying to retrieve what's printed on a guest check with a SQL query, stored procedure, or find a view that does this. I seem to be getting additional information with just a query.
select
ckdtl.chk_num,
tdtl.chk_emp_seq as Check_Emp,
tdtl.chk_seq as Check_Seq,
tdtl.cshr_ttl_seq as Cashier_Total_Seq,
tdtl.end_date_tm as End_date,
tdtl.trans_emp_seq as Transaction,
tdtl.trans_seq as Transaction_Seq,
d.chk_cnt as Check_Count,
d.chk_ttl as Check_Total,
d.date_time as Date_Time,
d.dtl_index as Detail_Index,
d.dtl_seq as Detail_Seq,
d.dtl_type as Detail_Type,
d.record_type as Record_type,
d.rpt_cnt as Report_Count,
d.rpt_ttl as Report_Total,
d.seat as Seat,
mdef.name_1 as MenuName1,
mdef.name_2 as MenuName2,
ckdtl.amt_due_ttl as TotalBill,
ckdtl.auto_svc_ttl as SvcChargeTtl,
ckdtl.chk_num as CheckNum,
ckdtl.chk_seq as CheckSeq,
ckdtl.cov_cnt as CoverCnt,
ckdtl.emp_seq as EmployeeSeq,
ckdtl.num_dtl as DetailNum,
ckdtl.order_type_seq as OrderType,
ckdtl.pg_num as PageNum,
ckdtl.pymnt_ttl as PaymentTot,
ckdtl.sub_ttl as SubTotal,
ckdtl.tax_ttl as TaxTotal,
ckdtl.tbl_open_date_time as TableOpnDate
from micros.trans_dtl as tdtl join
micros.dtl as d on
tdtl.trans_seq = d.trans_seq join
micros.mi_dtl as mdtl on
d.trans_seq = mdtl.trans_seq and
d.dtl_seq = mdtl.dtl_seq join
micros.mi_def as mdef on
mdtl.mi_seq = mdef.mi_seq join
micros.chk_dtl as ckdtl on
tdtl.chk_seq = ckdtl.chk_seq
where 1 = 1
order by Date_Time desc
Steps to get the additional information:
A check is created with one item. (Using the query there is just the one row that can be seen for the new check at the top)
This check is split. (Using the query there are now 2 rows that can be seen for the split items)
The split items are combined to one check (Using the query there are now 4 rows that can be seen for the split items. 3 rows for one check which includes the original item and the splits and 1 row for the old split that has been combined to the other check)
How do I get just the split items for the one check correct check? Is there something I should be filtering on. There is nothing that jumps out at me as something that makes sense. Please help!!!
Data included below:
chk_num,Check_Emp,Check_Seq,Cashier_Total_Seq,End_date,Transaction,Transaction_Seq,Check_Count,Check_Total,Date_Time,Detail_Index,Detail_Seq,Detail_Type,Record_type,Report_Count,Report_Total,Seat,MenuName1,MenuName2,TotalBill,SvcChargeTtl,CheckNum,CheckSeq,CoverCnt,EmployeeSeq,DetailNum,OrderType,PageNum,PaymentTot,SubTotal,TaxTotal,TableOpnDate
1079,1253,2747,,'2014-09-16 13:33:18.000',1253,7626,0,2.00,'2014-09-16 13:31:54.000',-1,1,'M','A',0,0.00,1,'Budweiser',,2.00,0.00,1079,2747,1,1253,17,1,0,0.00,2.00,0.00,'2014-09-16 13:31:27.000'
1078,1253,2746,,'2014-09-16 13:31:54.000',1253,7625,1,4.00,'2014-09-16 13:31:54.000',-1,1,'M','A',1,4.00,1,'Budweiser',,4.00,0.00,1078,2746,2,1253,18,1,0,0.00,4.00,0.00,'2014-09-16 13:31:27.000'
1078,1253,2746,,'2014-09-16 13:34:46.000',1253,7630,0,2.00,'2014-09-16 13:31:54.000',17,1,'M','I',0,0.00,2,'Budweiser',,4.00,0.00,1078,2746,2,1253,18,1,0,0.00,4.00,0.00,'2014-09-16 13:31:27.000'
1078,1253,2746,,'2014-09-16 13:33:19.000',1253,7628,1,2.00,'2014-09-16 13:31:54.000',16,2,'M','I',0,0.00,1,'Budweiser',,4.00,0.00,1078,2746,2,1253,18,1,0,0.00,4.00,0.00,'2014-09-16 13:31:27.000'
select
ckdtl.chk_num,
tdtl.chk_emp_seq as Check_Emp,
tdtl.chk_seq as Check_Seq,
tdtl.cshr_ttl_seq as Cashier_Total_Seq,
tdtl.end_date_tm as End_date,
tdtl.trans_emp_seq as Transaction,
tdtl.trans_seq as Transaction_Seq,
d.chk_cnt as Check_Count,
d.chk_ttl as Check_Total,
d.date_time as Date_Time,
d.dtl_index as Detail_Index,
d.dtl_seq as Detail_Seq,
d.dtl_type as Detail_Type,
d.record_type as Record_type,
d.rpt_cnt as Report_Count,
d.rpt_ttl as Report_Total,
d.seat as Seat,
mdef.name_1 as MenuName1,
mdef.name_2 as MenuName2,
ckdtl.amt_due_ttl as TotalBill,
ckdtl.auto_svc_ttl as SvcChargeTtl,
ckdtl.chk_num as CheckNum,
ckdtl.chk_seq as CheckSeq,
ckdtl.cov_cnt as CoverCnt,
ckdtl.emp_seq as EmployeeSeq,
ckdtl.num_dtl as DetailNum,
ckdtl.order_type_seq as OrderType,
ckdtl.pg_num as PageNum,
ckdtl.pymnt_ttl as PaymentTot,
ckdtl.sub_ttl as SubTotal,
ckdtl.tax_ttl as TaxTotal,
ckdtl.tbl_open_date_time as TableOpnDate
from micros.trans_dtl as tdtl join
micros.dtl as d on
tdtl.trans_seq = d.trans_seq join
micros.mi_dtl as mdtl on
d.trans_seq = mdtl.trans_seq and
d.dtl_seq = mdtl.dtl_seq join
micros.mi_def as mdef on
mdtl.mi_seq = mdef.mi_seq join
micros.chk_dtl as ckdtl on
tdtl.chk_seq = ckdtl.chk_seq
where 1 = 1
order by Date_Time desc
Steps to get the additional information:
A check is created with one item. (Using the query there is just the one row that can be seen for the new check at the top)
This check is split. (Using the query there are now 2 rows that can be seen for the split items)
The split items are combined to one check (Using the query there are now 4 rows that can be seen for the split items. 3 rows for one check which includes the original item and the splits and 1 row for the old split that has been combined to the other check)
How do I get just the split items for the one check correct check? Is there something I should be filtering on. There is nothing that jumps out at me as something that makes sense. Please help!!!
Data included below:
chk_num,Check_Emp,Check_Seq,Cashier_Total_Seq,End_date,Transaction,Transaction_Seq,Check_Count,Check_Total,Date_Time,Detail_Index,Detail_Seq,Detail_Type,Record_type,Report_Count,Report_Total,Seat,MenuName1,MenuName2,TotalBill,SvcChargeTtl,CheckNum,CheckSeq,CoverCnt,EmployeeSeq,DetailNum,OrderType,PageNum,PaymentTot,SubTotal,TaxTotal,TableOpnDate
1079,1253,2747,,'2014-09-16 13:33:18.000',1253,7626,0,2.00,'2014-09-16 13:31:54.000',-1,1,'M','A',0,0.00,1,'Budweiser',,2.00,0.00,1079,2747,1,1253,17,1,0,0.00,2.00,0.00,'2014-09-16 13:31:27.000'
1078,1253,2746,,'2014-09-16 13:31:54.000',1253,7625,1,4.00,'2014-09-16 13:31:54.000',-1,1,'M','A',1,4.00,1,'Budweiser',,4.00,0.00,1078,2746,2,1253,18,1,0,0.00,4.00,0.00,'2014-09-16 13:31:27.000'
1078,1253,2746,,'2014-09-16 13:34:46.000',1253,7630,0,2.00,'2014-09-16 13:31:54.000',17,1,'M','I',0,0.00,2,'Budweiser',,4.00,0.00,1078,2746,2,1253,18,1,0,0.00,4.00,0.00,'2014-09-16 13:31:27.000'
1078,1253,2746,,'2014-09-16 13:33:19.000',1253,7628,1,2.00,'2014-09-16 13:31:54.000',16,2,'M','I',0,0.00,1,'Budweiser',,4.00,0.00,1078,2746,2,1253,18,1,0,0.00,4.00,0.00,'2014-09-16 13:31:27.000'