473,390 Members | 842 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,390 software developers and data experts.

Finding "missing" rows

Hello, I have a DB table with data filled from a weather sensor probe,
I have one row every 10 minutes and the data fields is not
in DateTime format but in string format:
yyyyMMddHHmm

So for example I have
200804140340
200804140350
200804140400
200804140410
and so on

I need to write a query in SQL server that "tells" me if there is some
hole
in this table, for example if some data was not retrieved or the
sensor
probe didn't collected for an hour or other time interval...
for example if I have
200804140340
200804140350
200804140410
the query have to spot that the record with 200804140400 date is
missing

Some suggestion about building this query?

Thanks
Jun 27 '08 #1
5 3850
Create table ( temporary table/view/udf or even an SELECT expression that
can be used as a derived table) with yyyyMMddHHmm values starting from the
smallest value to the largest value for that column in your table. Below
you'll see a simple way to create such a table. Now you should be able to
write an simple query using OUTER JOIN or a correlated subquery to get the
list of missing values.

SET NOCOUNT ON
DECLARE @t TABLE( c CHAR(12) NOT NULL PRIMARY KEY) ;
DECLARE @i BIGINT ;
SET @i = 200804140330 ;
WHILE @i < 200804140410 BEGIN
SET @i = @i + 10 ;
IF ( @i % 100 ) < 60
INSERT @t SELECT CAST( @i AS CHAR(12)) ;
END
SELECT * FROM @t ;

To get the missing values, try:

SELECT * FROM @t t
WHERE NOT EXISTS ( SELECT * FROM <your table>
WHERE <your col= t.c );

--
Anith
Jun 27 '08 #2
Build a look up table of "hhmm" strings (6 periods * 24 hours = 144
rows) then create a query for a given day like this:

SELECT @my_date + T.probe_time
FROM TimeSlots AS T
WHERE @my_date + T.probe_time
NOT IN (SELECT P.probe_time FROM ProbeReadings);

Acutally, an entire year of strings would be only (365 days * 144
slots) = 52560 rows, so you could put this in a VIEW and be ahead of
the game.

Jun 27 '08 #3
Can I use something similare a FOR statement
in T-SQL?

I wouldn't create a new table with all the rows
(the DB is very 'huge' by now)

Thanks

M.A.

Jun 27 '08 #4
>Can I use something similar a FOR statement in T-SQL? <<

Technically, yes. It is a WHILE loop. But you are missing the whole
idea of SQL and RDBMS. This is a declarative data retrieval language.
Loops are for procedural, computational languages.
>I wouldn't create a new table with all the rows (the DB is very 'huge' by now) <<
Huge means Petabytes these days and will soon mean Exabytes. A table
with only 52560 rows is tiny; fill it for ten years and it is still
small-- less than 1 million rows of one column.

If this really bothers you, then set up a table with strings of just
the time slots for a day. Then create a VIEW which uses the
CURRENT_TIMESTAMP, extracts the date from it, concatenates the date
and time together. This VIEW will good for one day at a time. There
might be problems at midnight if the database and the data source are
not in synch.

Jun 27 '08 #5
On 15 Apr, 19:29, --CELKO-- <jcelko...@earthlink.netwrote:
Huge means Petabytes these days and will soon mean Exabytes. A table
with only 52560 rows is tiny; fill it for ten years and it is still
small-- less than 1 million rows of one column.
.... my concept of "huge database" was really wrong...
If this really bothers you, then set up a table with strings of just
the time slots for a day.
ok, this is a great idea...
thanks

M.A.

Jun 27 '08 #6

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
by: Bruce | last post by:
I have three tables in SQL serve tblareas (w/autoincrement ID tblprograms (w/autoincrement ID tblphases (w/autoincrement ID I've build cascading relation from tblareas <one-many> tblprograms...
81
by: Matt | last post by:
I have 2 questions: 1. strlen returns an unsigned (size_t) quantity. Why is an unsigned value more approprate than a signed value? Why is unsighned value less appropriate? 2. Would there...
2
by: Pete Hearn | last post by:
Hi For some reason, I cannot type "Using System.Web.Mail" in my ASP.Net app using intellisense. The dot after System offers me Web which I select, then another dot after Web does not put up...
2
by: eBob.com | last post by:
I've got the basics of an XML ini file working. I.E. I can stash away and retrieve user preferences. (Code below.) But how do I handle a new preference? Say I have A and B. And then I invent...
1
by: rb | last post by:
I'm a bit confused with this new data binding methodology - well, at least the "visual" part of it. With asp.net 1, I used be able to "mouse-my-way" around data binding instead of doing it in...
42
by: Martin Jørgensen | last post by:
Hi, I'm trying to move a matlab program into c language. For those who knows matlab, this is the line I want to program in c: hx(1:nx,1:ny) = 0; % nx=10, ny=10 It works on a 2-dimensional...
0
by: Sean Quilty | last post by:
Help! We are getting the following errors on a slave server: 060304 7:42:03 Error reading packet from server: Could not find first log (server_errno=65535) 060304 7:43:03 Slave: Failed...
1
by: BobPaul | last post by:
I'm following code out of a howto book and this is really bugging me. This header file was created by VStudio 6.0 when I did a "Right Click: Add Member Function" CLine is a class I wrote (per the...
2
by: Steve Harclerode | last post by:
Hi, When I used Visual Studio 2002, I was able to add / delete colums from a DataGrid at design time using a "property builder". Now I'm using Visual Studio 2005, the "property builder" option...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
0
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.