This discussion is archived
4 Replies Latest reply: Jul 1, 2013 11:21 PM by Purvesh K RSS

Get all Month between date Range

Sami Newbie
Currently Being Moderated

Hi All,

 

I have requirement that needs to display month & year for given date range. i.e Start date:01-11-12, End Date: 31-05-13

 

SELECT COUNT(T1.CUST_NM) AS COUNT, TO_CHAR(T1.CREATE_DT,'MON-YY') AS MONTH, SUM(T3.DISBURSEMENT_LIMIT) AS TOTAL
  FROM CUSTOMER T1, 
 ACCOUNT T2, LOAN_ACCOUNT T3, CREDIT_APPL T4, PORTFOLIO T5 WHERE  T1.CUST_ID = T2.CUST_ID  AND T2.ACCT_ID = T3.ACCT_ID  AND T3.APPL_ID = T4.APPL_ID  AND 
 T4.PORTFOLIO_ID = T5.PORTFOLIO_ID
and t1.CREATE_DT between to_date('01-01-10','DD-MM-YY') and  to_date('30-12-10','DD-MM-YY') 
 GROUP BY TO_CHAR(T1.CREATE_DT,'MON-YY')


SQL output:

 

 

CountMonth-YearTotal
8JAN-1015300
6FEB-104245000
11AUG-10144500
6DEC-1015500

 

Now SQL returns matching records for given date. But the requirement is to display the month name ,count and total as 0 if there is no data found.

 

Required Output:

 

CountMonth-YearTotal
8Jan15300
1Feb-10118750
0Mar-100
0Apr-100
0May-100
0Jun-100
0Jul-100
11Aug-10144500
0Sep-100
0Oct-100
0Nov-100
6Dec-1015500

 

Kindly suggest me SQL to  archive above requirement.

 

Thanks&Regards

 

Sami

  • 1. Re: Get all Month between date Range
    S10390 Journeyer
    Currently Being Moderated

    Try this NVL function.

     

    SELECT   nvl(COUNT (t1.cust_nm),0) AS COUNT,

             TO_CHAR (t1.create_dt, 'MON-YY') AS MONTH,

             nvl(SUM (t3.disbursement_limit),0) AS total

        FROM customer t1,

             ACCOUNT t2,

             loan_account t3,

             credit_appl t4,

             portfolio t5

       WHERE t1.cust_id = t2.cust_id

         AND t2.acct_id = t3.acct_id

         AND t3.appl_id = t4.appl_id

         AND t4.portfolio_id = t5.portfolio_id

         AND t1.create_dt BETWEEN TO_DATE ('01-01-10', 'DD-MM-YY')

                              AND TO_DATE ('30-12-10', 'DD-MM-YY')

    GROUP BY TO_CHAR (t1.create_dt, 'MON-YY');

  • 2. Re: Get all Month between date Range
    Purvesh K Guru
    Currently Being Moderated


    One way:

     

    select 0 count, to_char(add_months(to_date('01-Jan-10', 'DD-Mon-YYYY'), level -1), 'Mon-YY') month , 0 sum
      from dual
    connect by level <= 12
    union
    SELECT COUNT(T1.CUST_NM) AS COUNT, TO_CHAR(T1.CREATE_DT,'MON-YY') AS MONTH, SUM(T3.DISBURSEMENT_LIMIT) AS TOTAL   
      FROM CUSTOMER T1,  
    ACCOUNT T2, LOAN_ACCOUNT T3, CREDIT_APPL T4, PORTFOLIO T5 WHERE  T1.CUST_ID = T2.CUST_ID  AND T2.ACCT_ID = T3.ACCT_ID  AND T3.APPL_ID = T4.APPL_ID  AND  
    T4.PORTFOLIO_ID = T5.PORTFOLIO_ID 
    and t1.CREATE_DT between to_date('01-01-10','DD-MM-YY') and  to_date('30-12-10','DD-MM-YY')  
    GROUP BY TO_CHAR(T1.CREATE_DT,'MON-YY');
    
    

     

     

    However, if there is a chance of a Month being repeated in output if you have it occuring in your Original Query, it can be dealt with '"With" Clause:

     

    with original_query as
    (
    SELECT COUNT(T1.CUST_NM) AS COUNT, T1.CREATE_DT AS MONTH, SUM(T3.DISBURSEMENT_LIMIT) AS TOTAL   
      FROM CUSTOMER T1,   
    ACCOUNT T2, LOAN_ACCOUNT T3, CREDIT_APPL T4, PORTFOLIO T5 WHERE  T1.CUST_ID = T2.CUST_ID  AND T2.ACCT_ID = T3.ACCT_ID  AND T3.APPL_ID = T4.APPL_ID  AND   
    T4.PORTFOLIO_ID = T5.PORTFOLIO_ID  
    and t1.CREATE_DT between to_date('01-01-10','DD-MM-YY') and  to_date('30-12-10','DD-MM-YY')   
    GROUP BY TO_CHAR(T1.CREATE_DT,'MON-YY')
    ),
    get_default_data as
    (
    select 0 count, add_months(to_date('01-Jan-10', 'DD-Mon-YYYY'), level -1) mnth , 0 sm
      from dual
    connect by level <= 12
    )
    select cnt, to_char(mnth, 'Mon-YY'), sm
      from get_default_data
    where to_char(mnth, 'Mon') not in (select month from original_query)
    union
    select cnt, to_char(mnth, 'Mon-YY') mnth, sm
      from original_query
    
  • 3. Re: Get all Month between date Range
    Karthick_Arp Guru
    Currently Being Moderated

    Try this

     

    with t

    as

    (

    select count(t1.cust_nm) as count

         , to_char(t1.create_dt,'mon-yy') as month

         , sum(t3.disbursement_limit) as total 

      from customer t1

         , account t2

         , loan_account t3

         , credit_appl t4

         , portfolio t5

    where t1.cust_id = t2.cust_id

       and t2.acct_id = t3.acct_id

       and t3.appl_id = t4.appl_id

       and t4.portfolio_id = t5.portfolio_id 

       and t1.create_dt between to_date('01-01-10','dd-mm-yy')

                            and to_date('30-12-10','dd-mm-yy')  

    group

        by to_char(t1.create_dt,'mon-yy')

    )

    , t1

    as

    (

    select to_char(add_months(to_date('01-01-10','dd-mm-yy'), level-1), 'mon-yy') month_list

       from dual

    connect

         by add_months(to_date('01-01-10','dd-mm-yy'), level-1) <=  to_date('30-12-10','dd-mm-yy')

    )

    select nvl(t.count, 0) as count

         , t1.month_list as month

         , nvl(t.total, 0) as total

      from t1

      left

      join t

        on t1. month_list = t.month

  • 4. Re: Get all Month between date Range
    Sami Newbie
    Currently Being Moderated

    Hi All,

     

    Thanks to all for your quick response.

     

    Thanks & Regards

    Sami

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points