?
Solved

Subtracting two datetime values keeps failing.

Posted on 2005-04-08
8
Medium Priority
?
281 Views
Last Modified: 2012-06-27
I'm merely trying to determine the time it took to receive an order acknowledgement.  So, you get the ordertime of an order that comes in, then subtract that time from the ordertime of the subsequent acknowledgement -- but I keep erroring out with Server: Msg 512, Level 16, State 1, Line 7
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

my code is below.  i've changed several times.  right now i'm just trying to get the calculation going.  understand, all orders are in the same table.  each order has an ordernumber.  and the acknowledgement for that order can be associated with it via it's originalordernumber.

I'm nearly there, I'm just hoping somebody can help me get around this msg 512.  Please advise.

declare @odate datetime
declare @adate datetime

SET @odate = (select orderdate from orders where msgtype = 'o' and orderdate >= left(getdate()-0, 11) and endpoint = 'aaa')
SET @adate = (select orderdate from orders where msgtype = 'a' and orderdate >= left(getdate()-0, 11) and endpoint = 'aaa')

SELECT CAST(DATEDIFF(SECOND, @odate, @adate) / 86400 AS VARCHAR(2)) + ' Ack Time, '
+ CONVERT(CHAR(8), DATEADD(SECOND, DATEDIFF(SECOND, @odate, @adate) % 86400, '1900-01-01'), 8)
0
Comment
Question by:RLLewis
  • 3
  • 3
  • 2
8 Comments
 
LVL 11

Expert Comment

by:ram2098
ID: 13737732
Looks like the problem is with the duplicate rows....


Do it as below...

declare @odate datetime
declare @adate datetime

SET @odate = (select top 1 orderdate from orders where msgtype = 'o' and orderdate >= left(getdate()-0, 11) and endpoint = 'aaa')
SET @adate = (select top 1 orderdate from orders where msgtype = 'a' and orderdate >= left(getdate()-0, 11) and endpoint = 'aaa')

SELECT CAST(DATEDIFF(SECOND, @odate, @adate) / 86400 AS VARCHAR(2)) + ' Ack Time, '
+ CONVERT(CHAR(8), DATEADD(SECOND, DATEDIFF(SECOND, @odate, @adate) % 86400, '1900-01-01'), 8)

0
 
LVL 11

Expert Comment

by:ram2098
ID: 13737753
Run this query separately and see what are the values it is returning, if it is returning more than one value, decide which one you want and do an "order by" based on that.



select orderdate from orders where msgtype = 'o' and orderdate >= left(getdate()-0, 11)
0
 
LVL 11

Expert Comment

by:ram2098
ID: 13737776
Repeat the same for second query too...

select orderdate from orders where msgtype = 'a' and orderdate >= left(getdate()-0, 11) and endpoint = 'aaa'
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 1

Author Comment

by:RLLewis
ID: 13737878
Interesting.  Yes, it is because of duplicates.  Your version runs fine.  But see, if I account for the individual order and it's acknowledgement, there are nolonger any duplicates.  See, w/this relationship between the two --  the first being the order, the second being the acknowledgement --

     order.currentordernumber = order.originalordernumber

so, what's the best way to related the acknowledgement back to it's order?
0
 
LVL 28

Expert Comment

by:rafrancisco
ID: 13749507
Try this one:

declare @ordernumber int
declare @odate datetime
declare @adate datetime

SELECT TOP 1 @ordernumber = currentordernumber, @odate = orderdate
FROM Orders
WHERE msgtype = 'o' and orderdate >= left(getdate()-0, 11) and endpoint = 'aaa'
SELECT TOP 1 @adate = orderdate
FROM Orders
WHERE msgtype = 'a' and orderdate >= left(getdate()-0, 11) and endpoint = 'aaa' and
originalordernumber = @ordernumber

SELECT CAST(DATEDIFF(SECOND, @odate, @adate) / 86400 AS VARCHAR(2)) + ' Ack Time, '
+ CONVERT(CHAR(8), DATEADD(SECOND, DATEDIFF(SECOND, @odate, @adate) % 86400, '1900-01-01'), 8)

Hope this answers your question.
0
 
LVL 1

Author Comment

by:RLLewis
ID: 13762100
Server: Msg 245, Level 16, State 1, Line 8
Syntax error converting the varchar value '690021663:0' to a column of data type int.

both currentorderno and origorderno are varchar.  what's the best way to convert on this?
0
 
LVL 28

Accepted Solution

by:
rafrancisco earned 500 total points
ID: 13762153
Try changing the DECLARE @ordernumber in to DECLARE @ordernumber VARCHAR(20).
0
 
LVL 1

Author Comment

by:RLLewis
ID: 13762288
oh duh.  i should have seen that.  thank you - that was it
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question