|
View previous topic :: View next topic
|
| Author |
Message |
Ron Klop
New User
Joined: 28 Sep 2012
Posts: 28
Location: holland
|
|
|
|
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 |
|
 |
RahulG31
Active User
Joined: 20 Dec 2014
Posts: 446
Location: USA
|
|
|
|
| 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 |
|
|
Ron Klop
New User
Joined: 28 Sep 2012
Posts: 28
Location: holland
|
|
|
|
| It is the daily processing date, which is stored in a separate table. Or can also be CURRENT DATE |
|
| Back to top |
|
|
Pandora-Box
Global Moderator
.jpg)
Joined: 07 Sep 2006
Posts: 1592
Location: Andromeda Galaxy
|
|
|
|
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 |
|
|
agkshirsagar
Active Member
Joined: 27 Feb 2007
Posts: 691
Location: Earth
|
|
|
|
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 |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3053
Location: NYC,USA
|
|
|
|
| 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 |
|
|
agkshirsagar
Active Member
Joined: 27 Feb 2007
Posts: 691
Location: Earth
|
|
|
|
| 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 |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3053
Location: NYC,USA
|
|
|
|
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 |
|
|
Ron Klop
New User
Joined: 28 Sep 2012
Posts: 28
Location: holland
|
|
|
|
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 |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3053
Location: NYC,USA
|
|
|
|
| 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 |
|
|
Ron Klop
New User
Joined: 28 Sep 2012
Posts: 28
Location: holland
|
|
|
|
| Sure I will |
|
| Back to top |
|
|
|
|
