Sliding window

Author Message
Ron Klop

New User

Joined: 28 Sep 2012
Posts: 28
Location: holland

 Posted: Fri Sep 14, 2018 12:43 am Hai, a question. I have two tables. Contract-table, containing three attributes: - contract-id - sliding-window-number-of-weeks - sliding-window-limit-amount Transaction-table, , containing three attributes: - date - contract-id - amount Suppose we have in the contract-table a row with the next values: - contract-id = 'AS123' - sliding-window-number-of-weeks = 4 - sliding-window-limit-amount = 100.000 euro So, this means that contract-id 'AS123' can spend 100.000 euro in 4 weeks time. In the transaction table, we have two rows: - date = 12-09-2018 - contract-id 'AS123' - amount = 40.000 euro - date = 18-09-2018 - contract-id 'AS123' - amount = 60.000 euro The weeks are sliding. We want to see the percentage used of the sliding-window-limit-amount of the contract. Suppose the first week starts at 09-09-2018. Then we get the next output: Limit used in week 1: 40% (40.000 of 100.000) Limit used in week 2: 100% (40.000 of week 1 + 60.000 of week 2 equals 100.000 limit of contract) Limit used in week 3: 100% (sliding-weeks are 4) Limit used in week 4: 100% (sliding-weeks are 4) Limit used in week 5: 60% (sliding-weeks are 4, so here we look at week 2 up until 5. In week 2 60.000 euro is spent, is 60% of the limit) Limit used in week 6: 0% (sliding-weeks are 4, so here we look at week 3 up until 6. In these weeks nothing is spent, so 0% of the limit of contract) How can I get the percentages in a single query? Is there a function in DB2 z/OS for this? Thanks Ron
RahulG31

Active User

Joined: 20 Dec 2014
Posts: 446
Location: USA

Posted: Fri Sep 14, 2018 1:38 am

 Quote: Suppose the first week starts at 09-09-2018.

Where is this date (09-09-2018) stored?

How do we know the starting point?

.
Ron Klop

New User

Joined: 28 Sep 2012
Posts: 28
Location: holland

 Posted: Fri Sep 14, 2018 2:10 am It is the daily processing date, which is stored in a separate table. Or can also be CURRENT DATE
Pandora-Box

Moderator

Joined: 07 Sep 2006
Posts: 1565
Location: Andromeda Galaxy

 Posted: Fri Sep 14, 2018 2:53 am Hi Ron, Did you try anything ? I am not trying to be snide here... Just wanted to see what you have tried..
agkshirsagar

Active Member

Joined: 27 Feb 2007
Posts: 691
Location: Earth

Posted: Mon Sep 17, 2018 9:45 pm

Ron,
There's no built-in function but you could create UDF/Stored Proc with the results you want.

Crude attempt: (Assuming the start of week is the input parm)
 Code: WITH TRAN (TR_DATE, CON_ID, AMT) AS (                                        SELECT DATE('2018-09-12'), 'AS123', 40.00 FROM SYSIBM.SYSDUMMY1              UNION                                                                        SELECT DATE('2018-09-18'), 'AS123', 60.00 FROM SYSIBM.SYSDUMMY1),            CON (CON_ID, NUM_WEEKS, LIMIT) AS (                                          SELECT 'AS123', 4, 100.00 FROM SYSIBM.SYSDUMMY1 )                            SELECT SUM(AMT) * 100  /( SUM(LIMIT) / COUNT(*) ) AS PERCENTAGE              FROM TRAN JOIN CON ON TRAN.CON_ID = TRAN.CON_ID                                WHERE TR_DATE BETWEEN DATE('2018-09-09') AND                                  DATE((DATE('2018-09-09')+ 8 DAYS));

Output :
 Code: ---------+---------+---------+---------+-        PERCENTAGE                        ---------+---------+---------+---------+-               40.

You will need to change the number of days (8, 16, 24, 32) to get different percentages. Once you change the input date, query can provide the appropriate percentage.

Hope this helps.
Rohit Umarjikar

Global Moderator

Joined: 21 Sep 2010
Posts: 2572
Location: NYC,USA

 Posted: Tue Sep 18, 2018 1:16 am agkshirsagar, TS expecting output for next 6 weeks (format) from the current date or the mentioned date in single SQL without making no changes to it.
agkshirsagar

Active Member

Joined: 27 Feb 2007
Posts: 691
Location: Earth

Posted: Tue Sep 18, 2018 1:22 am

 Rohit Umarjikar wrote: agkshirsagar, TS expecting output for next 6 weeks (format) from the current date or the mentioned date in single SQL without making no changes to it.

I gave a possible approach. It's on OP to derive the final solution or clarify requirements.
Rohit Umarjikar

Global Moderator

Joined: 21 Sep 2010
Posts: 2572
Location: NYC,USA

Posted: Tue Sep 18, 2018 3:01 am

Try this.
Since I don't have all the table set up I would let you do rest of the stuff as
1. Use the other table to calculate % in the place of "SUM(A.AMT)"
2. Repeat this outer Union for another 4 times change the Where condition by incrementing the number by 1
 Code: SELECT 'Limit used in week 1: '||SUM(A.AMT)||' %'       ,A.CTS_ID   FROM (SELECT INT(VARCHAR_FORMAT('2018-09-01', 'W')) AS TRX_ID,        'AS123' AS CTS_ID, 40.000 AS AMT    FROM SYSIBM.SYSDUMMY1 UNION  SELECT INT(VARCHAR_FORMAT('2018-09-01', 'W')) AS TRX_ID,        'AS123' AS CTS_ID, 50.000 AS AMT    FROM SYSIBM.SYSDUMMY1 UNION SELECT INT(VARCHAR_FORMAT('2018-09-11', 'W')) AS TRX_ID ,        'AS123' AS CTS_ID, 40.000 AS AMT    FROM SYSIBM.SYSDUMMY1 UNION SELECT CASE WHEN (YEAR(DATE('2018-08-18')) = YEAR(CURRENT DATE)) AND                  (MONTH(DATE('2018-08-18')) < MONTH(CURRENT DATE))                  THEN INT(VARCHAR_FORMAT(CURRENT DATE, 'W') + 4)             ELSE INT(VARCHAR_FORMAT('2018-08-18', 'W')) END              AS TRX_ID        ,'AS123' AS CTS_ID, 20.000 AS AMT    FROM SYSIBM.SYSDUMMY1) A  WHERE A.TRX_ID = 1 GROUP BY A.TRX_ID         ,A.CTS_ID union SELECT 'Limit used in week 2: '||SUM(A.AMT)||' %'       ,A.CTS_ID   FROM (SELECT INT(VARCHAR_FORMAT('2018-09-01', 'W')) AS TRX_ID,        'AS123' AS CTS_ID, 40.000 AS AMT    FROM SYSIBM.SYSDUMMY1 UNION  SELECT INT(VARCHAR_FORMAT('2018-09-01', 'W')) AS TRX_ID,        'AS123' AS CTS_ID, 50.000 AS AMT    FROM SYSIBM.SYSDUMMY1 UNION SELECT INT(VARCHAR_FORMAT('2018-09-11', 'W')) AS TRX_ID ,        'AS123' AS CTS_ID, 40.000 AS AMT    FROM SYSIBM.SYSDUMMY1 UNION SELECT CASE WHEN (YEAR(DATE('2018-08-18')) = YEAR(CURRENT DATE)) AND                  (MONTH(DATE('2018-08-18')) < MONTH(CURRENT DATE))                  THEN INT(VARCHAR_FORMAT(CURRENT DATE, 'W') + 4)             ELSE INT(VARCHAR_FORMAT('2018-08-18', 'W')) END              AS TRX_ID        ,'AS123' AS CTS_ID, 20.000 AS AMT    FROM SYSIBM.SYSDUMMY1) A  WHERE A.TRX_ID  <= 2 GROUP BY A.CTS_ID;
Ron Klop

New User

Joined: 28 Sep 2012
Posts: 28
Location: holland

 Posted: Tue Sep 18, 2018 12:48 pm Ok, one of these days I will give it a try. I also read something about Windowing functions in DB2. Something like this SELECT datum, AVG(omzet) OVER (ORDER BY datum ROWS BETWEEN 15 PRECEDING AND 14 FOLLOWING) FROM my_table ORDER BY datum
Rohit Umarjikar

Global Moderator

Joined: 21 Sep 2010
Posts: 2572
Location: NYC,USA

 Posted: Tue Sep 18, 2018 11:54 pm Ron, You can learn more of OLAP specification. Let us know if you ever use them to implement for this problem statement so that others may be benefited. In free time I shall give a try and interesting to know if it works that easily.
Ron Klop

New User

Joined: 28 Sep 2012
Posts: 28
Location: holland

 Posted: Wed Sep 19, 2018 1:29 am Sure I will
 View Bookmarks All times are GMT + 6 Hours

 Topic Forum Replies Similar Topics IBM Debug Tool - Need help in window ... IBM Tools 4 Could not display a Pop up window TSO/ISPF 2 can we run two queries in same qmf wi... DB2 6 Panel: Position window in middle of t... TSO/ISPF 7 Finding if a label exists in a editor... TSO/ISPF 2
Search our Forums: