Solved

Provide Missing date from a table

Posted on 2004-10-13
14
351 Views
Last Modified: 2008-02-01
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!!

0
Comment
Question by:Cstj
  • 7
  • 5
14 Comments
 
LVL 15

Expert Comment

by:jdlambert1
ID: 12304407
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
 
LVL 15

Expert Comment

by:jdlambert1
ID: 12304469
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
 

Author Comment

by:Cstj
ID: 12311455
Thanks Lambert for answering
I am having trouble understanding your code though, What is Ad.date, ri.date, and allDAtes?
Please explain.
0
 
LVL 15

Expert Comment

by:jdlambert1
ID: 12311621
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
 

Author Comment

by:Cstj
ID: 12312056
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
 
LVL 15

Expert Comment

by:jdlambert1
ID: 12312235
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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

Author Comment

by:Cstj
ID: 12312288
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
 
LVL 15

Expert Comment

by:jdlambert1
ID: 12312443
>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
 

Author Comment

by:Cstj
ID: 12312463
01/01/1960
0
 
LVL 15

Expert Comment

by:jdlambert1
ID: 12312607
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
 
LVL 15

Accepted Solution

by:
jdlambert1 earned 125 total points
ID: 12314496
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
 

Author Comment

by:Cstj
ID: 12364037
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

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Fore-Foreword Today (2016) Maxmind has a new approach to the distribution of its data sets.  This article may be obsolete.  Instead of using the examples here, have a look at the MaxMind API (https://www.maxmind.com/en/geolite2-developer-package). …
Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now