Re: Determining missing dates from a table (A date logic prob)
Date: Fri, 17 Jun 1994 17:10:21 GMT
Message-ID: <CrJx1A.K2D_at_gremlin.nrtc.northrop.com>
In article <2tkk1v$mv_at_Venus.mcs.com> guptar_at_MCS.COM (Rajeev Gupta) writes:
>Ok the problem is as follows :
>
>I have a table in Oracle7 (DAILY_SALES) which contains a row for everyday
>with a date column (say sales_date).
>
>As an exception, some rows are missing for certain days [let's assume
>aproax. 10 rows are missing from a reference date (say Nov. 1st 93) to
>till date.]
>
>It is required to find out those dates which are missing from table.
>
>Thanks in Advance.
Sorry, I do not accept thanks in advance. If this is helpful I would appreciate hearing from you via e-mail.
(I assume you want to test for holes. Obviously one cannot store all dates, unless you subscribe to the notion that time is finite.)
I will provide the logic and leave the syntax to you. Write a P/L*SQL command that:
Selects the minimum and maximum dates from your table and stores them in variables.
Set up a loop that cycles from the earliest to the latest date searching for missing date in your table.
Write the exception to an exception table (that you create previously).
Write a SQL command that:
Reports the exceptions.
Alternately, you could insert the missing dates in your table, instead of writing them to an exception table, if this is your actual goal.
Good luck.
If you have question about how to write P/L*SQL you will have to re-post those questions.
-- ============================================================================ Michael Sallwasser | Down one path is utter dispair and hopelessness. Down Northrop Grumman | the other is total destruction. Let us choose wisely. ============================================================================Received on Fri Jun 17 1994 - 19:10:21 CEST