Hello,

I am new to webfocus and i was wondering if anyone was kind enough to help! What i would like to know is how to count consecutive numbers. More specifically I want to be able to count consecutive numbers that show up in a column. To make it easy,
Lets say N = the *count* of the number of consecutive numbers going down a column in a run, and S = first number of the run of conecutive numbers and E = last number of the run of consecutive numbers.

EX.

6
7
8
9
10
11
12
13
14
15
16
17
18
20
21
22
23
24



I would like to have data appear in four columns based on the value of N

column 1 = 5 <= N <= 11
column 2 = 12 <= N <= 17
column 3 = 18 <= N <= 21
column 4 = 22 <= N

and under each of those 4 columns, i would like to have three "sub columns"

Column a = S
Column b = E
Column c = N

So the data should look like this

Column1_____Column2____Column3_____Column4
S__E__N_____S__E__N____S__E__N_____S__E__N
20_24_5_____6__18_13______________________



I hope somene understands and is willing to help!! Thank you so Much!

This message has been edited. Last edited by: Charles Richards,

This was quite a challenge, but I think I got it.

Cheers,

-Fred-

FILEDEF HLD1 DISK HLD1.MAS
-RUN
-WRITE HLD1 FILENAME=HLD1    , SUFFIX=FIX     ,$
-WRITE HLD1   SEGMENT=HLD, SEGTYPE=S0, $
-WRITE HLD1   FIELDNAME=NUM, ALIAS=E01, USAGE=I2, ACTUAL=A2, $


FILEDEF HLD1 DISK HLD1.FTM
-RUN
-WRITE HLD1  6
-WRITE HLD1  7
-WRITE HLD1  8
-WRITE HLD1  9
-WRITE HLD1 10
-WRITE HLD1 11
-WRITE HLD1 12
-WRITE HLD1 13
-WRITE HLD1 14
-WRITE HLD1 15
-WRITE HLD1 16
-WRITE HLD1 17
-WRITE HLD1 18
-WRITE HLD1 20
-WRITE HLD1 21
-WRITE HLD1 22
-WRITE HLD1 23
-WRITE HLD1 24



DEFINE FILE HLD1
  COUNTER/I5 = IF NUM EQ LAST NUM+1 THEN LAST COUNTER + 1 ELSE 1;
END

TABLE FILE HLD1
  SUM
    COUNTER NOPRINT
    COMPUTE XSTART/I5S = IF COUNTER      EQ 1 THEN NUM ELSE
                         IF LAST COUNTER LE 1 THEN NUM ELSE 0;
    COMPUTE XEND/I5S   = IF LAST COUNTER GT COUNTER THEN LAST XEND ELSE XSTART;

    COMPUTE XOCC/I5S   = IF COUNTER GE LAST COUNTER THEN COUNTER ELSE LAST XOCC;
    COMPUTE ACR1/A10   = IF XOCC IS-FROM  5 TO 11 THEN 'Column 1' ELSE
                         IF XOCC IS-FROM 12 TO 17 THEN 'Column 2' ELSE
                         IF XOCC IS-FROM 18 TO 21 THEN 'Column 3' ELSE 'Column 4';

  BY HIGHEST NUM NOPRINT
  BY TOTAL XSTART NOPRINT
  WHERE TOTAL COUNTER EQ 1
  ON TABLE HOLD AS HLD2 FORMAT ALPHA
  ON TABLE SET HOLDLIST PRINTONLY
END


TABLE FILE HLD2
  SUM
    MIN.XSTART AS 'S'
    MAX.XEND   AS 'E'
    XOCC       AS 'N'
  ACROSS ACR1 AS '' COLUMNS 'Column 1' AND 'Column 2' AND 'Column 3' AND 'Column 4'
  ON TABLE SET PAGE NOPAGE
  ON TABLE SET HTMLCSS ON
  ON TABLE SET NODATA ''
  ON TABLE SET STYLE *
  TYPE=REPORT      ,UNITS=PTS   ,FONT='VERDANA', SIZE=9           ,$
  TYPE=REPORT      ,BORDER=1    ,BORDER-COLOR=RGB(210 210 210)    ,$
  TYPE=TITLE       ,STYLE=BOLD  ,BACKCOLOR=RGB(230 230 230)       ,$
  TYPE=TITLE       ,JUSTIFY=CENTER                                ,$
  TYPE=ACROSSVALUE ,JUSTIFY=CENTER                                ,$
  TYPE=DATA        ,TOPGAP=2    ,BOTTOMGAP=2                      ,$
  TYPE=DATA        ,WRAP=20     ,JUSTIFY=CENTER                   ,$
  TYPE=DATA        ,BACKCOLOR=(RGB(255 255 255) RGB(245 245 245)) ,$
  ENDSTYLE
END
-RUN

This message has been edited. Last edited by: <FreSte>,

Thank you so much for the help, Although I was not able to use the above code, I made a viwe in pl/sql developer and link it to that useing the following sql Code

WITH CTE1 AS -- This part is doing the "subtraction" that you described
(
SELECT ROWNUM AS ITEMCODE2 FROM TEST.ORDERLINEITEM WHERE ROWNUM < 100000
MINUS
SELECT ITEMCODE FROM AWI.ITEMMASTER

),
-- from here on, it is the same as the query from earlier, except it is using the
-- output from CTE1 above.
cte AS
(
SELECT
ITEMCODE2,
ITEMCODE2 - ROW_NUMBER() OVER(ORDER BY ITEMCODE2) AS grp
FROM
CTE1
)
SELECT
MIN(ITEMCODE2) AS startRange,
MAX(ITEMCODE2) AS endRange,
MAX(ITEMCODE2) -MIN(ITEMCODE2) + 1 AS ITEMcount,
CASE
WHEN MAX(ITEMCODE2) -MIN(ITEMCODE2) + 1 > 22 THEN '> 22'
WHEN MAX(ITEMCODE2) -MIN(ITEMCODE2) + 1 BETWEEN 18 AND 22 THEN '18-22'
WHEN MAX(ITEMCODE2) -MIN(ITEMCODE2) + 1 BETWEEN 12 AND 17 THEN '12-17'
WHEN MAX(ITEMCODE2) -MIN(ITEMCODE2) + 1 BETWEEN 5 AND 11 THEN ' 5-11'
ELSE ''
END AS Rnge
FROM
cte
GROUP BY
grp