Conditional statements in t-sql? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Conditional statements in t-sql?

Author

Topic

shwelch
Starting Member

33 Posts

Posted - 2006-11-21 : 21:40:18

Ok, Here is my what I am trying to do. Is this possible in T-sql?

Table:
PersonName
collectDate
collectTime
value

I want to look at the value, and if it meets some condition, for example, it is < 50, then I want to check (a select I presume) if there are any records for PersonName with a collectDate the same and collectTime + 4 hours from the time of the records that happens to meet the <50 criteria that is > 60, and if so, don't take the < 50 records, but instead take only the record that is > 60. I know I could do this easily in vb or c#, but how easy would this be in t-sql? I am using sql server 2000.

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-11-21 : 22:13:46

Post some example data, a set of sample records that you're selecting from and then a set that shows which ones would be selected.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-11-21 : 22:38:08

Can't give you an exact YES or NO answer without knowing your actual requirement.

If would be easier for all if you can provide the table's DDL, some sample data and the result that you want.

KH

PSamsig
Constraint Violating Yak Guru

384 Posts

Posted - 2006-11-22 : 02:43:42

[code]DECLARE @Table TABLE (PersonName varchar(30),
collectDate int, collectTime int, value int)

INSERT @Table
SELECT 'Peter', 20061101, 0, 49 UNION ALL
SELECT 'Peter', 20061101, 4, 51 UNION ALL
SELECT 'Peter', 20061101, 8, 49 UNION ALL
SELECT 'Peter', 20061101, 12, 61

SELECT PersonName, collectDate, collectTime, value
FROM @Table o
WHERE value > 50 OR NOT EXISTS (
SELECT * FROM @Table i
WHERE i.PersonName = o.PersonName AND
i.collectDate = o.collectDate AND
i.collectTime = o.collectTime + 4 AND
i.value > 60)
ORDER BY PersonName, collectDate, collectTime

PersonName collectDate collectTime value
------------------------------ ----------- ----------- -----------
Peter 20061101 0 49
Peter 20061101 4 51
Peter 20061101 12 61[/code]

-- Alice came to a fork in the road. "Which road do I take?" she asked. "Where do you want to go?" responded the Cheshire cat. "I don't know," Alice answered. "Then," said the cat, "it doesn't matter."

shwelch
Starting Member

33 Posts

Posted - 2006-11-29 : 13:52:32

Thanks,

This sample almost does it, but let me explain a little better.

There is a device that takes readings several times a day. On each reading, it records Name, age, collect date, collect time, result, etc. There are about 100,000 records that I am working with. I want to go through all of these and:

If a value is LESS THAN 50, and a subsequent reading obtained within 4 minutes of that value happens to be GREATER THAN 60, then take this value and ignore the one that is less than 50. IF THE VALUE LESS THAN 50 has no reading 4 minutes later, then take it, even though it is less than 50.

Sample:

name, age, collecttime, collectdate, value

John Doe, 77, 01:10:50, 2006-11-07, 20
John Doe, 77, 01:12:50, 2006-11-07, 85
John Doe, 77, 02:10:50, 2006-11-07, 5
John Doe, 77, 02:14:50, 2006-11-07, 122
John Doe, 77, 05:10:50, 2006-11-07, 17

So above using the SQL select I would want this returned:

John Doe, 77, 01:12:50, 2006-11-07, 85
John Doe, 77, 02:14:50, 2006-11-07, 122
John Doe, 77, 05:10:50, 2006-11-07, 17

the 17 was returned because there was not a reading within 4 minutes for John Doe on the same day that happened to be > 60.

PSamsig
Constraint Violating Yak Guru

384 Posts

Posted - 2006-11-29 : 16:26:03

[code]DECLARE @Table TABLE (PersonName varchar(30),
collectTime datetime, collectDate datetime, value int)

INSERT @Table
SELECT 'John Doe', '01:10:50', '20061107', 20 UNION ALL
SELECT 'John Doe', '01:12:50', '20061107', 85 UNION ALL
SELECT 'John Doe', '02:10:50', '20061107', 5 UNION ALL
SELECT 'John Doe', '02:14:50', '20061107', 122 UNION ALL
SELECT 'John Doe', '05:10:50', '20061107', 17

SELECT PersonName, collectDate, collectTime, value
FROM @Table o
WHERE value > 50 OR NOT EXISTS (
SELECT * FROM @Table i
WHERE i.PersonName = o.PersonName AND
i.collectDate = o.collectDate AND i.collectTime > o.collectTime AND
DATEDIFF(m, i.collectTime, o.collectTime) <= 4 AND
i.value > 60)
ORDER BY PersonName, collectDate, collectTime[/code]
I would suggest a combined collection date AND time, really no need to keep them seperate.

-- If you give someone a program, you will frustrate them for a day; if you teach them how to program, you will frustrate them for a lifetime.

shwelch
Starting Member

33 Posts

Posted - 2006-11-30 : 07:48:41

This is almost perfect, Except for the fact that if I try it with a value of say, 30 and no event within the next 4 minutes happens to be above 60, then I would want the 30 returned (although it is less than the 50, there is none within 4 minutes above 60, so that 30 should be returned. Is there a way to get that check in there?

PSamsig
Constraint Violating Yak Guru

384 Posts

Posted - 2006-11-30 : 08:06:14

Post a set of sample data (like I have) and the expected output, it should do as you request already.

-- If you give someone a program, you will frustrate them for a day; if you teach them how to program, you will frustrate them for a lifetime.

shwelch
Starting Member

33 Posts

Posted - 2006-11-30 : 08:45:05

The table name is onlycontroltest.

SELECT *
FROM onlycontroltest o

WHERE testresult > 50 OR NOT EXISTS (
SELECT * FROM onlycontroltest i
WHERE i.[person Name] = o.[person Name] AND
i.[collect Date odbc] = o.[collect Date ODBC] AND i.[collectTime] > o.[collectTime] AND
DATEDIFF(m, i.[collectTime], o.[collectTime]) <= 4 AND
i.testresult > 60)
ORDER BY [person Name], [collect Date ODBC], collectTime

Sample data:

DOE, JOHN 7/17/2006 22:45:00 120
DOE, JOHN 7/18/2006 01:46:00 20
DOE, JOHN 7/18/2006 01:18:00 60
DOE, JOHN 7/18/2006 02:22:11 134
DOE, JOHN 7/18/2006 02:44:10 90
DOE, JOHN 7/18/2006 03:23:10 118
DOE, JOHN 7/18/2006 04:19:00 80
DOE, JOHN 7/18/2006 04:44:00 94
DOE, JOHN 7/18/2006 05:12:00 105
DOE, JOHN 7/18/2006 07:12:00 75

The sql returns:

DOE, JOHN 2006-07-17 22:45:00 120
DOE, JOHN 2006-07-18 01:18:00 60
DOE, JOHN 2006-07-18 02:22:11 134
DOE, JOHN 2006-07-18 02:44:10 90
DOE, JOHN 2006-07-18 03:23:10 118
DOE, JOHN 2006-07-18 04:19:00 80
DOE, JOHN 2006-07-18 04:44:00 94
DOE, JOHN 2006-07-18 05:12:00 105
DOE, JOHN 2006-07-18 07:12:00 75

The record with a value 20 is not returned, and it should be since there are none >60 within the next 4 minutes.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-30 : 09:01:40

I have added code just to ensure there is no "dirty" data in the source (collectDate 20061130 12:00 or collectTime 19690101 12:15:02)
Also the sugegstion above does not work over midnight.

-- prepare test data
DECLARE @Test TABLE (PersonName varchar(8), collectTime datetime, collectDate datetime, value int)

INSERT	@Test
SELECT	'John Doe', '01:10:50', '20061107',  20 UNION ALL
SELECT	'John Doe', '01:12:50', '20061107',  85 UNION ALL
SELECT	'John Doe', '02:10:50', '20061107',   5 UNION ALL
SELECT	'John Doe', '02:14:50', '20061107', 122 UNION ALL
SELECT	'John Doe', '05:10:50', '20061107',  17 

-- initialize code
DECLARE	@LowerLimit INT,
	@HigherLimit INT,
	@TimeInterval INT

SELECT	@TimeInterval = 4,
	@LowerLimit = 30,
	@HigherLimit = 60

-- do the work
SELECT		t.PersonName,
		p.collectDate,
		p.collectTime,
		t.value,
		CASE WHEN t.value < @LowerLimit THEN @LowerLimit ELSE t.value END secValue
FROM		@Test t
INNER JOIN	(
			SELECT DISTINCT	CASE WHEN x.theDT <= w.theDT THEN w.collectDate ELSE x.collectDate END collectDate,
					CASE WHEN x.theDT <= w.theDT THEN w.collectTime ELSE x.collectTime END collectTime
			FROM		(
						SELECT	DATEADD(day, DATEDIFF(day, 0, collectdate), 0) collectDate,
							DATEADD(second, DATEDIFF(second, DATEADD(day, DATEDIFF(day, 0, collectTime), 0), collectTime), 0) collectTime,
							DATEADD(day, DATEDIFF(day, 0, collectdate), 0) + DATEADD(second, DATEDIFF(second, DATEADD(day, DATEDIFF(day, 0, collectTime), 0), collectTime), 0) theDT,
							value
						FROM	@Test
					) x
			LEFT JOIN	(
						SELECT	DATEADD(day, DATEDIFF(day, 0, collectdate), 0) collectDate,
							DATEADD(second, DATEDIFF(second, DATEADD(day, DATEDIFF(day, 0, collectTime), 0), collectTime), 0) collectTime,
							DATEADD(day, DATEDIFF(day, 0, collectdate), 0) + DATEADD(second, DATEDIFF(second, DATEADD(day, DATEDIFF(day, 0, collectTime), 0), collectTime), 0) theDT,
							value
						FROM	@Test
					) w ON w.theDT > x.theDT AND w.theDT < DATEADD(minute, @TimeInterval, x.theDT) AND w.value >= @HigherLimit
			WHERE		x.value <= @LowerLimit
					OR w.theDT IS NULL
		) p ON p.collectDate = t.collectDate AND p.collectTime = t.collectTime
ORDER BY	t.collectDate,
		t.collectTime

If there is no "dirty" data, use this

-- prepare test data
DECLARE @Test TABLE (PersonName varchar(8), collectTime datetime, collectDate datetime, value int)

INSERT	@Test
SELECT	'John Doe', '01:10:50', '20061107',  20 UNION ALL
SELECT	'John Doe', '01:12:50', '20061107',  85 UNION ALL
SELECT	'John Doe', '02:10:50', '20061107',   5 UNION ALL
SELECT	'John Doe', '02:14:50', '20061107', 122 UNION ALL
SELECT	'John Doe', '05:10:50', '20061107',  17 

-- initialize code
DECLARE	@LowerLimit INT,
	@HigherLimit INT,
	@TimeInterval INT

SELECT	@TimeInterval = 4,
	@LowerLimit = 30,
	@HigherLimit = 60

-- do the work
SELECT		t.PersonName,
		p.collectDate,
		p.collectTime,
		t.value,
		CASE WHEN t.value < @LowerLimit THEN @LowerLimit ELSE t.value END secValue
FROM		@Test t
INNER JOIN	(
			SELECT DISTINCT	CASE WHEN x.theDT <= w.theDT THEN w.collectDate ELSE x.collectDate END collectDate,
					CASE WHEN x.theDT <= w.theDT THEN w.collectTime ELSE x.collectTime END collectTime
			FROM		(
						SELECT	collectDate,
							collectTime,
							collectDate + collectTime theDT,
							value
						FROM	@Test
					) x
			LEFT JOIN	(
						SELECT	collectDate,
							collectTime,
							collectDate + collectTime theDT,
							value
						FROM	@Test
					) w ON w.theDT > x.theDT AND w.theDT < DATEADD(minute, @TimeInterval, x.theDT) AND w.value >= @HigherLimit
			WHERE		x.value <= @LowerLimit
					OR w.theDT IS NULL
		) p ON p.collectDate = t.collectDate AND p.collectTime = t.collectTime
ORDER BY	t.collectDate,
		t.collectTime

Peter Larsson
Helsingborg, Sweden

shwelch
Starting Member

33 Posts

Posted - 2006-11-30 : 09:27:44

Which part do you mean does not work after midnight?

There should be no dirty data since it is output data from a legacy system in a standard format and I am already checking for data integrity in a previous import step...

This actually seems to work!

shwelch
Starting Member

33 Posts

Posted - 2006-11-30 : 09:31:39

This method does seem to work, but to run it on 100,000+ records, it seems to time out over 3 minutes to execute! Is there a more efficient way do to this you think?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-30 : 09:33:31

20061130 23:59 and 20061201 00:02 are less than four minutes apart, but with the suggestions you got first, it does not work because they join in the date only and not date + time.

-- prepare test data
DECLARE @Test TABLE (PersonName varchar(8), collectTime datetime, collectDate datetime, value int)

INSERT	@Test
SELECT	'John Doe', '01:10:50', '20061107',  20 UNION ALL
SELECT	'John Doe', '01:12:50', '20061107',  85 UNION ALL
SELECT	'John Doe', '02:10:50', '20061107',   5 UNION ALL
SELECT	'John Doe', '02:14:50', '20061107', 122 UNION ALL
SELECT	'John Doe', '05:10:50', '20061107',  17 

-- initialize code
DECLARE	@LowerLimit INT,
	@HigherLimit INT,
	@TimeInterval INT

SELECT	@TimeInterval = 4,
	@LowerLimit = 30,
	@HigherLimit = 60

-- do the work
SELECT		t.PersonName,
		p.collectDate,
		p.collectTime,
		t.value,
		CASE WHEN t.value < @LowerLimit THEN @LowerLimit ELSE t.value END secValue
FROM		@Test t
INNER JOIN	(
			SELECT DISTINCT	CASE WHEN x.theDT <= w.theDT THEN w.collectDate ELSE x.collectDate END collectDate,
					CASE WHEN x.theDT <= w.theDT THEN w.collectTime ELSE x.collectTime END collectTime
			FROM		(
						SELECT	collectDate,
							collectTime,
							collectDate + collectTime theDT,
							value
						FROM	@Test
					) x
			LEFT JOIN	(
						SELECT	collectDate,
							collectTime,
							collectDate + collectTime theDT,
							value
						FROM	@Test
					) w ON w.theDT > x.theDT AND w.theDT < DATEADD(minute, @TimeInterval, x.theDT) AND w.value >= @HigherLimit
			WHERE		x.value <= @LowerLimit
					OR w.theDT IS NULL
		) p ON p.collectDate = t.collectDate AND p.collectTime = t.collectTime
ORDER BY	t.collectDate,
		t.collectTime

Peter Larsson
Helsingborg, Sweden

shwelch
Starting Member

33 Posts

Posted - 2006-11-30 : 09:47:10

It does work on the smaller test data, but when tried on the 130,000 set of records, I let it run for nearly 14 minutes and nothing. Must be a complex query!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-30 : 10:06:52

Yes.
It is because you need all adjactent readings within four minutes for all records... That's a lot of data!

Row#	Rows left
1	129999
2	129998
3	129997
......	......
129998	     2
129999	     1
130000	     0

The SQL Server has to process 8,450,065,000 records. That is almost 8.5 billion records.

Peter Larsson
Helsingborg, Sweden

PSamsig
Constraint Violating Yak Guru

384 Posts

Posted - 2006-11-30 : 10:10:22

Some prober indexing should do the trick.

-- If you give someone a program, you will frustrate them for a day; if you teach them how to program, you will frustrate them for a lifetime.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-30 : 10:12:34

There might be another way to go.
If you divide whole day into slots of 5 minutes and calculate the same thing for each slot.
This you do four times, with 1 minute slide for each time.
First slot of day is named Zero.

Now you take max value for slot Zero among the four calculations.
Then you take max value for slot 1 among the four calculations, and so on...

This can give you the same result but with MUCH BETTER performance.

Please post some real data here for us to test, some 50 records will do.

Peter Larsson
Helsingborg, Sweden

PSamsig
Constraint Violating Yak Guru

384 Posts

Posted - 2006-11-30 : 10:16:16

Combining data and time in one would help quite a bit too, and make the query much simpler.

-- If you give someone a program, you will frustrate them for a day; if you teach them how to program, you will frustrate them for a lifetime.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-30 : 10:18:24

I don't think that would help much.
Since there is 8.5 BILLION records to check, the SQL Server can't handle all those records in memory.
The SQL Server will read a lot from disk and TempDB...

Peter Larsson
Helsingborg, Sweden

PSamsig
Constraint Violating Yak Guru

384 Posts

Posted - 2006-11-30 : 10:33:01

With your derived table aproach you wont benefit from an index, but with a a combined date/time coloumn you could.

-- If you give someone a program, you will frustrate them for a day; if you teach them how to program, you will frustrate them for a lifetime.

shwelch
Starting Member

33 Posts

Posted - 2006-11-30 : 10:39:21

Does it really need to look at all records? should it not only have to look at records on those events (values) that are less than 50--then when it finds one, look to see for a record 4 minutes later and see if it is > 60? If there are only, say, 400 records that are actually less than 50 to begin with, is it necessary to check 8.5 BILLION record combinations? I know this is another forum, but do you think that I would be better off using vb code and build a DTS package to process the records into a new table?

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources