Solved

query to find missing gaps

Posted on 2013-11-04
3
317 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
levels for reporting 5 74
How to connect SQL Server from my Oracle database? 11 94
Component is listed with a Protocol more than once 3 27
Help on model clause 5 29
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that useā€¦
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
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.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

808 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