Datediff between multiple days and find Minutes in asp.net Sql query

Question

0.00/5 (No votes)

See more:

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

C#

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

then after 5 minutes ,i add another One item.

C#

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

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

C#

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

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...

Posted 20-Jun-13 2:20am

sarathtamil

Add a Solution

1 solution

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Mike Meinz · Accepted Answer · 2013-06-20T03:02:00

Solution 1

Create test data

SQL

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');

Run the queries

SQL

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

Posted 20-Jun-13 3:02am

Mike Meinz

Updated 21-Jun-13 7:22am

v7

Comments

sarathtamil 20-Jun-13 9:22am

Thank u sir....

sarathtamil 21-Jun-13 7:18am

Then,I find the time difference,,,But after 22 mins completed and extra 3 mins completed ...then i add another two item

orderid tableid Timetaken(Mins) itemname datetime
1 1 20 Dhosa 2013-06-20 06:03:33.343 PM
1 1 10 Juize 2013-06-20 06:03:33.343 PM
How to find the Current time taken in two items(30mins)??Advance thanks.....

Mike Meinz 21-Jun-13 7:44am

I updated Solution 1. I used the same query. If this is not correct, then I do not understand your business requirement and you will have to do a more thorough job of explaining exactly how the time difference should be calculated.

sarathtamil 21-Jun-13 7:47am

i need a time to delivery that items....

Mike Meinz 21-Jun-13 7:52am

You will have to do a better job of describing exactly what you want. You are assuming I understand your terms. When you use terms like "time to delivery", you need to specify the algorithm in words that describe how "time to delivery" is computed. If you are unable to do that, then you need to ask the person that gave you this assignment to do that.

sarathtamil 21-Jun-13 8:03am

if 30mins deliverytime in first threee items....after 35mins i order another 2item..now,i need these 2items time only..Because i use a time counter for deliverytime of that items.

sarathtamil 21-Jun-13 7:49am

if 30mins deliverytime in first threee items....after 35mins i order another 2item..now,i need these 2items time only..Because i use a time counter for deliverytime of that items.

Mike Meinz 21-Jun-13 8:23am

I still do not completely understand the algorithm to compute the required times. It seems to me that the second group of items should be a new order or perhaps there should be a OrderId-Part column that indicates part 1 of the order for the first three items and part 2 of the order for the last two items.

A good "rule of thumb" is that when the algorithm is so complicated that you can't adequately explain it in a few words, then maybe the algortihm needs to be simplifed. In this case, the data is key to the algrotihm. Simplifying the data to support the required algorithm would make your life a lot easier.

sarathtamil 21-Jun-13 8:40am

thanks for response,

first order some items it returns the time properly then again order from same table and same order id how can i find second order time only

example : for first order takes 30 min for 5 items then again order 1 item how to find that item time to complete its same table and same order id

Mike Meinz 21-Jun-13 8:49am

How to exclude rows that should not be considered (the first five items) in computing the time for the sixth item?

I think you need another column in your database table. A column like orderpart int. For each order, it starts at 1 and when a subsequent addition is made to the order that requires a separate calculation of time difference between the first item of the new items and the last item of the new items, the orderpart is incremented by 1.

orderid tableid OrderPart Timetaken(Mins) itemname datetime
1 1 2 20 Dhosa 2013-06-20 06:03:33.343 PM
1 1 2 10 Juize 2013-06-20 06:03:33.343 PM

I have revised Solution 1 again.

sarathtamil 26-Jun-13 8:37am

k sir...thank..

Datediff between multiple days and find Minutes in asp.net Sql query

1 solution

Solution 1

Add your solution here

Preview 0

Datediff between multiple days and find Minutes in asp.net Sql query

1 solution

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us