Home » SQL & PL/SQL » SQL & PL/SQL » identifying gaps (merged)
identifying gaps (merged) [message #265285] Thu, 06 September 2007 01:15 Go to next message
subusona
Messages: 70
Registered: March 2005
Location: delhi
Member
create table xyz( date_stamp date,seqno number(4))

This table 'xyz' should contain a single entry for each day, if not then that is what need to find

select * from xyz ;

date_stamp seqno
--------------------
08/06/2007 1004
08/07/2007 1005
08/08/2007 1006
08/09/2007 1007
08/11/2007 1009
08/14/2007 1012

object is to get the missing seqno when queried for date_stamp between 08/05/2007 and 08/12/2007

my result should look like

08/05/2007 1003
08/10/2007 1008
08/11/2007 1010


Thanks
Re: missing sequence number [message #265289 is a reply to message #265285] Thu, 06 September 2007 01:26 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

Quote:
, if not then that is what need to find


By inserting records into table xyz. Laughing
Re: missing sequence number [message #265313 is a reply to message #265285] Thu, 06 September 2007 02:09 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Search for calendar.

Regards
Michel
identifying gaps in a number sequence [message #265688 is a reply to message #265285] Fri, 07 September 2007 01:00 Go to previous messageGo to next message
subusona
Messages: 70
Registered: March 2005
Location: delhi
Member
select * from table_name order by dt;

seq_num |status |dt
-----------------------------
Missing Seq 8/23/2007
Missing Seq 8/24/2007
1001 8/25/2007
1002 8/26/2007
1003 8/27/2007
Missing Seq 8/28/2007
1005 8/29/2007

Design a SQL query or procedure that will result as below:

seq_num |status |dt
-----------------------------
999 Missing Seq 8/23/2007
1000 Missing Seq 8/24/2007
1001 8/25/2007
1002 8/26/2007
1003 8/27/2007
1004 Missing Seq 8/28/2007
1005 8/29/2007


Thanks
Re: identifying gaps in a number sequence [message #265693 is a reply to message #265688] Fri, 07 September 2007 01:10 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It is useless to repost the same question each day, the answer is the same.
There now it is not clear, if '8/23/2007' row is in the table and seq_num NULL or if it is not.

Regards
Michel
Re: identifying gaps in a number sequence [message #265699 is a reply to message #265693] Fri, 07 September 2007 01:29 Go to previous messageGo to next message
skooman
Messages: 912
Registered: March 2005
Location: Netherlands
Senior Member
Based on the first post (see select * from xyz), I would guess that the missing dates/seqno's are not in the table at all.

If so, "generate" the dates for the range you need to check (see asktom.com for a nice construction with level and connect by, or search orafaq), outerjoin the existing rows to that (or use minus) and you'll see the missing dates.
Re: identifying gaps in a number sequence [message #265706 is a reply to message #265699] Fri, 07 September 2007 01:55 Go to previous message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Sabine,
This is why I merged the two topics, to prevent from searching and giving wrong answers based on inaccurate question.

By the way, "calendar" topics tackled here should be sufficient to answer the question.

Regards
Michel
Previous Topic: selecting particular row (merged)
Next Topic: break output line at certain point
Goto Forum:
  


Current Time: Sat Dec 10 09:16:40 CST 2016

Total time taken to generate the page: 0.27078 seconds