Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

query to find missing gaps

Posted on 2013-11-04
3
Medium Priority
?
353 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 49

Accepted Solution

by:
PortletPaul earned 2000 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

Fill in the form and get your FREE NFR key NOW!

Veeam® is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Question has a verified solution.

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

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

597 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