Results 1 to 3 of 3

Thread: SQL Help

  1. #1
    Join Date
    Apr 2005
    Location
    USA
    Posts
    130

    Unanswered: 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

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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
    ;

  3. #3
    Join Date
    Apr 2005
    Location
    USA
    Posts
    130
    Hi Tonkuma,
    Thank you so much for all the help.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •