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
parSalesDetailM odifier. 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 ,
posemployeenumb er int, posemployeename nvarchar(30),
grossordertotal money,ordertota ltime datetime,
amounttendertim e datetime, BusinessDay DateTime)
Insert into parSalesHdr (parSalesHdrID, unitnumber, registernumber,
posemployeenumb er, posemployeename , grossordertotal ,ordertotaltime ,
amounttendertim e , 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, parSalesDetailI D bigint, quantity int,
itemprice money,[after] int, positem nvarchar(20),
amounttendertim e datetime, BusinessDay smalldatetime, UnitNumber int)
Insert Into parSalesDetail (parSalesHdrID , parSalesDetailI D ,
quantity,
itemprice ,[after] , positem ,
amounttendertim e , 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 , parSalesDetailI D ,
quantity,
itemprice ,[after] , positem ,
amounttendertim e , 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 , parSalesDetailI D ,
quantity,itempr ice ,[after] , positem ,
amounttendertim e , 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 , parSalesDetailI D ,
quantity,itempr ice ,[after] , positem ,
amounttendertim e , 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 , parSalesDetailI D ,
quantity,itempr ice ,[after] , positem ,
amounttendertim e , 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 , parSalesDetailI D ,
quantity,itempr ice ,[after] , positem ,
amounttendertim e , 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 , parSalesDetailI D ,
quantity,itempr ice ,[after] , positem ,
amounttendertim e , 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].[ParReductionFil e] (
[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) ,
[OriginalQuantit y] [int] ,
[NewQuantity] [int] ,
[ProductID] [nvarchar] (50) ,
[ProductName] [nvarchar] (50)
)
insert into parReductionFil e (UnitNumber
,ReductionType, RegisterNumber, CashierNumber,
CashierName,Red uctionDate,Redu ctionTime,Reduc tionCode,Manage rNumber,
ManagerName,Red uctionValue,Ori ginalQuantity,N ewQuantity,Prod uctID,ProductNa me)
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 parReductionFil e (UnitNumber
,ReductionType, RegisterNumber, CashierNumber,
CashierName,Red uctionDate,Redu ctionTime,Reduc tionCode,Manage rNumber,
ManagerName,Red uctionValue,Ori ginalQuantity,N ewQuantity,Prod uctID,ProductNa me)
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 parReductionFil e (UnitNumber
,ReductionType, RegisterNumber, CashierNumber,
CashierName,Red uctionDate,Redu ctionTime,Reduc tionCode,Manage rNumber,
ManagerName,Red uctionValue,Ori ginalQuantity,N ewQuantity,Prod uctID,ProductNa me)
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 parReductionFil e (UnitNumber
,ReductionType, RegisterNumber, CashierNumber,
CashierName,Red uctionDate,Redu ctionTime,Reduc tionCode,Manage rNumber,
ManagerName,Red uctionValue,Ori ginalQuantity,N ewQuantity,Prod uctID,ProductNa me)
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 parReductionFil e (UnitNumber
,ReductionType, RegisterNumber, CashierNumber,
CashierName,Red uctionDate,Redu ctionTime,Reduc tionCode,Manage rNumber,
ManagerName,Red uctionValue,Ori ginalQuantity,N ewQuantity,Prod uctID,ProductNa me)
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 parReductionFil e (UnitNumber
,ReductionType, RegisterNumber, CashierNumber,
CashierName,Red uctionDate,Redu ctionTime,Reduc tionCode,Manage rNumber,
ManagerName,Red uctionValue,Ori ginalQuantity,N ewQuantity,Prod uctID,ProductNa me)
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.registernumbe r REG,
h.posemployeenu mber EENUM,
h.posemployeena me EMPNAME,
d.itemprice * d.after TOTAL,
h.grossordertot al [ORDER TOTAL],
h.amounttendert ime TENDTIME,
d.after ATD,
d.positem [POS ITEM],
convert(nvarcha r(12),r.reducti ondate,101) + ' ' +
convert(nvarcha r(12),r.reducti ontime,108) as ReductionTime,
r.ReductionType ,
r.RegisterNumbe r,
r.CashierNumber ,
r.CashierName,
r.ManagerNumber ,
r.ManagerName,
--r.ReductionValu e,
r.OriginalQuant ity,
r.NewQuantity,
r.ProductName
from parreductionfil e r, parsaleshdr h, parsalesdetail d
where h.businessday between '8/30/05' and '8/30/05' and
h.unitnumber = 608
and convert(nvarcha r(12),r.reducti ondate,101) + ' ' +
convert(nvarcha r(12),r.reducti ontime,108) between
h.ordertotaltim e and h.amounttendert ime
and h.parsaleshdrid = d.parsaleshdrid
and d.unitnumber = r.unitnumber
and d.positem = r.productname
and d.after 0
and d.after = r.originalquant ity - r.newquantity
and d.quantity = r.originalquant ity
11  1707 
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.**********@gm ail.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
parSalesDetail Modifier. 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
(parSalesHdr ID bigint, unitnumber int ,registernumber int ,
posemployeenum ber int, posemployeename nvarchar(30),
grossordertota l money,ordertota ltime datetime,
amounttenderti me datetime, BusinessDay DateTime)
Insert into parSalesHdr (parSalesHdrID, unitnumber, registernumber,
posemployeenum ber, posemployeename , grossordertotal ,ordertotaltime ,
amounttenderti me , 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
(parSalesHdr ID bigint, parSalesDetailI D bigint, quantity int,
itemprice money,[after] int, positem nvarchar(20),
amounttenderti me datetime, BusinessDay smalldatetime, UnitNumber int)
Insert Into parSalesDetail (parSalesHdrID , parSalesDetailI D ,
quantity,
itemprice ,[after] , positem ,
amounttenderti me , 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 , parSalesDetailI D ,
quantity,
itemprice ,[after] , positem ,
amounttenderti me , 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 , parSalesDetailI D ,
quantity,itemp rice ,[after] , positem ,
amounttenderti me , 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 , parSalesDetailI D ,
quantity,itemp rice ,[after] , positem ,
amounttenderti me , 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 , parSalesDetailI D ,
quantity,itemp rice ,[after] , positem ,
amounttenderti me , 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 , parSalesDetailI D ,
quantity,itemp rice ,[after] , positem ,
amounttenderti me , 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 , parSalesDetailI D ,
quantity,itemp rice ,[after] , positem ,
amounttenderti me , 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].[ParReductionFil e] (
[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) ,
[OriginalQuantit y] [int] ,
[NewQuantity] [int] ,
[ProductID] [nvarchar] (50) ,
[ProductName] [nvarchar] (50)
)
insert into parReductionFil e (UnitNumber
,ReductionType ,RegisterNumber ,CashierNumber,
CashierName,Red uctionDate,Redu ctionTime,Reduc tionCode,Manage rNumber,
ManagerName,Red uctionValue,Ori ginalQuantity,N ewQuantity,Prod uctID,ProductNa me)
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 parReductionFil e (UnitNumber
,ReductionType ,RegisterNumber ,CashierNumber,
CashierName,Red uctionDate,Redu ctionTime,Reduc tionCode,Manage rNumber,
ManagerName,Red uctionValue,Ori ginalQuantity,N ewQuantity,Prod uctID,ProductNa me)
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 parReductionFil e (UnitNumber
,ReductionType ,RegisterNumber ,CashierNumber,
CashierName,Red uctionDate,Redu ctionTime,Reduc tionCode,Manage rNumber,
ManagerName,Red uctionValue,Ori ginalQuantity,N ewQuantity,Prod uctID,ProductNa me)
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 parReductionFil e (UnitNumber
,ReductionType ,RegisterNumber ,CashierNumber,
CashierName,Red uctionDate,Redu ctionTime,Reduc tionCode,Manage rNumber,
ManagerName,Red uctionValue,Ori ginalQuantity,N ewQuantity,Prod uctID,ProductNa me)
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 parReductionFil e (UnitNumber
,ReductionType ,RegisterNumber ,CashierNumber,
CashierName,Red uctionDate,Redu ctionTime,Reduc tionCode,Manage rNumber,
ManagerName,Red uctionValue,Ori ginalQuantity,N ewQuantity,Prod uctID,ProductNa me)
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 parReductionFil e (UnitNumber
,ReductionType ,RegisterNumber ,CashierNumber,
CashierName,Red uctionDate,Redu ctionTime,Reduc tionCode,Manage rNumber,
ManagerName,Red uctionValue,Ori ginalQuantity,N ewQuantity,Prod uctID,ProductNa me)
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.registernumbe r REG,
h.posemployeenu mber EENUM,
h.posemployeena me EMPNAME,
d.itemprice * d.after TOTAL,
h.grossordertot al [ORDER TOTAL],
h.amounttendert ime TENDTIME,
d.after ATD,
d.positem [POS ITEM],
convert(nvarcha r(12),r.reducti ondate,101) + ' ' +
convert(nvarch ar(12),r.reduct iontime,108) as ReductionTime,
r.ReductionType ,
r.RegisterNumbe r,
r.CashierNumber ,
r.CashierName,
r.ManagerNumber ,
r.ManagerName,
--r.ReductionValu e,
r.OriginalQuant ity,
r.NewQuantity,
r.ProductName
from parreductionfil e r, parsaleshdr h, parsalesdetail d
where h.businessday between '8/30/05' and '8/30/05' and
h.unitnumber = 608
and convert(nvarcha r(12),r.reducti ondate,101) + ' ' +
convert(nvarch ar(12),r.reduct iontime,108) between
h.ordertotaltim e and h.amounttendert ime
and h.parsaleshdrid = d.parsaleshdrid
and d.unitnumber = r.unitnumber
and d.positem = r.productname
and d.after 0
and d.after = r.originalquant ity - r.newquantity
and d.quantity = r.originalquant ity
Yeah, I didn't think I could really tie it back either. I was just
hoping someone would see something I wasn't. And just to say it, I'm
not tying Refunds. That is something completely different. This is
what we call an After Total Delete. Once someone places the order, the
cashier will tell the customer the total. Then the customer might
change his mind about the number of things he ordered, or cancel one of
the items all together. At that point, it is company policy to void
out the order and begin again because the order has been "totaled".
Instead, the cashier is using the "After Total Delete" functionality to
delete the item off the order, but before taking the money from the
customer. Clear as mud? The thing is, the cashier (or manager) can
use this functionality to steal money.
Thanks!
Jennifer
Roy Harvey wrote:
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.**********@gm ail.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
parSalesDetailM odifier. 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 ,
posemployeenumb er int, posemployeename nvarchar(30),
grossordertotal money,ordertota ltime datetime,
amounttendertim e datetime, BusinessDay DateTime)
Insert into parSalesHdr (parSalesHdrID, unitnumber, registernumber,
posemployeenumb er, posemployeename , grossordertotal ,ordertotaltime ,
amounttendertim e , 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, parSalesDetailI D bigint, quantity int,
itemprice money,[after] int, positem nvarchar(20),
amounttendertim e datetime, BusinessDay smalldatetime, UnitNumber int)
Insert Into parSalesDetail (parSalesHdrID , parSalesDetailI D ,
quantity,
itemprice ,[after] , positem ,
amounttendertim e , 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 , parSalesDetailI D ,
quantity,
itemprice ,[after] , positem ,
amounttendertim e , 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 , parSalesDetailI D ,
quantity,itempr ice ,[after] , positem ,
amounttendertim e , 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 , parSalesDetailI D ,
quantity,itempr ice ,[after] , positem ,
amounttendertim e , 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 , parSalesDetailI D ,
quantity,itempr ice ,[after] , positem ,
amounttendertim e , 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 , parSalesDetailI D ,
quantity,itempr ice ,[after] , positem ,
amounttendertim e , 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 , parSalesDetailI D ,
quantity,itempr ice ,[after] , positem ,
amounttendertim e , 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].[ParReductionFil e] (
[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) ,
[OriginalQuantit y] [int] ,
[NewQuantity] [int] ,
[ProductID] [nvarchar] (50) ,
[ProductName] [nvarchar] (50)
)
insert into parReductionFil e (UnitNumber
,ReductionType, RegisterNumber, CashierNumber,
CashierName,Red uctionDate,Redu ctionTime,Reduc tionCode,Manage rNumber,
ManagerName,Red uctionValue,Ori ginalQuantity,N ewQuantity,Prod uctID,ProductNa me)
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 parReductionFil e (UnitNumber
,ReductionType, RegisterNumber, CashierNumber,
CashierName,Red uctionDate,Redu ctionTime,Reduc tionCode,Manage rNumber,
ManagerName,Red uctionValue,Ori ginalQuantity,N ewQuantity,Prod uctID,ProductNa me)
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 parReductionFil e (UnitNumber
,ReductionType, RegisterNumber, CashierNumber,
CashierName,Red uctionDate,Redu ctionTime,Reduc tionCode,Manage rNumber,
ManagerName,Red uctionValue,Ori ginalQuantity,N ewQuantity,Prod uctID,ProductNa me)
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 parReductionFil e (UnitNumber
,ReductionType, RegisterNumber, CashierNumber,
CashierName,Red uctionDate,Redu ctionTime,Reduc tionCode,Manage rNumber,
ManagerName,Red uctionValue,Ori ginalQuantity,N ewQuantity,Prod uctID,ProductNa me)
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 parReductionFil e (UnitNumber
,ReductionType, RegisterNumber, CashierNumber,
CashierName,Red uctionDate,Redu ctionTime,Reduc tionCode,Manage rNumber,
ManagerName,Red uctionValue,Ori ginalQuantity,N ewQuantity,Prod uctID,ProductNa me)
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 parReductionFil e (UnitNumber
,ReductionType, RegisterNumber, CashierNumber,
CashierName,Red uctionDate,Redu ctionTime,Reduc tionCode,Manage rNumber,
ManagerName,Red uctionValue,Ori ginalQuantity,N ewQuantity,Prod uctID,ProductNa me)
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.registernumbe r REG,
h.posemployeenu mber EENUM,
h.posemployeena me EMPNAME,
d.itemprice * d.after TOTAL,
h.grossordertot al [ORDER TOTAL],
h.amounttendert ime TENDTIME,
d.after ATD,
d.positem [POS ITEM],
convert(nvarcha r(12),r.reducti ondate,101) + ' ' +
convert(nvarcha r(12),r.reducti ontime,108) as ReductionTime,
r.ReductionType ,
r.RegisterNumbe r,
r.CashierNumber ,
r.CashierName,
r.ManagerNumber ,
r.ManagerName,
--r.ReductionValu e,
r.OriginalQuant ity,
r.NewQuantity,
r.ProductName
from parreductionfil e r, parsaleshdr h, parsalesdetail d
where h.businessday between '8/30/05' and '8/30/05' and
h.unitnumber = 608
and convert(nvarcha r(12),r.reducti ondate,101) + ' ' +
convert(nvarcha r(12),r.reducti ontime,108) between
h.ordertotaltim e and h.amounttendert ime
and h.parsaleshdrid = d.parsaleshdrid
and d.unitnumber = r.unitnumber
and d.positem = r.productname
and d.after 0
and d.after = r.originalquant ity - r.newquantity
and d.quantity = r.originalquant ity
On Wed, 09 Aug 2006 17:07:13 GMT, Roy Harvey <ro********@sne t.net>
wrote:
>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.
Seconded. Just require the person processing the refund to enter some
appropriate data from the receipt. Store number + register number +
date + time is probably sufficient. What's your policy on refunds
without a receipt? And even if you do this, a number of customers
throw away their receipts at the store, so an unscrupulous manager
could collect those and approve bogus refunds against them.
OK, I have a much clearer understanding now. I should have read your
original post more carefully.
I think that attempting to make an unambigous relationship from
ambigous data will simply drive you nuts. You can relate it to the
order, but handling the specific line match up is simply not
guaranteed. The analysis needs to be at the order level, somehow.
I do think some good analysis could be done, but that would be by
coding in the front-end reporting tool combined with identifying
orders with the After Total Delete transaction in SQL. For example,
it sounds like there will be two rows in the data for the Total, one
from before the ATD and another after. Analysis of the net change
between the two might show a mix of positive and negative changes when
it is being used honestly, but a stronger bias toward lowering numbers
for theft. Can you identify when another item is added after the ATD?
Good luck!
Roy
On 9 Aug 2006 11:39:00 -0700, J.**********@gm ail.com wrote:
>Yeah, I didn't think I could really tie it back either. I was just
hoping someone would see something I wasn't. And just to say it, I'm
not tying Refunds. That is something completely different. This is
what we call an After Total Delete. Once someone places the order, the
cashier will tell the customer the total. Then the customer might
change his mind about the number of things he ordered, or cancel one of
the items all together. At that point, it is company policy to void
out the order and begin again because the order has been "totaled".
Instead, the cashier is using the "After Total Delete" functionality to
delete the item off the order, but before taking the money from the
customer. Clear as mud? The thing is, the cashier (or manager) can
use this functionality to steal money.
Thanks!
Jennifer
Roy Harvey wrote:
>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.**********@gm ail.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
parSalesDetail Modifier. 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
(parSalesHdr ID bigint, unitnumber int ,registernumber int ,
posemployeenum ber int, posemployeename nvarchar(30),
grossordertota l money,ordertota ltime datetime,
amounttenderti me datetime, BusinessDay DateTime)
Insert into parSalesHdr (parSalesHdrID, unitnumber, registernumber,
posemployeenum ber, posemployeename , grossordertotal ,ordertotaltime ,
amounttenderti me , 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
(parSalesHdr ID bigint, parSalesDetailI D bigint, quantity int,
itemprice money,[after] int, positem nvarchar(20),
amounttenderti me datetime, BusinessDay smalldatetime, UnitNumber int)
Insert Into parSalesDetail (parSalesHdrID , parSalesDetailI D ,
quantity,
itemprice ,[after] , positem ,
amounttenderti me , 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 , parSalesDetailI D ,
quantity,
itemprice ,[after] , positem ,
amounttenderti me , 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 , parSalesDetailI D ,
quantity,itemp rice ,[after] , positem ,
amounttenderti me , 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 , parSalesDetailI D ,
quantity,itemp rice ,[after] , positem ,
amounttenderti me , 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 , parSalesDetailI D ,
quantity,itemp rice ,[after] , positem ,
amounttenderti me , 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 , parSalesDetailI D ,
quantity,itemp rice ,[after] , positem ,
amounttenderti me , 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 , parSalesDetailI D ,
quantity,itemp rice ,[after] , positem ,
amounttenderti me , 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].[ParReductionFil e] (
[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) ,
[OriginalQuantit y] [int] ,
[NewQuantity] [int] ,
[ProductID] [nvarchar] (50) ,
[ProductName] [nvarchar] (50)
)
insert into parReductionFil e (UnitNumber
,ReductionType ,RegisterNumber ,CashierNumber,
CashierName,Red uctionDate,Redu ctionTime,Reduc tionCode,Manage rNumber,
ManagerName,Red uctionValue,Ori ginalQuantity,N ewQuantity,Prod uctID,ProductNa me)
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 parReductionFil e (UnitNumber
,ReductionType ,RegisterNumber ,CashierNumber,
CashierName,Red uctionDate,Redu ctionTime,Reduc tionCode,Manage rNumber,
ManagerName,Red uctionValue,Ori ginalQuantity,N ewQuantity,Prod uctID,ProductNa me)
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 parReductionFil e (UnitNumber
,ReductionType ,RegisterNumber ,CashierNumber,
CashierName,Red uctionDate,Redu ctionTime,Reduc tionCode,Manage rNumber,
ManagerName,Red uctionValue,Ori ginalQuantity,N ewQuantity,Prod uctID,ProductNa me)
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 parReductionFil e (UnitNumber
,ReductionType ,RegisterNumber ,CashierNumber,
CashierName,Red uctionDate,Redu ctionTime,Reduc tionCode,Manage rNumber,
ManagerName,Red uctionValue,Ori ginalQuantity,N ewQuantity,Prod uctID,ProductNa me)
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 parReductionFil e (UnitNumber
,ReductionType ,RegisterNumber ,CashierNumber,
CashierName,Red uctionDate,Redu ctionTime,Reduc tionCode,Manage rNumber,
ManagerName,Red uctionValue,Ori ginalQuantity,N ewQuantity,Prod uctID,ProductNa me)
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 parReductionFil e (UnitNumber
,ReductionType ,RegisterNumber ,CashierNumber,
CashierName,Red uctionDate,Redu ctionTime,Reduc tionCode,Manage rNumber,
ManagerName,Red uctionValue,Ori ginalQuantity,N ewQuantity,Prod uctID,ProductNa me)
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.registernumbe r REG,
h.posemployeenu mber EENUM,
h.posemployeena me EMPNAME,
d.itemprice * d.after TOTAL,
h.grossordertot al [ORDER TOTAL],
h.amounttendert ime TENDTIME,
d.after ATD,
d.positem [POS ITEM],
convert(nvarcha r(12),r.reducti ondate,101) + ' ' +
convert(nvarch ar(12),r.reduct iontime,108) as ReductionTime,
r.ReductionType ,
r.RegisterNumbe r,
r.CashierNumber ,
r.CashierName,
r.ManagerNumber ,
r.ManagerName,
--r.ReductionValu e,
r.OriginalQuant ity,
r.NewQuantity,
r.ProductName
from parreductionfil e r, parsaleshdr h, parsalesdetail d
where h.businessday between '8/30/05' and '8/30/05' and
h.unitnumber = 608
and convert(nvarcha r(12),r.reducti ondate,101) + ' ' +
convert(nvarch ar(12),r.reduct iontime,108) between
h.ordertotaltim e and h.amounttendert ime
and h.parsaleshdrid = d.parsaleshdrid
and d.unitnumber = r.unitnumber
and d.positem = r.productname
and d.after 0
and d.after = r.originalquant ity - r.newquantity
and d.quantity = r.originalquant ity
Agreed...it's been driving me nuts for a couple of days now. :) Well,
I do know that I can match the ATD to the Header table without too much
trouble. I think I just need to give up on the Detail table.
I can identify everything in one order - whether it was before the ATD
or after...no. All the records in the Detail table have a Header ID,
so we use the Header ID, unit number and business date to match the
detail to the header. It's just that the Reduction file is a new thing
that the Register Company did not think out properly (to my way of
thinking). In fact, the whole ATD functionality should just be turned
off at the register level and then we wouldn't have to worry about
theft from that particular angle. But that would just make too much
sense. :)
Thanks for the thought and time that you put in your replies. It is
appreciated.
Thanks,
Jennifer
Roy Harvey wrote:
OK, I have a much clearer understanding now. I should have read your
original post more carefully.
I think that attempting to make an unambigous relationship from
ambigous data will simply drive you nuts. You can relate it to the
order, but handling the specific line match up is simply not
guaranteed. The analysis needs to be at the order level, somehow.
I do think some good analysis could be done, but that would be by
coding in the front-end reporting tool combined with identifying
orders with the After Total Delete transaction in SQL. For example,
it sounds like there will be two rows in the data for the Total, one
from before the ATD and another after. Analysis of the net change
between the two might show a mix of positive and negative changes when
it is being used honestly, but a stronger bias toward lowering numbers
for theft. Can you identify when another item is added after the ATD?
Good luck!
Roy
On 9 Aug 2006 11:39:00 -0700, J.**********@gm ail.com wrote:
Yeah, I didn't think I could really tie it back either. I was just
hoping someone would see something I wasn't. And just to say it, I'm
not tying Refunds. That is something completely different. This is
what we call an After Total Delete. Once someone places the order, the
cashier will tell the customer the total. Then the customer might
change his mind about the number of things he ordered, or cancel one of
the items all together. At that point, it is company policy to void
out the order and begin again because the order has been "totaled".
Instead, the cashier is using the "After Total Delete" functionality to
delete the item off the order, but before taking the money from the
customer. Clear as mud? The thing is, the cashier (or manager) can
use this functionality to steal money.
Thanks!
Jennifer
Roy Harvey wrote:
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.**********@gm ail.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
parSalesDetailM odifier. 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 ,
posemployeenumb er int, posemployeename nvarchar(30),
grossordertotal money,ordertota ltime datetime,
amounttendertim e datetime, BusinessDay DateTime)
Insert into parSalesHdr (parSalesHdrID, unitnumber, registernumber,
posemployeenumb er, posemployeename , grossordertotal ,ordertotaltime ,
amounttendertim e , 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, parSalesDetailI D bigint, quantity int,
itemprice money,[after] int, positem nvarchar(20),
amounttendertim e datetime, BusinessDay smalldatetime, UnitNumber int)
Insert Into parSalesDetail (parSalesHdrID , parSalesDetailI D ,
quantity,
itemprice ,[after] , positem ,
amounttendertim e , 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 , parSalesDetailI D ,
quantity,
itemprice ,[after] , positem ,
amounttendertim e , 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 , parSalesDetailI D ,
quantity,itempr ice ,[after] , positem ,
amounttendertim e , 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 , parSalesDetailI D ,
quantity,itempr ice ,[after] , positem ,
amounttendertim e , 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 , parSalesDetailI D ,
quantity,itempr ice ,[after] , positem ,
amounttendertim e , 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 , parSalesDetailI D ,
quantity,itempr ice ,[after] , positem ,
amounttendertim e , 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 , parSalesDetailI D ,
quantity,itempr ice ,[after] , positem ,
amounttendertim e , 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].[ParReductionFil e] (
[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) ,
[OriginalQuantit y] [int] ,
[NewQuantity] [int] ,
[ProductID] [nvarchar] (50) ,
[ProductName] [nvarchar] (50)
)
insert into parReductionFil e (UnitNumber
,ReductionType, RegisterNumber, CashierNumber,
CashierName,Red uctionDate,Redu ctionTime,Reduc tionCode,Manage rNumber,
ManagerName,Red uctionValue,Ori ginalQuantity,N ewQuantity,Prod uctID,ProductNa me)
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 parReductionFil e (UnitNumber
,ReductionType, RegisterNumber, CashierNumber,
CashierName,Red uctionDate,Redu ctionTime,Reduc tionCode,Manage rNumber,
ManagerName,Red uctionValue,Ori ginalQuantity,N ewQuantity,Prod uctID,ProductNa me)
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 parReductionFil e (UnitNumber
,ReductionType, RegisterNumber, CashierNumber,
CashierName,Red uctionDate,Redu ctionTime,Reduc tionCode,Manage rNumber,
ManagerName,Red uctionValue,Ori ginalQuantity,N ewQuantity,Prod uctID,ProductNa me)
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 parReductionFil e (UnitNumber
,ReductionType, RegisterNumber, CashierNumber,
CashierName,Red uctionDate,Redu ctionTime,Reduc tionCode,Manage rNumber,
ManagerName,Red uctionValue,Ori ginalQuantity,N ewQuantity,Prod uctID,ProductNa me)
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 parReductionFil e (UnitNumber
,ReductionType, RegisterNumber, CashierNumber,
CashierName,Red uctionDate,Redu ctionTime,Reduc tionCode,Manage rNumber,
ManagerName,Red uctionValue,Ori ginalQuantity,N ewQuantity,Prod uctID,ProductNa me)
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 parReductionFil e (UnitNumber
,ReductionType, RegisterNumber, CashierNumber,
CashierName,Red uctionDate,Redu ctionTime,Reduc tionCode,Manage rNumber,
ManagerName,Red uctionValue,Ori ginalQuantity,N ewQuantity,Prod uctID,ProductNa me)
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.registernumbe r REG,
h.posemployeenu mber EENUM,
h.posemployeena me EMPNAME,
d.itemprice * d.after TOTAL,
h.grossordertot al [ORDER TOTAL],
h.amounttendert ime TENDTIME,
d.after ATD,
d.positem [POS ITEM],
convert(nvarcha r(12),r.reducti ondate,101) + ' ' +
convert(nvarcha r(12),r.reducti ontime,108) as ReductionTime,
r.ReductionType ,
r.RegisterNumbe r,
r.CashierNumber ,
r.CashierName,
r.ManagerNumber ,
r.ManagerName,
--r.ReductionValu e,
r.OriginalQuant ity,
r.NewQuantity,
r.ProductName
from parreductionfil e r, parsaleshdr h, parsalesdetail d
where h.businessday between '8/30/05' and '8/30/05' and
h.unitnumber = 608
and convert(nvarcha r(12),r.reducti ondate,101) + ' ' +
convert(nvarcha r(12),r.reducti ontime,108) between
h.ordertotaltim e and h.amounttendert ime
and h.parsaleshdrid = d.parsaleshdrid
and d.unitnumber = r.unitnumber
and d.positem = r.productname
and d.after 0
and d.after = r.originalquant ity - r.newquantity
and d.quantity = r.originalquant ity
On 9 Aug 2006 13:10:46 -0700, J.**********@gm ail.com wrote:
>In fact, the whole ATD functionality should just be turned
off at the register level and then we wouldn't have to worry about
theft from that particular angle. But that would just make too much
sense. :)
I was about to say just that, after seeing your "customer-changes-mind
is supposed to be handled via void and restart". What is ATD intended
to be used for, then? The simple response would seem to be "display
the total somewhere where the customer can see it, both before and
after ATD is used".
The ATD functionality is just an option that the register company put
in their registers. It is there so that someone can delete something
off the order without having to start completely over. It's a nice
idea, but some people use it to steal.
Ed Murphy wrote:
On 9 Aug 2006 13:10:46 -0700, J.**********@gm ail.com wrote:
In fact, the whole ATD functionality should just be turned
off at the register level and then we wouldn't have to worry about
theft from that particular angle. But that would just make too much
sense. :)
I was about to say just that, after seeing your "customer-changes-mind
is supposed to be handled via void and restart". What is ATD intended
to be used for, then? The simple response would seem to be "display
the total somewhere where the customer can see it, both before and
after ATD is used".
The Powers-That-Be listened! I know, y'all don't really care, but I
thought it was cool. :) I suggested last week to just turn off the
ATD, and was told it was being considered. Usually that just means no.
Well, they are sending techs out to all the restaurants to turn it
off. Too bad I wasted all this time on the silly thing. But it's nice
to know that sometimes I'm listed to. :) J.**********@gm ail.com wrote:
The ATD functionality is just an option that the register company put
in their registers. It is there so that someone can delete something
off the order without having to start completely over. It's a nice
idea, but some people use it to steal.
Ed Murphy wrote:
On 9 Aug 2006 13:10:46 -0700, J.**********@gm ail.com wrote:
>In fact, the whole ATD functionality should just be turned
>off at the register level and then we wouldn't have to worry about
>theft from that particular angle. But that would just make too much
>sense. :)
I was about to say just that, after seeing your "customer-changes-mind
is supposed to be handled via void and restart". What is ATD intended
to be used for, then? The simple response would seem to be "display
the total somewhere where the customer can see it, both before and
after ATD is used".
On 9 Aug 2006 13:10:46 -0700, J.**********@gm ail.com wrote:
>t's just that the Reduction file is a new thing
that the Register Company did not think out properly (to my way of
thinking). In fact, the whole ATD functionality should just be turned
off at the register level and then we wouldn't have to worry about
theft from that particular angle. But that would just make too much
sense. :)
That occured to me too, but I decided I didn't have enough information
to justify suggesting it. 8-)
Roy This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics | |
by: rachel |
last post by:
Hi all,
I am new in ASP.Net. I have a question on link multiple web forms together.
Here is the scenario:
I create an Index.aspx WebForm which consists of a banner and three navigator buttons as well as a body to load the content. It is exactly like a normal HTML page where there are banner, menu bar, and body to load content. In my case, there are three .aspx WebForms which I have to link them together in the Index.aspx page. So that...
|
by: John M |
last post by:
Hi
For reporting grades and comments about students, I use one subreport per
subject. This includes a number of text boxes, some containing numerical
data, but one containing a textual report. Depending on how much is
written, sometime there are three on a page, sometimes two etc. What I
cannot seem to do is to get it to 'not start a report for a subject unless
it can fit it on a page'. The keep together option seem to have no effect...
|
by: Steve Jorgensen |
last post by:
I tried to fix a problem for a client today in which report sections and even
individual text controls in some of their reports are being split across page
boundaries. Of course, I was thinking the answer was just going to be setting
some Keep Together properties to Yes. Alas, it was not to be.
It turns out that the report in question is actually a combination of 2 other
reports based on different tables. The reports should follow one...
|
by: John Spiegel |
last post by:
Hi all,
I'm working with the .NET framework and Web Matrix and am having trouble
finding how to tie together the files when deriving classes. What I've got
is a TestBase.cs file that defines a class derived from page. I also have a
codebehind file that I'm referencing from an ASP.NET page. For example,
something like:
Test.aspx...
<%@ Page Language="C#" Src="Test.aspx.cs"
|
by: Brian Henry |
last post by:
If I have two tables in a data set say like this
Table1
======
ItemID
Name
Table2
=======
| | |
by: robertmeyer1 |
last post by:
Hey,
I have about 5 tables, and each table has a corresponding form. The 1st table (A) is a client data table (client information) with a ClientID (PK). This clientId is a (FK) in all 4 other tables (B-E). Now I have the tables linked in a relationship by PK-FK (1-to-1 relationships). The forms are set to go form A to form E by use of command buttons (click on in form A and opens form B...). The issue is, when I enter data in form A then...
|
by: Okonita |
last post by:
Hi all,
How can I implement using REORGCHK to tell REORG what DB2 UDB v8
tables, etc to perform REORG on?
Any example script will be highly appreciated.
Okonita
|
by: MGM |
last post by:
This is a bit hard to explain but I'll try my best.
I have 2 tables. One table has a row with the following value:
2,3
The two numbers are both ID's that are in the second table. I need to write a query that will ultimately give me the values of the second table, merged together as follows:
Data One, Data Two
|
by: knix |
last post by:
I have this access project consisting of multiple tables that are linked together in a relationship. I would like to migrate the consolidated information through appending in a datasheet form or importing the gathered information . Can anyone please give solutions with my problem?
Can I make a datasheet form (consisting fields from multiple tables that are linked together with relationships) in ms access which I could paste append or insert...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| | |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| | |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |
