Results 1 to 15 of 15
  1. #1
    Join Date
    Dec 2001
    Posts
    21

    Unanswered: Attempting to do something very complicated with dates - HELP!

    Ok, so I have a table which has two values in it. A date, and an interval. The interval is one of {1month,3months,6months,12months}.

    I have a cron job which runs every MONDAY and calls an SQL to find a set of records in this table. The criteria is

    "The set of records such that sysdate is between 7 and 13 days before the end of interval X after the seed date in the table."

    For example, if the seed date is 4/6/2009 and the interval is three months:

    - 3 months after 4/6/2009 is 7/6/2009 (approximately - I know it's hard to handle the number of days in each specific month, leap years, etc)

    - Looking between 7 and 13 days before 7/6/2009, the only Monday is the 29th, so this is the day that I need to identify.

    I know that I need to do something with modulo in my query, but I cannot for the life of me get it to work.

    If the seed date column is called seed_date and the interval is called interval, I would need SOMETHING like this

    select
    case when
    mod(sysdate-seed_date, interval * {number of days in a month}) between 7 and 13 then 'Y' else 'N'
    end

    Can anyone help me with this calculation!?

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >I know that I need to do something with modulo in my query,
    Based upon what?

    I hope you realize that part of the problem is your 2 column table;
    or more specifically the interval column.
    If the table contained a DATE datatype with the actual end date, this problem would be easily solved.

    If you can not or will not change this table then CREATE VIEW which contains both the start date & actual end date.
    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
    Dec 2001
    Posts
    21
    I don't have any problem with creating a view, but I still need to do the calculation. That is, I still have to figure out if "right now" is 7-13 days before the end of ANY interval.

    I can't get my users to just enter the date for each interval (they only want to enter the "seed" date) so i still have to figure it out... Am I being totally unclear? LOL

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    First learn to walk, before trying to run.
    Develop SQL which will compute the END_DATE of the interval based upon the seed date & period duration.

    Post DDL for table.
    Post DML for test data.

    Post SQL for view which contains both START_DATE & END_DATE.
    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
    Dec 2001
    Posts
    21
    In order to simplify the problem I have greatly oversimplified the structure. Ok, so the "seed date" and the "interval" are stored in studyid_id in this table which stores an unlimited set of "attributes" associated with "studies" and referenced by fk_study. The fk_codelst_idtype column stores the type of attribute. Studies are stored in a table called eres.er_study, the primary key for which is pk_study and fk_study in studyid is a fkey to pk_study in er_study table.

    CREATE TABLE ER_STUDYID
    (
    PK_STUDYID NUMBER primary key,
    FK_STUDY NUMBER,
    FK_CODELST_IDTYPE NUMBER,
    STUDYID_ID VARCHAR2(100 BYTE)
    )


    test data:

    insert into eres.er_studyid (pk_studyid, fk_study, fk_codelst_idtype, studyid_id) values
    (seq_er_studyid.nextval,13,276,"3months")

    insert into eres.er_studyid (pk_studyid, fk_study, fk_codelst_idtype, studyid_id) values
    (seq_er_studyid.nextval,13,400,"4/13/2008")


    insert into eres.er_studyid (pk_studyid, fk_study, fk_codelst_idtype, studyid_id) values
    (seq_er_studyid.nextval,28,276,"1month")

    insert into eres.er_studyid (pk_studyid, fk_study, fk_codelst_idtype, studyid_id) values
    (seq_er_studyid.nextval,28,400,"9/28/2007")

    The select statement that I am using that is, so far, not working the way I need it to is roughly this:

    select case when days_between >=7 and days_between <=13 then 'Y' else 'N' end as in_period, pk_study
    from
    (
    --secondmost
    select pk_study,
    mod(trunc(sysdate-seed_date),trunc(30.4 * decode(interval,'12months',12,'1month',1,'3months' ,3,'6months',6)) ) as days_between
    from
    (
    --innermost query
    select pk_study
    (select studyid_id from eres.er_studyid where fk_study = pk_study and
    fk_codelst_idtype = 276) as interval,
    (select to_date(studyid_id,'MM/DD/YYYY') from eres.er_studyid where fk_study = pk_study and fk_codelst_idtype = 400) as seed_date
    from
    eres.er_study
    --end innermost
    )
    --end secondmost
    )

    The problem is that I'm not just trying to calculate an end date from a start date. I am trying to calculate whether sysdate is within 7-13 before the end of interval times X where X is any whole integer...

  6. #6
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    This "design" GREATLY complicates producing any solution.
    Code:
    CREATE TABLE er_studyid ( 
      pk_studyid        NUMBER    PRIMARY KEY, 
      fk_study          NUMBER, 
      fk_codelst_idtype NUMBER, 
      studyid_id        VARCHAR2(100 BYTE))
    I was hoping/expecting something similar to:
    Code:
    CREATE TABLE er_studyid ( 
      pk_studyid        NUMBER    PRIMARY KEY, 
      fk_study          NUMBER, 
      fk_codelst_idtype NUMBER, 
      base_date         DATE, 
      studyid_interval  VARCHAR2(100 BYTE))
    By having the start date in a VARCHAR in a separate record is POOR design.
    By having start date in VARCHAR2 is POOR design.
    Keep in mind that rows in a table have no inherent order.
    If this can be done in plain SQL, it will not be easy or pretty.
    I decline to continue this abomination.
    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
    Dec 2001
    Posts
    21
    I agree with you wholeheartedly on the design. But this is a third-party vendor package and I cannot change the design. The studyid table is a table which holds user-defined variables which can be any datatype. In order to handle this, they just make the datatype of that field varchar so that explicit datatype conversions could be done later. Imperfect? Yes. But that's what I have to work with.

    I realize it will not be easy or pretty, that's why I asked for help!

    The fact remains that I have users that are holding ME responsible for making this extraction work, and I cannot change the structure.

    Anyone else want to give it a try?

  8. #8
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Code:
    select a.fk_study,add_months(to_date(a.studyid_id,'mm/dd/yyyy'),to_number(rtrim(b.studyid_id,'months')))
    from er_studyid a,
    er_studyid b
    where a.fk_study = b.fk_study
    and a.fk_codelst_idtype=400
    and b.fk_codelst_idtype=276;
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  9. #9
    Join Date
    Dec 2001
    Posts
    21
    That gets me part of the way there. This tells me if sysdate is one and exactly one "interval" past the "seed" date. But what if I am with 7 and 13 days of the second interval? Or the third?

    in order words I need to do something like this:

    case
    when sysdate between
    add_months(seed_date, X * decode(interval,'1month',1,'3months',3,'6months',6 ,'12months',12)) -13
    and
    add_months(seed_date, X * decode(interval,'1month',1,'3months',3,'6months',6 ,'12months',12)) -7
    then 'Y' else 'N' end

    Where X is any whole integer. Your query gets me only where X = 1. But I need to return "Y" for ANY number of intervals... Does that make any sense?

  10. #10
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Not true. The rtrim strips out the word months and then the to_number converts the string to a number. then it uses add_months to go out the number of months that you wanted.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  11. #11
    Join Date
    Dec 2001
    Posts
    21
    Maybe I'm not being clear. I'm not attempting to find some date X months in the future. That would be very easy. Let me step through an example.

    Say the seed date is 4/12/2006 and the interval is every 3 months. That gives me a pattern of (roughly):

    7/12/2006
    10/12/2006
    1/12/2007
    4/12/2007
    7/12/2007
    10/12/2007

    and so on. The SQL that I want will tell me if sysdate is between 7 and 13 days before ANY of the dates in the pattern above. I'm not asking "when is the next date in the pattern from today" but rather "is today between 7 and 13 days before ANY date in the sequence"

    Is that any clearer?

  12. #12
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >and so on.
    This "requirement" is somewhat nonsensical as it does to infinity & never completes.
    In my opinion, some finite upper bound needs to exist or be specified?
    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.

  13. #13
    Join Date
    Dec 2001
    Posts
    21
    It will be specified, probably once it has passed. There is a flag in another table that says "stop looking."

    It's amazing, but business processes are OFTENTIMES nonsensical but software systems must nonetheless be made to model them. This is the user requirement and I must meet it.

  14. #14
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Nothing is impossible for the person who does not have to do it.
    You better start writing a PL/SQL procedure to produce the desired results.
    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.

  15. #15
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Hi,

    Does this help... a couple of approaches, one using an approximation of a month which is a more efficient but less accurrate calculation, and a second using a precise month which is far less efficient. For the second approach you might want to make sure that (count(all_objects) / smallest_interval_in_days) will cover as far into the future as needed.

    Code:
    create table seeds 
       (
       seed date,
       mth_interval number(2)
       );
       
    insert into seeds (seed,mth_interval) 
       (
       select trunc(sysdate-(1095+rownum)) rn,
              decode(mod(rownum,5),0,1,1,3,2,6,3,9,4,12)
       from   all_objects
       where  rownum < 366
       )    
       
    select * from seeds order by seed   
    
    SEED        MTH_INTERVAL
    =========   ============
    28/10/2005	1
    29/10/2005	12
    30/10/2005	9
    31/10/2005	6
    01/11/2005	3
    ...
    23/10/2006	1
    24/10/2006	12
    25/10/2006	9
    26/10/2006	6
    27/10/2006	3
    
    -- Using an approximation of a month as (365/12) = 30.417 
    select seed,
           mth_interval,
           next_target
    from   (
           select seed,
                  mth_interval,
                  trunc( seed + 
                                (
                                 (trunc( (trunc( sysdate )-seed) / (mth_interval*30.417) )+1)
                                 *
                                 (mth_interval*30.417)
                                )
                       ) next_target
           from   seeds
           order  by seed
           )
    where  next_target between trunc(sysdate+7) and trunc(sysdate+13)     
    
    SEED        MT NEXT_TARGE
    ==========  == ==========
    24/04/2005	6	23/04/2009
    25/04/2005	3	24/04/2009
    26/04/2005	1	25/04/2009
    27/04/2005	12	26/04/2009
    29/04/2005	6	28/04/2009
    26/05/2005	1	24/04/2009
    25/06/2005	1	24/04/2009
    24/07/2005	3	22/04/2009
    25/07/2005	1	23/04/2009
    27/07/2005	9	25/04/2009
    29/07/2005	3	27/04/2009
    30/07/2005	1	28/04/2009
    24/08/2005	1	23/04/2009
    29/08/2005	1	28/04/2009
    23/09/2005	1	22/04/2009
    28/09/2005	1	27/04/2009
    23/10/2005	1	22/04/2009
    26/10/2005	6	25/04/2009
    27/10/2005	3	26/04/2009
    28/10/2005	1	27/04/2009
    22/11/2005	1	22/04/2009
    27/11/2005	1	27/04/2009
    27/12/2005	1	26/04/2009
    25/01/2006	3	25/04/2009
    26/01/2006	1	26/04/2009
    25/02/2006	1	25/04/2009
    27/03/2006	1	25/04/2009
    
    -- using precise months              
    select seed,
           icount,
           mth_interval,
           add_months( seed, icount*mth_interval ) as next_target
    from   seeds s,
           (
            select rownum as icount
            from   all_objects o,
                   (select trunc( ((trunc(sysdate)-min( seed )) / 28)+1 ) as max_interval from seeds) mi 
            where  rownum <= mi.max_interval 
           ) mi
    where  add_months( seed, icount*mth_interval ) between trunc(sysdate+7) and trunc(sysdate+13)                       
    order by seed
    
    SEED        IC MT NEXT_TARGE
    ==========  == == ==========    
    22/04/2005	4	12	22/04/2009
    24/04/2005	8	6	24/04/2009
    25/04/2005	16	3	25/04/2009
    26/04/2005	48	1	26/04/2009
    27/04/2005	4	12	27/04/2009
    26/05/2005	47	1	26/04/2009
    25/06/2005	46	1	25/04/2009
    22/07/2005	5	9	22/04/2009
    24/07/2005	15	3	24/04/2009
    25/07/2005	45	1	25/04/2009
    27/07/2005	5	9	27/04/2009
    24/08/2005	44	1	24/04/2009
    23/09/2005	43	1	23/04/2009
    28/09/2005	43	1	28/04/2009
    22/10/2005	14	3	22/04/2009
    23/10/2005	42	1	23/04/2009
    26/10/2005	7	6	26/04/2009
    27/10/2005	14	3	27/04/2009
    28/10/2005	42	1	28/04/2009
    22/11/2005	41	1	22/04/2009
    27/11/2005	41	1	27/04/2009
    22/12/2005	40	1	22/04/2009
    27/12/2005	40	1	27/04/2009
    25/01/2006	13	3	25/04/2009
    26/01/2006	39	1	26/04/2009
    25/02/2006	38	1	25/04/2009
    22/03/2006	37	1	22/04/2009
    27/03/2006	37	1	27/04/2009
    Note, the first returns 27 rows, the second 28. This doesn't mean the first is 'missing' any targets, merely that the month approximation means it will occur on a different date.

    Hth
    Bill
    Please don't email me directly with questions. I've probably just got home from the pub and cannot guarantee the sanity of my answers. In fact, I can't believe I actually made it home.

Posting Permissions

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