Using SQL, can I iterate through a table on a composite key to find specific data and compute new values?

Question

If I have a data set with a composite key of an order number and a timestamp, can I iterate through the timestamps to search for data values at the specific time? For example, I would want to iterate through the specific order number until I find the first timestamp that contains a BOL and then insert that date record into a new table. In general I am trying to find days between events in the "life-cycle" of an order.

Example Data:

| Order | Timestamp        | BOL     |
| ------| ----------       |---------|
| 12345 | 05.25.21.05:00   |null     |
| 12345 | 05.26.21.05:00   |512345   |
| 67890 | 05.25.21.05:00   |null     |
| 67890 | 05.26.21.05:00   |556725   |

Pseudocode:

SET @i=0;   
while(i<size(order.timestamps())){  
    if(order.timestamp[i].BOL is NULL)
        i++;
    else
        order.bolDate = order.timestamp(i);
        exit;
}

Thank you for any assistance even if it's nothing more than a link!

| Order | BOLDate | | ------| ---------- | | 12345 | 05.26.21.05:00 | | 67890 | 05.26.21.05:00 | — Andrew Scheidt, May 25 '21 at 17:11
With Alteryx: (1) sort on group-by field (order number) and timestamp; (2) use Multi-Row Formula to calculate DateDiff between "row" and "row -1" timestamps; and group by the order number. — johnjps111, May 26 '21 at 01:16

score 0 · Answer 1 · answered May 25 '21 at 14:50

well, seems like you want to to this , this way you don't need to iterate :

insert into newtable (<column list>)
select  order , timestamp , bop 
from ( select * , row_number() over (partition by Order order by timestamp) rn 
 from dataTable
where BOP is not null 
) t 
where rn = 1

query above finds the earliest timestamp for each Order that has BOP and directly can data be inserted into new table.

Using SQL, can I iterate through a table on a composite key to find specific data and compute new values?

1 Answers1