Solved

query to find missing gaps

Posted on 2013-11-04
3
310 Views
Last Modified: 2013-12-04
Hi,
I have data like this

Position_tcd     start_date(date)  end_date(date)
101                      Jan-01-2000          Feb-20-2003                      
101                      Feb-21-2003          Nov-11-2011
101                     Nov-12-2011             Dec-31-9999

102                   Jan-01-2000           Feb-20-2003
102                    Nov-2-2011            Dec-11-2012
102                    Dec-12-2011          Dec-31-9999

I want to run a validation query to see if an position_tcd have gaps of dates in them.

how can i do that..


I want query to give me date gaps based upon position_tcd so something like this:

102  Feb-21-2003    Nov-1-2003  missing data
0
Comment
Question by:sam2929
3 Comments
 
LVL 48

Accepted Solution

by:
PortletPaul earned 500 total points
ID: 39623479
Using the LAG() function we can compare across rows, so below we compare a "previous" end_date to "this" start_date, if the difference between those 2 dates is > 1 then we list the missing dates.
This result:
| POSITION_TCD | MISSED_START | MISSED_END |
|--------------|--------------|------------|
|          102 |   2003-02-21 | 2011-11-01 |

Open in new window

produced by the following query
SELECT
        position_tcd
      , to_char(x + 1,'YYYY-MM-DD') missed_start
      , to_char(start_date - 1,'YYYY-MM-DD') missed_end
FROM (
      SELECT
              position_tcd
            , lag(end_date,1) over (partition BY position_tcd ORDER BY start_date) AS x
            , start_date
            , end_date
      FROM s_position
     )
WHERE start_date - x > 1
;


############### data
CREATE TABLE S_POSITION
	("POSITION_TCD" int, "START_DATE" date, "END_DATE" date)
;

INSERT ALL 
	INTO S_POSITION ("POSITION_TCD", "START_DATE", "END_DATE")
		 VALUES (101, '01-Jan-2000', '20-Feb-2003')
	INTO S_POSITION ("POSITION_TCD", "START_DATE", "END_DATE")
		 VALUES (101, '21-Feb-2003', '11-Nov-2011')
	INTO S_POSITION ("POSITION_TCD", "START_DATE", "END_DATE")
		 VALUES (101, '12-Nov-2011', '31-Dec-9999')
	INTO S_POSITION ("POSITION_TCD", "START_DATE", "END_DATE")
		 VALUES (102, '01-Jan-2000', '20-Feb-2003')
	INTO S_POSITION ("POSITION_TCD", "START_DATE", "END_DATE")
		 VALUES (102, '02-Nov-2011', '11-Dec-2012')
	INTO S_POSITION ("POSITION_TCD", "START_DATE", "END_DATE")
		 VALUES (102, '12-Dec-2011', '31-Dec-9999')
SELECT * FROM dual
;
-- http://sqlfiddle.com/#!4/6ce5c/8

Open in new window

0
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 39624176
I think the lag function suggested by PortletPaul is the best way to solve this problem.  Your question is another example of the hardest kind of query to write in Oracle, basically:
"give me a report of what is *NOT* in the database."

Some common business examples are: invoices not paid, orders not shipped, POs not received, etc.  These are always more-complex to write (and almost always slower for the database to answer) than queries of what *IS* in the database.
0
 
LVL 32

Expert Comment

by:awking00
ID: 39624700
Can we assume that the start_date for the 3rd 102 record is a type and should be
Dec-12-2012?
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

867 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

21 Experts available now in Live!

Get 1:1 Help Now