Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
Sliding window

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
Ron Klop

New User


Joined: 28 Sep 2012
Posts: 24
Location: holland

PostPosted: Fri Sep 14, 2018 12:43 am    Post subject: Sliding window
Reply with quote

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
Back to top
View user's profile Send private message

RahulG31

Active User


Joined: 20 Dec 2014
Posts: 444
Location: USA

PostPosted: Fri Sep 14, 2018 1:38 am    Post subject: Reply to: Sliding window
Reply with quote

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?

.
Back to top
View user's profile Send private message
Ron Klop

New User


Joined: 28 Sep 2012
Posts: 24
Location: holland

PostPosted: Fri Sep 14, 2018 2:10 am    Post subject:
Reply with quote

It is the daily processing date, which is stored in a separate table. Or can also be CURRENT DATE
Back to top
View user's profile Send private message
Pandora-Box

Moderator


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

PostPosted: Fri Sep 14, 2018 2:53 am    Post subject:
Reply with quote

Hi Ron,

Did you try anything ? I am not trying to be snide here... Just wanted to see what you have tried..
Back to top
View user's profile Send private message
agkshirsagar

Active Member


Joined: 27 Feb 2007
Posts: 691
Location: Earth

PostPosted: Mon Sep 17, 2018 9:45 pm    Post subject:
Reply with quote

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.
Back to top
View user's profile Send private message
Rohit Umarjikar

Senior Member


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

PostPosted: Tue Sep 18, 2018 1:16 am    Post subject:
Reply with quote

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.
Back to top
View user's profile Send private message
agkshirsagar

Active Member


Joined: 27 Feb 2007
Posts: 691
Location: Earth

PostPosted: Tue Sep 18, 2018 1:22 am    Post subject:
Reply with quote

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.
Back to top
View user's profile Send private message
Rohit Umarjikar

Senior Member


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

PostPosted: Tue Sep 18, 2018 3:01 am    Post subject:
Reply with quote

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;
Back to top
View user's profile Send private message
Ron Klop

New User


Joined: 28 Sep 2012
Posts: 24
Location: holland

PostPosted: Tue Sep 18, 2018 12:48 pm    Post subject:
Reply with quote

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
Back to top
View user's profile Send private message
Rohit Umarjikar

Senior Member


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

PostPosted: Tue Sep 18, 2018 11:54 pm    Post subject:
Reply with quote

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.
Back to top
View user's profile Send private message
Ron Klop

New User


Joined: 28 Sep 2012
Posts: 24
Location: holland

PostPosted: Wed Sep 19, 2018 1:29 am    Post subject:
Reply with quote

Sure I will
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

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

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us