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.

Thanks for your support!
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

Open in new window

skahlert2010Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

skahlert2010Author Commented:
No one having an idea?
0
sdstuberCommented:
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

Open in new window

0
POracleCommented:
<<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
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

sdstuberCommented:
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
skahlert2010Author 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.

Thanks for your effort!

Seb
table-export.txt
0
sdstuberCommented:
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

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
skahlert2010Author 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
sdstuberCommented:
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
skahlert2010Author 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
skahlert2010Author 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
skahlert2010Author 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

Open in new window

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

Thanks!
0
sdstuberCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.