November 7, 2011 at 8:05 pm
I have this table:
From | To
1 | 500
900 | 1020
5220 | 5340
6000 | 7000
9000 | 10000
I want to output these data based on that table:
IdleFrom | IdleTo
500 | 900
1020 | 5220
5340 | 6000
7000 | 9000
I think I can do that logic using cursor loop (still trying now). But I kinda wanna know if there is a quick way to do that using simple query.
Thanks for your help!
November 7, 2011 at 8:21 pm
Here is my solution:
DECLARE @TableResult TABLE(
Value1 INT
,Value2 INT
)
DECLARE @Value1 INT, @Value2 INT
DECLARE @cur CURSOR
SET @cur = CURSOR FOR
SELECT Value1, Value2 FROM Tester ORDER BY Value1 ASC
OPEN @cur
FETCH NEXT FROM @cur INTO @Value1, @Value2
WHILE @@FETCH_STATUS = 0 BEGIN
DECLARE @NextValue1 INT, @NextValue2 INT
SELECT TOP 1 @NextValue1 = Value1, @NextValue2 = Value2
FROM Tester WHERE Value1 >= @Value2
ORDER BY Value1
IF @NextValue1 > @Value2 BEGIN
INSERT @TableResult VALUES (
@Value2
,@NextValue1
)
END
FETCH NEXT FROM @cur INTO @Value1, @Value2
END CLOSE @cur DEALLOCATE @cur
SELECT Value1, Value2 FROM Tester ORDER BY Value1 ASC
SELECT Value1, Value2 FROM @TableResult
November 7, 2011 at 9:10 pm
How's this:
SELECTB.[To] AS IdleFrom
, A.[From] AS IdleTo
FROM
A
OUTER APPLY(
SELECTTOP 1 [To]
FROM
WHERE[To] < A.[From]
ORDER BY[To] DESC) B
WHEREB.[To] IS NOT NULL;
November 7, 2011 at 9:16 pm
First lets set up the sample data ( remember, you chances of getting a lovely query will depend solely on how clear and elaborate your question is)
SET NOCOUNT ON;
BEGIN
IF OBJECT_ID('TempDB..#SourceTable') IS NOT NULL
DROP TABLE #SourceTable;
END;
/* Create a source table */
SELECT *
INTO #SourceTable
FROM
(
VALUES (1 , 500 )
,(900 , 1020 )
,(5220 , 5340 )
,(6000 , 7000)
,(9000 , 10000)
) AS SourceData (FromValue, ToValue);
Now the solution to your puzzle ( this does not require any cursor/while loop )
/* create a temp table to get From and To columns in a single column called
vals using UNPIVOT */
SELECT ID = IDENTITY(INT, 1,1),
Vals
INTO #TempTable
FROM #SourceTable
UNPIVOT ( Vals FOR Cols IN ([FromValue], [ToValue])) UnPivot_Handler
ORDER BY Vals ASC
-- A supportive index
CREATE NONCLUSTERED INDEX NIX_#TempTable_ID
ON #TempTable(ID)
INCLUDE (VALS)
-- make sure the smallest and largest values are deleted )
DELETE FROM #TempTable
WHERE ID = ( SELECT TOP 1 ID FROM #TempTable ORDER BY ID DESC)
OR ID = ( SELECT TOP 1 ID FROM #TempTable ORDER BY ID ASC)
-- with the set of data, arrive at the desired result
; WITH CTE0 AS
(
SELECT RN = ((ROW_NUMBER() OVER (ORDER BY ID) + 1 ) / 2 ) ,
Vals
FROM #TempTable
)
SELECT MIN( Vals ) IdleFrom, MAX(Vals) IdleTo
FROM CTE0
GROUP BY RN
Doest this work for you?
November 7, 2011 at 9:19 pm
goofbauer (11/7/2011)
How's this:
SELECTB.[To] AS IdleFrom
, A.[From] AS IdleTo
FROM
A
OUTER APPLY(
SELECTTOP 1 [To]
FROM
WHERE[To] < A.[From]
ORDER BY[To] DESC) B
WHEREB.[To] IS NOT NULL;
Wonderful idea; i always thot we could accomplish this via APPLY, but dint put much thot in realizing it. Nicely done.
Your code assumes 2 things though
1. The From values will always be greater than To values
2. There will be no overlapping values of From or To with other rows
November 7, 2011 at 9:43 pm
Thanks, ColdCoffee. I owe drew.allen for expanding my thinking on using APPLY.
And good points about the assumptions. I guess a false assumption on either of those two points could complicate things a bit. Though I think rktn_odin's initial post and cursor work might have suggested such, it's never a good idea in this line of work to make assumptions.
November 7, 2011 at 9:54 pm
Hello to both of you, thanks for your help.
And to clarify, yes my source table will never have overlapped values of From and To.
Thanks again!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply