How to query the previous row in SQL calculated table?

Question

question

Claire Krupp asked Jan 2, '19 Claire Krupp commented Jan 10, '19

How to query the previous row in SQL calculated table?

I am new to FlexSim (experienced with other discrete-event software) and have a very basic knowledge of SQL.

I want to calculate Time-Between-Arrivals (for each PartNum) from a Statistics Collector where all 6 PartNums are in the same table.

In the single PartNum case I used FlexScipt to calculate a field each time the part arrived:

The custom code is

But that doesn't work when all PartNums are in the same table, since it compares the previous arrival regardless of PartNum.

Solution 1: Use a Calculated Table to Filter an individual PartNum. But then I need to do the difference calculation within SQL. Is this possible? (Here using ROW_NUMBER() -1 as a test for creating a calculated column.)

? Is there an SQL command or function that allows me to access an individual field from the previous record so I can do a calculation with it - something like

"Arrival Time (ROW_NUMBER) - Arrival Time (ROW_NUMBER - 1) " ?

? Then how do I deal with the first row where you can't look at a previous row ? Can I use a CASE-WHEN-THEN-ELSE-END clause?

? OR.. Can I use FlexScript again within the Query Box above?

Solution 2: I could just create a separate copy of the Statistics Collector for every PartNum, but that seems to be less efficient and elegant!

Solution 3: I could just export the data to Excel and manipulate it there, but that does not allow me to have a chart within the model that can be updated dynamically to use for validation and warm-up calculation.

Any suggestions?

Thanks!

Software Version:

FlexSim 18.2.2

statistics collector calculated table sql queries

grall.png (37.9 KiB)

yczoz.png (33.2 KiB)

ib98m.png (29.3 KiB)

Answer 1 · 2019-01-03T17:03:00Z

anthony.johnson answered Jan 3, '19 Claire Krupp commented Jan 10, '19

I would suggest Solution 1. Standard SQL provides (and FlexSim supports) the following window functions to access values in previous and next cells:

LAG([Arrival Time]) OVER()

LEAD([Arrival Time]) OVER()

You can use any standard SQL documentation for more information on this. The OVER() clause allows you to get the previous/next values across multiple rows by partitioning and/or sorting. If a given row does not have a desired previous or next row, then the value of LAG()/LEAD() will simply be null, so you can filter that out as needed.

· 2

Claire Krupp commented · Jan 04, 2019 at 09:08 PM

Here it is - a thing of beauty!

Thanks to @anthony.johnson

1 ·

bcqzz.png (91.2 KiB)

Claire Krupp commented · Jan 04, 2019 at 07:41 PM

Thanks @anthony.johnson that is just what I was looking for. I will give it a try.

0 ·

question