|
|
Posted by Roy Harvey on 08/09/06 17:07
I see no way you can reliably tie things back. Most, or at least
many, such refunds will happen some time after the person starts to
(try to) eat, and at most such places the register took three more
orders before you ever receive your food. I have received refunds the
NEXT DAY when an order I received on a drive-through was completely
wrong.
Maybe when business is slow you might tie it back with a fair chance
of making an accurate match, but I would not count on having anything
worth using.
Roy Harvey
Beacon Falls, CT
On 9 Aug 2006 07:31:57 -0700, J.Evans.1970@gmail.com wrote:
>Hello. I've got a table I'm trying to tie to two other tables. The
>problem is that there is nothing distinct between the 3 tables. Yes,
>I know... But this is what I have to work with. Let me explain
>exactly what it is I'm trying to do with a little background history.
>
>First, the fast food company I work for has registers in their stores.
>We capture TLD files from the registers every 15 minutes. On a daily
>basis those files are brough to headquarters where they are put in 3
>different tables - parSalesHdr, parSalesDetail and
>parSalesDetailModifier. The header table has the register number, the
>time the order wazs tendered, who was the cashier, gross order total,
>etc. The detail record has the details of that order - what was
>ordered, the price of the items, what was discounted, when it was
>discounted, etc. The modifier table has the modifiers of what is in
>the detail table - no pickes, add onions, etc. Okay, so now the
>register company has added the possibility of a new file to be picked
>up if we want it. It is the reduction file. This file contains
>information for orders where an item was deleted from an order after
>the order was totaled. This is a bad thing - it allows theft. We want
>to use the r eduction file to find out who is doing this. A deletion
>from an order requires a manager to swipe their card on the register to
>allow a deletion. The reduction file contains that - who swiped their
>card, for what item, the cost of the item.
>
>Basically what I want to do is to tie what is in that Reduction file to
>the detail table and header table. The detail and header table
>diferent date/time stamps, but none of them match the date/time stamp
>in the reduction file. The header fille has the time the first item
>was placed and the time the cash was tendered. The detail table has
>the time the cash was tendered. The reduction file just has the time
>the manager card was swiped. The only thing I can see to do is try to
>match the reduction time to be between the first item order time and
>the cash tender time. Then I can match on the items being deleted from
>the order. The detail table has a field called [after] which would
>indicate an item being deleted as well. The [after] field will have the
>quantity of the items being deleted from the order after it is totaled.
> So I can use that as well. The problem comes in when there are many
>of the same items being sold. Some have been deleted - some not.
>There's no real way to match those up.
>
>That's my question - is there some what to tie the reduction table to
>the detail and header tables that I'm not seeing? I've got table
>creations/inserts and the query I running to ties them all together
>below.
>
>Thanks,
>Jennifer
>
>
>Create Table parSalesHdr
>(parSalesHdrID bigint, unitnumber int ,registernumber int ,
>posemployeenumber int, posemployeename nvarchar(30),
>grossordertotal money,ordertotaltime datetime,
>amounttendertime datetime, BusinessDay DateTime)
>
>Insert into parSalesHdr (parSalesHdrID, unitnumber, registernumber,
>posemployeenumber, posemployeename , grossordertotal ,ordertotaltime,
>amounttendertime , BusinessDay) values (5948325, 608, 3,7,
>'Larry',6.11,'8/30/05 12:11:06 am', '8/30/05 12:18:26 am', '8/30/05')
>
>create Table parSalesDetail
>(parSalesHdrID bigint, parSalesDetailID bigint, quantity int,
>itemprice money,[after] int, positem nvarchar(20),
>amounttendertime datetime, BusinessDay smalldatetime, UnitNumber int)
>
>
>Insert Into parSalesDetail (parSalesHdrID , parSalesDetailID ,
>quantity,
>itemprice ,[after] , positem ,
>amounttendertime , BusinessDay , UnitNumber) values (5948325,
>26143916, 1, 4.8900, 1, 'WC-ML', '2005-08-30 00:18:26.000',
>
>'2005-08-30 00:00:00.000', 608)
>Insert Into parSalesDetail (parSalesHdrID , parSalesDetailID ,
>quantity,
>itemprice ,[after] , positem ,
>amounttendertime , BusinessDay , UnitNumber) values (5948325,
>26143917, 1, 4.8900, 1, 'WC-ML',
> '2005-08-30 00:18:26.000',
>'2005-08-30 00:00:00.000', 608)
>Insert Into parSalesDetail (parSalesHdrID , parSalesDetailID ,
>quantity,itemprice ,[after] , positem ,
>amounttendertime , BusinessDay , UnitNumber) values (5948325,
>26143918, 7, 4.1900, 7, 'WB-ML',
> '2005-08-30 00:18:26.000',
>'2005-08-30 00:00:00.000', 608)
>Insert Into parSalesDetail (parSalesHdrID , parSalesDetailID ,
>quantity,itemprice ,[after] , positem ,
>amounttendertime , BusinessDay , UnitNumber) values (5948325,
>26143919, 1, 4.1900, 0, 'WB-ML',
> '2005-08-30 00:18:26.000',
>'2005-08-30 00:00:00.000', 608)
>Insert Into parSalesDetail (parSalesHdrID , parSalesDetailID ,
>quantity,itemprice ,[after] , positem ,
>amounttendertime , BusinessDay , UnitNumber) values (5948325,
>26143920, 7, 4.1900, 7, 'WB-ML',
> '2005-08-30 00:18:26.000',
>'2005-08-30 00:00:00.000', 608)
>Insert Into parSalesDetail (parSalesHdrID , parSalesDetailID ,
>quantity,itemprice ,[after] , positem ,
>amounttendertime , BusinessDay , UnitNumber) values (5948325,
>26143921, 4, 4.1900, 4, 'WB-ML',
> '2005-08-30 00:18:26.000',
>'2005-08-30 00:00:00.000', 608)
>Insert Into parSalesDetail (parSalesHdrID , parSalesDetailID ,
>quantity,itemprice ,[after] , positem ,
>amounttendertime , BusinessDay , UnitNumber) values (5948325,
>26143922, 1, 4.1900, 1, 'WB-ML',
> '2005-08-30 00:18:26.000',
>'2005-08-30 00:00:00.000', 608)
>
>CREATE TABLE [dbo].[ParReductionFile] (
> [UnitNumber] [int] ,
> [ReductionType] [int] ,
> [RegisterNumber] [int] ,
> [CashierNumber] [int] ,
> [CashierName] [nvarchar] (16) ,
> [ReductionDate] [datetime] ,
> [ReductionTime] [datetime] ,
> [ReductionCode] [char] (1) ,
> [ManagerNumber] [int] ,
> [ManagerName] [nvarchar] (16) ,
> [ReductionValue] [decimal](18, 4) ,
> [OriginalQuantity] [int] ,
> [NewQuantity] [int] ,
> [ProductID] [nvarchar] (50) ,
> [ProductName] [nvarchar] (50)
>)
>
>
>
>insert into parReductionFile (UnitNumber
>,ReductionType,RegisterNumber,CashierNumber,
> CashierName,ReductionDate,ReductionTime,ReductionCode,ManagerNumber,
> ManagerName,ReductionValue,OriginalQuantity,NewQuantity,ProductID,ProductName)
>values (608, 2, 3, 7, 'Larry Shirley', '8/30/2005', '12:12:00
>AM', 'A', 30, 'ADRIANE PATTERSO', 4.59, 1, 0, 'WB-ML', 'WB-ML')
>
>
>insert into parReductionFile (UnitNumber
>,ReductionType,RegisterNumber,CashierNumber,
> CashierName,ReductionDate,ReductionTime,ReductionCode,ManagerNumber,
> ManagerName,ReductionValue,OriginalQuantity,NewQuantity,ProductID,ProductName)
>values (608, 2, 3, 7, 'Larry Shirley', '8/30/2005', '12:12:00
>AM', 'A', 30, 'ADRIANE PATTERSO', 18.36, 4, 0, 'WB-ML', 'WB-ML')
>
>
>insert into parReductionFile (UnitNumber
>,ReductionType,RegisterNumber,CashierNumber,
> CashierName,ReductionDate,ReductionTime,ReductionCode,ManagerNumber,
> ManagerName,ReductionValue,OriginalQuantity,NewQuantity,ProductID,ProductName)
>values (608, 2, 3, 7, 'Larry Shirley', '8/30/2005', '12:12:00
>AM', 'A', 30, 'ADRIANE PATTERSO', 32.13, 7, 0, 'WB-ML', 'WB-ML')
>
>
>insert into parReductionFile (UnitNumber
>,ReductionType,RegisterNumber,CashierNumber,
> CashierName,ReductionDate,ReductionTime,ReductionCode,ManagerNumber,
> ManagerName,ReductionValue,OriginalQuantity,NewQuantity,ProductID,ProductName)
>values (608, 2, 3, 7, 'Larry Shirley', '8/30/2005', '12:12:00
>AM', 'A', 30, 'ADRIANE PATTERSO', 32.13, 7, 0, 'WB-ML', 'WB-ML')
>
>
>insert into parReductionFile (UnitNumber
>,ReductionType,RegisterNumber,CashierNumber,
> CashierName,ReductionDate,ReductionTime,ReductionCode,ManagerNumber,
> ManagerName,ReductionValue,OriginalQuantity,NewQuantity,ProductID,ProductName)
>values (608, 2, 3, 7, 'Larry Shirley', '8/30/2005', '12:12:00
>AM', 'A', 30, 'ADRIANE PATTERSO', 4.89, 1, 0, 'WC-ML', 'WC-ML')
>
>
>insert into parReductionFile (UnitNumber
>,ReductionType,RegisterNumber,CashierNumber,
> CashierName,ReductionDate,ReductionTime,ReductionCode,ManagerNumber,
> ManagerName,ReductionValue,OriginalQuantity,NewQuantity,ProductID,ProductName)
>values (608, 2, 3, 7, 'Larry Shirley', '8/30/2005', '12:13:00
>AM', 'A', 30, 'ADRIANE PATTERSO', 4.89, 1, 0, 'WC-ML', 'WC-ML')
>
>
>
>
>
>
>SELECT
> h.unitnumber UNIT,
> h.registernumber REG,
>
> h.posemployeenumber EENUM,
> h.posemployeename EMPNAME,
> d.itemprice * d.after TOTAL,
> h.grossordertotal [ORDER TOTAL],
> h.amounttendertime TENDTIME,
> d.after ATD,
> d.positem [POS ITEM],
> convert(nvarchar(12),r.reductiondate,101) + ' ' +
>convert(nvarchar(12),r.reductiontime,108) as ReductionTime,
> r.ReductionType,
> r.RegisterNumber,
> r.CashierNumber,
> r.CashierName,
> r.ManagerNumber,
> r.ManagerName,
> --r.ReductionValue,
> r.OriginalQuantity,
> r.NewQuantity,
> r.ProductName
>
>from parreductionfile r, parsaleshdr h, parsalesdetail d
>
>where h.businessday between '8/30/05' and '8/30/05' and
> h.unitnumber = 608
> and convert(nvarchar(12),r.reductiondate,101) + ' ' +
>convert(nvarchar(12),r.reductiontime,108) between
> h.ordertotaltime and h.amounttendertime
> and h.parsaleshdrid = d.parsaleshdrid
> and d.unitnumber = r.unitnumber
> and d.positem = r.productname
> and d.after > 0
> and d.after = r.originalquantity - r.newquantity
> and d.quantity = r.originalquantity
Navigation:
[Reply to this message]
|