Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2006
    Posts
    140

    Red face Unanswered: Need help with last 4 Weeks query

    Hello,

    I need some help on querying the last 4 weeks of data (not in the where clause but in a case statement) where the weeks start from Monday at 12:00PM - Monday at 11:59:59 PM.

    This is what I have but I don't know how to change my weekdays to accomodate the Mon-Mon rather then Sunday-Sat.

    Code:
    (CASE WHEN Date_Convert(Date) BETWEEN trunc(sysdate, 'DAY')-20+(12/24)
    AND trunc(sysdate,'DAY')+1+(12/24) THEN Date_Convert(Date) ELSE NULL END)Last4WeeksStartWeek
    Thanks again.

  2. #2
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    I dont understand the requirement at all. I suggest you put some sample data (with create tables and insert intos as well) and the result you want.

  3. #3
    Join Date
    Apr 2006
    Posts
    140

    Red face

    O.k J Martinez let me explain. What I want to see is this

    Date_Convert(Date) Last4WeeksStart
    1/22/2008 2:30:38AM 1/21/2008 12:00:00PM
    1/23/2008 2:30:38AM 1/21/2008 12:00:00PM
    1/24/2008 2:30:38AM 1/21/2008 12:00:00PM
    1/26/2008 12:30:38AM 1/21/2008 12:00:00PM
    1/22/2008 2:30:38AM 1/21/2008 12:00:00PM
    1/27/2008 3:12:01AM 1/21/2008 12:00:00PM
    1/28/2008 1:49:51PM 1/28/2008 12:00:00PM

    Right now my query is showing, for some records, the Last4WeeksStart as the week of the 28th which it shouldn't because the week range is Monday - Monday. So for example

    Date_Convert(Date) Last4WeeksStart
    1/26/2008 12:30:38AM 1/28/2008 12:00:00PM (should still be 1/21/2008)
    1/27/2008 2:30:38AM 1/28/2008 12:00:00PM (should still be 1/21/2008)
    1/27/2008 3:12:01AM 1/28/2008 12:00:00PM(should still be 1/21/2008)
    1/28/2008 1:49:51PM 1/28/2008 12:00:00PM(should still be 1/21/2008)

    However, this isn't correct and should be displayed as above. I'm assuming this is happening because oracle thinks the work week is done, however, I want my work week to be Mon-Mon. Please let me know if that makes sense. Thanks

  4. #4
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    I still can't understand. Perhaps someone else can help.

  5. #5
    Join Date
    Dec 2003
    Posts
    1,074
    Code:
    with qry as (select sysdate-5 as dt from dual union all
                 select sysdate-4 as dt from dual union all
                 select sysdate-3 as dt from dual union all 
                 select sysdate-2 as dt from dual union all 
                 select sysdate-1 as dt from dual union all
                 select sysdate as dt from dual union all
                 select sysdate+1 as dt from dual union all
                 select sysdate+2 as dt from dual union all
                 select sysdate+3 as dt from dual )
    select dt, 
           CASE when trim(to_char(dt,'DAY')) = 'MONDAY' Then dt		 
                when trim(to_char(dt,'DAY')) = 'SUNDAY' Then (dt - 6)
                else (dt - to_number(to_char(dt-2,'D')))				 
           END as f1
    from qry
    gives you

    Code:
    DT        F1       
    --------- ---------
    17-FEB-08 11-FEB-08
    18-FEB-08 18-FEB-08
    19-FEB-08 18-FEB-08
    20-FEB-08 18-FEB-08
    21-FEB-08 18-FEB-08
    22-FEB-08 18-FEB-08
    23-FEB-08 18-FEB-08
    24-FEB-08 18-FEB-08
    25-FEB-08 25-FEB-08

  6. #6
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    First of all, beware of the 'D' format that depends on NLS_TERRITORY.

    Secondly, TRUNC does this very efficiently :
    Code:
    with qry as (select sysdate-7 as dt from dual union all
                 select sysdate-6 as dt from dual union all
                 select sysdate-5 as dt from dual union all
                 select sysdate-4 as dt from dual union all
                 select sysdate-3 as dt from dual union all 
                 select sysdate-2 as dt from dual union all 
                 select sysdate-1 as dt from dual union all
                 select sysdate as dt from dual union all
                 select sysdate+1 as dt from dual union all
                 select sysdate+2 as dt from dual union all
                 select sysdate+3 as dt from dual union all
                 select sysdate+4 as dt from dual union all
                 select sysdate+5 as dt from dual )
    select dt, 
           TRUNC(dt, 'D') as f1
    from qry
    Code:
    rbaraer@Ora10g> alter session set nls_territory='FRANCE';
    
    Session altered.
    
    rbaraer@Ora10g> with qry as (select sysdate-7 as dt from dual union all
                 select sysdate-6 as dt from dual union all
      2               select sysdate-5 as dt from dual union all
      3    4               select sysdate-4 as dt from dual union all
      5               select sysdate-3 as dt from dual union all
      6               select sysdate-2 as dt from dual union all
      7               select sysdate-1 as dt from dual union all
      8               select sysdate as dt from dual union all
      9               select sysdate+1 as dt from dual union all
     10               select sysdate+2 as dt from dual union all
     11               select sysdate+3 as dt from dual union all
     12               select sysdate+4 as dt from dual union all
     13               select sysdate+5 as dt from dual )
     14  select dt,
     15         TRUNC(dt, 'D') as f1
     16  from qry;
    
    DT       F1
    -------- --------
    21/02/08 18/02/08
    22/02/08 18/02/08
    23/02/08 18/02/08
    24/02/08 18/02/08
    25/02/08 25/02/08
    26/02/08 25/02/08
    27/02/08 25/02/08
    28/02/08 25/02/08
    29/02/08 25/02/08
    01/03/08 25/02/08
    02/03/08 25/02/08
    03/03/08 03/03/08
    04/03/08 03/03/08
    
    13 rows selected.
    
    rbaraer@Ora10g>
    Regards,

    rbaraer
    ORA-000TK : No bind variable detected... Shared Pool Alert code 5 - Nuclear query ready .

Posting Permissions

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