# Generating dates between two dates???

Hey guys!

I have a table holding dates and some other important values.

In the query I need to generate rows for the dates between the first and the second record.

Let's assume I have the following parent records in table "fangzahlen":

Date_datum = 05.04.2010 objectid = 1 amount = 12
Date_datum = 09.04.2010 objectid = 2 amount = 16
Date_datum = 12.04.2010 objectid = 3 amount = 2

then I need to add rows for the dates in between:

Date_datum = 05.04.2010 objectid = 1 amount = 12
Date_datum = 06.04.2010 objectid = 1 amount = 12
Date_datum = 07.04.2010 objectid = 1 amount = 12
Date_datum = 08.04.2010 objectid = 1 amount = 12
Date_datum = 09.04.2010 objectid = 1 amount = 12
Date_datum = 10.04.2010 objectid = 2 amount = 16
Date_datum = 11.04.2010 objectid = 2 amount = 16
Date_datum = 12.04.2010 objectid = 2 amount = 16

and so on...

Is there a way to achieve that with a simple function or is it inevitable to use a loop function in pl/sql. I seem to have some problems with that and like to calculate the dates and values right away.

Brgds,
Seb

``````select objectid,
date_datum,
case
when lag(date_datum) over(order by date_datum) is null
then to_date('31.03.2010')
else lag(date_datum) over(order by date_datum)
end as prev_date,
int_volumen,
int_anzahl,
int_fallennummer,
lng_falle,
lng_schaedling
from borki.fangzahlen
where lng_falle      = :pr_falle
and int_fallennummer = :pr_fallennummer
and lng_schaedling   = :pr_schaedling
and date_datum       > '31.03.2010'
order by date_datum desc
``````
###### Who is Participating?

Commented:
use the same query I posted above just add the other columns
and, again, adjust the 100 to something sufficiently large to cover your date range.

``````SELECT DISTINCT
LAST_VALUE(objectid IGNORE NULLS) OVER (ORDER BY date_datum) objectid,
LAST_VALUE(date_datum IGNORE NULLS) OVER (ORDER BY date_datum) date_datum,
LAST_VALUE(int_volumen IGNORE NULLS) OVER (ORDER BY date_datum) int_volumen,
LAST_VALUE(int_anzahl IGNORE NULLS) OVER (ORDER BY date_datum) int_anzahl,
LAST_VALUE(int_fallennummer IGNORE NULLS) OVER (ORDER BY date_datum) int_fallennummer,
LAST_VALUE(lng_falle IGNORE NULLS) OVER (ORDER BY date_datum) lng_falle,
LAST_VALUE(lng_schaedling IGNORE NULLS) OVER (ORDER BY date_datum) lng_schaedling
FROM (SELECT objectid,
date_datum,
int_volumen,
int_anzahl,
int_fallennummer,
lng_falle,
lng_schaedling
FROM borki.fangzahlen
WHERE lng_falle = :pr_falle
AND   int_fallennummer = :pr_fallennummer
AND   lng_schaedling = :pr_schaedling
AND   date_datum > TO_DATE('31.03.2010', 'dd.mm.yyyy')
UNION ALL
SELECT NULL,
mind + n d,
NULL,
NULL,
NULL,
NULL,
NULL
FROM (SELECT MIN(date_datum) mind, MAX(date_datum) maxd
FROM fangzahlen
WHERE lng_falle = :pr_falle
AND   int_fallennummer = :pr_fallennummer
AND   lng_schaedling = :pr_schaedling
AND   date_datum > TO_DATE('31.03.2010', 'dd.mm.yyyy')),
(SELECT LEVEL - 1 n
FROM DUAL
CONNECT BY LEVEL <= 100)
WHERE maxd - mind >= n)
ORDER BY date_datum
``````
0

Author Commented:
No one having an idea?
0

Commented:
try this...
``````SELECT DISTINCT
LAST_VALUE(date_datum IGNORE NULLS) OVER (ORDER BY date_datum) date_datum,
LAST_VALUE(objectid IGNORE NULLS) OVER (ORDER BY date_datum) objectid,
LAST_VALUE(amount IGNORE NULLS) OVER (ORDER BY date_datum) amount
FROM (SELECT * FROM fangzahlen
UNION ALL
SELECT mind + n d, NULL, NULL
FROM (SELECT MIN(date_datum) mind, MAX(date_datum) maxd FROM fangzahlen),
(SELECT LEVEL - 1 n
FROM DUAL
CONNECT BY LEVEL <= 100)
WHERE maxd - mind >= n)
ORDER BY date_datum
``````
0

Commented:
<<No one having an idea?>>
Still trying to understand what you want!!!!
I think you want to add record in between some range....

What I suggest is....Just Insert record as many as you want in oracle table....don't worry about the place where those record actually inserted....

When you want to retrieve  data back just use "Orader By Date"...

0

Commented:
usage note on my query above,  change 100 to some value large enough to cover the total number of days in your date range.
0

Author Commented:
Thanks a lot sdstuber! Nevertheless, I guess it is not what I need.

You helped me with the code posted in the top of this thread. What I am looking for is a function that delivers the same output columns and values for each "objectid" as the top query does, while adding rows with the same values for each objectid for the date range between prev_date and date_datum.

Please have a look at the attached file. I hope I was able to give you a closer look at what I am looking for. If you need a create table statement for testing please let me know.

Seb
table-export.txt
0

Author Commented:
Thanks sdstuber! I really appreciate your effort and believe you're the only one able to push me into the right direction.

The statement as you have it looks promising but is messing up the dates:

In fact it returns the following rows:

• 97534      03.04.10      (null)      100      1      39      2
• 97534      04.04.10      (null)      100      1      39      2
• 97534      05.04.10      (null)      100      1      39      2
• 97534      06.04.10      (null)      100      1      39      2
• 97550      07.04.10      (null)      300      1      39      2
whereas the prev_date for the first objectid is 31.03.2010 and the date_datum (date to which I need to add these extra rows) is 03.04.2010 for this id. These are the dates I need to work with.

Therefore the total output should be:

objectid
• 97534      01.04.10      (null)      100      1      39      2
• 97534      02.04.10      (null)      100      1      39      2
• 97534      03.04.10      (null)      100      1      39      2
• 97550      04.04.10      (null)      100      1      39      2
• 97550      05.04.10      (null)      300      1      39      2
• 97550      06.04.10      (null)      100      1      39      2
•  97550      07.04.10      (null)      300      1      39 2
Do you understand my problem?
If you could try to help me once more without loosing patience I'd be very happy!

Brgds,

Seb

0

Commented:
where is the data prior to Apr 3, 2010 coming from?

Is it supposed to be from the prior record and carried forward?  Or is it supposed to be propogated backward from the Apr 3 data?

Also, what if the prior date to Apr 3 was Jan 1, 2000?  Do you want 10 years of back data filled in?
If not, what is the maximum range prior to the selected date range that should be included for the purposes of defining the total range of generated data?
0

Author Commented:
Hello sdstuber! Thanks for having yet another look at this topic!
I appreciate it and hope to fix this problem today!
Well, the data prior to the 3rd of Apr. is supposed to be propogated backwards.

The core query defines the date ranges for each record and makes sure that the first
considered date is the 31st of March 2010. Thus, no ten years of data that is being inserted!

The core query as I call it delivers the date_datum, which is the date on which the record was saved.
This date represents the upper limit of the data range for each record resp. objectid. In the statement below I reference it as
"maxdateforeachrecord". Might make things a little clearer. ;-)
At the same time it is evaluated if there exists another record for the same attributes lng_falle, int_fallennummer, and lng_schaedling with a lower
date_datum, which will then be the prev_date (previous_date) and represents the lower end of the date range for a record.
Both these dates are listed in each row for the attributes mentioned above.
Now the quest is to generate a query that uses the prev_date and date_datum range of each record and generates rows for each day between prev_date and date_datum, while containing the
additional info such as objectid, lng_schaedling etc. that is originally part of that very first row.

I hope I was able to clarify things a little and hope you have another brilliant idea!?
In the meanwhile I'll try my best to think of some statement that does what I need.

select date_datum as maxdateforeachrecord,
case
when lag(date_datum) over(order by date_datum) is null
then to_date('31.03.2010')
else lag(date_datum) over(order by date_datum)
end as prev_date
Brgds, Seb

0

Author Commented:
Good evening sdstuber! I hope you haven't given up on my case yet! Yet another day where I tried various things without a working solution. If you find some time to spend some thoughts on this problem it would probably the most elegant solution. All others are friendly invited to participate of course!

Brgds,
skahlert2010
0

Author Commented:
Okay I got it! Finally I'd say! The result was overdue but I focussed too much on trigger and procedural techniques to generate the same output and eventually ran into problems with mutating table errors!

This way no triggers are needed and load is kept at a minimum! Thanks to you sdstuber for giving me a good start into this stuff. I'd like to learn more about window functions as they are highly interesting and powerful! If you have a good resource with some examples, please be so kind as to share them with me!

Thanks a lot for your work!

Brgds,

Seb
``````SELECT lng_fangzahlen, to_date(mind + n) date_datum, counted_bugs, int_volumen,
int_fallennummer,
lng_falle,
lng_schaedling
FROM (SELECT MIN(prev_date)+1 mind, MAX(date_datum) maxd, lng_fangzahlen, counted_bugs, int_volumen,
int_fallennummer,
lng_falle,
lng_schaedling FROM (SELECT objectid lng_fangzahlen,
date_datum, prev_date,
round((
case
when nvl(int_volumen,0) > 0
and lng_schaedling      = 1
then int_volumen * 40
when nvl(int_volumen,0) > 0
and lng_schaedling      = 2
then int_volumen * 550
when nvl(int_anzahl,0) > 0
then int_anzahl
end ) / (date_datum - prev_date),3) counted_bugs,
int_volumen,
int_fallennummer,
lng_falle,
lng_schaedling
from
(select objectid,
date_datum,
case
when lag(date_datum) over(order by date_datum) is null
then to_date('31.03.2010')
else lag(date_datum) over(order by date_datum)
end as prev_date,
int_volumen,
int_anzahl,
int_fallennummer,
lng_falle,
lng_schaedling
from borki.fangzahlen
where lng_falle      = :pr_falle
and int_fallennummer = :pr_fallennummer
and lng_schaedling   = :pr_schaedling
and date_datum       > '31.03.2010'
order by date_datum asc)) group by lng_fangzahlen, counted_bugs, int_volumen,
int_fallennummer,
lng_falle,
lng_schaedling),
(SELECT LEVEL - 1 n
FROM DUAL
CONNECT BY LEVEL <= 100)
WHERE maxd - mind >= n order by date_datum asc
``````
0

Author Commented:
Your post pointed me into the right direction! Great work!

Thanks!
0

Commented:
glad I could help, sorry I couldn't do more.

I learned to use analytics (window functions) by reading the oracle references and then forced myself to use them until they became intuitive.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.