问题描述
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
表名: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..
推荐答案
如果你能得到一个带有列的表格:
If you could get a table with columns:
- 或否
- 当前日期
- 上一个日期
- CurrAmount
- 上一个金额
那么解决您的问题将是微不足道的.给定 AnonymousTable,这个查询生成数据:
Then solving your problem would be trivial. Given the AnonymousTable, this query generates the data:
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
这里的关键技术是 UNION 查询和自联接.您的数据具有一致的日期和时间线程化",因此当前日期和上一个日期列之间的比较是微不足道的.
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.