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

1. Registered User
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. Registered User
Join Date
Aug 2003
Location
Where the Surf Meets the Turf @Del Mar, CA
Posts
7,776
>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.

3. Registered User
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. Registered User
Join Date
Aug 2003
Location
Where the Surf Meets the Turf @Del Mar, CA
Posts
7,776
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.

5. Registered User
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. Registered User
Join Date
Aug 2003
Location
Where the Surf Meets the Turf @Del Mar, CA
Posts
7,776
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.

7. Registered User
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. Registered User
Join Date
Jun 2004
Location
Liverpool, NY USA
Posts
2,520
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;```

9. Registered User
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. Registered User
Join Date
Jun 2004
Location
Liverpool, NY USA
Posts
2,520
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.

11. Registered User
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. Registered User
Join Date
Aug 2003
Location
Where the Surf Meets the Turf @Del Mar, CA
Posts
7,776
>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?

13. Registered User
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. Registered User
Join Date
Aug 2003
Location
Where the Surf Meets the Turf @Del Mar, CA
Posts
7,776
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.

15. Drunkard
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

#### Posting Permissions

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