• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 287
  • Last Modified:

Subtracting two datetime values keeps failing.

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
RLLewis
Asked:
RLLewis
  • 3
  • 3
  • 2
1 Solution
 
ram2098Commented:
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
 
ram2098Commented:
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
 
ram2098Commented:
Repeat the same for second query too...

select orderdate from orders where msgtype = 'a' and orderdate >= left(getdate()-0, 11) and endpoint = 'aaa'
0
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

 
RLLewisAuthor Commented:
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
 
rafranciscoCommented:
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
 
RLLewisAuthor Commented:
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
 
rafranciscoCommented:
Try changing the DECLARE @ordernumber in to DECLARE @ordernumber VARCHAR(20).
0
 
RLLewisAuthor Commented:
oh duh.  i should have seen that.  thank you - that was it
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now