Find the TimeTaken in Ordered ITems..

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
Then,I find the time difference,,,But after 22 mins completed and extra 3 mins completed ...then i add another two item

C#

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

Posted 21-Jun-13 1:10am

sarathtamil

Add a Solution

Comments

gvprabu 21-Jun-13 7:30am

in your table u have any ID column.... or base on time only we have to list all the ordered Items.

sarathtamil 21-Jun-13 7:43am

table have orderid and tableid only...

Mike Meinz 21-Jun-13 13:04pm

This is the same question that you already posted at Datediff between multiple days and find Minutes in asp.net Sql query.

You are still not explaining how to make the decision to treat the last two items differently than the first three items. In other words, how to select two sets of rows: one set for the first group of three items and and the other set consisting of the last two items.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

andyharman · Answer 1 · 2013-06-21T09:19:00

I created this table for the test data (I recommend adding a primary key):

CREATE TABLE OrderItem (
	OrderId INT NOT NULL,
	TableId INT NOT NULL,
	TimeTaken INT NOT NULL,
	ItemName VARCHAR(20) NOT NULL,
	OrderTime DATETIME NOT NULL
	);
GO

INSERT INTO OrderItem VALUES (1, 2, 10, 'Pizza', '2013-06-20 05:30:33.343 PM');
INSERT INTO OrderItem VALUES (1, 2, 10, 'Burger', '2013-06-20 05:35:33.343 PM');
INSERT INTO OrderItem VALUES (1, 2, 10, 'Burger', '2013-06-20 05:38:33.343 PM');
INSERT INTO OrderItem VALUES (1, 2, 20, 'Dhosa', '2013-06-20 06:03:33.343 PM');
INSERT INTO OrderItem VALUES (1, 2, 10, 'Juize', '2013-06-20 06:03:33.343 PM');
GO

Your question was a bit unclear - but I think it is based on separating the order-items that don't overlap. To work out the separated orders I did this:

SELECT DISTINCT OrderId, TableId, OrderTime AS EarliestOrderTime
FROM OrderItem AS T1
WHERE NOT EXISTS (
	SELECT 1 FROM OrderItem AS T2
	WHERE T2.OrderId = T1.OrderId
	AND T2.TableId = T1.TableId
	AND T2.OrderTime < T1.OrderTime
	AND DateAdd(mi, T2.TimeTaken, T2.OrderTime) >= T1.OrderTime)

This gives 2 rows - one for 17:30:33.343 and the other for 18:03:33.343.

I converted that SQL-Select into a CTE so that I can reference it twice so that I can join it to the associated order lines:

WITH SeparatedOrders_CTE (OrderId, TableId, EarliestOrderTime)
AS
(
	SELECT DISTINCT OrderId, TableId, OrderTime AS EarliestOrderTime
	FROM OrderItem AS T1
	WHERE NOT EXISTS (
		SELECT 1 FROM OrderItem AS T2
		WHERE T2.OrderId = T1.OrderId
		AND T2.TableId = T1.TableId
		AND T2.OrderTime < T1.OrderTime
		AND DateAdd(mi, T2.TimeTaken, T2.OrderTime) >= T1.OrderTime)
)
SELECT SO1.OrderId, SO1.TableId, SO1.EarliestOrderTime,
	SUM(T1.TimeTaken) AS TotalTimeTaken,
	DateDiff(mi, SO1.EarliestOrderTime, MAX(T1.OrderTime)) AS WaitingTime,
	(SUM(T1.TimeTaken) - DateDiff(mi, SO1.EarliestOrderTime, MAX(T1.OrderTime))) AS TimeToDelivery
FROM SeparatedOrders_CTE as SO1
INNER JOIN OrderItem AS T1
ON T1.OrderId = SO1.OrderId
AND T1.TableId = SO1.TableId
AND T1.OrderTime >= SO1.EarliestOrderTime
WHERE NOT EXISTS (
	SELECT 1 FROM SeparatedOrders_CTE AS SO2
	WHERE SO2.OrderId = SO1.OrderId
	AND SO2.TableId = SO1.TableId
	AND SO2.EarliestOrderTime > SO1.EarliestOrderTime
	AND SO2.EarliestOrderTime <= T1.OrderTime)
GROUP BY SO1.OrderId, SO1.TableId, SO1.EarliestOrderTime
ORDER BY SO1.OrderId, SO1.TableId, SO1.EarliestOrderTime

Which gives:

CSS

OrderId  TableId  EarliestOrderTime       TotalTimeTaken WaitingTime TimeToDelivery
-------- -------- ----------------------- -------------- ----------- --------------
1        2        2013-06-20 17:30:33.343 30             8           22
1        2        2013-06-20 18:03:33.343 30             0           30

Your question does say what would happen if customers move between tables during the meal. I would expect that TableId should in-fact be moved to a parent table (keyed by OrderId).

Alternative methods might be to use SQL Server's windowing functions[^] or a cursor.

I hope that helps.

Regards
Andy