Results 1 to 2 of 2

Thread: Decode or CASE

  1. #1
    Join Date
    Jul 2003
    Location
    US
    Posts
    314

    Arrow Unanswered: Decode or CASE

    Hi
    I have a weird query to write.
    I need immediate help.

    I have two dates in a table. date1 and date2. I have to group data in terms of difference of the two dates.

    select trunc((date1-date2)*24) AS diff, count(*) as cnt
    from sales_comm
    group by trunc((date1_date2)*24)

    The following are the ranges I am interested in

    1. 0-1
    2. 1-2
    3. 2-3
    4. 3-6
    5. 6-12
    6. 12-36
    7. 36-72
    8. 72 +

    select trunc((date1-date2)*24) AS diff, count(*) as cnt
    from sales_comm
    group by
    decode (
    trunc((date1_date2)*24),
    0, '01',
    1, '12',
    2, '23',
    3, '34',
    ................
    }

    But since the number of integer value differences are 73, therefore writing decode wont be such a good idea.
    I am thinking of using CASE WHEN THEN instead of DECODE, but it is giving me errors.

    Can somebody help me out in this respect.

    Intended output is


    0-1 24
    1-2 45
    2-3 12
    3-6 98
    72+ 101
    Thanx and Regards
    Aruneesh

  2. #2
    Join Date
    Jul 2003
    Posts
    2,296
    you would not decode on the group by clause.

    your output you want is really confusing me.

    so if I run this code:
    PHP Code:
    SELECT TRUNC((SYSDATE-(SYSDATE-2))*24) AS diffCOUNT(*) AS cnt
    FROM dual
    GROUP BY TRUNC
    ((SYSDATE-(SYSDATE-2))*24);

          
    DIFF        CNT
    ---------- ----------
            
    48          1 
    what if the value is '12'?? do you want it as 6-12 or 12-24??
    you want to convert the DIFF column to what? '36-72'??

    if that is the case, try this:
    PHP Code:
    SELECT CASE WHEN diff BETWEEN  6 AND 11 THEN '6-12'
                
    WHEN diff BETWEEN 12 AND 35 THEN '12-36'
                
    WHEN diff BETWEEN 36 AND 72 THEN '36-72'
                
    WHEN diff 72              THEN '72 +' 
           
    END AS diff
           
    cnt 
    FROM 
    (
    SELECT TRUNC((SYSDATE-(SYSDATE-3))*24) AS diffCOUNT(*) AS cnt
    FROM dual
    GROUP BY TRUNC
    ((SYSDATE-(SYSDATE-2))*24)
    ); 
    - The_Duck
    you can lead someone to something but they will never learn anything ...

Posting Permissions

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