3 Replies Latest reply: Jul 10, 2014 8:53 AM by ascheffer RSS

    Greater then days count 3

    Vedant

      Hello Friends,

       

      i have create report using decode .Below are report query

       

      select

      REGION, COUNTRY, PORT_ID,QUESTIONNAIRE_SCOPE,

      DECODE(trunc(SYSDATE) -trunc(TARGET_DATE), 1, SUM(decode(QUESTIONNAIRE_TYPE ,'TRA',1,0)), 0) TRA1,

      DECODE(trunc(SYSDATE) -trunc(TARGET_DATE), 1, SUM(decode(QUESTIONNAIRE_TYPE ,'SHX',1,0)), 0) SHX1,

      DECODE(trunc(SYSDATE) -trunc(TARGET_DATE), 1, SUM(decode(QUESTIONNAIRE_TYPE ,'HTP',1,0)), 0) HTP1,

      DECODE(trunc(SYSDATE) -trunc(TARGET_DATE), 1, SUM(decode(QUESTIONNAIRE_TYPE ,'PAG',1,0)), 0) PAG1,

      DECODE(trunc(SYSDATE) -trunc(TARGET_DATE), 1, SUM(decode(QUESTIONNAIRE_TYPE ,'TRP',1,0)), 0) TRP1,

      DECODE(trunc(SYSDATE) -trunc(TARGET_DATE), 2, SUM(decode(QUESTIONNAIRE_TYPE ,'TRA',1,0)), 0) TRA2,

      DECODE(trunc(SYSDATE) -trunc(TARGET_DATE), 2, SUM(decode(QUESTIONNAIRE_TYPE ,'SHX',1,0)), 0) SHX2,

      DECODE(trunc(SYSDATE) -trunc(TARGET_DATE), 2, SUM(decode(QUESTIONNAIRE_TYPE ,'HTP',1,0)), 0) HTP2,

      DECODE(trunc(SYSDATE) -trunc(TARGET_DATE), 2, SUM(decode(QUESTIONNAIRE_TYPE ,'PAG',1,0)), 0) PAG2,

      DECODE(trunc(SYSDATE) -trunc(TARGET_DATE), 2, SUM(decode(QUESTIONNAIRE_TYPE ,'TRP',1,0)), 0) TRP2,

      DECODE(trunc(SYSDATE) -trunc(TARGET_DATE), 3, SUM(decode(QUESTIONNAIRE_TYPE ,'TRA',1,0)), 0) TRA3,

      DECODE(trunc(SYSDATE) -trunc(TARGET_DATE), 3, SUM(decode(QUESTIONNAIRE_TYPE ,'SHX',1,0)), 0) SHX3,

      DECODE(trunc(SYSDATE) -trunc(TARGET_DATE), 3, SUM(decode(QUESTIONNAIRE_TYPE ,'HTP',1,0)), 0) HTP3,

      DECODE(trunc(SYSDATE) -trunc(TARGET_DATE), 3, SUM(decode(QUESTIONNAIRE_TYPE ,'PAG',1,0)), 0) PAG3,

      DECODE(trunc(SYSDATE) -trunc(TARGET_DATE), 3, SUM(decode(QUESTIONNAIRE_TYPE ,'TRP',1,0)), 0) TRP3

      from SURVEYS_ALL

      where trunc(SYSDATE) -trunc(TARGET_DATE) <= 3

      AND STATE='ACTIVE'

      --AND TO_CHAR(START_DATE,'MON')=TO_CHAR(SYSDATE,'MON')

      --AND TO_CHAR(START_DATE,'YYYY')=TO_CHAR(SYSDATE,'YYYY')

      group by REGION, COUNTRY, PORT_ID,QUESTIONNAIRE_SCOPE,trunc(SYSDATE) -trunc(TARGET_DATE)


      Here i count 1,2 and 3 days missing survey which are not fill by user .Now it count only till 3 ,Now i need if differance between trunc(SYSDATE) -trunc(TARGET_DATE) are greater then or equal to 3 then it count in below section


      DECODE(trunc(SYSDATE) -trunc(TARGET_DATE), 3, SUM(decode(QUESTIONNAIRE_TYPE ,'TRA',1,0)), 0) TRA3,

      DECODE(trunc(SYSDATE) -trunc(TARGET_DATE), 3, SUM(decode(QUESTIONNAIRE_TYPE ,'SHX',1,0)), 0) SHX3,

      DECODE(trunc(SYSDATE) -trunc(TARGET_DATE), 3, SUM(decode(QUESTIONNAIRE_TYPE ,'HTP',1,0)), 0) HTP3,

      DECODE(trunc(SYSDATE) -trunc(TARGET_DATE), 3, SUM(decode(QUESTIONNAIRE_TYPE ,'PAG',1,0)), 0) PAG3,

      DECODE(trunc(SYSDATE) -trunc(TARGET_DATE), 3, SUM(decode(QUESTIONNAIRE_TYPE ,'TRP',1,0)), 0) TRP3


      How can i do this


      Thanks.


        • 1. Re: Greater then days count 3
          BluShadow

          It's not clear what your issue is.

           

          Please can you post some example data and expected output (use CREATE TABLE and INSERT statements so we can put it on our own database).

           

          Please read: Re: 2. How do I ask a question on the forums?

          • 2. Re: Greater then days count 3
            Frank Kulash

            Hi,

             

            As Blushadow said, you should always post CREATE TABLE and INSERT statements for a little sample data.

             

            Perhaps you want something like this:

             

            WITH    relevant_data    AS

            (

                SELECT  region, country, port_id, questionnaire_scope, questionnaire_type

                ,       GREATEST ( TRUNC (SYSDATE) - TRUNC (target_date)

                                 , 3

                                 )    AS days_ago

                FROM    surveys_all

                WHERE   target_date         <= TRUNC (SYSDATE) + 1

                AND     questionnaire_type  IN ('TRA, 'SHX', 'HTP', 'PAG', 'TRP')

            )

            SELECT    *

            FROM      relevant_data

            PIVOT     (    COUNT (*)

                      FOR (questionnaire_type, days_ago)

                      IN  ( ('TRA', 1)  AS tra1

                          , ('SHX', 1)  AS shx1

                          , ('HTP', 1)  AS htp1

            --            ...

                          , ('PAG', 3)  AS pag3

                          , ('TRP', 3)  AS trp3

                          )

                      )

            ;

            Of course, I can't test it without the table.

            • 3. Re: Greater then days count 3
              ascheffer

              sum( case when trunc(SYSDATE) - trunc(TARGET_DATE) >= 3 and QUESTIONNAIRE_TYPE = 'TRP' then 1 else 0 end ) TRP3orMore