How do I iterate through subsets of a table in SQL

Question

I'm new to SQL and I would appreciate any advice!

I have a table that stores the history of an order. It includes the following columns: ORDERID, ORDERMILESTONE, NOTES, TIMESTAMP.

There is one TIMESTAMP for every ORDERMILESTONE in an ORDERID and vice versa.

What I want to do is compare the TIMESTAMPs for certain ORDERMILESTONEs to obtain the amount of time it takes to go from start to finish or from order to shipping, etc.

To get this, I have to gather all of the lines for a specific ORDERID, then somehow iterate through them... while I was trying to do this by declaring a TVP for each ORDERID, but this is just going to take more time because some of my datasets are like 20000 rows long.

What do you recommend? Thanks in advance.

EDIT:

In my problem, I want to find the number of days that the order spends in QA. For example, once an order is placed, we need to make the item requested and then send it to QA. so there's a milestone "Processing" and a milestone "QA". The item could be in "Processing" then "QA" once, and get shipped out, or it could be sent back to QA several times, or back and forth between "Processing" and "Engineering". I want to find the total amount of time that the item spends in QA.

Here's some sample data:

0332AT | Placed | lightsaber custom made with rainbow colors | 07-06-2020 01:00:09
0332AT | Processing| lightsaber being built | 07-06-2020 06:00:09
0332AT | QA | lightsaber being tested | 07-06-2020 06:00:09

I want the total number of days that each order spends with QA.

So I suppose I could create a lookup table that has each QA milestone and its next milestone. Then sum up the difference between each QA milestone and the one that follows. My main issue is that I don't necessarily know how many times the item will need to be sent to QA on each order...

Please provide sample data, desired results, and an appropriate database tag. — Gordon Linoff, Jul 13 '20 at 13:24
Sample data would be nice. What is the primary key, I'm guessing (ORDERID, ORDERMILESTONE) so that each order has each milestone a single time. — Jason Goemaat, Jul 13 '20 at 13:32
Each ORDERID can have multiple MILESTONEs that are the same name, just with a different timestamp. I will come up with some sample data. I changed the problem that I'm actually working on a little bit so that I don't accidentally give away any proprietary information for the company I'm working for if that makes sense. Thanks for your help! — LAMPORT, Jul 13 '20 at 14:42
@GordonLinoff I think I provided the sample data and desired results. I am not sure what database tag to include. — LAMPORT, Jul 13 '20 at 15:26

score 1 · Answer 1 · answered Jul 13 '20 at 13:18

1

To get the hours to complete a specific mile stone of all orders you can do

select orderid, 
       DATEDIFF(hh, min(TIMESTAMP), max(TIMESTAMP))
from your_table
where ORDERMILESTONE = 'mile stone name'
group by orderid

answered Jul 13 '20 at 13:18

juergen d

201,996
37
293
362

score 0 · Answer 2 · answered Jul 13 '20 at 13:28

Assuming you are using SQL Server and your milestones are not repeated, then you can use:

select om.orderid, 
       datediff(seconds, min(timestamp), max(timestamp))
from order_milestones om
where milestone in ('milestone1', 'milestone2')
group by om.orderid;

If you want to do this more generally on every row, you can use a cumulative aggregation function:

select om.*, 
       datediff(seconds,
                timestamp, 
                min(case when milestone = 'order' then timestamp end) over 
                    (partition by orderid
                     order by timestamp
                     rows between current row and unbounded following
                    )
               ) as time_to_order
from order_milestones om
group by om.orderid;

score 0 · Answer 3 · answered Jul 13 '20 at 13:46

0

You can create a lookup table taking a milestone and giving you the previous milestone. Then you can left join to it and left join back to the original table to get the row for the same order at the previous milestone and compare the dates (sqlfiddle):

select om.*, datediff(minute, pm.TIMESTAMP, om.TIMESTAMP) as [Minutes]
from OrderMilestones om
left join MilestoneSequence ms on ms.ORDERMILESTONE = om.ORDERMILESTONE
left join OrderMilestones pm on pm.ORDERID = om.ORDERID
      and pm.ORDERMILESTONE = ms.PREVIOUSMILESTONE
order by om.TIMESTAMP

answered Jul 13 '20 at 13:46

Jason Goemaat

28,692
15
86
113

Thank you for the recommendation Jason! In my problem, I want to find the number of days that the order spends in QA. For example, once an order is placed, we need to make the item requested and then send it to QA. so there's a milestone "Processing" and a milestone "QA". The item could be in "Processing" then "QA" once, and go out, or it could be sent back to QA several times. I want to find the total amount of time that the item spends in QA... does that make sense? – LAMPORT Jul 13 '20 at 15:11
Ah, I see you added more data to your question. Look up row_number(). You could use a CTE and `row_number() over (partition by ORDERID order by TIMESTAMP) as LogId`. Then link this back to itself with `b.ORDERID = a.ORDERID and b.LogId = a.LogId + 1`, so a is the log row you are looking at and b is the *next* row for the same order with a higher timestamp. Then you can get the date diff for that row in 'a'. Put that in a CTE and you can group by the 'order milestone' to find the total duration. 'Milestone' is a poor choice I think, it normally means you reach it only a single time. – Jason Goemaat Jul 13 '20 at 18:50
That makes so much sense. Thank you!! And yeah, maybe "Step" or "Work area" would be better than milestone. – LAMPORT Jul 13 '20 at 20:59

How do I iterate through subsets of a table in SQL

3 Answers3