| View previous topic :: View next topic |
| Author |
Message |
seekaysk
Beginner
Joined: 31 Aug 2007
Posts: 49
Topics: 15
|
 Posted: Mon Aug 11, 2008 9:13 am Post subject: Query to find next missing record based on date |
 |
|
Hi,
I am trying to write a query for a table which has two columns. first is date and second is some number. The date column should have values for each date. Say if the table is starting to have the rows from 2000Jan01, then it should have dates for 2000Jan02, 2000jan03, 2000jan04, etc...In short, all the calendar dates. I want to write a query to find if there is any gap in these dates.
Please note the start date can be random. I just need to find if there is any gap in between the start date and end date.
Sample Table data:
| Code: | Date Exchange rate
2000-01-01 100.4567
2000-01-02 100.6547
2000-01-04 99.9876
2000-01-05 100.0001 |
As you can see in the above table there is no date for 2000-01-03 which I want to find through a query.
Can sb please help. Thanks.
_________________
Thanks. |
|
| Back to top |
|
 |
CraigG
Intermediate
Joined: 02 May 2007
Posts: 202
Topics: 0
Location: Viginia, USA
|
| Posted: Mon Aug 11, 2008 10:58 am Post subject: |
|
|
This will give you the dates that don't have the next date in the table
| Code: |
SELECT DATE1 FROM TABLE1 A
WHERE NOT EXISTS (SELECT 1 FROM TABLE1 B
WHERE DAYS(A.DATE1) + 1 = DAYS(B.DATE1));
|
Depending on the table size response time may be terrible. |
|
| Back to top |
|
|
seekaysk
Beginner
Joined: 31 Aug 2007
Posts: 49
Topics: 15
|
| Posted: Mon Aug 11, 2008 12:02 pm Post subject: |
|
|
great. Thanks. I have seen you craig quite a lot helping people. You are the best.
_________________
Thanks. |
|
| Back to top |
|
|
|
|
|
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
