减去两行相同的表并总结差异

OrNo    CurrentDate         PreviousDate        Finished    Amount
G988    02.05.2013 14:00:47 NULL                False       1560
G988    02.05.2013 21:30:00 02.05.2013 14:00:47 False       3170
G988    03.05.2013 06:00:00 02.05.2013 21:30:00 False       5095
G988    03.05.2013 07:46:24 03.05.2013 06:00:00 True        5254

Table Name: oldDate

表名:oldDate

I have this data, and I have to calculate the total amount on a single day but I need to also subtract the previous day's amount, so that only the amount which was produced today (current date) is calculated.

我有这些数据,我必须计算一天的总金额,但我还需要减去前一天的金额,以便只计算今天(当前日期)产生的金额。

Current Date is the real date on which the order is processed and previous date is the last day's date on which this order was processed. Point me out if m not clear in explanation of the data, I tried ..

当前日期是订单处理的实际日期,上一个日期是处理此订单的最后一天。如果我不清楚数据的解释,请指出我,我试过..

if t2.CurrentDate = t1.PreviousDate and datepart(t2.CurrentDate)= datepart(t1.CurrentDate) 

then
     if t1.CurrentDate>t2.CurrentDate

        then  @amount = t1.Amount

     else @amount = t2.Amount

I'm bad at dealing with joins .. :( so I have problems with this logic, I tried some other code from other examples but was not successful, any ideas will be really appreciated..

我很难处理连接.. :(所以我有这个逻辑的问题,我尝试了其他示例的其他代码,但没有成功,任何想法将真的很感激..

1 个解决方案

#1

If you could get a table with columns:

如果你能得到一个包含列的表:

OrNo
CurrDate
PrevDate
CurrAmount
PrevAmount

Then solving your problem would be trivial. Given the AnonymousTable, this query generates the data:

然后解决你的问题将是微不足道的。给定AnonymousTable,此查询生成数据:

SELECT a.OrNo,
       a.CurrentDate AS CurrDate, a.PreviousDate AS PrevDate,
       a.Amount AS CurrAmount,    b.Amount AS PrevAmount
  FROM AnonymousTable AS a
  JOIN AnonymousTable AS b
    ON a.OrNo = b.OrNo AND a.PrevDate = b.CurrDate
UNION
SELECT a.OrNo,
       a.CurrentDate AS CurrDate, a.PreviousDate AS PrevDate,
       a.Amount AS CurrAmount,    0 AS PrevAmount
  FROM AnonymousTable AS a
 WHERE a.PreviousDate IS NULL

So, presumably, you could write:

所以,大概你可以写:

SELECT OrNo, CurrDate, CurrAmount - PrevAmount AS NewAmount
  FROM (SELECT a.OrNo,
               a.CurrentDate AS CurrDate, a.PreviousDate AS PrevDate,
               a.Amount AS CurrAmount,    b.Amount AS PrevAmount
          FROM AnonymousTable AS a
          JOIN AnonymousTable AS b
            ON a.OrNo = b.OrNo AND a.PrevDate = b.CurrDate
        UNION
        SELECT a.OrNo,
               a.CurrentDate AS CurrDate, a.PreviousDate AS PrevDate,
               a.Amount AS CurrAmount,    0 AS PrevAmount
          FROM AnonymousTable AS a
         WHERE a.PreviousDate IS NULL
       )

Equally clearly, if you put your mind to it, you can simplify things by writing:

同样清楚的是,如果你把它放在心上,你可以通过写下来简化:

SELECT a.OrNo,
       a.CurrentDate AS CurrDate, a.PreviousDate AS PrevDate,
       a.Amount - b.Amount AS NewAmount
  FROM AnonymousTable AS a
  JOIN AnonymousTable AS b
    ON a.OrNo = b.OrNo AND a.PrevDate = b.CurrDate
UNION
SELECT a.OrNo,
       a.CurrentDate AS CurrDate, a.PreviousDate AS PrevDate,
       a.Amount AS NewAmount
  FROM AnonymousTable AS a
 WHERE a.PreviousDate IS NULL

The key techniques here are the UNION query and the self-join. Your data has consistent 'threading' of the dates and times, so the comparison between the current date and previous date columns is trivial.

这里的关键技术是UNION查询和自联接。您的数据具有一致的日期和时间“线程”,因此当前日期和上一个日期列之间的比较是微不足道的。

#1