Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Apr 2006
    Posts
    140

    Red face Unanswered: Is this even possible or should I call it a day

    Using oracle 9.2 and trying to construct a sql query that has the following. I will show the logic/code that I am trying to do and then explain.

    Code:
    WHERE 
        trunc(field a) BETWEEN 
        (trunc(sysdate,'DAY')-6) and (trunc(sysdate,'DAY')-2)
    AND
        trunc(field a) BETWEEN (trunc(sysdate + 90)) and
        (trunc(sysdate + 90,'DAY'))+ 5;
    I am trying to say within last week Monday to Friday date range, show me how many requests were greater then 90 days but only limit it to the end of the week of the beginning of that 90 day day because there may be hundreds of requests.

    So last week May 21-25/07
    90 days from may 21 = Lets just say August 21/07
    August 21/07 falls on a Tuesday so show everything from Tuesday to Friday
    There may be 300 entries that are greater then August 21/07 but I only want to capture anything till the end of the week of August 21/07. This will obviously change depending on what day the 90 days falls on. Am I making sense. So basically I am comparing everything that is happening in one week to a furture week so to speak.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    MrhelpMe,
    What is stopping you from create a TESTER table with limited columns & populate it with limited test data so YOU can confirm that your SELECT statement doing what you need it to do?
    Please make the effort to HELP yourself!
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Apr 2006
    Posts
    140

    Red face

    Well anacedent I didn't chose my words wisely. You are right only because I did not say in my post that I did try the code provided and no success. I always do check before I post anacedent...to be quite honest I hate posting So to answer the question I did try and received no data returned which is not correct. I did a compare of last week's start date to the greater then 90 day and received 20 requests. So something is missing.

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    So post your code; post the CREATE TABLE SQL; post INSERT of test data so we can see EXACTLY what is or is not happening
    Yes, you have a problem. Exactly what is To Be Determined.
    Please make it easy for folks to assist with the solution.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Apr 2006
    Posts
    140
    I made some changes to my code

    Code:
     
       SELECT Ticket_ID AS Ticket,
              (CASE Status
                    when 0 then 'New'
                    when  1 then 'Active'
                    when  2 then 'Working'
                    when  3 then 'resolved'
                    when  4 then 'Closed'
               END)Status,
               (nvl(fName, 'N/A')) AS FName, 
               (nvl(offe, 'N/A')) AS Checked,
                (trunc(sysdate + 90)) AS StartDate,
               (trunc(sysdate + 90,'DAY'))+ 5 AS EndDate,
               (nvl(Beez, 'N/A')) AS Bsiness
    from Table1
    WHERE 
        trunc(date) BETWEEN 
        (trunc(sysdate,'DAY')-6) and (trunc(sysdate,'DAY')-2)
    AND
        trunc(date) BETWEEN (trunc(sysdate + 90)) and
        (trunc(sysdate + 90,'DAY'))+ 5;
    What I get are all the records that fall between the where and the AND and then all the ones that fall after the AND. This is not what I want is I just need all the records(count) for the last week without counting those dates in the results that are between the second AND.

  6. #6
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >without counting those dates in the results that are between the second AND.
    Possibly a place where MINUS can/should be used
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  7. #7
    Join Date
    Jan 2004
    Posts
    370
    Instead of:

    Code:
    WHERE 
        trunc(field a) BETWEEN 
        (trunc(sysdate,'DAY')-6) and (trunc(sysdate,'DAY')-2)
    AND
        trunc(field a) BETWEEN (trunc(sysdate + 90)) and
        (trunc(sysdate + 90,'DAY'))+ 5;


    Try:

    Code:
    WHERE 
        trunc(field a) BETWEEN 
        (trunc(sysdate,'DAY')-6) and (trunc(sysdate,'DAY')-2)
    OR
        trunc(field a) BETWEEN (trunc(sysdate + 90)) and
        (trunc(sysdate + 90,'DAY'))+ 5;

  8. #8
    Join Date
    Apr 2006
    Posts
    140

    Red face

    Skywriter thanks for the response. I have been working on this all night and before you even posted had changed my AND to OR but I am still struggling with understanding if this is what I want Could you confirm if this is saying that for last week(Mon-Friday) there were total number(I will perform a count) requests that are greater then 90 days ending on the Friday?

  9. #9
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    If I understand what you want, try the following

    WHERE TRUNC(FIELD) BETWEEN next_day( TRUNC(SYSDATE)-6 ,'MON')+83 AND NEXT_DAY(next_day( TRUNC(SYSDATE)-6 ,'MON')+83,'fri')
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  10. #10
    Join Date
    Apr 2006
    Posts
    140

    Smile

    Wow beilstwh,

    I really don't know how you understood me because I didn't even understand myself I think this is exactly what I wanted. I was just playing around with your syntax trying to understand it and just one last question(well I hope). Should your last syntax not be
    Code:
    NEXT_DAY(next_day( TRUNC(SYSDATE)-2 ,'FRI')+83,'fri')
    Only because I want to see everything for last week(Mon-Fri) that is 90 days old and that does not exceed 90 days of the Friday of Last week. I like your code. Thanks again

  11. #11
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    The -6 goes back 6 days and then finds the very next monday and adds 83 days. If the code is run on a monday then it will use that monday to add the 83 to. If you say -2, and run it on (for example) thursday, you will start on the monday of the following week. However my code did have a problem, it should have been -8 instead of -6. See the following.

    >select next_day(trunc(sysdate)-7,'fri') from dual;

    NEXT_DAY(
    ---------
    01-JUN-07

    >select next_day(trunc(sysdate)-8,'fri') from dual
    rfetime@etimedb>/

    NEXT_DAY(
    ---------
    25-MAY-07

    If you always want it to always go back to the previous monday, use -8 if you want it to go to the current day if it is already a monday then use -7.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  12. #12
    Join Date
    Apr 2006
    Posts
    140
    I think you got it Beilstwh. According to what you are saying it should give me all requests greater than 90 days for the previous full week(Mon-fri). Thank you so much and apologies to the forum if I was unclear. I think the request didn't make sense to me so I became unclear in communicating. Thanks again.

  13. #13
    Join Date
    Apr 2006
    Posts
    140

    Red face

    BEILSTWH,

    I reviewed the code again and this seems to be wrong. All this is giving me is everything between 19-AUG-07 and 24-AUG-07. This is not what I want.
    I want all requests from just last week(or whatever last week dynamically turns out to be) that are greater than 90 days but don't include requests after 24-Aug-07 or whatever NEXT_DAY(next_day( TRUNC(SYSDATE)-8 ,'MON')+83,'fri') value works out. Any ideas?

  14. #14
    Join Date
    Apr 2006
    Posts
    140
    I think I am going somewhere with this everyone,

    I tried a different strategy and got this
    Code:
    Select 
    sum(CASE when Date between (trunc(sysdate,'DAY')-6) and next_day( TRUNC(SYSDATE)-12 ,'MON') + 90 then 1 else 0 
    END)AS "Mon",
    sum(CASE when date between (trunc(sysdate,'DAY')-5) and next_day( TRUNC(SYSDATE)-12 ,'Tues') + 90 then 1 else 0 
    END)AS "Tues",
    sum(CASE when date between (trunc(sysdate,'DAY')-4) and next_day( TRUNC(SYSDATE)-12 ,'Wed') + 90 then 1 else 0 
    END)AS "Wed",
    sum(CASE when date between (trunc(sysdate,'DAY')-3) and next_day( TRUNC(SYSDATE)-12 ,'Thurs') + 90 then 1 else 0 
    END)AS "Thurs", 
    sum(CASE when date between (trunc(sysdate,'DAY')-2) and next_day( TRUNC(SYSDATE)-12 ,'Fri') + 90 then 1 else 0 
    END)AS "Fri" 
    from table;
    Then at the end of this I will sum all the days. Can anyone see anything wrong with this?

  15. #15
    Join Date
    Jan 2004
    Posts
    370
    OK, I think I understand what you are looking for. All days within a 90 day period starting on the previous Monday and ending on the last Friday of the 90 day period? I think you just need to adjust beilstwh's code to not add 83 at the start of the period.

    between next_day( TRUNC(SYSDATE)-8 ,'MON') and NEXT_DAY(next_day( TRUNC(SYSDATE)-8 ,'MON')+83,'fri')
    I think it just needs to be tweaked a little to ensure you go back far enough to catch the correct start and end days.

    between next_day( TRUNC(SYSDATE)-(8 + to_char(sysdate,'d')) ,'MON')
    and NEXT_DAY(next_day( TRUNC(SYSDATE)-(8 + to_char(sysdate,'d')) ,'MON')+83,'fri')
    I've let the implicit type conversion convert the TO_CHAR function

Posting Permissions

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