CODEWITHSUNDEEP
sql-server
Roy
Roy

Reputation: 1225

SQL Query returning multiple values

I am trying to write a query that returns the time taken by an Order from start to completion.

My table looks like below.

Order No.   Action      DateTime
111         Start       3/23/2018 8:18
111         Complete    3/23/2018 9:18
112         Start       3/24/2018 6:00
112         Complete    3/24/2018 11:10

Now I am trying to calculate the date difference between start and completion of multiple orders and below is my query:

Declare @StartDate VARCHAR(100), @EndDate VARCHAR(100), @Operation VARCHAR(100)

declare @ORDERTable table
(
    order varchar(1000)
)

insert into @ORDERTable values ('111')
insert into @ORDERTable values ('112')

Select @Operation='Boiling'

set @EndDate = (SELECT DATE_TIME from PROCESS WHERE ACTION='COMPLETE' AND ORDER in (select order from @ORDERTable) AND OPERATION=@Operation)

---SELECT @EndDate

set @StartDate = (SELECT DATE_TIME from PROCESS WHERE ACTION='START' AND ORDER in (select order from @ORDERTable) AND OPERATION=@Operation)

---SELECT @StartDate

SELECT DATEDIFF(minute, @StartDate, @EndDate) AS Transaction_Time

So, I am able to input multiple orders but I want to get multiple output as well.

And my second question is if I am able to achieve multiple records as output, how am I gonna make sure which datediff is for which Order?

Awaiting for your answers. Thanks in advance.

I am using MSSQL.

Upvotes: 0

Views: 4375

Answers (3)

MK_
MK_

Reputation: 1169

Why don't you attempt to approach this problem with a set-based solution? After all, that's what a RDBMS is for. With an assumption that you'd have orders that are of interest to you in a table variable like you described, @ORDERTable(Order), it would go something along the lines of:

SELECT DISTINCT
      [Order No.]
    , DATEDIFF(
        minute,
        FIRST_VALUE([DateTime]) OVER (PARTITION BY [Order No.] ORDER BY [DateTime] ASC),
        FIRST_VALUE([DateTime]) OVER (PARTITION BY [Order No.] ORDER BY [DateTime] DESC)
      ) AS Transaction_Time
FROM tableName
WHERE [Order No.] IN (SELECT Order FROM @ORDERTable);

This query works if all the values in the Action attribute are either Start or Complete, but also if there are others in between them.

To read up more on the FIRST_VALUE() window function, check out the documentation.

NOTE: works in SQL Server 2012 or newer versions.

Upvotes: 0

Thorsten Kettner
Thorsten Kettner

Reputation: 94913

You can aggregate by order number and use MAX or MIN with CASE WHEN to get start or end time:

select
  order_no,
  max(case when action = 'Start' then date_time end) as start_time,
  max(case when action = 'Completed' then date_time end) as end_time,
  datediff(
    minute, 
    max(case when action = 'Start' then date_time end), 
    max(case when action = 'Completed' then date_time end)
  ) as transaction_time
from process
group by order_no
order by order_no;

Upvotes: 1

dfundako
dfundako

Reputation: 8324

You can split up your table into two temp tables, cte's, whatever, and then join them together to find the minutes it took to complete

DECLARE @table1 TABLE (OrderNO INT, Action VARCHAR(100), datetime datetime)

INSERT INTO @table1 (OrderNO, Action, datetime)
VALUES 

(111         ,'Start'       ,'3/23/2018 8:18'),
(111         ,'Complete'    ,'3/23/2018 9:18'),
(112         ,'Start'       ,'3/24/2018 6:00'),
(112         ,'Complete'    ,'3/24/2018 11:10')



;with cte_start AS (
SELECT orderno, Action, datetime

FROM @table1
WHERE Action = 'Start')
, cte_complete AS (
SELECT orderno, Action, datetime

FROM @table1
WHERE Action = 'Complete')

SELECT 
start.OrderNO, DATEDIFF(minute, start.datetime, complete.datetime) AS duration

FROM cte_start start

INNER JOIN cte_complete complete
    ON start.OrderNO = complete.OrderNO

Upvotes: 1

Related Questions