SQL -操作小时数

我很难理解一个看起来有点简单的问题。假设我有一家公司，营业时间是每天中午12点到凌晨3点。每个顾客每天都会根据他们当天的首次购买获得一次奖励。那么，假设他们在当天的第一笔交易中花费了20美元——他们可能会在这笔交易中获得20%的折扣，这就是当天的交易。

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

我们使用VB6前端为我们的POS，与SQL Server 2008 R2数据库。每次应用奖励时，都会在数据库端对其进行审计，因此我可以轻松地查询上一次应用奖励的时间。

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

我不确定您想要应用业务逻辑的哪一边(VB或SQL)，但在任何情况下，过程都应该是相同的:您需要使用两个属性持久化每个客户的日常操作时间:

时间(一天中营业的时间)
时间跨度(运行小时数)

然后检查事务的时间是否在Time和Time + TimeSpan之间，以计算业务逻辑和客户的奖金。这两个计算在VB和SQL中都相当简单。您只需要确保在逻辑上持久化数据并一致地使用它。

我想你的答案会更清楚，如果你把它修改成这样:

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语句来解决这个问题。如果我的孩子没有耳朵感染，我自己也会试试的。

如何:

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

其中lastBonusTime为最后一次奖金交易的时间

您可以用稍微不同的方式来看待这个问题。如果客户现在在这里(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()

我还会考虑数据的具体情况——上面的根本没有针对性能进行调优。我只是想解释一下我的思考过程。

为了区分您的关注点，我将添加一个新表，如CUSTOMER_BONUS，其中包含以下列:

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

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

我想出了一个我满意的答案，但它有点笨拙，如果有一个更优雅的答案，我将非常乐意接受。此外，由于时间已经很晚了，我还没有彻底测试过这个问题，但如果我的逻辑有缺陷，我会很高兴地修改或接受有修改的答案。

基本上，我要确定一周中的哪一天是工作日，即四小时前开始的那一天。这意味着一直到凌晨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点左右，当简单地减去四个小时将重叠到上一个工作日，但减去更少的时间将不足以达到上一个工作日)。因此，它将暂时工作，但我希望看到一个更好的解决方案。

相关内容

最新更新

热门标签：