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
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
Comment