Provide Missing date from a table

I have a table Named "ReconInst" with the following info

date
-------------            
1/1/1986              
1/2/1986      
1/3/1986        
1/3/1986      
1/7/1986        
1/9/1986      


I created another table called MissingDAtes,

I need the PLSQL code that will generate any date missing from ReconINst table to the MissingDates Table for ex: for the above I would Need to see the following dates

Missing_dates
-----------------
01/04/1986              
01/06/1986
01/08/1986

I would need to see the date in a format in which i could tell what day of the week it is for example for

01/04/1986 I want to see "Tuesday, January 6, 1986"
Please help!!

CstjAsked:
Who is Participating?
 
jdlambert1Connect With a Mentor Commented:
Okay, you can download dates.txt from http://www.exabyte.net/lambert/db/dates.txt This has dates from Jan 1, 1960 through Dec 31, 2010. If you have MySql 4.1 (or higher) you can keep all the dates, if your MySQL is older than that, you'll need to delete everything older than (SELECT Max(Date) FROM ReconInst). I explain the reason below.


Create an AllDates table like this:

CREATE TABLE alldates(date Date);


Load the dates like this (this is for windows -- adjust for your file system and folder path):

LOAD DATA INFILE 'c:\\dates.txt'
INTO TABLE alldates;


Finally, to create MissingDates. If you have 4.1 or higher, you can use subselects, like this:

SELECT ad.Date
INTO MissingDates
FROM AllDates ad LEFT JOIN ReconInst ri ON ad.Date = ri.Date
WHERE ri.Date IS NULL
 AND ad.Date <= (SELECT Max(Date) FROM ReconInst);

If you don't have 4.1, you can't use subqueries, so you need to make sure the AllDates table doesn't have any dates higher than (SELECT Max(Date) FROM ReconInst). You can delete them from the text file before you load it, or you can delete them after the load. Then you run this to create the table:

SELECT ad.Date
INTO MissingDates
FROM AllDates ad LEFT JOIN ReconInst ri ON ad.Date = ri.Date
WHERE ri.Date IS NULL
0
 
jdlambert1Commented:
Do you realize you posted under the MySQL topic area? PL/SQL is an Oracle language, and MySQL doesn't provide for procedural code unless you're using the beta version of 5.0.
0
 
jdlambert1Commented:
Regardless of which database you're using, you can do this... Create a table with all dates from the maximum and minimum dates in ReconInst. Then you can do an exclusion like this:

SELECT ad.Date As Missing_Dates
FROM AllDates ad LEFT JOIN ReconInst ri ON ad.Date = ri.Date
WHERE ri.Date IS NULL

or

SELECT ad.Date As Missing_Dates
FROM AllDates ad
WHERE NOT EXISTS (SELECT Date FROM ReconInst ri WHERE ad.Date = ri.Date)
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
CstjAuthor Commented:
Thanks Lambert for answering
I am having trouble understanding your code though, What is Ad.date, ri.date, and allDAtes?
Please explain.
0
 
jdlambert1Commented:
Sure, I suggested making a new table to hold all dates the maximum and minimum dates in ReconInst. So, if ReconInst only has 2 records with dates of Jan 1, 2004 and Jan 3, 2004, the all-dates table would have 3 rows, with dates of Jan 1, 2, and 3.

AllDates is the name I chose to represent such a table.

Table and column names can have aliases, which can allow you to do a lot less typing, and making it a little easier to read code. In the examples above "ad" is the alias I assigned to the AllDates table, and "ri" is the alias I assigned to the ReconInst table. That way I could type "ad.Date = ri.Date" instead of "AllDates.Date = ReconInst.Date", but either way works.
0
 
CstjAuthor Commented:
oh, I understand, but I don't want any extra dates on my new table, I just want to see the dates are missing from  ReconInst.Date.
0
 
jdlambert1Commented:
You have to get the missing dates one of 2 ways: 1.) Loop through all your existing dates, temporarily store the missing ones, and then return the missing dates when you're through looping, or 2.) Compare the dates in ReconInst with a different table that has a complete set of dates.

MySQL doesn't provide for looping until version 5, which is still in beta. If you use version 5, either way will work, but the second way will be much faster. If you don't use version 5, you'll either have to use a programming language to pull the data and loop through it to detect the missing dates, or use the second option (which is also much faster than using a custom program).

So, I'm recommending 3 tables: ReconInst, MissingDates, and AllDates. Once you populate the AllDates table with a date for every day from the earliest date in ReconInst to the latest date in ReconInst, you can create and populate MissingDates like this:

SELECT ad.Date
INTO MissingDates
FROM AllDates ad LEFT JOIN ReconInst ri ON ad.Date = ri.Date
WHERE ri.Date IS NULL
0
 
CstjAuthor Commented:
I heard that Oracle Toad got a calender program in it already which supports dates from January 1, 0001 to Dec 31,999, could I somehow use that as my All dates table?
0
 
jdlambert1Commented:
>heard that Oracle Toad got a calender program
Couldn't find a mention of it at toadsoft, but did notice they have a "preview" version of Toad for MySQL. It'll only run for 60 days. Hopefully, after that they'll have a full release available. (http://www.toadsoft.com/toadmysql/toad_mysql.htm)

If you want, tell me what the earliest date is you have in ReconInst, and I'll build you a list of dates and a query to import it. To check the earliest date, run this:

SELECT Min(Date) FROM ReconInst;
0
 
CstjAuthor Commented:
01/01/1960
0
 
jdlambert1Commented:
Okay, it'll be a few hours before I can run that and post it online. If I run the dates to the year 2010, that would only be 14,600 records, so it should take very little hard drive space since it's only one date column.

I'll let you know as soon as I can get it posted.
0
 
CstjAuthor Commented:
Thanks MR. Lambert, I finally figured out how to load the dates into the table.
now let me ask you this, How did you get those dates, I am curious for future reference if I wanted to get dates like that, how would I go about doing it.
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.

All Courses

From novice to tech pro — start learning today.