1 2 3 Previous Next 32 Replies Latest reply on Feb 2, 2014 1:25 AM by GuruKumar Go to original post
      • 30. Re: SQL Query
        GuruKumar

        Hi, Thanks Again,

         

        But in this cases its not working as expected,

        Insert into DOC_TAB
           (CREATED_DATE, EMPNO, AGREEMENT_NO, DOCUMENT_TYPE, STATUS,
            MODIFIED_DATE)
        Values
           (TO_DATE('03/25/2013 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 1099, 'B123', 'ccm', 'pending',
            TO_DATE('06/23/2013 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));

         

        Output expecting:

        current_month : pendingcnt 1
                        tota_cnt   1
        previous_month: pendingcnt 1
                        tota_cnt   1
        previous_fin_year:pendingcnt 1
                          tota_cnt   1

         

        Insert into DOC_TAB
           (CREATED_DATE, EMPNO, AGREEMENT_NO, DOCUMENT_TYPE, STATUS,
            MODIFIED_DATE)
        Values
           (TO_DATE('03/25/2013 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 1099, 'B123', 'ccm', 'pending',
            TO_DATE('03/30/2013 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));

        Output expected
        current_month : pendingcnt 1
                        tota_cnt   1
        previous_month: pendingcnt 1
                        tota_cnt   1
        previous_fin_year:pendingcnt 1
                          tota_cnt   1

        • 31. Re: SQL Query
          AnnEdmund

          If i understand correctly below query works for your requirement. Otherwise please provide all scenarios. Because from this data only i understood the requirement. So, if you are giving all kind of data i can understand even better and i can give you a correct query. Anyway below query works for everything with my understanding. Kindly check and let me know

          SELECT

               period

              ,empno

              ,SUM((CASE WHEN status='pending' AND document_type = 'aam' THEN 1 END) * VALUE) cnt_aam_pending

              ,SUM((CASE WHEN document_type = 'aam' THEN 1 END) * VALUE) cnt_aam_total

              ,SUM((CASE WHEN status='pending' AND document_type = 'ccm' THEN 1 END) * VALUE) cnt_ccm_pending

              ,SUM((CASE WHEN document_type = 'ccm' THEN 1 END) * VALUE) cnt_ccm_total

              FROM (

              SELECT

              empno

              ,document_type

              ,status

              ,COUNT( CASE WHEN (TRUNC(created_date, 'MM') BETWEEN TO_DATE('01/apr/2008') AND TRUNC(SYSDATE, 'MM'))

                           AND (TRUNC(CREATED_DATE,'MM') <= ADD_MONTHS(TRUNC(SYSDATE, 'MM'), -1)) THEN NULL

                           WHEN (TRUNC(created_date, 'MM') BETWEEN TO_DATE('01/apr/2008') AND TRUNC(SYSDATE, 'MM')) THEN 1

                           ELSE NULL END) cnt_act_month

              ,COUNT( CASE WHEN (TRUNC(created_date, 'MM') BETWEEN TO_DATE('01/apr/2008') AND ADD_MONTHS(TRUNC(SYSDATE, 'MM'), -1))

                           AND (TRUNC(CREATED_DATE,'MM') <= ADD_MONTHS(TRUNC(SYSDATE, 'MM'), -2)) THEN NULL

                           WHEN TRUNC(created_date, 'MM') BETWEEN TO_DATE('01/apr/2008') AND ADD_MONTHS(TRUNC(SYSDATE, 'MM'), -1) THEN 1

                           ELSE NULL END) cnt_prv_month

              ,COUNT( CASE WHEN TRUNC(created_date, 'MM') BETWEEN TO_DATE('01/apr/2012') AND TO_DATE('31/mar/2013') THEN 1 ELSE NULL END) cnt_fin_year

              FROM (SELECT CASE WHEN LEVEL = 1 THEN created_date

                                WHEN LEVEL = 2

                                     AND status = 'pending'

                                     AND TRUNC(modified_date,'MM') <= TRUNC(SYSDATE,'MM') THEN ADD_MONTHS(TRUNC(SYSDATE,'MM'),-1)

                                WHEN LEVEL = 3

                                     AND status = 'pending'

                                     AND TRUNC(modified_date,'MM') <= TRUNC(SYSDATE,'MM') THEN TRUNC(SYSDATE,'MM')

                                WHEN LEVEL = 2

                                     AND status = 'completed'

                                     AND TRUNC(modified_date,'MM') = TRUNC(SYSDATE,'MM') THEN ADD_MONTHS(TRUNC(SYSDATE,'MM'),-1)

                           ELSE modified_date

                           END AS created_date,

                           empno,

                           agreement_no,

                           document_type,

                           CASE WHEN (TRUNC(modified_date,'MM') = TRUNC(SYSDATE,'MM')

                                AND LEVEL IN (1,2)) THEN 'pending'

                                 ELSE status

                           END AS status

                    FROM doc_tab

                    WHERE (TRUNC(created_date,'MM') <> TRUNC(modified_date,'MM')

                          OR (TRUNC(created_date,'MM') = TRUNC(modified_date,'MM')

                              AND status = 'pending'))

                    CONNECT BY LEVEL <= CASE WHEN (TRUNC(modified_date,'MM') = TRUNC(SYSDATE,'MM'))

                                             OR (TRUNC(modified_date,'MM') <= ADD_MONTHS(TRUNC(SYSDATE,'MM'),-1)

                                             AND status = 'pending') THEN 3

                                        ELSE 2

                                        END

                    AND PRIOR agreement_no = agreement_no

                    AND PRIOR document_type = document_type

                    AND PRIOR DBMS_RANDOM.VALUE IS NOT NULL

                    UNION ALL

                    SELECT created_date,

                           empno,

                           agreement_no,

                           document_type,

                           status

                    FROM doc_tab

                    WHERE TRUNC(created_date,'MM') = TRUNC(modified_date,'MM')

                    AND status = 'completed')

              GROUP BY empno,document_type,status

              )

              unpivot (

              VALUE

              FOR period IN (cnt_act_month,cnt_prv_month,cnt_fin_year)

              )

              GROUP BY period, empno

              ORDER BY DECODE(period,'CNT_ACT_MONTH',1,'CNT_PRV_MONTH',2,3), empno;

          • 32. Re: SQL Query
            GuruKumar

            Hi,         Yes now its working for all scenarios, Thanks a lot...

             

             

            You are so passionate and dedicated on providing solutions on this OTN, I never seen like you, can i get your mail id , always i want to be friend like this fellows... ...You are so quick and going to reach Expert Level.... Congrats..

             

            Thanks & Regards

              Gururkumar

            1 2 3 Previous Next