March 31, 2011 at 8:35 am
Hi,
I would like some help with an tsql script I am trying to write.
I have tried this different ways, but it is still not giving me the required results.
I have attached an image to illustrate what I'm trying to achieve.
Essentially I have a input table that contains interval data, it has FROM, TO and LENGTH fields. The idea is that the interval length determines the output results for the query, for example:
if the interval is >= 10 it should create new intervals by splitting the original one i.e. 40.3 - 69, for eaxample 1m interval followed by a larger 9m.
so the input interval 40.3 - 69 would output as:
ID IDFROM
A40.341.31
A41.350.39
A50.351.31
A51.360.39
A60.361.31
A61.3697.7
Thanks
DECLARE @TEMPDATA TABLE
(ID VARCHAR(20)
,IDFROM FLOAT
,IDTO FLOAT
,LENGTH FLOAT
)
INSERT INTO @TEMPDATA VALUES('A','0','5','5')
INSERT INTO @TEMPDATA VALUES('A','5','15','10')
INSERT INTO @TEMPDATA VALUES('A','15','25','10')
INSERT INTO @TEMPDATA VALUES('A','25','40.3','15.3')
INSERT INTO @TEMPDATA VALUES('A','40.3','69','28.7')
INSERT INTO @TEMPDATA VALUES('A','69','80','11')
INSERT INTO @TEMPDATA VALUES('A','80','89','9')
INSERT INTO @TEMPDATA VALUES('A','89','98','9')
INSERT INTO @TEMPDATA VALUES('A','98','100','2')
INSERT INTO @TEMPDATA VALUES('A','100','110','10')
DECLARE @TEMPDATA2 TABLE
(ID2 VARCHAR(20)
,IDFROM2 FLOAT
,IDTO2 FLOAT
,PROCESS VARCHAR(50)
,PROCESS2 VARCHAR(50)
)
DECLARE @ID VARCHAR(20)
DECLARE @IDFROM FLOAT
DECLARE @IDTO FLOAT
DECLARE @LENGTH FLOAT
DECLARE @INTERVAL FLOAT
DECLARE csr CURSOR FOR
SELECT * FROM @TEMPDATA
OPEN csr
FETCH NEXT FROM csr INTO @ID,@IDFROM,@IDTO,@LENGTH
WHILE @@FETCH_STATUS=0
BEGIN
SET @INTERVAL = 10
WHILE @LENGTH >=@INTERVAL
BEGIN
IF @LENGTH >=@INTERVAL
BEGIN
SET @IDTO = @IDFROM + @INTERVAL
INSERT @TEMPDATA2 (ID2,IDFROM2,IDTO2,PROCESS,PROCESS2)
VALUES(@ID,@IDFROM,@IDTO-(@INTERVAL-1),'1m','@LENGTH >=10')
SET @IDFROM = @IDTO
SET @LENGTH = @LENGTH - @INTERVAL - (@IDFROM - CAST((@IDFROM) AS INT))
--insert no sample entries for remaining 9 metres
SET @IDFROM = @IDTO-(@INTERVAL-1)
INSERT @TEMPDATA2 (ID2,IDFROM2,IDTO2,PROCESS,PROCESS2)
VALUES(@ID,@IDFROM,@IDTO,'leftover','@LENGTH >=10')
END
END
SET @INTERVAL = 9
WHILE @LENGTH >=@INTERVAL
BEGIN
IF @LENGTH >=@INTERVAL
BEGIN
SET @IDTO = @IDFROM + @INTERVAL
INSERT @TEMPDATA2 (ID2,IDFROM2,IDTO2,PROCESS,PROCESS2)
VALUES(@ID,@IDFROM,@IDTO-(@INTERVAL-1),'1m','@LENGTH >=9')
SET @IDFROM = @IDTO
SET @LENGTH = @LENGTH - @INTERVAL - (@IDFROM - CAST((@IDFROM) AS INT))
--insert no sample entries for remaining 8 metres
SET @IDFROM = @IDTO-(@INTERVAL-1)
INSERT @TEMPDATA2 (ID2,IDFROM2,IDTO2,PROCESS,PROCESS2)
VALUES(@ID,@IDFROM,@IDTO,'leftover','@LENGTH >=9')
END
END
SET @INTERVAL = @LENGTH
WHILE (@LENGTH >0 AND @LENGTH < 9)
BEGIN
IF (@LENGTH >0 AND @LENGTH < 9)
BEGIN
SET @IDTO = @IDFROM + @LENGTH
INSERT @TEMPDATA2 (ID2,IDFROM2,IDTO2,PROCESS,PROCESS2)
VALUES(@ID,@IDFROM,@IDTO-(@INTERVAL-1),'1m','@LENGTH >0 AND @LENGTH < 9')
SET @IDFROM = @IDTO
SET @LENGTH = @LENGTH - @INTERVAL - (@IDFROM - CAST((@IDFROM) AS INT))
--insert no sample entries for remaining interval
SET @IDFROM = @IDTO-(@INTERVAL-1)
INSERT @TEMPDATA2 (ID2,IDFROM2,IDTO2,PROCESS,PROCESS2)
VALUES(@ID,@IDFROM,@IDTO,'leftover','@LENGTH >0 AND @LENGTH < 9')
END
END
FETCH NEXT FROM csr INTO @ID,@IDFROM,@IDTO,@LENGTH
END
CLOSE csr
DEALLOCATE csr
SELECT * FROM @TEMPDATA2
March 31, 2011 at 9:23 am
This should get you started. You can use it to create a table valued function, which you can CROSS APPLY to your table to get the results you're looking for. Sorry I haven't got time at the moment to expand more on that, but please do post back if there's anything you don't understand.
John
DECLARE @Interval float
DECLARE @IDFrom float
DECLARE @IDTo float
SELECT @IDFrom = 40.3, @IDTo = 69
SET @Interval = @IDTo - @IDFrom
IF @Interval > 10
BEGIN
DECLARE @Tens int, @Rows int
SELECT @Tens = FLOOR(@Interval/10)
SELECT @Rows = @Tens * 2 + CASE
WHEN @Interval - @Tens * 10 > 1 THEN 2
ELSE 1
END
SELECT @IDFrom - (number%2) * 4 + number * 5
FROM master.dbo.spt_values -- or you can create your own Numbers table
WHERE number < @Rows
AND name IS NULL
END
April 1, 2011 at 6:36 am
Hi Thanks for your help, I tried to put the code into a table function so I could test the CROSS APPLY, but I keep getting a syntax error:
ALTER FUNCTION dbo.fn_GetIntervals(
@IDFrom AS float
, @IDTo AS float
, @Interval AS float
)
RETURNS TABLE
AS
--DECLARE @Interval float
--DECLARE @IDFrom float
--DECLARE @IDTo float
--SELECT @IDFrom = 40.3, @IDTo = 69
--SET @Interval = @IDTo - @IDFrom
BEGIN
IF @Interval >= 10
BEGIN
DECLARE @Tens int, @Rows int
SELECT @Tens = FLOOR(@Interval/10)
SELECT @Rows = @Tens * 2 + CASE
WHEN @Interval - @Tens * 10 > 1 THEN 2
ELSE 1
END
SELECT @IDFrom - (NUM%2) * 4 + NUM * 5
FROM master.dbo.spt_values -- or you can create your own Numbers table
WHERE NUM < @Rows
END
RETURN
END
April 1, 2011 at 7:21 am
You seem to have mangled my original code a little by changing number to NUM and removing the [font="Courier New"]AND name IS NULL[/font] line! I assume you used your own numbers table and then forget to put the code back how it was? Anyhow, it should work in a multi-statement table valued function. I strongly recommend that you don't pass @Interval as a parameter: it introduces redundancy since it can be computed from the other two parameters. You end up with something like this:
CREATE FUNCTION dbo.fn_GetIntervals(
@IDFrom AS float
, @IDTo AS float
)
RETURNS @Intervals table (IDFrom float)
AS
BEGIN
DECLARE @Interval float
SET @Interval = @IDTo - @IDFrom
IF @Interval >= 10
BEGIN
DECLARE @Tens int, @Rows int
SELECT @Tens = FLOOR(@Interval/10)
SELECT @Rows = @Tens * 2 + CASE
WHEN @Interval - @Tens * 10 > 1 THEN 2
ELSE 1
END
INSERT INTO @Intervals
SELECT @IDFrom - (Number%2) * 4 + Number * 5
FROM master.dbo.spt_values -- or you can create your own Numbers table
WHERE Number < @Rows
AND name IS NULL
END
ELSE
INSERT INTO @Intervals VALUES (@IDFrom)
RETURN
END
GO
-- Try it
SELECT IDFrom FROM fn_GetIntervals(40.3,69)
April 1, 2011 at 12:58 pm
Hi, Many thanks for the help,
I created the function and using the code below I am trying to create the IDFrom and IDTo, it seems to work but there is a problem in the results where IDFrom = 15 and IDTo = 15, is this because of the join?
Thanks
DECLARE @TEMPDATA TABLE
(ID VARCHAR(20)
,IDFROM FLOAT
,IDTO FLOAT
,LENGTH FLOAT
)
INSERT INTO @TEMPDATA VALUES('A','0','5','5')
INSERT INTO @TEMPDATA VALUES('A','5','15','10')
INSERT INTO @TEMPDATA VALUES('A','15','25','10')
INSERT INTO @TEMPDATA VALUES('A','25','40.3','15.3')
INSERT INTO @TEMPDATA VALUES('A','40.3','69','28.7')
INSERT INTO @TEMPDATA VALUES('A','69','80','11')
INSERT INTO @TEMPDATA VALUES('A','80','89','9')
INSERT INTO @TEMPDATA VALUES('A','89','98','9')
INSERT INTO @TEMPDATA VALUES('A','98','100','2')
INSERT INTO @TEMPDATA VALUES('A','100','110','10')
SELECT NewRowID = ROW_NUMBER() OVER (ORDER BY ID,G.IDFROM), G.ID, G.IDFROM [FROM], G.IDTO [TO], O.*
INTO #Product
FROM
@TEMPDATA AS G
CROSS APPLY
fn_GetIntervals(G.IDFROM,G.IDTO) AS O
SELECT A.ID
,A.[FROM]
,A.[TO]
,A.IDFrom
,B.IDFrom [IDTo]
FROM #Product A
LEFT JOIN #Product B
ON A.NewRowID = B.NewRowID-1
DROP TABLE #Product
April 4, 2011 at 4:44 am
This appears to be the special case where your interval is a multiple of 10. You can resolve this by changing your outer join into an inner join and add a check that A.IDFrom <> B.IDFrom in your join predicate.
John
April 4, 2011 at 6:53 am
Hi,
Thanks for your help again, this worked.
I just want to ask something about your code, in the following line:
SELECT @IDFrom - (NUM%2) * 4 + NUM * 5
what does the *4 and *5 do? is this unique for an interval of 10, would this need to change if the interval was say 6
Thanks
April 4, 2011 at 7:36 am
For even row numbers (starting from 0), this adds 0, 10, 20... to @IDFrom, and for odd numbers, it adds 1, 11, 21... So yes, if you wanted to do it in 6s instead of 10s, or if you wanted to sub-divide after 2 instead of 1, for example, then you'd need to rewrite that line. Of course, you could make it totally generic by parameterising the interval and sub-interval and writing the line to take account of that.
John
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply