Portal | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref

Author Message
Ron Klop

New User

Joined: 28 Sep 2012
Posts: 28
Location: holland

 Posted: Fri Sep 14, 2018 12:43 am    Post subject: Sliding window 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: 447
Location: USA

Posted: Fri Sep 14, 2018 1:38 am    Post subject: Reply to: Sliding window

 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    Post subject: 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: 1568
Location: Andromeda Galaxy

 Posted: Fri Sep 14, 2018 2:53 am    Post subject: 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    Post subject:

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

Senior Member

Joined: 21 Sep 2010
Posts: 2461
Location: NY,USA

 Posted: Tue Sep 18, 2018 1:16 am    Post subject: 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    Post subject:

 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

Senior Member

Joined: 21 Sep 2010
Posts: 2461
Location: NY,USA

Posted: Tue Sep 18, 2018 3:01 am    Post subject:

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    Post subject: 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

Senior Member

Joined: 21 Sep 2010
Posts: 2461
Location: NY,USA

 Posted: Tue Sep 18, 2018 11:54 pm    Post subject: 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    Post subject: Sure I will
 All times are GMT + 6 Hours
 Page 1 of 1

Search our Forum:

 Topic Author Forum Replies Posted Similar Topics IBM Debug Tool - Need help in window ... satish.ms10 IBM Tools 4 Tue Jul 02, 2013 7:07 pm Could not display a Pop up window magesh23586 TSO/ISPF 2 Thu Sep 08, 2011 4:10 pm can we run two queries in same qmf wi... nandeshwar DB2 6 Tue Jan 18, 2011 7:17 pm Panel: Position window in middle of t... paravinda TSO/ISPF 7 Tue Sep 21, 2010 7:17 pm Finding if a label exists in a editor... GaneshMF TSO/ISPF 2 Tue Jun 01, 2010 7:09 pm

 © 2003-2020 IBM MAINFRAME Software Support Division
 Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us