RLLewis
asked on
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)
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)
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)
select orderdate from orders where msgtype = 'o' and orderdate >= left(getdate()-0, 11)
Repeat the same for second query too...
select orderdate from orders where msgtype = 'a' and orderdate >= left(getdate()-0, 11) and endpoint = 'aaa'
select orderdate from orders where msgtype = 'a' and orderdate >= left(getdate()-0, 11) and endpoint = 'aaa'
ASKER
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?
order.currentordernumber = order.originalordernumber
so, what's the best way to related the acknowledgement back to it's order?
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.
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.
ASKER
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?
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
oh duh. i should have seen that. thank you - that was it
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)