SQL - 营业时间

Question

我很难理解一个看似简单的问题。假设我有一家公司，每天的营业时间为中午 12 点至凌晨 3 点。每个客户每天都会根据当天的首次购买获得一次奖金。因此，假设他们当天的第一笔交易花费了 20 美元——他们可能会在该交易中获得 20% 的折扣，仅此而已。

我正在尝试找出最准确的方法来检查上次发放的奖金，并确保客户有资格获得奖金。显然，我无法进行简单的 24 小时检查，因为例如，如果客户在周一晚上 11 点进来，然后在周二中午再次进来，他们将不会获得第二笔奖金。

我们的 POS 使用 VB6 前端，并使用 SQL Server 2008 R2 数据库。每次应用奖金时，都会在数据库端进行审核，因此我可以轻松查询上次应用奖金的时间。

编辑：我应该注意，由于各种原因，解决方案不能包括对数据库结构进行任何更改。

Answer 1

我不确定您想要在哪一方（VB 或 SQL）应用业务逻辑，但在任何一种情况下，过程都应该是相同的：您需要使用两个属性来保存每个客户的每日操作时间：

• 时间（时间） 然后
• TimeSpan（营业时间）

，您检查交易时间是否在 Time 和 Time + TimeSpan 之间，以计算您的业务逻辑和客户的奖金。这两种计算在 VB 和 SQL 中都相当简单。您只需要确保以逻辑方式保留数据并一致地使用它。

Answer 2

我认为如果您将其修改为以下内容，您的答案会更清晰：

IF @LastBonus BETWEEN @store_open AND @store_close
   BEGIN
     SET @BonusDue = 0
   END
ELSE
   BEGIN
     SET @BonusDue = 1
   END

您根据添加到最后奖金的日期部分的固定时间来计算商店的营业和关闭日期。像

Set @openTime = '12:00'
Convert(date, @LastBonus) + @openTime

然后添加时间跨度（如建议的那样）以获得关闭时间。这可能有点棘手，因为如果是在午夜之后，则需要将开放时间添加到前一个日期，但您可能可以使用 CASE 语句来解决此问题。如果我的宝宝没有耳朵感染，我会自己尝试一下。希望这对您有用。

Answer 3

怎么样：

IF (DATEPART(dayofyear, [lastBonusTime]) <> DATEPART(dayofyear, GetDate())) ...

lastBonusTime 是最后一次奖金交易的时间？

Answer 4

你可以稍微不同地看待这个问题。如果客户现在在这里 (GETDATE())，距上次购买是否已超过 24 小时？

所以，

SELECT *
 FROM Customers c
   INNER JOIN CustomerVisits cv
     ON c.CustomerId=cv.CustomerId
   INNER JOIN CustomerBonus cb
     ON cv.VisitId=cb.VisitId
  WHERE c.CustomerId=@CustomerId
       AND LastVisitDt BETWEEN 
           ( 
              DATEADD(hh,12,convert(DATE, LastVisitDt))
           )
           AND
           ( 
              DATEADD(hh,27,convert(DATE, LastVisitDt))
           )               
       AND DATEADD(hh,24,LastVisitDT)<=GETDATE()

我也会考虑数据的细节——上面的内容根本没有针对性能进行调整。我只是想解释一下我的思维过程。

Answer 5

为了区分您的担忧，我将添加一个新表，例如 CUSTOMER_BONUS，其中包含以下列：

BonusStart datetime
BonusEnd datetime
CustomerID int/uniqueidentifier/whatever
TransactionID int/whatever (points to what qualified for the bonus)

当您为客户申请一天的奖金时，请在该表中写入一条新记录它适用的时期。此表中存在记录表示客户没有资格在 BonusStart 和 BonusEnd 之间获得其他奖金。当您创建新销售时，请查看此表。如果记录存在，则没有奖金，但如果不存在，则应用奖金并在此处创建新记录。

Answer 6

我想出了一个令我满意的答案，但它有点笨拙，如果提供一个更优雅的答案，我将非常乐意接受。另外，我还没有彻底测试过这个，因为已经很晚了，但是如果我的逻辑有缺陷，我会很乐意修改或接受修改后的答案。

基本上，我只是要确定以工作日计算的一周中的哪一天是四小时前开始的任何一天。这意味着，一直到凌晨 3:59，“今天”将被视为前一天，这对于这些运营时间来说是正确的（我超出了凌晨 3 点的关闭时间，以考虑到网站决定保持开放状态）稍后）。然后，我使用相同的规则将此时间跨度与最近一次向该客户的帐户应用奖金的时间进行比较。如果两者匹配，则奖金已在本工作日应用。如果不同，则说明没有，并且客户符合资格。

DECLARE @CustID AS int
DECLARE @LastBonus AS date
DECLARE @BonusDue AS bit

SET @LastBonus = (SELECT TOP 1 [DateTime] FROM Audit WHERE CustomerID = @CustID AND TransactionType = 'BONUS' ORDER BY [DateTime] DESC)

IF (SELECT DATEADD(hh, -4, CURRENT_TIMESTAMP)) <>
   (SELECT DATEADD(hh, -4, @LastBonus))
   BEGIN
      SET @BonusDue = 1
   END
ELSE
   BEGIN
      SET @BonusDue = 0
   END

如果我将其放入存储过程中，我可以简单地向其中放入一个客户 ID，并让它吐出一点，如果客户符合条件，则显示 1，否则显示 0。我不喜欢的是，如果客户的营业时间提前了很多，我就会陷入困境（我猜大约是上午 7:00，此时简单地减去四个小时就会与前一个工作日重叠，但减去较少的金额不足以达到前一个工作日）。所以它暂时有效，但我很想看到更好的解决方案。