While Loop - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

While Loop

Author

Topic

eembme
Starting Member

18 Posts

Posted - 2007-01-30 : 08:58:26

I need to keep the first 4 values above 80 or the first 2 values above 90. If there are not enough, I need to keep as many values as possible. Should this be done with a while loop, if so, how would it be done.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-30 : 09:03:13

Where is the data?
What is "first" values?
What to do with other values?

Peter Larsson
Helsingborg, Sweden

nr
SQLTeam MVY

12543 Posts

Posted - 2007-01-30 : 09:04:02

select top 6 * from
(select top 4 * from tbl where val > 80 order by val
union
select top 2 * from tbl where val > 90 order by val
) a
order by val

It's probably not what you want but you should be able to amend it.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

eembme
Starting Member

18 Posts

Posted - 2007-01-30 : 09:15:16

I have my query completly developed except I have too many values being returned. This is a large quantity of data, so it is difficult to display. Basically on a given day, testing is performed on a person. The results of the test are given a score. I need to retain the first 4 tests above 80 for a given day, or the first 2 above 90 (which ever happens first). The remaining data is discarded. The current query is sorted by patient ID, date, time (ascending).

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-30 : 09:30:14

Are you using SQL Server 2005?

Peter Larsson
Helsingborg, Sweden

eembme
Starting Member

18 Posts

Posted - 2007-01-30 : 09:39:31

Yes, SQL server 2005 (just the standard edition)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-30 : 09:46:49

This will give you an hint of how to select the two subsets

SELECT		*
FROM		TestDataTable
WHERE		Score >= 80
		AND ROW_NUMBER() OVER (PARTITION BY PersonID, SomeDateCol ORDER BY SomeDateTimeCol) <= 4

SELECT		*
FROM		TestDataTable
WHERE		Score >= 90
		AND ROW_NUMBER() OVER (PARTITION BY PersonID, SomeDateCol ORDER BY SomeDateTimeCol) <= 2

Peter Larsson
Helsingborg, Sweden

eembme
Starting Member

18 Posts

Posted - 2007-01-30 : 10:20:44

Here is my sample code-complete just a table of the completed query. It has all the fields that I need.

SELECT *
FROM Complete
WHERE [Operator Index] >= 80 AND ROW_NUMBER() OVER (PARTITION BY [Patient Identifier], Date
ORDER BY [Patient Identifier], Date, Time) <= 4

Error Message: windowed functions can only appear in the SELECT or ORDER BY cluases.

I know when I have used the row number function before, it needed to be included in the select statement.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-30 : 10:28:30

Ok. Try a derived table then.

SELECT	[Patient Identifier],
	[Operator Index],
	Date,
	Time
FROM	(
		SELECT	[Patient Identifier],
			[Operator Index],
			Date,
			Time
			ROW_NUMBER() OVER (PARTITION BY [Patient Identifier], Date ORDER BY [Patient Identifier], Date, Time) AS RowNum
		FROM	Complete
		WHERE	[Operator Index] >= 80
	) AS d
WHERE	RowNum <= 4

Peter Larsson
Helsingborg, Sweden

eembme
Starting Member

18 Posts

Posted - 2007-01-30 : 11:31:15

everything is working except I need it to keep either 4 readings of 80 or 2 readings of 90 (not both). If there are 2 90 readings, it is still picking up some of the 80 readings. How can I limit the number to either 2 90's or 4 80's?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-30 : 12:04:44

Something like this?

SELECT	d.[Patient Identifier],
	d.Date,
	d.[Operator Index],
	d.Time
FROM	(
		SELECT		ISNULL(t9.[Patient Identifier], t8.[Patient Identifier]) AS [Patient Identifier],
				ISNULL(t9.Date, t8.Date) AS Date,
				ISNULL(t9.[Rows], t8.[Rows]) AS [Rows],
				c.[Operator Index],
				c.Time,
				ROW_NUMBER() OVER (PARTITION BY ISNULL(t9.[Patient Identifier], t8.[Patient Identifier]), ISNULL(t9.Date, t8.Date) ORDER BY c.Time) AS RowNum
		FROM		(
					SELECT		[Patient Identifier],
							Date,
							2 AS [Rows]
					FROM		Complete
					WHERE		[Operator Index] >= 90
					GROUP BY	[Patient Identifier],
							Date
					HAVING		COUNT(*) >= 2
				) AS t9
		FULL JOIN	(
					SELECT		[Patient Identifier],
							Date,
							4 AS [Rows]
					FROM		Complete
					WHERE		[Operator Index] >= 80
					GROUP BY	[Patient Identifier],
							Date
					HAVING		COUNT(*) >= 4
				) AS t8 ON t8.[Patient Identifier] = t9.[Patient Identifier] AND t8.Date = t9.Date
		INNER JOIN	Complete AS c ON c.[Patient Identifier] = ISNULL(t9.[Patient Identifier], t8.[Patient Identifier]) AND c.Date = ISNULL(t9.Date, t8.Date)
	) AS d
WHERE	d.RowNum <= d.[Rows]

EDIT: According to reserved words and some typos.

Peter Larsson
Helsingborg, Sweden

eembme
Starting Member

18 Posts

Posted - 2007-01-30 : 15:01:21

The error message for that code is "no column was specified for column 3 of 't8'
Invalid column name 'Rows'
Invalid column name 'OperatorIndex'

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-30 : 15:04:42

1) Change "4" to "4 AS [Rows]"
2) Change "2 AS Rows" to "2 AS [Rows"]
3) Change "WHERE d.RowNum <= d.Rows" to "WHERE d.RowNum <= d.[Rows]"
4) Change "c.OperatorIndex" to "c.[Operator Index]"

Peter Larsson
Helsingborg, Sweden

eembme
Starting Member

18 Posts

Posted - 2007-01-30 : 15:08:56

It worked. That was amazing. I have tried everything to get this to work. Thanks!

eembme
Starting Member

18 Posts

Posted - 2007-01-30 : 19:14:19

I reviewed all of my data, and it is not quite picking up the right numbers. It looked correct during a quick review, but I have found several instances where it is taking a value in the 80's instead of the value in the 90's. Any suggestions?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-30 : 23:54:43

Not without some proper sample data and your expected output based on the provided sample data.

Peter Larsson
Helsingborg, Sweden

maya_zakry
Constraint Violating Yak Guru

379 Posts

Posted - 2007-01-31 : 01:01:40

hello,
what is this line means?

ROW_NUMBER() OVER (PARTITION BY [Patient Identifier], Date ORDER BY [Patient Identifier], Date, Time) AS RowNum

~~~Focus on problem, not solution~~~

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-31 : 01:45:19

It sets the rownumber for a record in the resultset according to [Patient Identifier], Date and Time.
For every new combination of [Patient Identifier] and Date the rownumber is reset to 1, and incremented by 1 within the group ordered by Time.

All this is readable in Books Online.

Peter Larsson
Helsingborg, Sweden

eembme
Starting Member

18 Posts

Posted - 2007-01-31 : 09:18:09

Patient Identifier Patient Initials Date Time Operator Index
0517_00003 GHV 18-Oct-06 11:48 91 (keep)
0517_00003 GHV 18-Oct-06 11:50 100 (keep)
0517_00004 JMH 17-Oct-06 11:41 89 (discard)
0517_00004 JMH 17-Oct-06 11:50 93 (keep)
0517_00004 JMH 17-Oct-06 11:52 91 (keep)
0517_00004 JMH 17-Oct-06 12:00 93 (discard)
0534_00003 JS 21-Nov-06 12:35 100 (keep)
0534_00003 JS 21-Nov-06 12:46 100 (keep)
0534_00004 ChM 20-Nov-06 10:49 100 (keep)
0534_00004 ChM 20-Nov-06 10:51 100 (keep)
0534_00006 JK 4-Dec-06 9:38 100 (keep)
0534_00006 JK 4-Dec-06 9:47 84 (discard)
0534_00006 JK 4-Dec-06 9:50 93 (keep)
0534_00007 TL 29-Nov-06 9:22 98 (keep)
0534_00007 TL 29-Nov-06 9:34 100 (keep)
0539_00001 PGL 9-Oct-06 9:39 100 (keep)
0539_00001 PGL 9-Oct-06 9:43 95 (keep)
0539_00002 DWR 27-Oct-06 10:04 91 (keep)
0539_00002 DWR 31-Oct-06 11:40 92 (keep)
0539_00002 DWR 31-Oct-06 11:41 96 (discard)
0539_00002 DWR 31-Oct-06 11:42 92 (discard)
0539_00003 JmL 30-Nov-06 9:14 96 (keep)
0539_00003 JmL 30-Nov-06 9:18 97 (keep)

eembme
Starting Member

18 Posts

Posted - 2007-01-31 : 22:20:32

Current Code

SELECT [Patient Identifier], Date, [Operator Index], Time
FROM (SELECT ISNULL(t9.[Patient Identifier], t8.[Patient Identifier]) AS [Patient Identifier], ISNULL(t9.Date, t8.Date) AS Date, ISNULL(t9.Rows, t8.Rows)
AS Rows, c.[Operator Index], c.Time, ROW_NUMBER() OVER (PARTITION BY ISNULL(t9.[Patient Identifier], t8.[Patient Identifier]),
ISNULL(t9.Date, t8.Date)
ORDER BY c.Time) AS RowNum
FROM (SELECT [Patient Identifier], Date, 2 AS [Rows]
FROM [First Step]
WHERE [Operator Index] >= 90
GROUP BY [Patient Identifier], Date
HAVING COUNT(*) >= 2) AS t9 FULL JOIN
(SELECT [Patient Identifier], Date, 4 AS [Rows]
FROM [First Step]
WHERE [Operator Index] >= 80
GROUP BY [Patient Identifier], Date
HAVING COUNT(*) >= 4) AS t8 ON t8.[Patient Identifier] = t9.[Patient Identifier] AND t8.Date = t9.Date INNER JOIN
Complete AS c ON c.[Patient Identifier] = ISNULL(t9.[Patient Identifier], t8.[Patient Identifier]) AND c.Date = ISNULL(t9.Date, t8.Date)) AS d
WHERE d .RowNum <= d .[Rows]

Current Input
Patient ID DATE Time Operator Index
51700003 18OCT2006 11:48 91
51700003 18OCT2006 11:50 100
51700004 17OCT2006 11:41 89
51700004 17OCT2006 11:50 93
51700004 17OCT2006 11:52 91
51700004 17OCT2006 12:00 93

Current Output

Patient ID DATE Time Operator Index
0517_00003 18OCT2006 11:48 91
0517_00003 18OCT2006 11:50 100
0517_00004 17OCT2006 11:41 89
0517_00004 17OCT2006 11:50 93

It should be
Patient ID DATE Time Operator Index
51700003 18OCT2006 11:48 91
51700003 18OCT2006 11:50 100
51700004 17OCT2006 11:50 93
51700004 17OCT2006 11:52 91

The data is organized by patient id, date, time (ascending)
For a given patient id, on a certain data, testing was performed. A value between 80 and 100 is acceptable data. I need either the first 2 tests with a score above 90 or the first 4 tests above 80. (The tests are further sorted by time because the testing is time dependant. On some occassions, there is just too much data. What is wrong with my current query?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources