多天之间的Datediff并在asp.net Sql查询中找到Minutes [英] Datediff between multiple days and find Minutes in asp.net Sql query

问题描述

我有一个日期时间表是一列....我创建一个酒店应用程序，现在我需要，如果用户订购1项。

I have one table with datetime is a one column....i create a hotel application ,Now i need,if user order 1 item.

orderid   tableid   Timetaken(Mins)   itemname        datetime  
   1        1           10              Pizza    2013-06-20 05:30:33.343 PM  

然后在5分钟后，再添加一个item。

then after 5 minutes ,i add another One item.

orderid   tableid   Timetaken(Mins)   itemname        datetime  
   1        1           10              Burger    2013-06-20 05:35:33.343 PM

现在，我发现两个项目之间的时差...使用Sql Query ... Timedifference = 5mins，timetodelivery是15mins（减少5分钟，因为这是等待时间）....

之后，3分钟我添加另一个项目

Now,i find the timedifference between two item....using Sql Query...Timedifference=5mins and timetodelivery is 15mins(Reduce the 5 mins because this is waiting time)....
After,3 mins i add another one item

orderid   tableid   Timetaken(Mins)   itemname        datetime  
   1        1           10              Burger    2013-06-20 05:38:33.343 PM

现在，等待时间是3分钟....

总时间= 30分钟

TimeDiffernece = {item1 to item2是5分钟，第2项到第3项是3分钟} ==> 8分钟

现在，timetodelivery = 30mins-8mins = 22mins

如何找出三个项目之间的时差。 .....请提前谢谢...

Now,the waiting time is 3mins....
Totaltime=30mins
TimeDiffernece={item1 to item2 is 5mins and item2 to item3 is 3mins}==>8mins
Now,the timetodelivery=30mins-8mins=22mins
How i find out the Timedifference between three items......Please Advance thanks...

推荐答案

创建测试d ata

create table orders (orderid int,tableid int,orderpart int,timetaken int,itemname varchar(50),datetimeentered datetime);
insert into orders values (1,1,1,10,'Pizza','2013-06-20 05:30:33:343 PM');
insert into orders values (1,1,1,10,'Burger','2013-06-20 05:35:33:343 PM');
insert into orders values (1,1,1,10,'Burger','2013-06-20 05:38:33:343 PM');
insert into orders values (1,1,2,20,'Dhosa','2013-06-20 06:03:33:343 PM');
insert into orders values (1,1,2,10,'Juize','2013-06-20 06:03:33:343 PM');

运行查询

select datediff(minute,
    min(datetimeentered),
    max(datetimeentered)
    ) As TimeDifferenceInMinutes from orders t1 where orderid=1 and tableid=1 and orderpart=1
 
select datediff(minute,
    min(datetimeentered),
    max(datetimeentered) 
    ) As TimeDifferenceInMinutes from orders t1 where orderid=1 and tableid=1 and orderpart=2

结果

Results

TimeDifferenceInMinutes
8
TimeDifferenceInMinutes
0

这篇关于多天之间的Datediff并在asp.net Sql查询中找到Minutes的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！