1 2 Previous Next 29 Replies Latest reply: May 3, 2012 12:24 PM by Himanshu Binjola Go to original post RSS
      • 15. Re: Help : Complex Output Format
        user3308033
        Hi there,

        With the help of your query guidance, I was able to get the requisite output but the query fails when the following data is entered in the TAB_DTL table and
        there are negative values in the BALANCE field. I require assistance in getting the required ouput till the BALANCE is greater than 0. Further, when you sum up the ACCAMT value for the year, it shows wrong result.
        Any way to correct the same and insert a row with totals of BALANCE and ACCAMT for each 31st March, YYYY.
        INSERT INTO TAB_DTL ( APPDATE, AMOUNT, STATUS, RATE ) VALUES TO_DATE( '19/04/2011 10:50:45 AM','DD/MM/YYYY HH:MI:SS AM'), 496000, 'D', 13 );
        INSERT INTO TAB_DTL ( APPDATE, AMOUNT, STATUS, RATE ) VALUES TO_DATE( '27/05/2011 1:15:30 PM','DD/MM/YYYY HH:MI:SS AM'), 5000, 'R', 13 );
        INSERT INTO TAB_DTL ( APPDATE, AMOUNT, STATUS, RATE ) VALUES TO_DATE( '28/06/2011 3:20:31 PM','DD/MM/YYYY HH:MI:SS AM'), 5000, 'R', 13 );
        INSERT INTO TAB_DTL ( APPDATE, AMOUNT, STATUS, RATE ) VALUES TO_DATE( '27/07/2011 10:25:11 AM','DD/MM/YYYY HH:MI:SS AM'), 5000, 'R', 13 );
        INSERT INTO TAB_DTL ( APPDATE, AMOUNT, STATUS, RATE ) VALUES TO_DATE( '09/08/2011 4:23:34 PM','DD/MM/YYYY HH:MI:SS AM'), 115000, 'R', 13 );
        INSERT INTO TAB_DTL ( APPDATE, AMOUNT, STATUS, RATE ) VALUES TO_DATE( '11/08/2011 11:15:45 AM','DD/MM/YYYY HH:MI:SS AM'), 90000, 'R', 13 );
        INSERT INTO TAB_DTL ( APPDATE, AMOUNT, STATUS, RATE ) VALUES TO_DATE( '26/08/2011 11:55:10 AM','DD/MM/YYYY HH:MI:SS AM'), 5000, 'R', 13 );
        INSERT INTO TAB_DTL ( APPDATE, AMOUNT, STATUS, RATE ) VALUES TO_DATE( '24/09/2011 1:22:10 PM','DD/MM/YYYY HH:MI:SS AM'), 5000, 'R', 13 );
        INSERT INTO TAB_DTL ( APPDATE, AMOUNT, STATUS, RATE ) VALUES TO_DATE( '22/10/2011 3:35:50 PM','DD/MM/YYYY HH:MI:SS AM'), 100000, 'R', 13 );
        INSERT INTO TAB_DTL ( APPDATE, AMOUNT, STATUS, RATE ) VALUES TO_DATE( '31/10/2011 4:10:10 PM','DD/MM/YYYY HH:MI:SS AM'), 140000, 'R', 13 );
        INSERT INTO TAB_DTL ( APPDATE, AMOUNT, STATUS, RATE ) VALUES TO_DATE( '01/11/2011 11:55:10 AM','DD/MM/YYYY HH:MI:SS AM'), 5000, 'R', 13 );
        INSERT INTO TAB_DTL ( APPDATE, AMOUNT, STATUS, RATE ) VALUES TO_DATE( '28/11/2011 12:10:10 PM','DD/MM/YYYY HH:MI:SS AM'), 5000, 'R', 13 );
        INSERT INTO TAB_DTL ( APPDATE, AMOUNT, STATUS, RATE ) VALUES TO_DATE( '26/12/2011 10:51:12 AM','DD/MM/YYYY HH:MI:SS AM'), 5000, 'R', 13 );
        INSERT INTO TAB_DTL ( APPDATE, AMOUNT, STATUS, RATE ) VALUES TO_DATE( '28/01/2012 11:55:10 AM','DD/MM/YYYY HH:MI:SS AM'), 5000, 'R', 13 );
        INSERT INTO TAB_DTL ( APPDATE, AMOUNT, STATUS, RATE ) VALUES TO_DATE( '28/02/2012 12:23:18 PM','DD/MM/YYYY HH:MI:SS AM'), 5000, 'R', 13 );
        INSERT INTO TAB_DTL ( APPDATE, AMOUNT, STATUS, RATE ) VALUES TO_DATE( '29/03/2012 12:40:28 PM','DD/MM/YYYY HH:MI:SS AM'), 1000, 'R', 13 );
        INSERT INTO TAB_DTL ( APPDATE, AMOUNT, STATUS, RATE ) VALUES TO_DATE( '29/03/2012 12:41:20 PM','DD/MM/YYYY HH:MI:SS AM'), 4000, 'R', 13 );
        INSERT INTO TAB_DTL ( APPDATE, AMOUNT, STATUS, RATE ) VALUES TO_DATE( '31/03/2012 11:59:00 AM','DD/MM/YYYY HH:MI:SS AM'), 0, 'B', 13 );
        SQL for the purpose
        SELECT TO_CHAR (tab_dtl_vw.appdate, 'DD/MM/YY fmHHfm:MI:SS AM') appdate,
               CASE
                   WHEN round(tab_dtl_vw.dr,2) < 10000 THEN to_char(round(tab_dtl_vw.dr,2))
                   ELSE TO_CHAR(round(tab_dtl_vw.dr,2),'FM99,99,99,99,99,99,99,999.00')
               END dr,
               CASE
                   WHEN round(tab_dtl_vw.cr,2) <10000 THEN to_char(round(tab_dtl_vw.cr,2))
                   ELSE TO_CHAR(round(tab_dtl_vw.cr,2),'FM99,99,99,99,99,99,99,999.00')
               END cr,
                decode(sign(tab_dtl_vw.balance),-1,0,tab_dtl_vw.balance) balance,
                decode(sign(tab_dtl_vw.balance),-1,0,tab_dtl_vw.days) days,
                decode(sign(tab_dtl_vw.balance),-1,0,ROUND ((tab_dtl_vw.balance * tab_dtl_vw.days * 13) / (tab_dtl_vw.daysinyear*100))) accamt,
                decode(sign(tab_dtl_vw.balance),-1,0,SUM(ROUND((tab_dtl_vw.balance * tab_dtl_vw.days * 13) / (tab_dtl_vw.daysinyear*100))) 
                           OVER(partition by tab_dtl_vw.yr ORDER BY tab_dtl_vw.yr)) as fy_amt,
                CASE
                   WHEN tab_dtl_vw.days IS NOT NULL THEN SUM(ROUND((tab_dtl_vw.balance * tab_dtl_vw.days * 13) / (tab_dtl_vw.daysinyear*100))) 
                   OVER(ORDER BY tab_dtl_vw.appdate)
                   ELSE NULL
               END cummamt
        FROM
          ( SELECT appdate , 
               decode(sign(3-extract(month from appdate)),-1,extract(year from appdate), extract(year from appdate)-1) AS yr,
                CASE  WHEN status = 'D' THEN amount   ELSE 0  END AS dr , 
                CASE   WHEN status = 'R' THEN amount  ELSE 0  END AS Cr ,
             SUM (amount * CASE WHEN status = 'D' THEN 1 WHEN status = 'R' THEN -1 END) OVER (ORDER BY appdate) AS balance ,
                CEIL (LEAD(appdate) OVER (ORDER BY appdate) - appdate ) AS days, 
                 CASE  
                   WHEN (appdate >= '01-APR-' || TO_CHAR(appdate,'YYYY')  AND 
                        TO_CHAR(LAST_DAY('01-FEB-' || (TO_CHAR(appdate,'YYYY') + 1)),'DD-MON') = '29-FEB') 
                   THEN 366
                   WHEN TO_CHAR(LAST_DAY('01-FEB-' || TO_CHAR(appdate,'YYYY')),'DD-MON') = '29-FEB'
                   THEN 366
                   ELSE 365
                   END daysinyear
                FROM (SELECT CASE WHEN (TO_CHAR(TRUNC(APPDATE),'DD-MON') = '31-MAR' AND APPDATE > TRUNC(APPDATE - 1/2 - 60/86400)) THEN TRUNC(APPDATE) +1 ELSE TRUNC(APPDATE) END appdate,
                      status,
                      amount
                      FROM  TAB_DTL )) tab_dtl_vw
        ORDER BY tab_dtl_vw.appdate ASC
        Thanks
        • 16. Re: Help : Complex Output Format
          Frank Kulash
          Hi,
          user3308033 wrote:
          Hi there,

          With the help of your query guidance, I was able to get the requisite output but the query fails when the following data is entered in the TAB_DTL table and
          there are negative values in the BALANCE field. I require assistance in getting the required ouput till the BALANCE is greater than 0. Further, when you sum up the ACCAMT value for the year, it shows wrong result.
          Any way to correct the same and insert a row with totals of BALANCE and ACCAMT for each 31st March, YYYY.
          Sure. Post the exact results you want from the given data.
          .. SQL for the purpose
          ...      CASE  
          WHEN (appdate >= '01-APR-' || TO_CHAR(appdate,'YYYY')  AND ...
          Appdate is a DATE; don't try to compare it to a VARCHAR2, such as '01-APR-2012'.
          • 17. Re: Help : Complex Output Format
            user3308033
            Hi there,

            The result required from the Query is somewhat like this :
            APPDATE          DR              CR          BALANCE          days     accamt          fy_amt          cumm          
            19/04/2011     496,000.00     0.00          496,000.00     38.00     6,695.00     27,771.00     6,695.00     
            27/05/2011     0.00          5,000.00     491,000.00     32.00     5,581.00     27,771.00     12,276.00     
            28/06/2011     0.00          5,000.00     486,000.00     29.00     5,006.00     27,771.00     17,282.00     
            27/07/2011     0.00          5,000.00     481,000.00     13.00     2,221.00     27,771.00     19,503.00     
            09/08/2011     0.00          115,000.00     366,000.00     2.00     260.00          27,771.00     19,763.00     
            11/08/2011     0.00          90,000.00     276,000.00     15.00     1,470.00     27,771.00     21,233.00     
            26/08/2011     0.00          5,000.00     271,000.00     29.00     2,791.00     27,771.00     24,024.00     
            24/09/2011     0.00          5,000.00     266,000.00     28.00     2,645.00     27,771.00     26,669.00     
            22/10/2011     0.00          100,000.00     166,000.00     9.00     531.00          27,771.00     27,200.00     
            31/10/2011     0.00          140,000.00     26,000.00     1.00     9.00          27,771.00     27,209.00     
            01/11/2011     0.00          5,000.00     21,000.00     27.00     201.00          27,771.00     27,410.00     
            28/11/2011     0.00          5,000.00     16,000.00     28.00     159.00          27,771.00     27,569.00     
            26/12/2011     0.00          5,000.00     11,000.00     33.00     129.00          27,771.00     27,698.00     
            28/01/2012     0.00          5,000.00     6,000.00     31.00     66.00          27,771.00     27,764.00     
            28/02/2012     0.00          5,000.00     1,000.00     30.00     11.00          27,771.00     27,775.00     
            29/03/2012     0.00          1,000.00     0.00          0.00     0.00          27,771.00     27,775.00     
            29/03/2012     0.00          4,000.00     0.00          3.00     0.00          27,771.00     27,771.00     
            01/04/2012     0.00          0.00          0.00          0     0          0          27,771.00     
            You'll notice that sum of ACCAMT which comes to 27775 does not tally with fy_amt for the financial year due to the fact that on 29/03/2012 there are 2 transactions due to which the balances becomes (-). The last of the two transactions gives -4 as ACCAMT on computation by the Query , thereby leading to mismatch. It is required that when the Balances becomes 0 or (-) or when the days difference is 0, the ACCAMT should be 0.
            Further from the above query, Financial Year-wise sum of days and accamt is required, may be in a separate column.

            Thanks
            • 18. Re: Help : Complex Output Format
              Himanshu Binjola
              CREATE TABLE TAB_DTL
              (
              APPDATE        DATE,
              AMOUNT        NUMBER(12,2),
              STATUS        VARCHAR2(1),
              RATE        NUMBER(5,2)
              );
              
              INSERT INTO TAB_DTL ( APPDATE, AMOUNT, STATUS, RATE ) VALUES( TO_DATE( '19/04/2011 10:50:45 AM','DD/MM/YYYY HH:MI:SS AM'), 496000, 'D', 13 );
              INSERT INTO TAB_DTL ( APPDATE, AMOUNT, STATUS, RATE ) VALUES( TO_DATE( '27/05/2011 1:15:30 PM','DD/MM/YYYY HH:MI:SS AM'), 5000, 'R', 13 );
              INSERT INTO TAB_DTL ( APPDATE, AMOUNT, STATUS, RATE ) VALUES( TO_DATE( '28/06/2011 3:20:31 PM','DD/MM/YYYY HH:MI:SS AM'), 5000, 'R', 13 );
              INSERT INTO TAB_DTL ( APPDATE, AMOUNT, STATUS, RATE ) VALUES( TO_DATE( '27/07/2011 10:25:11 AM','DD/MM/YYYY HH:MI:SS AM'), 5000, 'R', 13 );
              INSERT INTO TAB_DTL ( APPDATE, AMOUNT, STATUS, RATE ) VALUES( TO_DATE( '09/08/2011 4:23:34 PM','DD/MM/YYYY HH:MI:SS AM'), 115000, 'R', 13 );
              INSERT INTO TAB_DTL ( APPDATE, AMOUNT, STATUS, RATE ) VALUES( TO_DATE( '11/08/2011 11:15:45 AM','DD/MM/YYYY HH:MI:SS AM'), 90000, 'R', 13 );
              INSERT INTO TAB_DTL ( APPDATE, AMOUNT, STATUS, RATE ) VALUES( TO_DATE( '26/08/2011 11:55:10 AM','DD/MM/YYYY HH:MI:SS AM'), 5000, 'R', 13 );
              INSERT INTO TAB_DTL ( APPDATE, AMOUNT, STATUS, RATE ) VALUES( TO_DATE( '24/09/2011 1:22:10 PM','DD/MM/YYYY HH:MI:SS AM'), 5000, 'R', 13 );
              INSERT INTO TAB_DTL ( APPDATE, AMOUNT, STATUS, RATE ) VALUES( TO_DATE( '22/10/2011 3:35:50 PM','DD/MM/YYYY HH:MI:SS AM'), 100000, 'R', 13 );
              INSERT INTO TAB_DTL ( APPDATE, AMOUNT, STATUS, RATE ) VALUES( TO_DATE( '31/10/2011 4:10:10 PM','DD/MM/YYYY HH:MI:SS AM'), 140000, 'R', 13 );
              INSERT INTO TAB_DTL ( APPDATE, AMOUNT, STATUS, RATE ) VALUES( TO_DATE( '01/11/2011 11:55:10 AM','DD/MM/YYYY HH:MI:SS AM'), 5000, 'R', 13 );
              INSERT INTO TAB_DTL ( APPDATE, AMOUNT, STATUS, RATE ) VALUES( TO_DATE( '28/11/2011 12:10:10 PM','DD/MM/YYYY HH:MI:SS AM'), 5000, 'R', 13 );
              INSERT INTO TAB_DTL ( APPDATE, AMOUNT, STATUS, RATE ) VALUES( TO_DATE( '26/12/2011 10:51:12 AM','DD/MM/YYYY HH:MI:SS AM'), 5000, 'R', 13 );
              INSERT INTO TAB_DTL ( APPDATE, AMOUNT, STATUS, RATE ) VALUES( TO_DATE( '28/01/2012 11:55:10 AM','DD/MM/YYYY HH:MI:SS AM'), 5000, 'R', 13 );
              INSERT INTO TAB_DTL ( APPDATE, AMOUNT, STATUS, RATE ) VALUES( TO_DATE( '28/02/2012 12:23:18 PM','DD/MM/YYYY HH:MI:SS AM'), 5000, 'R', 13 );
              INSERT INTO TAB_DTL ( APPDATE, AMOUNT, STATUS, RATE ) VALUES( TO_DATE( '29/03/2012 12:40:28 PM','DD/MM/YYYY HH:MI:SS AM'), 1000, 'R', 13 );
              INSERT INTO TAB_DTL ( APPDATE, AMOUNT, STATUS, RATE ) VALUES( TO_DATE( '29/03/2012 12:41:20 PM','DD/MM/YYYY HH:MI:SS AM'), 4000, 'R', 13 );
              INSERT INTO TAB_DTL ( APPDATE, AMOUNT, STATUS, RATE ) VALUES( TO_DATE( '31/03/2012 11:59:00 AM','DD/MM/YYYY HH:MI:SS AM'), 0, 'B', 13 );
              
              
              SELECT TO_CHAR (tab_dtl_vw.appdate, 'DD/MM/YY') appdate,
                     CASE
                         WHEN round(tab_dtl_vw.dr,2) < 10000 THEN to_char(round(tab_dtl_vw.dr,2))
                         ELSE TO_CHAR(round(tab_dtl_vw.dr,2),'FM99,99,99,99,99,99,99,999.00')
                     END dr,
                     CASE
                         WHEN round(tab_dtl_vw.cr,2) <10000 THEN to_char(round(tab_dtl_vw.cr,2))
                         ELSE TO_CHAR(round(tab_dtl_vw.cr,2),'FM99,99,99,99,99,99,99,999.00')
                     END cr,
                     CASE
                         WHEN round(tab_dtl_vw.balance,2) <10000 THEN to_char(round(tab_dtl_vw.balance,2))
                         ELSE TO_CHAR(round(tab_dtl_vw.balance,2),'FM99,99,99,99,99,99,99,999.00')
                     END balance,
                     tab_dtl_vw.days,
                     ROUND ((tab_dtl_vw.balance * tab_dtl_vw.days * tab_dtl_vw.rate) / (tab_dtl_vw.daysinyear*100)) accamt,
                     CASE WHEN fin_year_end_day IS NOT NULL THEN 
                           SUM(ROUND((tab_dtl_vw.balance * tab_dtl_vw.days * tab_dtl_vw.rate) / (tab_dtl_vw.daysinyear*100))) 
                          OVER(PARTITION BY fin_year ORDER BY tab_dtl_vw.appdate ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) 
                        ELSE 0 
                        END fy_amt,
                      SUM(ROUND((tab_dtl_vw.balance * tab_dtl_vw.days * tab_dtl_vw.rate) / (tab_dtl_vw.daysinyear*100))) 
                     OVER(PARTITION BY fin_year ORDER BY tab_dtl_vw.appdate) cumm
              FROM
                ( SELECT appdate , 
                             CASE WHEN status = 'D' THEN amount   ELSE 0  END AS dr , 
                             CASE WHEN status = 'R' THEN amount  ELSE 0  END AS Cr , 
                             CASE WHEN (SUM (amount * CASE WHEN status = 'D' THEN 1 WHEN status = 'R' THEN -1 END) OVER (ORDER BY appdate)) <=0 THEN 0
                                  ELSE SUM (amount * CASE WHEN status = 'D' THEN 1 WHEN status = 'R' THEN -1 END) OVER (ORDER BY appdate)
                              END balance ,
                             SUM (amount * CASE WHEN status = 'D' THEN 1 WHEN status = 'R' THEN -1 END) OVER (ORDER BY appdate) actualbalance ,
                             CEIL (LEAD(appdate,1,appdate) OVER (ORDER BY appdate) - appdate ) AS days ,
                                CEIL (LEAD(appdate) OVER (ORDER BY appdate) - appdate ) AS fin_year_end_day ,
                             rate,
                             daysinyear,
                             fin_year,
                             status
                     FROM (SELECT CASE WHEN (TO_CHAR(TRUNC(APPDATE),'DD-MON') = '31-MAR' AND APPDATE > TRUNC(APPDATE - 1/2 - 60/86400)) THEN TRUNC(APPDATE) +1 ELSE TRUNC(APPDATE) END appdate,
                                  CASE  
                                       WHEN (appdate >= TO_DATE('01-APR-' || TO_CHAR(appdate,'YYYY'),'DD-MON-YYYY')  AND 
                                          TO_CHAR(LAST_DAY('01-FEB-' || (TO_CHAR(appdate,'YYYY') + 1)),'DD-MON') = '29-FEB') 
                                       THEN 366
                                  WHEN TO_CHAR(LAST_DAY('01-FEB-' || TO_CHAR(appdate,'YYYY')),'DD-MON') = '29-FEB'
                                       THEN 366
                                  ELSE 365
                                  END daysinyear,
                                  CASE  
                                       WHEN (appdate >= TO_DATE('01-APR-' || TO_CHAR(appdate,'YYYY'),'DD-MON-YYYY')) THEN EXTRACT(YEAR FROM APPDATE) + 1
                                       ELSE EXTRACT(YEAR FROM APPDATE)
                                  END fin_year,
                                  status,
                                  amount,
                                  rate
                            FROM  tab_dtl
                           ) 
                   ) tab_dtl_vw
              ORDER BY tab_dtl_vw.appdate ASC
              
              APPDATE              DR              CR              BALANCE          DAYS     ACCAMT     FY_AMT       CUMM
              -------------------- --------------- --------------- --------------- ----- ---------- ---------- ----------
              19/04/11             4,96,000.00     0               4,96,000.00        38       6695      27775       6695
              27/05/11             0               5000            4,91,000.00        32       5581      27775      12276
              28/06/11             0               5000            4,86,000.00        29       5006      27775      17282
              27/07/11             0               5000            4,81,000.00        13       2221      27775      19503
              09/08/11             0               1,15,000.00     3,66,000.00         2        260      27775      19763
              11/08/11             0               90,000.00       2,76,000.00        15       1470      27775      21233
              26/08/11             0               5000            2,71,000.00        29       2791      27775      24024
              24/09/11             0               5000            2,66,000.00        28       2645      27775      26669
              22/10/11             0               1,00,000.00     1,66,000.00         9        531      27775      27200
              31/10/11             0               1,40,000.00     26,000.00           1          9      27775      27209
              01/11/11             0               5000            21,000.00          27        201      27775      27410
              28/11/11             0               5000            16,000.00          28        159      27775      27569
              26/12/11             0               5000            11,000.00          33        129      27775      27698
              28/01/12             0               5000            6000               31         66      27775      27764
              28/02/12             0               5000            1000               30         11      27775      27775
              29/03/12             0               1000            0                   0          0      27775      27775
              29/03/12             0               4000            0                   3          0      27775      27775
              01/04/12             0               0               0                   0          0          0      27775
              
              {code}
              
              Edited by: Himanshu Binjola on Apr 28, 2012 11:07 PM
              
              Ammendment - Exclude balances when <=0 or number of days = 0
              
              Edited by: Himanshu Binjola on Apr 29, 2012 12:22 AM                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
              • 19. Re: Help : Complex Output Format
                user3308033
                Hi there,

                In the output, when you add up ACCAMT for the Financial Year i.e. 1st Apr 2011 to 31st Mar, 2012, the Total comes to 27775 but the FY_AMT which is the Cummulative sum of ACCAMT for the Financial Year shows 27771. Both should match.

                Thanks
                • 20. Re: Help : Complex Output Format
                  user3308033
                  Hi there,

                  In the output, when you add up ACCAMT for the Financial Year i.e. 1st Apr 2011 to 31st Mar, 2012, the Total comes to 27775 but the FY_AMT which is the Cummulative sum of ACCAMT for the Financial Year shows 27771. Both should match.

                  Thanks
                  • 21. Re: Help : Complex Output Format
                    user3308033
                    Hi there,

                    In the output, when you add up ACCAMT for the Financial Year i.e. 1st Apr 2011 to 31st Mar, 2012, the Total comes to 27775 but the FY_AMT which is the Cummulative sum of ACCAMT for the Financial Year shows 27771. Both should match.

                    Thanks
                    • 22. Re: Help : Complex Output Format
                      Himanshu Binjola
                      Corrected the query above.
                      Balances becomes 0 or (-) or when the days difference is 0, the ACCAMT should be 0.
                      Note: I have exlcuded addition of balances to cumm or financial amount calcuation when balance <=0 or days = 0. The transaction for date 29-MAR-2012 produces balance of -4 which we showed in balance as 0 and ignored in cacluation for cumulative and fin amount calculation
                      • 23. Re: Help : Complex Output Format
                        user3308033
                        Hi there,

                        That's very right. You have hit the nail right on the head. That's the output required. Now, how about correcting the FY_AMT (which shows all 27775) with the Financial Year wise cummulative sum of ACCAMT ?

                        Thanks
                        • 24. Re: Help : Complex Output Format
                          Himanshu Binjola
                          FY_AMT is shown as requested in your earlier post. Can you display the type of output you require.

                          Please see my earlier post. Is output same as requested?
                          • 25. Re: Help : Complex Output Format
                            user3308033
                            Hi

                            The FY_AMT should be the financial year-wise cummulative sum of ACCAMT i.e. at the change of financial year, the FY_AMT should be re-cummulated. The only difference between FY_AMT and CUMM field is that FY_AMT cummulation with the change in the year whereas the CUMM does not till the Balance becomes 0. For example : FY_AMT should be : 6695 ; 6695+5581; 6695+5581+5006 and so on for financial year 2011-12.

                            Thanks
                            • 26. Re: Help : Complex Output Format
                              Himanshu Binjola
                              The SQL above calculate the FY_AMT year on year ( as aggregate for complete year) does not show cumulate row by row. The FY_AMT will also change year on year but since currently the sample data has only 2012 as financial year it does not changes.

                              I am repeating again you display the output you need if the above does not match your requirement.
                              • 27. Re: Help : Complex Output Format
                                user3308033
                                Hi,

                                In order to explain my point, I take up a different case with the output produced by your Query.
                                APPDATE          DR          CR          BALANCE          DAYS     ACCAMT     FY_AMT     CUMM     REQ_CUMM
                                04/03/11     50,000.00     0          50,000.00     28.00     364.00     364.00     364.00     364.00
                                01/04/11     0          0          50,000.00     27.00     350.00     794.00     350.00     714.00
                                28/04/11     0          1000          49,000.00     29.00     369.00     794.00     719.00     1083.00
                                27/05/11     0          1000          48,000.00     6.00     75.00     794.00     794.00     1158.00
                                02/06/11     0          48,000.00     0          0.00     0.00     794.00     794.00     1158.00
                                02/06/11     0          1161          0          0.00     0.00     0.00     794.00     1158.00
                                The column REQ_CUMM shows the actually required CUMM value. Any thoughts.

                                Thanks
                                • 28. Re: Help : Complex Output Format
                                  user3308033
                                  Hi there,

                                  Any clues getting REQ_CUMM in the Query.

                                  Thanks
                                  • 29. Re: Help : Complex Output Format
                                    Himanshu Binjola
                                    SELECT 
                                            -- APPDATE
                                           TO_CHAR (tab_dtl_vw.appdate, 'DD/MM/YY') appdate,
                                           -- DR
                                           CASE
                                               WHEN round(tab_dtl_vw.dr,2) < 10000 THEN to_char(round(tab_dtl_vw.dr,2))
                                               ELSE TO_CHAR(round(tab_dtl_vw.dr,2),'FM99,99,99,99,99,99,99,999.00')
                                           END dr,
                                           -- CR
                                           CASE
                                               WHEN round(tab_dtl_vw.cr,2) <10000 THEN to_char(round(tab_dtl_vw.cr,2))
                                               ELSE TO_CHAR(round(tab_dtl_vw.cr,2),'FM99,99,99,99,99,99,99,999.00')
                                           END cr,
                                           -- balance
                                           CASE
                                               WHEN round(tab_dtl_vw.balance,2) <10000 THEN to_char(round(tab_dtl_vw.balance,2))
                                               ELSE TO_CHAR(round(tab_dtl_vw.balance,2),'FM99,99,99,99,99,99,99,999.00')
                                           END balance,
                                           -- days
                                           tab_dtl_vw.days,
                                           -- accmt
                                           ROUND ((tab_dtl_vw.balance * tab_dtl_vw.days * tab_dtl_vw.rate) / (tab_dtl_vw.daysinyear*100)) accamt,
                                           -- fy_amt
                                           CASE WHEN fin_year_end_day IS NOT NULL THEN 
                                                SUM(ROUND((tab_dtl_vw.balance * tab_dtl_vw.days * tab_dtl_vw.rate) / (tab_dtl_vw.daysinyear*100))) 
                                                OVER(PARTITION BY fin_year ORDER BY tab_dtl_vw.appdate ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) 
                                            ELSE 0 
                                            END fy_amt,
                                           -- cumm
                                           SUM(ROUND((tab_dtl_vw.balance * tab_dtl_vw.days * tab_dtl_vw.rate) / (tab_dtl_vw.daysinyear*100))) 
                                           OVER(PARTITION BY fin_year ORDER BY tab_dtl_vw.appdate) cumm,
                                           -- req_cumm
                                           SUM(ROUND((tab_dtl_vw.balance * tab_dtl_vw.days * tab_dtl_vw.rate) / (tab_dtl_vw.daysinyear*100))) 
                                           OVER(ORDER BY tab_dtl_vw.appdate) req_cumm
                                    FROM
                                      ( SELECT appdate , 
                                                   CASE WHEN status = 'D' THEN amount   ELSE 0  END AS dr , 
                                                   CASE WHEN status = 'R' THEN amount  ELSE 0  END AS Cr , 
                                                   CASE WHEN (SUM (amount * CASE WHEN status = 'D' THEN 1 WHEN status = 'R' THEN -1 END) OVER (ORDER BY appdate)) <=0 THEN 0
                                                        ELSE SUM (amount * CASE WHEN status = 'D' THEN 1 WHEN status = 'R' THEN -1 END) OVER (ORDER BY appdate)
                                                    END balance ,
                                                   SUM (amount * CASE WHEN status = 'D' THEN 1 WHEN status = 'R' THEN -1 END) OVER (ORDER BY appdate) actualbalance ,
                                                   CEIL (LEAD(appdate,1,appdate) OVER (ORDER BY appdate) - appdate ) AS days ,
                                                   CEIL (LEAD(appdate) OVER (PARTITION BY fin_year ORDER BY appdate) - appdate ) AS fin_year_end_day ,
                                                   rate,
                                                   daysinyear,
                                                   fin_year,
                                                   status
                                           FROM (SELECT CASE WHEN (TO_CHAR(TRUNC(APPDATE),'DD-MON') = '31-MAR' AND APPDATE > TRUNC(APPDATE - 1/2 - 60/86400)) THEN TRUNC(APPDATE) +1 ELSE TRUNC(APPDATE) END appdate,
                                                        CASE  
                                                             WHEN (appdate >= TO_DATE('01-APR-' || TO_CHAR(appdate,'YYYY'),'DD-MON-YYYY')  AND 
                                                                TO_CHAR(LAST_DAY('01-FEB-' || (TO_CHAR(appdate,'YYYY') + 1)),'DD-MON') = '29-FEB') 
                                                             THEN 366
                                                        WHEN TO_CHAR(LAST_DAY('01-FEB-' || TO_CHAR(appdate,'YYYY')),'DD-MON') = '29-FEB'
                                                             THEN 366
                                                        ELSE 365
                                                        END daysinyear,
                                                        CASE  
                                                             WHEN (appdate >= TO_DATE('01-APR-' || TO_CHAR(appdate,'YYYY'),'DD-MON-YYYY')) THEN EXTRACT(YEAR FROM APPDATE) + 1
                                                             ELSE EXTRACT(YEAR FROM APPDATE)
                                                        END fin_year,
                                                        status,
                                                        amount,
                                                        rate
                                                  FROM  tab_dtl
                                                 ) 
                                         ) tab_dtl_vw
                                    ORDER BY tab_dtl_vw.appdate ASC
                                    
                                    {code}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
                                    1 2 Previous Next