SQL/MX 2.x Reference Manual (H06.04+)

SQL/MX Clauses
HP NonStop SQL/MX Reference Manual—540440-003
7-21
Examples of SEQUENCE BY
4000.00 18000.00 18000.00
100250 3 6500 1997-01-23
950.00 18950.00 18950.00
200300 1 244 1997-02-06
28000.00 28000.00 46950.00
200300 2 2001 1997-02-06
10000.00 38000.00 56950.00
200300 3 2002 1997-02-06
14000.00 52000.00 70950.00
... ... ... ...
800660 18 7102 1997-10-09
1650.00 187360.00 1113295.00
800660 19 7301 1997-10-09
5100.00 192460.00 1118395.00
--- 69 row(s) selected.
Note that, for example, for order number 200300, the ORDER_TOTAL is a moving
sum within the order date 1997-02-06, and the TOTAL_SALES is a running sum for
all orders. The current window for the moving sum is defined as ROWS SINCE
(THIS(o.ordernum)<>o.ordernum), which restricts the ORDER_TOTAL to the
current order number.
•
Show the amount of time between orders by calculating the interval between two
dates:
SELECT RUNNINGCOUNT(*),o.order_date,DIFF1(o.order_date)
FROM orders o
SEQUENCE BY o.order_date, o.ordernum
ORDER BY o.order_date, o.ordernum ;
(EXPR) Order/Date (EXPR)
-------------------- ---------- -------------
1 1997-01-23 ?
2 1997-02-06 14
3 1997-02-17 11
4 1997-03-03 14
5 1997-03-19 16
6 1997-03-19 0
7 1997-03-27 8
8 1997-04-10 14
9 1997-04-20 10
10 1997-05-12 22
11 1997-06-01 20
12 1997-07-21 50
13 1997-10-09 80
--- 13 row(s) selected.