469,160 Members | 2,063 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,160 developers. It's quick & easy.

Tying together tables

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,ReductionC ode,ManagerNumber,
ManagerName,ReductionValue,OriginalQuantity,NewQua ntity,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,ReductionC ode,ManagerNumber,
ManagerName,ReductionValue,OriginalQuantity,NewQua ntity,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,ReductionC ode,ManagerNumber,
ManagerName,ReductionValue,OriginalQuantity,NewQua ntity,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,ReductionC ode,ManagerNumber,
ManagerName,ReductionValue,OriginalQuantity,NewQua ntity,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,ReductionC ode,ManagerNumber,
ManagerName,ReductionValue,OriginalQuantity,NewQua ntity,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,ReductionC ode,ManagerNumber,
ManagerName,ReductionValue,OriginalQuantity,NewQua ntity,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

Aug 9 '06 #1
11 1561
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.**********@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,ReductionC ode,ManagerNumber,
ManagerName,ReductionValue,OriginalQuantity,NewQua ntity,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,ReductionC ode,ManagerNumber,
ManagerName,ReductionValue,OriginalQuantity,NewQua ntity,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,ReductionC ode,ManagerNumber,
ManagerName,ReductionValue,OriginalQuantity,NewQua ntity,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,ReductionC ode,ManagerNumber,
ManagerName,ReductionValue,OriginalQuantity,NewQua ntity,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,ReductionC ode,ManagerNumber,
ManagerName,ReductionValue,OriginalQuantity,NewQua ntity,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,ReductionC ode,ManagerNumber,
ManagerName,ReductionValue,OriginalQuantity,NewQua ntity,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
Aug 9 '06 #2
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.**********@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,ReductionC ode,ManagerNumber,
ManagerName,ReductionValue,OriginalQuantity,NewQua ntity,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,ReductionC ode,ManagerNumber,
ManagerName,ReductionValue,OriginalQuantity,NewQua ntity,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,ReductionC ode,ManagerNumber,
ManagerName,ReductionValue,OriginalQuantity,NewQua ntity,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,ReductionC ode,ManagerNumber,
ManagerName,ReductionValue,OriginalQuantity,NewQua ntity,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,ReductionC ode,ManagerNumber,
ManagerName,ReductionValue,OriginalQuantity,NewQua ntity,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,ReductionC ode,ManagerNumber,
ManagerName,ReductionValue,OriginalQuantity,NewQua ntity,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
Aug 9 '06 #3
On Wed, 09 Aug 2006 17:07:13 GMT, Roy Harvey <ro********@snet.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.
Aug 9 '06 #4
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.**********@gmail.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.**********@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,ReductionC ode,ManagerNumber,
ManagerName,ReductionValue,OriginalQuantity,NewQua ntity,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,ReductionC ode,ManagerNumber,
ManagerName,ReductionValue,OriginalQuantity,NewQua ntity,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,ReductionC ode,ManagerNumber,
ManagerName,ReductionValue,OriginalQuantity,NewQua ntity,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,ReductionC ode,ManagerNumber,
ManagerName,ReductionValue,OriginalQuantity,NewQua ntity,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,ReductionC ode,ManagerNumber,
ManagerName,ReductionValue,OriginalQuantity,NewQua ntity,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,ReductionC ode,ManagerNumber,
ManagerName,ReductionValue,OriginalQuantity,NewQua ntity,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
Aug 9 '06 #5
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.**********@gmail.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.**********@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,ReductionC ode,ManagerNumber,
ManagerName,ReductionValue,OriginalQuantity,NewQua ntity,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,ReductionC ode,ManagerNumber,
ManagerName,ReductionValue,OriginalQuantity,NewQua ntity,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,ReductionC ode,ManagerNumber,
ManagerName,ReductionValue,OriginalQuantity,NewQua ntity,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,ReductionC ode,ManagerNumber,
ManagerName,ReductionValue,OriginalQuantity,NewQua ntity,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,ReductionC ode,ManagerNumber,
ManagerName,ReductionValue,OriginalQuantity,NewQua ntity,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,ReductionC ode,ManagerNumber,
ManagerName,ReductionValue,OriginalQuantity,NewQua ntity,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
Aug 9 '06 #6
On 9 Aug 2006 13:10:46 -0700, J.**********@gmail.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".
Aug 9 '06 #7
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.**********@gmail.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".
Aug 9 '06 #8
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.**********@gmail.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.**********@gmail.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".
Aug 9 '06 #9
On 9 Aug 2006 13:10:46 -0700, J.**********@gmail.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
Aug 9 '06 #10
On 9 Aug 2006 15:03:11 -0700, J.**********@gmail.com wrote:
>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. :)
Congratulations! Being listened to is a very under-utilized form of
compensation. 8-)

Roy
Aug 9 '06 #11
(J.**********@gmail.com) writes:
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. :)
Sometimes the resolution of difficult query problems lies outside the
realm of the database world. Congratulations to a great solution to a
hard problem! I think you should reward youself with a visit to a
slow-food restaurant!
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Aug 10 '06 #12

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by John M | last post: by
6 posts views Thread by Steve Jorgensen | last post: by
2 posts views Thread by John Spiegel | last post: by
1 post views Thread by Brian Henry | last post: by
1 post views Thread by Mortomer39 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.