Help needed with SQL Query

[Peter Redei]

Here is a clear SQL Server stored procedure:

IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID('dbo.LindsaysProc') AND OBJECTPROPERTY(id, 'IsProcedure') = 1)
	DROP PROCEDURE dbo.LindsaysProc
GO

CREATE PROCEDURE dbo.LindsaysProc
AS

IF not EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID('Lindsay') AND OBJECTPROPERTY(id, 'IsUserTable') = 1)
begin
	-- create table
	create table Lindsay
	(
		DateTimeRecord Datetime PRIMARY KEY CLUSTERED,
		Result int,
	)

	create index  Lindsay_Idx on Lindsay(Result)

	-- populate it with data
	insert into Lindsay values('2009-07-13 00:00:00', 1)
	insert into Lindsay values('2009-07-13 00:00:01', 2)
	insert into Lindsay values('2009-07-13 00:00:02', 4)
	insert into Lindsay values('2009-07-13 00:00:03', -8888)
	insert into Lindsay values('2009-07-13 00:00:04', 2)
	insert into Lindsay values('2009-07-13 00:00:05', 5)
	insert into Lindsay values('2009-07-13 00:00:06', 6)
	insert into Lindsay values('2009-07-13 00:00:07', -8888)
	insert into Lindsay values('2009-07-13 00:00:08', -8888)
	insert into Lindsay values('2009-07-13 00:00:09', 2)
	insert into Lindsay values('2009-07-13 00:00:10', 2)
	insert into Lindsay values('2009-07-13 00:00:11', -8888)
	insert into Lindsay values('2009-07-13 00:00:12', -8888)
	insert into Lindsay values('2009-07-13 00:00:13', -8888)
	insert into Lindsay values('2009-07-13 00:00:14', 2)
	insert into Lindsay values('2009-07-13 00:00:15', 2)
	insert into Lindsay values('2009-07-13 00:00:16', 2)
	insert into Lindsay values('2009-07-13 00:00:17', 2)
	insert into Lindsay values('2009-07-13 00:00:18', 2)
	insert into Lindsay values('2009-07-13 00:00:19', 2)
	insert into Lindsay values('2009-07-13 00:00:20', 2)
	insert into Lindsay values('2009-07-13 00:00:21', 2)
	insert into Lindsay values('2009-07-13 00:00:22', -8888)
end
-- create temp table for result
create table #tmpLindsay
(
	BeginnningDateTime Datetime,
	EndingDateTime Datetime
)


declare @dt datetime,
		@value int,
		@b datetime,
		@e datetime,
		@i bit


declare l cursor for
select *
from Lindsay
order by DateTimeRecord

open l
fetch next from l into @dt, @value
while @@fetch_status=0
begin
	if @value = -8888
	begin
		if @i = 0
		begin
			set @i = 1
			set @b = @dt
			set @e = @dt
		end
		else
			set @e = @dt
	end
	else
	begin
		if (@i = 1)
		begin
			insert into #tmpLindsay values(@b, @e)
			set @b = null
			set @e = null
		end
		set @i = 0
	end
	fetch next from l into @dt, @value
end
close l 
deallocate l


select CONVERT(VARCHAR, BeginnningDateTime,120) + ' - ' + CONVERT(VARCHAR, EndingDateTime,120)
from #tmpLindsay

drop table #tmpLindsay

copy + paste the code to a query window and execute it.

The result is displayed when you execute
exec LindsaysProc

Peter Redei

[Christopher Carroll]

Cannot do it with a single query as there are subsets or ranges of min..max data within the data set. You have not said what you want to do with the resulting data - is it to be put into a table, or used in a report, for example.

Two possible methods are to use a series of SQL statements on two result sets, or to use an array. The first method requires an extra field in the table - or a copy of the table, and so would be slower than using an array as it would write data to the extra field.

Array
My recommended method of using an array is easy and fast: dump all the records in the table to a string array, compare and sort to get the desired result. All the records are required so that the start and end ranges of subset of data can be identified. This method should be easy enough to determine what to do.

SQL
Using SQL involves more work. Firstly, either create an extra field (MinDate) in the original table, or copy the data in this table to a new one. The new field should have no data. Assume the fields are:
- DateTimeStamp, string. i.e. "2009-07-13 00:00:00"
- Result, numeric. i.e. -8888
- MinDate, string. i.e. "2009-07-13 00:00:00" - minimum date time stamp

Create a result set (assuming you are using ODBC or similar which can do as such) with this SQL statement:

SELECT DateTimeStamp, Result FROM MyTable ORDER BY DateTimeStamp;

Expand the order clause if there could be missing or duplicate rows of data (your set shows a 1 second time interval, but could some of these be dropped or be duplicated?):

... ORDER BY DateTimeStamp, Result DESC;

Loop through each record in the result set (using PB code). If a record is missing data (Result = -8888), then update that record with the minimum date time stamp of that subset of data. Pseudo code follows:

LOCAL sValue AS STRING
WHILE NOT END OF RESULT SET
    IF Result = -8888 THEN
        ' only change sValue at the start of each subset
        sValue = IIF$(LEN(sValue) = 0, DateTimeStamp, sValue)
        ' update record using this SQL (will need to use a different statement number):
        UPDATE MyTable SET MinDate = sValue WHERE DateTimeStamp = sValue;
    ELSE
        sValue = ""
    END IF
LOOP

So

2009-07-13 00:00:06     6
2009-07-13 00:00:07     -8888
2009-07-13 00:00:08     -8888
2009-07-13 00:00:09     2

becomes

2009-07-13 00:00:06     6         <empty>
2009-07-13 00:00:07     -8888     2009-07-13 00:00:07
2009-07-13 00:00:08     -8888     2009-07-13 00:00:07
2009-07-13 00:00:09     2         <empty>

Create a new result set, changing the sort order. Result is not required (left in for clarity):

SELECT DateTimeStamp, Result, MinDate FROM MyTable ORDER BY DateTimeStamp DESC;

This gives:

2009-07-13 00:00:09     2         <empty>
2009-07-13 00:00:08     -8888     2009-07-13 00:00:07
2009-07-13 00:00:07     -8888     2009-07-13 00:00:07
2009-07-13 00:00:06     6         <empty>

You can see that line 2 has the maximum date time stamp in the DateTimeStamp field, and the minimum one in the MinDate field for that subset. Looping through the result set, this would be the first line of each subset of data that has a value in the MinDate field.

Now comes the tricky part as I do not know what you want to do with the resulting data.

Anyway, as one example, loop through this set. Pseudo code follows:

LOCAL sOutput AS STRING
sValue = ""
WHILE NOT END OF RESULT SET
    IF LEN(MinDate) > 0 THEN
        IF LEN(sValue) = 0 THEN
            ' set sValue as a trigger to drop subsequent records in subset
            sValue = MinDate
            ' output data from first line of subset
            sOutput = sOutput + MinDate + " - " + DateTimeStamp + $crlf
        ELSE
            ' assertion. ignore record as a subsequent record
        END IF
    ELSE
        ' assertion. ignore record as not in subset. i.e. Result is not -8888
        sValue = ""    
    END IF
LOOP
sOutput = RTRIM$(sOutput, $crlf)
? sOutput

Please note that I have not tested the logic extensively for all use cases. That's your job!

[David L Morris]

Try IsNull

Have you tried select dtRecord, IsNull(ResultField,-8888) from table etc

[Joseph Cote]

I also think this is best as an SQL + Programming problem.

Select all the rows ordered by timestamp, program notes when the missing data comes and goes.

[Eric Pearson]

No, Andrew needs...

> a series of ranges

...per the example result.

Andrew, I don't think you'll get that result directly from a SQL statement. It seems to me that it would be easier to extract the raw data with SQL then use PB (or a Report Generator like Crystal) to process the result set. But that's just me; if you are more comfortable with SQL than PB, go for it.

-- Eric

[Edwin Knoppert]

SELECT ....

FROM ....

WHERE fieldname here = -8888

[Andrew Lindsay]

Hello,

I would like to know if it is possible (and if so how) to write a query to do the following.

I want to search a table of data and return ranges with missing data as follows.

DateTime Record          Result
2009-07-13 00:00:00     1
2009-07-13 00:00:01     2
2009-07-13 00:00:02     4
2009-07-13 00:00:03     -8888
2009-07-13 00:00:04     2
2009-07-13 00:00:05     5
2009-07-13 00:00:06     6
2009-07-13 00:00:07     -8888
2009-07-13 00:00:08     -8888
2009-07-13 00:00:09     2
2009-07-13 00:00:10     2
2009-07-13 00:00:11     -8888
2009-07-13 00:00:12     -8888
2009-07-13 00:00:13     -8888
2009-07-13 00:00:14     2
2009-07-13 00:00:15     2
2009-07-13 00:00:16     2
2009-07-13 00:00:17     2
2009-07-13 00:00:18     2
2009-07-13 00:00:19     2
2009-07-13 00:00:20     2
2009-07-13 00:00:21     2
2009-07-13 00:00:22     -8888

The result from the query would be a series of ranges where the value was -8888, e.g.
2009-07-13 00:00:03 - 2009-07-13 00:00:03
2009-07-13 00:00:07 - 2009-07-13 00:00:08
2009-07-13 00:00:11 - 2009-07-13 00:00:13
2009-07-13 00:00:22 - 2009-07-13 00:00:22

Is this possible?

Any assistance would be appreciated.

Regards

Andrew