If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > DB2 > SQL Help

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old 05-03-11, 17:28
schintala schintala is offline
Registered User
 
Join Date: Apr 2005
Location: USA
Posts: 119
SQL Help

Hi,
I need some help in forming the SQL. For each month in the last 12 months what day was the highest volume of payments processed for that month and what was the volume? For example, in May 2010, Friday the 7th was the highest volume day with 5,000 payments processed. I have this SQL created but it returns every thing for all the days in a year. Is there any better way. We are in z/OS DB2V8.

SELECT PRCS_DT
,COUNT(*) AS TOTAL
FROM PSSDBAP.PSIPAYV1 PAY
,PSSDBAP.PSIBLRV1 BLR
WHERE PAY.BLLR_ID = BLR_BLLR_ID
AND DATA_SRC_CD = 'P'
AND VD_CD = '2'
AND TXN_SRC_CD NOT IN ('AV','AS')
AND PRCS_DT BETWEEN '05/01/2010' AND '04/30/2011'
AND BLR.BLLR_PROD_TYP_CD NOT IN ('RT1','RT2')
AND PAY.BLLR_ID = '003443'
GROUP BY PRCS_DT
ORDER BY 2 DESC
Reply With Quote
  #2 (permalink)  
Old 05-03-11, 21:40
tonkuma tonkuma is offline
Registered User
 
Join Date: Feb 2008
Location: Japan
Posts: 2,195
If you are using z/OS DB2V9 or later, you may want to use OLAP specifications.

But, you are using z/OS DB2V8.
So, how about something like this(not tested)?
(Common table expression was supported from z/OS DB2V8.)

Note:
If more than two days were the highest volume days for a month,
all the days with highest volume would be reported.
Code:
WITH
/****************************************
** The CTE is same as your query       **
**   except removing ORDER BY clause.  **
****************************************/
 daily_payment AS (
SELECT PRCS_DT 
     , COUNT(*) AS TOTAL 
 FROM  PSSDBAP.PSIPAYV1 PAY 
     , PSSDBAP.PSIBLRV1 BLR 
 WHERE PAY.BLLR_ID = BLR_BLLR_ID 
   AND DATA_SRC_CD = 'P' 
   AND VD_CD       = '2' 
   AND TXN_SRC_CD
               NOT IN ('AV','AS') 
   AND PRCS_DT BETWEEN '05/01/2010'
                   AND '04/30/2011' 
   AND BLR.BLLR_PROD_TYP_CD
               NOT IN ('RT1','RT2') 
   AND PAY.BLLR_ID = '003443' 
 GROUP BY
       PRCS_DT 
)
/****************************************
** End of the CTE.                     **
****************************************/
SELECT m.year_month
     , m.monthly_total
     , d.PRCS_DT AS day_of_max_volume_in_month
     , m.max_day_total
 FROM  (SELECT SUBSTR( CHAR(PRCS_DT , ISO) , 1 , 7 ) AS year_month
             , SUM(TOTAL) AS monthly_total
             , MAX(TOTAL) AS max_day_total
         FROM  daily_payment
         GROUP BY
               SUBSTR( CHAR(PRCS_DT , ISO) , 1 , 7 )
       ) AS m
     , daily_payment AS d
 WHERE m.year_month    = SUBSTR( CHAR(d.PRCS_DT , ISO) , 1 , 7 )
   AND m.max_day_total = d.TOTAL
 ORDER BY
       m.year_month
     , d.PRCS_DT
;
Reply With Quote
  #3 (permalink)  
Old 05-04-11, 12:02
schintala schintala is offline
Registered User
 
Join Date: Apr 2005
Location: USA
Posts: 119
Hi Tonkuma,
Thank you so much for all the help.
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On