Getting results from two table and do calculations
-
I have two tables named order and order_line...orderline is related to order by orderid.Calculates the sum of durations from orderline table and add it with the time column in order table as end time and check whether the end time exeeds the current time,if exeeds return the order rows
- amalbaby - Wednesday, February 27, 2019 10:54 PM
Kindly provide test data with create and insert statement. Also what you tried so far?
Saravanan
-
table datas
---------------
Order table
Order line
I want to find the sum of duration column and add it with appoint_time for each order
- amalbaby - Wednesday, February 27, 2019 11:55 PMtable datas
---------------Order table
Order line
I want to find the sum of duration column and add it with appoint_time for each order
A picture really does not help you need to post DDL and DML, and your expected results.
...
-
I've never actually used the Time datatype before, which is what it looks like you are using for OrderLine.duration. Based on my testing, it seems like the following should work for what you need. Note it is limited to minutes, it doesn't process fractions of a minute.
SELECT
O.id AS OrderID,
O.appointment_date AS ApptDate,
SUM(DATEDIFF(MINUTE, '0:00:00', OL.duration)) AS SumDuration,
CAST(SUM(DATEDIFF(MINUTE, '0:00:00', OL.duration)) AS DATETIME) + CAST(O.appointment_date AS DATETIME) AS FinalDateTime,
GETDATE() AS CurrentDateTime
FROM [Order] AS O
INNER JOIN [OrderLine] AS OL ON
O.id = OL.order_id
GROUP BY
O.id,
O.appointment_date
HAVING
CAST(SUM(DATEDIFF(MINUTE, '0:00:00', OL.duration)) AS DATETIME) + CAST(O.appointment_date AS DATETIME) > GETDATE()
"If I had been drinking out of that toilet, I might have been killed." -Ace Ventura
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply