IBM i5OS SQL: Forecasting periodic events

Question

I have a situation where I have about 4000 tasks that all have different periodic rules for occurences. They are preventive maintenance tasks. The table I get them from only provides me the start date and frequency of occurence in units of weeks.

Example:
Task (A) is scheduled to occur every two weeks, starting on week 1 of 2015.
Task (B) is scheduled to occur every 6 weeks, starting on week 2 of 2011.
And so on...

What I need to do is produce a resultset that contains a record for each occurence since the start point, for each task.
It's like generating a sequence.

Example:

Task  | Year  | Week  
------|-------|-------
A     | 2015  | 1
A     | 2015  | 3
A     | 2015  | 5
A     | 2015  | 7
[...]
B     | 2011  | 2
B     | 2011  | 8
And so on...

You probably think "hey, that is simple, just put it in a loop then your good."

Not so fast!

The trick is that I need this to be within one SQL query.

I know I probably should be doing it in a stored procedure or a function. But I can't, for now. I could also do it in some VbA code since it will go in an Excel spreadsheet. But Excel has become an unstable product lately and I do not want to risk my code to fail after an update from Microsoft. So I try as much as possible to stay within the limits of IBM i5OS SQL queries.

I know the answer could be that it is impossible. But I believe in this community.

Thanks in advance,

EDIT : I have found this post where it shows how to list dates within a range. IBM DB2: Generate list of dates between two dates

I tried to generate a list of dates based on periodicity and it worked. I am still struggling on the generation of multiple sequences based on multiple periodicity.

Here's the code I have so far:

SELECT d.min + num.n DAYS AS DATES
FROM 
    (VALUES(DATE('2017-01-01'), DATE('2017-03-01'))) AS d(min, max)
JOIN
    (  
    -- Creates a table of numbers based on periodicity
    SELECT
        n1.n + n10.n + n100.n AS n
    FROM
        (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) AS n1(n)
    CROSS JOIN
        (VALUES(0),(10),(20),(30),(40),(50),(60),(70),(80),(90)) AS n10(n)
    CROSS JOIN
        (VALUES(0),(100),(200),(300),(400),(500),(600),(700),(800),(900)) AS n100(n)
    -- I just need to replace the 2nd argument by the desired frequency */
    WHERE MOD(n1.n+n10.n+n100.n, 6 )=0  
    ORDER BY n1.n + n10.n + n100.n
    ) num
ON
    d.min + num.n DAYS<= d.max
ORDER BY num.n

In other words, I need the dates in table d to be dynamic as well as the periodicity (6) in num's table WHERE clause.

Should I be using a WITH statement? If so, can someone please guide me because I am not very used to this kind of statement.

EDIT#2: Here is the table structure I'm working with:

TABLE NAME: SGTRCDP (Programmed Tasks):

              |            | Start    | Start    | Freq.    
 Asset        | Task       | Year     | Week     | (week)   
--------------|------------|----------|----------|----------
TMPC531       | VER0560    | 2011     | 10       | 26       
BAT0404       | IPNET030   | 2011     | 2        | 4        
B-EXTINCT-151 | 001H-0011  | 2014     | 15       | 17
[...]         | [...]      | [...]    | [...]    | [...]    
4000 more like these, the unique key being combination of `Asset` and `Task` fields.

What I would like to have is this:

 Asset        | Task       | Year     | Week     
--------------|------------|----------|----------
TMPC531       | VER0560    | 2011     | 10       
TMPC531       | VER0560    | 2011     | 36
TMPC531       | VER0560    | 2012     | 10
TMPC531       | VER0560    | 2012     | 36
TMPC531       | VER0560    | 2013     | 10
TMPC531       | VER0560    | 2013     | 36
TMPC531       | VER0560    | 2014     | 10
TMPC531       | VER0560    | 2014     | 36
TMPC531       | VER0560    | 2015     | 10
TMPC531       | VER0560    | 2015     | 36
TMPC531       | VER0560    | 2016     | 10
TMPC531       | VER0560    | 2016     | 36
TMPC531       | VER0560    | 2017     | 10
TMPC531       | VER0560    | 2017     | 36
BAT0404       | IPNET030   | 2011     | 2
BAT0404       | IPNET030   | 2011     | 6
BAT0404       | IPNET030   | 2011     | 10
BAT0404       | IPNET030   | 2011     | 14
BAT0404       | IPNET030   | 2011     | 18
BAT0404       | IPNET030   | 2011     | 22
BAT0404       | IPNET030   | 2011     | 26
BAT0404       | IPNET030   | 2011     | 30
BAT0404       | IPNET030   | 2011     | 34
BAT0404       | IPNET030   | 2011     | 38
[...]         | [...]      | [...]    | [...]
BAT0404       | IPNET030   | 2017     | 34
BAT0404       | IPNET030   | 2017     | 38
B-EXTINCT-151 | 001H-0011  | 2014     | 15        
B-EXTINCT-151 | 001H-0011  | 2014     | 32
B-EXTINCT-151 | 001H-0011  | 2014     | 49
B-EXTINCT-151 | 001H-0011  | 2015     | 14
B-EXTINCT-151 | 001H-0011  | 2015     | 31
[...]         | [...]      | [...]    | [...]    
B-EXTINCT-151 | 001H-0011  | 2017     | 8
B-EXTINCT-151 | 001H-0011  | 2017     | 24

I was able to make it using CTE, but it generates so many records that whenever I want to filter or order data, it takes forever. Same goes for downloading the whole resultset.
And I wouldn't risk creating a temporary table and bust the disk space. Another caveat of CTE, is that It cannot be referenced as a subquery.

And guess what, my plan was to use it as a subquery in FROM clause of a SELECT joining it with the actual work orders table and do Asset-Task-Year-Week matching to see if the programmed tasks were executed as planned or not.

Anyway, here is the CTE I used to get it:

WITH PPM (EQ, TASK, FREQ, OCCYR, OCCWK, OCCDAT, NXTDAT) AS
    (
    SELECT 
        TRCD.DLACCD EQ, 
        TRCD.DLJ1CD TASK,
        INT(SUBSTR(TRCD.DLL1TX,9,3)) FREQ,
        AOAGNB OCCYR,
        AOAQNB OCCWK,
        CASE 
        WHEN aoaddt/1000000 >= 1 THEN 
            DATE('20'||substr(aoaddt,2,2)||'-'||substr(aoaddt,4,2)||'-'||substr(aoaddt,6,2))
        ELSE 
            DATE('19'||substr(aoaddt,1,2)||'-'||substr(aoaddt,3,2)||'-'||substr(aoaddt,5,2))
        END OCCDAT,
        (CASE
        WHEN aoaddt/1000000 >= 1 THEN 
            DATE('20'||substr(aoaddt,2,2)||'-'||substr(aoaddt,4,2)||'-'||substr(aoaddt,6,2))
        ELSE 
            DATE('19'||substr(aoaddt,1,2)||'-'||substr(aoaddt,3,2)||'-'||substr(aoaddt,5,2))
        END + (INT(SUBSTR(TRCD.DLL1TX,9,3)) * 7) DAYS) NXTDAT
    FROM
        (SELECT * FROM SGTRCDP WHERE DLIMST<>'H' AND TRIM(DLK5Cd)='S') TRCD
        JOIN 
            (
            SELECT 
                AOAGNB, 
                AOAQNB, 
                min(AOADDT) aoaddt 
            FROM SGCALDP 
            GROUP BY AOAGNB, AOAQNB
            ) CLND 
        ON AOAGNB=SUBSTR(TRCD.DLL1TX,1,4) AND AOAQNB=INT(SUBSTR(TRCD.DLL1TX,12,2))
    WHERE DLACCD='CON0539' AND DLJ1CD='CON0539-04'  
    UNION ALL

    SELECT 
        PPMNXT.EQ, 
        PPMNXT.TASK,
        PPMNXT.FREQ,
        AOAGNB OCCYR,
        AOAQNB OCCWK,
        CASE 
        WHEN aoaddt/1000000 >= 1 THEN 
            DATE('20'||substr(aoaddt,2,2)||'-'||substr(aoaddt,4,2)||'-'||substr(aoaddt,6,2))
        ELSE 
            DATE('19'||substr(aoaddt,1,2)||'-'||substr(aoaddt,3,2)||'-'||substr(aoaddt,5,2))
        END OCCDAT,
        (CASE
        WHEN aoaddt/1000000 >= 1 THEN 
            DATE('20'||substr(aoaddt,2,2)||'-'||substr(aoaddt,4,2)||'-'||substr(aoaddt,6,2))
        ELSE 
            DATE('19'||substr(aoaddt,1,2)||'-'||substr(aoaddt,3,2)||'-'||substr(aoaddt,5,2))
        END + (PPMNXT.FREQ * 7) DAYS) NXTDAT
    FROM
        PPM
 PPMNXT
        JOIN 
            (
            SELECT 
                AOAGNB, 
                AOAQNB, 
                min(AOADDT) aoaddt 
            FROM SGCALDP 
            GROUP BY AOAGNB, AOAQNB
            ) CLND 
        ON AOAGNB=YEAR(PPMNXT.NXTDAT) AND AOAQNB=WEEK_ISO(PPMNXT.NXTDAT)
    WHERE 
        YEAR(CASE
        WHEN aoaddt/1000000 >= 1 THEN 
            DATE('20'||substr(aoaddt,2,2)||'-'||substr(aoaddt,4,2)||'-'||substr(aoaddt,6,2))
        ELSE 
            DATE('19'||substr(aoaddt,1,2)||'-'||substr(aoaddt,3,2)||'-'||substr(aoaddt,5,2))
        END + (PPMNXT.FREQ * 7) DAYS) <= YEAR(CURRENT_DATE)
    )

SELECT EQ, TASK, OCCYR, OCCWK, OCCDAT FROM PPM

That was the best I could do.

You will notice that I set a root to a specific Asset and Task:
WHERE DLACCD='CON0539' AND DLJ1CD='CON0539-04'

Normally I would not filter data in order to retrieve all the scheduled weeks for each tasks. I had to filter on one root key to avoid the query to eventually eat up resources make our AS/400 crash.

Again, I am not an expert in CTEs, there might be a better solution.

Thanks

`WITH` indicates the start of one or more **Common Table Expressions (CTE)**. Think of them as a temporary table built in memory that you can then use in another CTE or in the final `SELECT` statement. They don't really offer much more than a **Nested table exprression (NTE)** But CTE's are reusable, whereas an NTE is not. Also IMHO, CTE is cleaner. — Charles, Aug 24 '17 at 16:01
Thanks Charles for your explanation of CTE and NTE (didn't know about that last one until now). — Firefighter1017, Aug 25 '17 at 11:15

IBM i5OS SQL: Forecasting periodic events

0 Answers0