Highest and Lowest values - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Highest and Lowest values

Author

Topic

s_k_sameer
Starting Member

9 Posts

Posted - 2008-07-07 : 07:03:25

Hi,

MY input values

Low Value:
A value(MIDDLE VALUE) to which RIGHT SIDE and LEFTSIDE values are should be HIGH.

High Value:
A value(MIDDLE VALUE) to which RIGHT SIDE and LEFTSIDE values are should be LOW.

create table temp(time datetime,[close] money)

insert into temp values('11:20','4006.00')

insert into temp values('11:21','4007.20')

insert into temp values('11:22','4008.00')

insert into temp values('11:23','4009.70')

insert into temp values('11:24','4010.00')

insert into temp values('11:25','4011.50')

insert into temp values('11:26','4012.80')

insert into temp values('11:27','4013.00')

insert into temp values('11:28','4012.00')

insert into temp values('11:29','4011.90')

insert into temp values('11:30','4012.80')

insert into temp values('11:31','4013.60')

insert into temp values('11:32','4014.20')

insert into temp values('11:33','4013.00')

insert into temp values('11:34','4012.40')

insert into temp values('11:35','4014.20')

insert into temp values('11:36','4015.60')

insert into temp values('11:37','4017.40')

insert into temp values('11:38','4018.50')

insert into temp values('11:39','4019.00')

insert into temp values('11:40','4020.50')----------high--

insert into temp values('11:41','4018.00')

insert into temp values('11:42','4017.70')

insert into temp values('11:43','4016.80')

insert into temp values('11:44','4017.50')

insert into temp values('11:45','4018.40')

insert into temp values('11:46','4016.00')

insert into temp values('11:47','4015.60')

insert into temp values('11:48','4014.00')

insert into temp values('11:49','4013.30')

insert into temp values('11:50','4012.60')

insert into temp values('11:51','4011.40')

insert into temp values('11:52','4010.19')------low---

insert into temp values('11:53','4011.60')

insert into temp values('11:54','4013.50')

insert into temp values('11:55','4014.40')

insert into temp values('11:56','4015.00')

insert into temp values('11:57','4017.80')

insert into temp values('11:58','4018.00')

insert into temp values('11:59','4019.40')

insert into temp values('12:00','4018.10')

insert into temp values('12:01','4017.30')

insert into temp values('12:02','4019.00')

insert into temp values('12:03','4021.50')

insert into temp values('12:04','4025.00')

insert into temp values('12:05','4027.70')

insert into temp values('12:06','4029.80')

insert into temp values('12:07','4031.00')

insert into temp values('12:08','4032.20')

insert into temp values('12:09','4033.00')----high---

insert into temp values('12:10','4032.00')

insert into temp values('12:11','4031.00')

insert into temp values('12:12','4030.00')

insert into temp values('12:13','4030.50')

insert into temp values('12:14','4031.80')

insert into temp values('12:15','4032.00')

insert into temp values('12:16','4031.00')

insert into temp values('12:17','4029.00')

insert into temp values('12:18','4027.00')

insert into temp values('12:19','4025.00')

insert into temp values('12:20','4024.00')

insert into temp values('12:21','4022.00')----low---

insert into temp values('12:22','4023.00')

insert into temp values('12:23','4024.80')

insert into temp values('12:24','4024.10')

insert into temp values('12:25','4023.50')

insert into temp values('12:26','4022.00')

insert into temp values('12:27','4023.00')

insert into temp values('12:28','4024.50')

insert into temp values('12:29','4025.00')

insert into temp values('12:30','4027.00')

insert into temp values('12:31','4028.00')

insert into temp values('12:32','4029.00')

insert into temp values('12:33','4031.00')

insert into temp values('12:34','4033.00')

insert into temp values('12:35','4035.00')

insert into temp values('12:36','4037.00')

insert into temp values('12:37','4039.00')

insert into temp values('12:38','4042.00')

out put table looks like:

11:40 4020.50

11:52 4010.19

12:09 4033.00

12:21 4022.00

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-07-07 : 08:05:57

why this 11:27 record is not consider HIGH ?


insert into temp values('11:26','4012.80')
insert into temp values('11:27','4013.00')
insert into temp values('11:28','4012.00')

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-07-07 : 09:50:48

saw your post in MSDN Forum.
http://forums.microsoft.com/msdn/ShowPost.aspx?siteid=1&PostID=3576564

quote:
I need to find the LOWEST value, to this value LEFT SIDE of 5 minutes should be HIGH and the same time RIGHT SIDE of 5 minutes values should be HIGH. If there is No such type of value ignore. And similarly for HIGHEST value:

To find HIGHEST value, a value to which LEFT SIDE and RIGH SIDE of 5 minutes values should be LOW. if this satisfied i.e HIGHEST value else ignore.

You should have also explain your rules / conditions.

When finding the Higest / lowest value is it inclusive of records of same value (record 12:26) ? The query i posted checks for greater or smaller value. As if includes equal value it will include record 12:15.


insert into [temp] values('12:16','4031.00')
insert into [temp] values('12:17','4029.00')
insert into [temp] values('12:18','4027.00')
insert into [temp] values('12:19','4025.00')
insert into [temp] values('12:20','4024.00')
insert into [temp] values('12:21','4022.00')----low---
insert into [temp] values('12:22','4023.00')
insert into [temp] values('12:23','4024.80')
insert into [temp] values('12:24','4024.10')
insert into [temp] values('12:25','4023.50')
insert into [temp] values('12:26','4022.00')

CREATE TABLE [temp]([time] datetime, [close] money)
INSERT INTO [temp] VALUES('11:20','4006.00')
INSERT INTO [temp] VALUES('11:21','4007.20')
INSERT INTO [temp] VALUES('11:22','4008.00')
INSERT INTO [temp] VALUES('11:23','4009.70')
INSERT INTO [temp] VALUES('11:24','4010.00')
INSERT INTO [temp] VALUES('11:25','4011.50')
INSERT INTO [temp] VALUES('11:26','4012.80')
INSERT INTO [temp] VALUES('11:27','4013.00')
INSERT INTO [temp] VALUES('11:28','4012.00')
INSERT INTO [temp] VALUES('11:29','4011.90')
INSERT INTO [temp] VALUES('11:30','4012.80')
INSERT INTO [temp] VALUES('11:31','4013.60')
INSERT INTO [temp] VALUES('11:32','4014.20')
INSERT INTO [temp] VALUES('11:33','4013.00')
INSERT INTO [temp] VALUES('11:34','4012.40')
INSERT INTO [temp] VALUES('11:35','4014.20')
INSERT INTO [temp] VALUES('11:36','4015.60')
INSERT INTO [temp] VALUES('11:37','4017.40')
INSERT INTO [temp] VALUES('11:38','4018.50')
INSERT INTO [temp] VALUES('11:39','4019.00')
INSERT INTO [temp] VALUES('11:40','4020.50')----------high--
INSERT INTO [temp] VALUES('11:41','4018.00')
INSERT INTO [temp] VALUES('11:42','4017.70')
INSERT INTO [temp] VALUES('11:43','4016.80')
INSERT INTO [temp] VALUES('11:44','4017.50')
INSERT INTO [temp] VALUES('11:45','4018.40')
INSERT INTO [temp] VALUES('11:46','4016.00')
INSERT INTO [temp] VALUES('11:47','4015.60')
INSERT INTO [temp] VALUES('11:48','4014.00')
INSERT INTO [temp] VALUES('11:49','4013.30')
INSERT INTO [temp] VALUES('11:50','4012.60')
INSERT INTO [temp] VALUES('11:51','4011.40')
INSERT INTO [temp] VALUES('11:52','4010.19')------low---
INSERT INTO [temp] VALUES('11:53','4011.60')
INSERT INTO [temp] VALUES('11:54','4013.50')
INSERT INTO [temp] VALUES('11:55','4014.40')
INSERT INTO [temp] VALUES('11:56','4015.00')
INSERT INTO [temp] VALUES('11:57','4017.80')
INSERT INTO [temp] VALUES('11:58','4018.00')
INSERT INTO [temp] VALUES('11:59','4019.40')
INSERT INTO [temp] VALUES('12:00','4018.10')
INSERT INTO [temp] VALUES('12:01','4017.30')
INSERT INTO [temp] VALUES('12:02','4019.00')
INSERT INTO [temp] VALUES('12:03','4021.50')
INSERT INTO [temp] VALUES('12:04','4025.00')
INSERT INTO [temp] VALUES('12:05','4027.70')
INSERT INTO [temp] VALUES('12:06','4029.80')
INSERT INTO [temp] VALUES('12:07','4031.00')
INSERT INTO [temp] VALUES('12:08','4032.20')
INSERT INTO [temp] VALUES('12:09','4033.00')----high---
INSERT INTO [temp] VALUES('12:10','4032.00')
INSERT INTO [temp] VALUES('12:11','4031.00')
INSERT INTO [temp] VALUES('12:12','4030.00')
INSERT INTO [temp] VALUES('12:13','4030.50')
INSERT INTO [temp] VALUES('12:14','4031.80')
INSERT INTO [temp] VALUES('12:15','4032.00')
INSERT INTO [temp] VALUES('12:16','4031.00')
INSERT INTO [temp] VALUES('12:17','4029.00')
INSERT INTO [temp] VALUES('12:18','4027.00')
INSERT INTO [temp] VALUES('12:19','4025.00')
INSERT INTO [temp] VALUES('12:20','4024.00')
INSERT INTO [temp] VALUES('12:21','4022.00')----low---
INSERT INTO [temp] VALUES('12:22','4023.00')
INSERT INTO [temp] VALUES('12:23','4024.80')
INSERT INTO [temp] VALUES('12:24','4024.10')
INSERT INTO [temp] VALUES('12:25','4023.50')
INSERT INTO [temp] VALUES('12:26','4022.00')
INSERT INTO [temp] VALUES('12:27','4023.00')
INSERT INTO [temp] VALUES('12:28','4024.50')
INSERT INTO [temp] VALUES('12:29','4025.00')
INSERT INTO [temp] VALUES('12:30','4027.00')
INSERT INTO [temp] VALUES('12:31','4028.00')
INSERT INTO [temp] VALUES('12:32','4029.00')
INSERT INTO [temp] VALUES('12:33','4031.00')
INSERT INTO [temp] VALUES('12:34','4033.00')
INSERT INTO [temp] VALUES('12:35','4035.00')
INSERT INTO [temp] VALUES('12:36','4037.00')
INSERT INTO [temp] VALUES('12:37','4039.00')
INSERT INTO [temp] VALUES('12:38','4042.00')

SELECT	p.[time], p.[close]	-- HIGH VALUE
FROM	(
		SELECT	c.[time], c.[close]
		FROM	[temp] c
			INNER JOIN [temp] p	ON	p.[time]	>= DATEADD(minute, -5, c.[time])
						AND	p.[time]	<  c.[time]
		WHERE	c.[close] > p.[close]
		GROUP BY c.[time], c.[close]
		HAVING COUNT(*) = 5
	) p
	INNER JOIN
	(
		SELECT	c.[time], c.[close]
		FROM	[temp] c
			INNER JOIN [temp] n	ON	n.[time]	<= DATEADD(minute, 5, c.[time])
						AND	n.[time]	>  c.[time]
		WHERE	c.[close] > n.[close]
		GROUP BY c.[time], c.[close]
		HAVING COUNT(*) = 5
	) n	ON	p.[time]	= n.[time]

UNION ALL

SELECT	p.[time], p.[close]	-- LOW VALUE
FROM	(
		SELECT	c.[time], c.[close]
		FROM	[temp] c
			INNER JOIN [temp] p	ON	p.[time]	>= DATEADD(minute, -5, c.[time])
						AND	p.[time]	<  c.[time]
		WHERE	c.[close] < p.[close]
		GROUP BY c.[time], c.[close]
		HAVING COUNT(*) = 5
	) p
	INNER JOIN
	(
		SELECT	c.[time], c.[close]
		FROM	[temp] c
			INNER JOIN [temp] n	ON	n.[time]	<= DATEADD(minute, 5, c.[time])
						AND	n.[time]	>  c.[time]
		WHERE	c.[close] < n.[close]
		GROUP BY c.[time], c.[close]
		HAVING COUNT(*) = 5
	) n	ON	p.[time]	= n.[time]
ORDER BY [time]

/*
time                      close                 
------------------------- --------------------- 
1900-01-01 11:40:00.000               4020.5000 
1900-01-01 11:52:00.000               4010.1900 
1900-01-01 12:09:00.000               4033.0000 

(3 row(s) affected)
*/

DROP TABLE [temp]

KH
[spoiler]Time is always against us[/spoiler]

ayamas
Aged Yak Warrior

552 Posts

Posted - 2008-07-07 : 10:03:23

Try this one to.Not sure whether this is what you want.

create table temp(id int identity(1,1),time datetime,[close] money)

insert into temp values('11:20','4006.00')

insert into temp values('11:21','4007.20')

insert into temp values('11:22','4008.00')

insert into temp values('11:23','4009.70')

insert into temp values('11:24','4010.00')

insert into temp values('11:25','4011.50')

insert into temp values('11:26','4012.80')

insert into temp values('11:27','4013.00')

insert into temp values('11:28','4012.00')

insert into temp values('11:29','4011.90')

insert into temp values('11:30','4012.80')

insert into temp values('11:31','4013.60')

insert into temp values('11:32','4014.20')

insert into temp values('11:33','4013.00')

insert into temp values('11:34','4012.40')

insert into temp values('11:35','4014.20')

insert into temp values('11:36','4015.60')

insert into temp values('11:37','4017.40')

insert into temp values('11:38','4018.50')

insert into temp values('11:39','4019.00')

insert into temp values('11:40','4020.50')----------high--

insert into temp values('11:41','4018.00')

insert into temp values('11:42','4017.70')

insert into temp values('11:43','4016.80')

insert into temp values('11:44','4017.50')

insert into temp values('11:45','4018.40')

insert into temp values('11:46','4016.00')

insert into temp values('11:47','4015.60')

insert into temp values('11:48','4014.00')

insert into temp values('11:49','4013.30')

insert into temp values('11:50','4012.60')

insert into temp values('11:51','4011.40')

insert into temp values('11:52','4010.19')------low---

insert into temp values('11:53','4011.60')

insert into temp values('11:54','4013.50')

insert into temp values('11:55','4014.40')

insert into temp values('11:56','4015.00')

insert into temp values('11:57','4017.80')

insert into temp values('11:58','4018.00')

insert into temp values('11:59','4019.40')

insert into temp values('12:00','4018.10')

insert into temp values('12:01','4017.30')

insert into temp values('12:02','4019.00')

insert into temp values('12:03','4021.50')

insert into temp values('12:04','4025.00')

insert into temp values('12:05','4027.70')

insert into temp values('12:06','4029.80')

insert into temp values('12:07','4031.00')

insert into temp values('12:08','4032.20')

insert into temp values('12:09','4033.00')----high---

insert into temp values('12:10','4032.00')

insert into temp values('12:11','4031.00')

insert into temp values('12:12','4030.00')

insert into temp values('12:13','4030.50')

insert into temp values('12:14','4031.80')

insert into temp values('12:15','4032.00')

insert into temp values('12:16','4031.00')

insert into temp values('12:17','4029.00')

insert into temp values('12:18','4027.00')

insert into temp values('12:19','4025.00')

insert into temp values('12:20','4024.00')

insert into temp values('12:21','4022.00')----low---

insert into temp values('12:22','4023.00')

insert into temp values('12:23','4024.80')

insert into temp values('12:24','4024.10')

insert into temp values('12:25','4023.50')

insert into temp values('12:26','4022.00')

insert into temp values('12:27','4023.00')

insert into temp values('12:28','4024.50')

insert into temp values('12:29','4025.00')

insert into temp values('12:30','4027.00')

insert into temp values('12:31','4028.00')

insert into temp values('12:32','4029.00')

insert into temp values('12:33','4031.00')

insert into temp values('12:34','4033.00')

insert into temp values('12:35','4035.00')

insert into temp values('12:36','4037.00')

insert into temp values('12:37','4039.00')

insert into temp values('12:38','4042.00')

declare @tblhigh as table(id int identity(1,1),close1 money,close2 money)
declare @tbllow as table(id int identity(1,1),close1 money,close2 money)

insert into @tblhigh
select t1.[close]as t1close,t.[close]as t2close from temp t1
cross apply
(select [close] from temp t2 where t2.id=t1.id+1 and t2.[close]<t1.[close] )t

insert into @tbllow
select t1.[close]as t1close,t.[close]as t2close from temp t1
cross apply
(select [close] from temp t2 where t2.id=t1.id+1 and t2.[close]>t1.[close] )t

select t1.close1,'high' as status from @tblhigh
t1 inner join @tblhigh t2 on t1.id=t2.id+1 where t1.close1<>t2.close2
union all
select t1.close1,'low' as status from @tbllow
t1 inner join @tbllow t2 on t1.id=t2.id+1 where t1.close1<>t2.close2

drop table temp

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources