How toGet Values Between Clause in circular format

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)

How toGet Values Between Clause in circular format

Author

Topic

baburk
Posting Yak Master

108 Posts

Posted - 2008-12-20 : 02:00:11

Hi,

DECLARE @Sample TABLE
(
StationID INT,
Value INT
)

INSERT @Sample

SELECT 1, 3 UNION ALL
SELECT 1, 2 UNION ALL
SELECT 1, 4 UNION ALL
SELECT 1, 5 UNION ALL
SELECT 1, 1 UNION ALL
SELECT 1, 8 UNION ALL
SELECT 1, 6 UNION ALL
SELECT 1, 7 UNION ALL
SELECT 2, 8 UNION ALL
SELECT 2, 6 UNION ALL
SELECT 2, 7 UNION ALL
SELECT 2, 5 UNION ALL
SELECT 2, 1 UNION ALL
SELECT 2, 3 UNION ALL
SELECT 2, 2 UNION ALL
SELECT 2, 4

SELECT StationID, Value FROM @Sample WHERE StationID = 2 AND Value BETWEEN 5 AND 7

I wants to get Valueas in these ways.

How can I?

StationID Value
1 5
1 6
1 7

1 5
1 4
1 3
1 2
1 1
1 8
1 7

Thanks in advance.

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2008-12-22 : 01:28:35

select identity(int,1,1)AS id ,stationid,value into #temp from @sample where stationid = 1 and value between 5 and 7
insert into #temp select '',''
insert into #temp
select * from @sample where stationid = 1
select stationid , value from #temp order by id

baburk
Posting Yak Master

108 Posts

Posted - 2008-12-22 : 02:54:48

quote:
Originally posted by bklr

select identity(int,1,1)AS id ,stationid,value into #temp from @sample where stationid = 1 and value between 5 and 7
insert into #temp select '',''
insert into #temp
select * from @sample where stationid = 1
select stationid , value from #temp order by id

Thanks for your reply.

stationid value

1 5
1 6
1 7
0 0
1 3
1 2
1 4
1 5
1 1
1 8
1 6
1 7

Here 6 should not come.

Since we give only 5 and 7

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-22 : 03:02:22

[code]DECLARE @Sample TABLE
(
StationID INT,
Value INT
)

INSERT @Sample
SELECT 1, 3 UNION ALL
SELECT 1, 2 UNION ALL
SELECT 1, 4 UNION ALL
SELECT 1, 5 UNION ALL
SELECT 1, 1 UNION ALL
SELECT 1, 8 UNION ALL
SELECT 1, 6 UNION ALL
SELECT 1, 7 UNION ALL
SELECT 2, 8 UNION ALL
SELECT 2, 6 UNION ALL
SELECT 2, 7 UNION ALL
SELECT 2, 5 UNION ALL
SELECT 2, 1 UNION ALL
SELECT 2, 3 UNION ALL
SELECT 2, 2 UNION ALL
SELECT 2, 4

DECLARE @StationID INT,
@LowLevel INT,
@HighLevel INT

SELECT @StationID = 2,
@LowLevel = 5,
@HighLevel = 7

-- Peso 1
SELECT StationID,
Value
FROM (
SELECT StationID,
Value,
0 AS grp,
Value AS ordnung
FROM @Sample
WHERE StationID = @StationID
AND Value >= @LowLevel
AND Value <= @HighLevel

UNION ALL

SELECT StationID,
Value,
1,
@LowLevel - Value
FROM @Sample
WHERE StationID = @StationID
AND Value <= @LowLevel

UNION ALL

SELECT StationID,
Value,
2,
@HighLevel - Value
FROM @Sample
WHERE StationID = @StationID
AND Value >= @HighLevel
) AS d
ORDER BY grp,
ordnung[/code]

E 12°55'05.63"
N 56°04'39.26"

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2008-12-22 : 03:08:11

quote:
Originally posted by baburk

quote:
Originally posted by bklr

select identity(int,1,1)AS id ,stationid,value into #temp from @sample where stationid = 1 and value between 5 and 7
insert into #temp select '',''
insert into #temp
select * from @sample where stationid = 1
select stationid , value from #temp order by id

Thanks for your reply.

stationid value

1 5
1 6
1 7
0 0
1 3
1 2
1 4
1 5
1 1
1 8
1 6
1 7

Here 6 should not come.

Since we give only 5 and 7

Welcome

select identity(int,1,1)AS id ,stationid,value into #temp from @sample where stationid = 1 and value between 5 and 7
insert into #temp select '',''
insert into #temp
select * from @sample where stationid = 1 and value <> 6
select stationid , value from #temp order by id

o/p as
1 5
1 6
1 7
0 0
1 3
1 2
1 4
1 5
1 1
1 8
1 7

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources