我认为你无法可靠地将事情联系起来。大多数，或者至少很多b $ b很多，这样的退款将在人们开始花费一段时间后发生（尝试）吃掉，并且在最多这样的地方，寄存器需要花费更多

在您收到食物之前订购。我已收到退款

NEXT DAY当我在直通车上收到的订单完全错误时

错误。


也许当生意很慢的时候你可以用公平的机会把它绑回来

做一个准确的比赛，但我不会指望有任何值得使用的价值

。


Roy Harvey

Beacon Falls，CT


2006年8月9日07:31:57 -0700， J。********** @ gmail.com 写道：

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:

>您好。我有一张桌子，我正试图绑在另外两张桌子上。
问题是3个表之间没有什么区别。是的，
我知道......但这是我必须要合作的。让我解释一下我正在尝试用一点背景历史做些什么。

首先，我工作的快餐公司已在他们的商店注册。
我们每15分钟从寄存器中捕获TLD文件。在每天的基础上，这些文件都会传到总部，在那里将它们放在3个不同的表中 - parSalesHdr，parSalesDetail和
parSalesDetailModifier。标题表有寄存器编号，订单wazs招标的时间，谁是收银员，总订单总额，
等。详细记录包含该订单的详细信息 - 订购的内容，商品的价格，折扣的内容，折扣的时间等等。修改器表中包含的内容的修饰符详细信息表 - 没有pickes，添加洋葱等等。好的，现在
注册公司已经添加了一个新文件的可能性，如果我们需要它可以选择
。这是减少文件。此文件包含订单总计后订单中订单中已删除商品的订单的
信息。这是一件坏事 - 它允许盗窃。我们希望
使用转发文件来找出这样做的人。从订单中删除
需要经理在登记册上刷卡以允许删除。减少文件包含 - 谁刷他们的
卡，什么项目，项目的成本。

基本上我想做的是将减少文件中的内容绑定到
详细信息表和标题表。细节和标题表
不同的日期/时间戳，但它们都没有与缩小文件中的日期/时间戳相匹配。标题文件有时间放置第一个项目
以及现金投标的时间。详细信息表有现金投标的时间。减少文件只有时间
经理卡刷卡。我唯一能做的就是尝试将缩短时间与第一个订单时间和现金投标时间之间的匹配相匹配。然后我可以匹配从订单中删除的项目。详细信息表有一个名为[after]的字段，它将指示要删除的项目。 [after]字段将在订购后从订单中删除项目的数量。

所以我也可以使用它。当有许多相同的物品被出售时，问题就出现了。有些已被删除 - 有些没有删除。
没有真正的方法来匹配这些。

这是我的问题 - 是否有一些将减少表与
我没有看到的细节和标题表？我有桌子
创作/插入和我运行的查询将它们联系在一起
下面。

谢谢，
Jennifer

创建表parSalesHdr
（parSalesHdrID bigint，unitnumber int，registernumber int，
posemployeenumber int，posemployeename nvarchar（30），
grossordertotal money，ordertotaltime datetime，
amounttentime datetime，BusinessDay DateTime）

插入parSalesHdr（parSalesHdrID，unitnumber，registernumber，
posemployeenumber，posemployeename，grossordertotal，ordertotaltime，
amounttendertime，BusinessDay）值（5948325,608,3,7，
''拉里''，6.11，''8/30/05 12:11:06 am''，''8/30/05 12:18:26 am''，''8/30 / 05''）

创建表parSalesDetail
（parSalesHdrID bigint，parSalesDetailID bigint，数量int，
itemprice money，[after] int，positem nvarchar（20），> amounttendertime datetime，BusinessDay smalldatetime，UnitNumber int）

插入parS alesDetail（parSalesHdrID，parSalesDetailID，
数量，
itemprice，[after]，positem，
amounttendertime，BusinessDay，UnitNumber）值（5948325，
26143916,1,4.8900,1， ''WC-ML''，''2005-08-30 00:18:26000''，

''2005-08-30 00：00：00.000''，608）
插入parSalesDetail（parSalesHdrID，parSalesDetailID，
数量，
itemprice，[after]，positem，
amounttendertime，BusinessDay，UnitNumber）值（5948325，
26143917,1， 4.8900,1，''WC-ML''，

''2005-08-30 00:18:26000''，
''2005-08-30 00:00： 00.000''，608）
插入parSalesDetail（parSalesHdrID，parSalesDetailID，
数量，itemprice，[after]，positem，
amounttendertime，BusinessDay，UnitNumber）值（5948325，
26143918,7,4.1900,7，''WB-ML''，

'''2005-08-3 0 00：18：26.000''，
''2005-08-30 00：00：00.000''，608）
插入parSalesDetail（parSalesHdrID，parSalesDetailID，
数量，itemprice， [after]，positem，
amounttendertime，BusinessDay，UnitNumber）值（5948325，
26143919,1,41900,0，''WB-ML''，

'' 2005-08-30 00：18：26.000''，
''2005-08-30 00：00：00.000''，608）
插入parSalesDetail（parSalesHdrID，parSalesDetailID，
数量，itemprice，[after]，positem，
amounttendertime，BusinessDay，UnitNumber）值（5948325，
26143920,7,4.1900,7，''WB-ML''，

''2005-08-30 00:18:26000''，
''2005-08-30 00：00：00.000''，608）
插入parSalesDetail（parSalesHdrID，parSalesDetailID，
数量，itemprice，[after]，positem，
amounttendertime，BusinessDay，UnitNumber）值（5948325，
26143921,4,4.1900,4，''WB-ML''，

''2005-08-30 00 ：18：26.000''，
''2005-08-30 00：00：00.000''，608）
插入parSalesDetail（parSalesHdrID，parSalesDetailID，
数量，itemprice，[after] ]，positem，
amounttendertime，BusinessDay，UnitNumber）值（5948325，
26143922,1,41900,1，''WB-ML''，

''2005- 08-30 00:18:26000''，
''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），
[Manag] erNumber] [int]，
[ManagerName] [nvarchar]（16），
[ReductionValue] [decimal]（18,4），
[OriginalQuantity] [int]，> [NewQuantity] [int]，
[ProductID] [nvarchar]（50），
[ProductName] [nvarchar]（50）

插入parReductionFile（UnitNumber
，ReductionType，RegisterNumber，CashierNumber，
CashierName，ReductionDate，ReductionTime，ReductionCode，ManagerNumber，
ManagerName，ReductionValue，OriginalQuantity，NewQua ntity，ProductID，ProductName）
价值观（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''）

插入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''）

插入parReductionFile（UnitNumber
，ReductionType，RegisterNumber，CashierNumber，经理名称，减少价值，原始数量，NewQua，产品ID，产品名称）
价值（608,2,3,7，'''拉里雪莉' '，'''8/30/2005''，''12：12：00
AM''，''A''，30，''ADRIANE PATTERSO''，32.13,7,0，'' WB-ML''，''WB-ML''）

插入parReductionFile（UnitNumber
，ReductionType，RegisterNumber，CashierNumber，
CashierName，ReductionDate，ReductionTime，ReductionC ode ，ManagerNumber，
ManagerName，ReductionValue，OriginalQuantity，NewQua ntity，ProductID，ProductName）
值（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''）

插入parReductionFile（UnitNumber
，ReductionType，RegisterNumber，CashierNumber，
CashierName，ReductionDate，ReductionTime，ReductionC ode，ManagerNumber，
ManagerName，ReductionValue，OriginalQuantity，NewQua ntity，ProductID，ProductName）
值（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''）

插入parReductionFile（UnitNumber
，ReductionType，RegisterNumber，CashierNumber，
CashierName，ReductionDate，ReductionTime，ReductionC ode，ManagerNumber，
ManagerName，ReductionValue，OriginalQuantity，NewQua ntity，ProductID，ProductName）
值（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单位，
h.registernumber REG，

d.itemprice * d.after TOTAL，
h.grossordertotal [ORDER TOTAL]，
dateddertime TENDTIME，
d.after ATD，
d.positem [POS ITEM]，
转换（nvarchar（12），r.reductiontime，108）作为ReductionTime，
r.ReductionType， r.RegisterNumber，
r.CashierNumber，
r.CashierName，
r.ManagerNumber，
r.ManagerName，
- r.ReductionValue， r.NewQuantity，
r.ProductName

来自parreductionfile r，parsaleshdr h，parsalesdetail d

其中h。 ''8/30/05''和''8/30/05''之间的营业日和
h.unitnumber = 608
和转换（nvarchar （12），r.reductiondate，101）+''''+
转换（nvarchar（12），r.reductiontime，108）
h.ordertotaltime和h.amounttendertime
h.parsaleshdrid = d.parsaleshdrid
和d.unitnumber = r.unitnumber
和d.positem = r.productname
和d.after 0
和d.after = r .originalquantity - r.newquantity
和d.quantity = r.originalquantity

>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

是的，我没想到我真的可以把它绑起来回来。我只是希望有人会看到我不是的东西。而且只是这样说，我是没有与退款相关的b $ b。这是完全不同的东西。这是

我们称之为After Total Delete。一旦有人下订单，

收银员将告诉客户总数。然后客户可能会改变他对他订购的商品数量的看法，或者取消所有商品中的一件。此时，公司政策是取消订单中的
并重新开始，因为订单已经合计。

相反，收银员正在使用 ;完全删除后功能到

删除订单中的项目，但是从

客户那里取钱之前。像泥一样清楚？问题是，收银员（或经理）可以使用此功能来赚钱。


谢谢！

Jennifer

Roy Harvey写道：

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:

我认为你无法可靠地把事情束缚住。大多数，或者至少很多b $ b很多，这样的退款将在人们开始花费一段时间后发生（尝试）吃掉，并且在最多这样的地方，寄存器需要花费更多

在您收到食物之前订购。我已收到退款

NEXT DAY当我在直通车上收到的订单完全错误时

错误。


也许当生意很慢的时候你可以用公平的机会把它绑回来

做一个准确的比赛，但我不会指望有任何值得使用的价值

。


Roy Harvey

Beacon Falls，CT


2006年8月9日07:31:57 -0700， J。********** @ gmail.com 写道：

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:

您好。我有一张桌子，我正试图绑在另外两张桌子上。

问题是3个表之间没有什么区别。是的，

我知道......但这是我必须要合作的。让我解释一下

我正在尝试用一点背景历史来做什么。


首先，我工作的快餐公司有在他们的商店注册。

我们每15分钟从寄存器中捕获TLD文件。在每日

的基础上，这些文件将被发送到总部，在那里它们被放入3个/ b
不同的表 - parSalesHdr，parSalesDetail和
parSalesDetailModifier。标题表有寄存器编号，订单wazs招标的时间是

，谁是收银员，总订单总额，

等详细记录有详细信息订单 - 订购了什么是
，物品的价格，折扣的价格，什么时候打折

打折等等。修饰表有以下内容的修饰符br />
详细信息表 - 没有pickes，添加洋葱等等。好的，现在

注册公司增加了新文件被选中的可能性

如果我们想要的话。这是减少文件。此订单包含

信息，其中订单已从订单中删除订单后，订单总计为
。这是一件坏事 - 它允许盗窃。我们想要

来使用r eduction文件来找出这是谁。从订单中删除

需要经理在登记册上刷卡，以便允许删除。减少文件包含 - 谁刷了他们的

卡，项目的费用，项目的费用。


基本上我想做的就是领带还原文件中的内容是

详细信息表和标题表。详细信息和标题表

不同的日期/时间戳，但它们都没有与减少文件中的日期/时间戳

相匹配。标题文件有第一项

的时间和现金投标的时间。现金提交时，详细信息表格为
。减少文件只有时间

刷卡经理卡。我唯一能看到的就是尝试将
与减少时间相匹配，在第一个订单时间和现金投标时间之间。然后我可以匹配从订单中删除的项目

。详细信息表有一个名为[after]的字段，

表示一个项目也被删除。 [after]字段将在订购后从订单中删除

数量的商品。

所以我也可以使用它。当出售相同物品有很多

时出现问题。有些已被删除 - 有些没有删除。

没有真正的方法来匹配这些。


这是我的问题 - 是否有一些什么把减少表与

绑在一起我看不到的细节和标题表？我有桌子

创作/插入和我运行的查询将它们绑在一起

以下。


谢谢，

Jennifer

创建表parSalesHdr

（parSalesHdrID bigint，unitnumber int，registernumber int，

posemployeenumber int， posemployeename nvarchar（30），

grossordertotal money，ordertotaltime datetime，

amounttendertime datetime，BusinessDay DateTime）


插入parSalesHdr（ parSalesHdrID，unitnumber，registernumber，

posemployeenumber，posemployeename，grossordertotal，ordertotaltime，

amounttendertime，BusinessDay）值（5948325,608,3,7，

''Larry''，6.11，''8/30/05 12:11:06 am''，''8/30/05 12:18:26 am''，''8/30/05' '）


创建表parSalesDetail

（parSalesHdrID bigint，parSalesDetailID bigint，数量int，

itemprice money，[after] int ，positem nvarchar（20），

amounttendertime datetime，BusinessDay smalldatetime，UnitNumber int）

插入parSalesDetail（parSalesHdrID，parSalesDetailID，

数量，

itemprice，[after]， positem，
amounttendertime，BusinessDay，UnitNumber）值（5948325，

26143916,1,4.8900,1，''WC-ML''，''2005-08- 30 00：18：26.000''，


''2005-08-30 00：00：00.000''，608）

插入parSalesDetail（ parSalesHdrID，parSalesDetailID，

数量，

amounttendertime，BusinessDay，UnitNumber）值（5948325，

26143917,1,4.8900,1，''WC-ML''，

''2005-08-30 00:18:26000''，

''2005-08-30 00：00：00.000''，608）

插入parSalesDetail（parSalesHdrID，parSalesDetailID，

数量，itemprice，[after ]，positem，

amounttendertime，BusinessDay，UnitNumber）值（5948325，

26143918,7,4.1900,7，''WB-ML''，

'' 2005-08-30 00:18:26000''，

''2005-08-30 00：00：00.000''，608）

插入parSalesDetail（ parSalesHdrID，parSalesDetailID，

数量，itemprice，[after]，positem，

amounttendertime，BusinessDay，UnitNumber）值（5948325，

26143919， 1,4A9,0，''WB-ML''，

''2005-08-30 00：18：26.000''，

''2005-08 -30 00：00：00.000''，608）

插入parSalesDetail（parSalesHdrID，parSalesDetailID，

数量，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] (

\t[UnitNumber] [int] ,

\t[ReductionType] [int] ,

\t[RegisterNumber] [int] ,

\t[CashierNumber] [int] ,

\t[CashierName] [nvarchar] (16) ,

\t[ReductionDate] [datetime] ,

\t[ReductionTime] [datetime] ,

\t[ReductionCode] [char] (1) ,

\t[ManagerNumber] [int] ,

\t[ManagerName] [nvarchar] (16) ,

\t[ReductionValue] [decimal](18, 4) ,

\t[OriginalQuantity] [int] ,

\t[NewQuantity] [int] ,

\t[ProductID] [nvarchar] (50) ,

\t[ProductName] [nvarchar] (50)

)


insert into parReductionFile (UnitNumber

,ReductionType,RegisterNumber,CashierNumber,

\tCashierName,ReductionDate,ReductionTime,ReductionC ode,ManagerNumber,

\tManagerName,ReductionValue,OriginalQuantity,NewQua ntity,ProductID,ProductName)

values\t(608,\t2,\t3,\t7,\t’’Larry S hirley’’, \t’’8/30/2005’’,\t’’12:12:00

AM’’,\t’’A’’,\t30,\t’’ADRIANE PATTERSO’’,\t4.59,\t1,\t0,\t’’WB-ML’’, \t’’WB-ML’’)

insert into parReductionFile (UnitNumber

,ReductionType,RegisterNumber,CashierNumber,

\tCashierName,ReductionDate,ReductionTime,ReductionC ode,ManagerNumber,

\tManagerName,ReductionValue,OriginalQuantity,NewQua ntity,ProductID,ProductName)

values\t(608,\t2,\t3,\t7,\t’’Larry Shirley’’, \t’’8/30/2005’’,\t’’12:12:00

AM’’,\t’’A’’,\t30,\t’’ADRIANE PATTERSO’’,\t18.36,\t4,\t0,\t’’WB-ML’’, \t’’WB-ML’’)

insert into parReductionFile (UnitNumber

,ReductionType,RegisterNumber,CashierNumber,

\tCashierName,ReductionDate,ReductionTime,ReductionC ode,ManagerNumber,

\tManagerName,ReductionValue,OriginalQuantity,NewQua ntity,ProductID,ProductName)

values\t(608,\t2,\t3,\t7,\t’’Larry Shirley’’, \t’’8/30/2005’’,\t’’12:12:00

AM’’,\t’’A’’,\t30,\t’’ADRIANE PATTERSO’’,\t32.13,\t7,\t0,\t’’WB-ML’’, \t’’WB-ML’’)

insert into parReductionFile (UnitNumber

,ReductionType,RegisterNumber,CashierNumber,

\tCashierName,ReductionDate,ReductionTime,ReductionC ode,ManagerNumber,

\tManagerName,ReductionValue,OriginalQuantity,NewQua ntity,ProductID,ProductName)

values\t(608,\t2,\t3,\t7,\t’’Larry Shirley’’, \t’’8/30/2005’’,\t’’12:12:00

AM’’,\t’’A’’,\t30,\t’’ADRIANE PATTERSO’’,\t32.13,\t7,\t0,\t’’WB-ML’’, \t’’WB-ML’’)

insert into parReductionFile (UnitNumber

,ReductionType,RegisterNumber,CashierNumber,

\tCashierName,ReductionDate,ReductionTime,ReductionC ode,ManagerNumber,

\tManagerName,ReductionValue,OriginalQuantity,NewQua ntity,ProductID,ProductName)

values\t(608,\t2,\t3,\t7,\t’’Larry Shirley’’, \t’’8/30/2005’’,\t’’12:12:00

AM’’,\t’’A’’,\t30,\t’’ADRIANE PATTERSO’’,\t4.89,\t1,\t0,\t’’WC-ML’’, \t’’WC-ML’’)

insert into parReductionFile (UnitNumber

,ReductionType,RegisterNumber,CashierNumber,

\tCashierName,ReductionDate,ReductionTime,ReductionC ode,ManagerNumber,

\tManagerName,ReductionValue,OriginalQuantity,NewQua ntity,ProductID,ProductName)

values\t(608,\t2,\t3,\t7,\t’’Larry Shirley’’, \t’’8/30/2005’’,\t’’12:13:00

AM’’,\t’’A’’,\t30,\t’’ADRIANE PATTERSO’’,\t4.89,\t1,\t0,\t’’WC-ML’’, \t’’WC-ML’’)



SELECT

\th.unitnumber\t\t\tUNIT,

\th.registernumber \t\tREG,


\th.posemployeenumber \t\tEENUM,

\th.posemployeename\t\tEMPNAME,

\td.itemprice * d.after\t\tTOTAL,

\th.grossordertotal\t\t[ORDER TOTAL],
\th.amounttendertime\t\tTENDTIME,

\td.after\t\t\t\tATD,

\td.positem\t\t\t[POS ITEM],

\tconvert(nvarchar(12),r.reductiondate,101) + ’’ ’’ +

convert(nvarchar(12),r.reductiontime,108) as ReductionTime,

\tr.ReductionType,

\tr.RegisterNumber,

\tr.CashierNumber,

\tr.CashierName,

\tr.ManagerNumber,

\tr.ManagerName,

\t--r.ReductionValue,

\tr.OriginalQuantity,

\tr.NewQuantity,

\tr.ProductName


from parreductionfile r, parsaleshdr h, parsalesdetail d


where \th.businessday between ’’8/30/05’’ and ’’8/30/05’’ \tand

\th.unitnumber = 608

\tand convert(nvarchar(12),r.reductiondate,101) + ’’ ’’ +

convert(nvarchar(12),r.reductiontime,108) between

\t\th.ordertotaltime and h.amounttendertime

\tand h.parsaleshdrid = d.parsaleshdrid

\tand d.unit number = r.unitnumber

\tand d.positem = r.productname

\tand d.after 0

\tand d.after = r.originalquantity - r.newquantity

\tand d.quantity = r.originalquantity

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

On Wed, 09 Aug 2006 17:07:13 GMT, Roy Harvey <ro********@snet.net>

wrote:

On Wed, 09 Aug 2006 17:07:13 GMT, Roy Harvey <ro********@snet.net>
wrote:

\t
\t\t\t\t>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.

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

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.