Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 347
  • Last Modified:

WHILE LOOP TSQL

Hi,
I would like some help with an sql script that is querying a temp table and populating another temp table.
My first table TEMPDATA has intervals IDFROM and IDTO, which are in metres.
I want to query the intervals by 10m but create a 1m interval and then a 9m interval, so:

TEMPDATA
ID    IDFROM   IDTO   LENGTH
A      0             5        5
A      5            10      15
A      10            20      10
A      20            35      15
A      35            65      30

TEMPDATA2 should be:
ID    IDFROM   IDTO   LENGTH
A      0             1        1
A      1             10        9
A      10              11        1
A      11              20        9
A      20              21        1
A      21              30        9
A      30              31        1
A      31              40        9
A      40              41        1
A      41              50        9
A      50              51        1
A      51              60        9
A      60              61        1
A      61              65        4


Thank you
DECLARE @TEMPDATA TABLE
	(ID VARCHAR(20)
	,IDFROM FLOAT
	,IDTO FLOAT
	,LENGTH FLOAT
	)

DECLARE @TEMPDATA2 TABLE
	(ID2 VARCHAR(20)
	,IDFROM2 FLOAT
	,IDTO2 FLOAT
	)

INSERT @TEMPDATA VALUES ('A','0','5','5')
INSERT @TEMPDATA VALUES ('A','5','10','15')
INSERT @TEMPDATA VALUES ('A','10','20','10')
INSERT @TEMPDATA VALUES ('A','20','35','15')
INSERT @TEMPDATA VALUES ('A','35','65','30')

DECLARE @ID VARCHAR(20)
DECLARE @IDFROM FLOAT
DECLARE @IDTO FLOAT
DECLARE @LENGTH FLOAT

DECLARE csr CURSOR FOR
SELECT * FROM @TEMPDATA

OPEN csr
FETCH NEXT FROM csr INTO @ID,@IDFROM,@IDTO,@LENGTH

WHILE @@FETCH_STATUS=0
BEGIN

WHILE @LENGTH >=10
				BEGIN
					IF(@LENGTH >= 10)
						BEGIN--B	
							SET @IDTO = @IDFROM + 10
									INSERT @TEMPDATA2 (ID2,IDFROM2,IDTO2)
									VALUES(@ID,@IDFROM,@IDTO-9)

									SET @LENGTH = @LENGTH - 10 - (@IDTO - CAST((@IDFROM) AS INT))
						END--B
				END--END WHILE LOOP

FETCH NEXT FROM csr INTO @ID,@IDFROM,@IDTO,@LENGTH

END
CLOSE csr
DEALLOCATE csr

SELECT * FROM @TEMPDATA2

Open in new window

0
crompnk
Asked:
crompnk
  • 2
  • 2
1 Solution
 
8080_DiverCommented:
Try the attached:

DECLARE @TEMPDATA TABLE(ID   VARCHAR(20),IDFROM FLOAT,IDTO FLOAT,LENGTH FLOAT)
DECLARE @TEMPDATA2 TABLE(ID2 VARCHAR(20),IDFROM2 FLOAT,IDTO2 FLOAT)
INSERT @TEMPDATA VALUES
    (
        'A'
      ,'0'
      ,'5'
      ,'5'
    )
INSERT @TEMPDATA VALUES
    (
        'A'
      ,'5'
      ,'10'
      ,'15'
    )
INSERT @TEMPDATA VALUES
    (
        'A'
      ,'10'
      ,'20'
      ,'10'
    )
INSERT @TEMPDATA VALUES
    (
        'A'
      ,'20'
      ,'35'
      ,'15'
    )
INSERT @TEMPDATA VALUES
    (
        'A'
      ,'35'
      ,'65'
      ,'30'
    )
DECLARE @ID VARCHAR(20)
DECLARE @IDFROM FLOAT
DECLARE @IDTO FLOAT 
DECLARE @LENGTH FLOAT
DECLARE @ModFlag INT;
DECLARE csr CURSOR FOR
SELECT  ID
       ,IDFROM
       ,(IDFROM % 10) ASW ModFlag
FROM @TEMPDATA
OPEN csr
FETCH NEXT
FROM csr
INTO @ID
  ,@IDFROM
  ,@ModFlag
--  ,@IDTO
--  ,@LENGTH
WHILE @@FETCH_STATUS = 0
BEGIN
	IF (@ModFlag = 0)
    BEGIN
	    SET @IDTO = @IDFROM + 1;
		SET @LENGTH = 1
--    WHILE @LENGTH >= 10
--    BEGIN
--        BEGIN--B

--     Insert the first row into @TempData2
            INSERT @TEMPDATA2
                (
                    ID2
                  ,IDFROM2
                  ,IDTO2
                )
                VALUES
                (
                    @ID
                  ,@IDFROM
                  ,@IDTO
                )


			SET	@IDFROM = @IDFROM + 1
			SET	@IDTO = @IDTO + 9
            SET @LENGTH = @LENGTH + 9

            INSERT @TEMPDATA2
                (
                    ID2
                  ,IDFROM2
                  ,IDTO2
                )
                VALUES
                (
                    @ID
                  ,@IDFROM
                  ,@IDTO
                )

        END--B
--    END    --END WHILE LOOP
    FETCH NEXT
    FROM csr
    INTO @ID
      ,@IDFROM
	  ,@ModFlag
--	  ,@IDTO
--	  ,@LENGTH
END
CLOSE csr
DEALLOCATE csr
SELECT *
FROM @TEMPDATA2

Open in new window

0
 
SharathData EngineerCommented:
I prefer not to use loops. you can try CTE.
;with cte as (
select ID,MIN(IDFROM) IDFROM, MIN(IDFROM) + 10 NextID,MAX(IDTO) IDTO from @TEMPDATA group by ID union all
select ID,IDFROM + 10,case when NextID + 10 < IDTO then NextID+10 else IDTO end as NextID, IDTO from CTE where IDFROM + 10 <= IDTO)
select ID,IDFROM,IDTO from (
select 1 rn,ID,IDFROM,IDFROM + 1 as IDTO from cte union all
select 2,ID,NextID-1,NextID IDTO from cte) t1 order by IDFROM

Open in new window

Sample result with your data.
DECLARE @TEMPDATA TABLE
	(ID VARCHAR(20)
	,IDFROM FLOAT
	,IDTO FLOAT
	,LENGTH FLOAT
	)
INSERT @TEMPDATA VALUES ('A','0','5','5')
INSERT @TEMPDATA VALUES ('A','5','10','15')
INSERT @TEMPDATA VALUES ('A','10','20','10')
INSERT @TEMPDATA VALUES ('A','20','35','15')
INSERT @TEMPDATA VALUES ('A','35','65','30')

DECLARE @TEMPDATA2 TABLE
	(ID2 VARCHAR(20)
	,IDFROM2 FLOAT
	,IDTO2 FLOAT
	)

;with cte as (
select ID,MIN(IDFROM) IDFROM, MIN(IDFROM) + 10 NextID,MAX(IDTO) IDTO from @TEMPDATA group by ID union all
select ID,IDFROM + 10,case when NextID + 10 < IDTO then NextID+10 else IDTO end as NextID, IDTO from CTE where IDFROM + 10 <= IDTO)
INSERT @TEMPDATA2
select ID,IDFROM,IDTO from (
select 1 rn,ID,IDFROM,IDFROM + 1 as IDTO from cte union all
select 2,ID,NextID-1,NextID IDTO from cte) t1 order by IDFROM

SELECT * FROM @TEMPDATA2
/*
ID	IDFROM	IDTO
A	0	1
A	9	10
A	10	11
A	19	20
A	20	21
A	29	30
A	30	31
A	39	40
A	40	41
A	49	50
A	50	51
A	59	60
A	60	61
A	64	65
*/

Open in new window

Question - do you always have same ID. In this example A? If it is diffent ID, I may need to tweak the query little bit.
0
 
crompnkAuthor Commented:
Hi, thanks for the help,

8080 Diver, I tried your script but it gave me the error:
Msg 402, Level 16, State 1, Line 47
The data types float and int are incompatible in the modulo operator.

If I use float values i.e. 10.2 I get the error, see code below.

Sharath 123, I've not used CTE before, bit tried the script, it was close but the output should be:
/*
ID      IDFROM      IDTO
A      0      1
A      9      10
A      10      11
A      11      20
A      20      21
A      21      30
A      30      31
A      31      40
A      40      41
A      41      50
A      50      51
A      51      60
A      60      61
A      61      65
*/

DECLARE @TEMPDATA TABLE(ID   VARCHAR(20),IDFROM FLOAT,IDTO FLOAT,LENGTH FLOAT)
DECLARE @TEMPDATA2 TABLE(ID2 VARCHAR(20),IDFROM2 FLOAT,IDTO2 FLOAT)
INSERT @TEMPDATA VALUES
    (
        'A'
      ,'0'
      ,'5.2'
      ,'5.2'
    )
INSERT @TEMPDATA VALUES
    (
        'A'
      ,'5.2'
      ,'10.2'
      ,'5'
    )
INSERT @TEMPDATA VALUES
    (
        'A'
      ,'10.2'
      ,'22.2'
      ,'12.2'
    )
INSERT @TEMPDATA VALUES
    (
        'A'
      ,'22.2'
      ,'35'
      ,'12.8'
    )
INSERT @TEMPDATA VALUES
    (
        'A'
      ,'35'
      ,'65'
      ,'30'
    )

--SELECT * FROM @TEMPDATA

DECLARE @ID VARCHAR(20)
DECLARE @IDFROM FLOAT
DECLARE @IDTO FLOAT 
DECLARE @LENGTH FLOAT
DECLARE @ModFlag INT
DECLARE csr CURSOR FOR
SELECT  ID
       ,IDFROM
       ,(IDFROM % 10) AS ModFlag
FROM @TEMPDATA
OPEN csr
FETCH NEXT
FROM csr
INTO @ID
  ,@IDFROM
  ,@ModFlag
WHILE @@FETCH_STATUS = 0
BEGIN
	IF (@ModFlag = 0)
    BEGIN
	    SET @IDTO = @IDFROM + 1
		SET @LENGTH = 1
--     Insert the first row into @TempData2
            INSERT @TEMPDATA2
                (
                    ID2
                  ,IDFROM2
                  ,IDTO2
                )
                VALUES
                (
                    @ID
                  ,@IDFROM
                  ,@IDTO
                )


			SET	@IDFROM = @IDFROM + 1
			SET	@IDTO = @IDTO + 9
            SET @LENGTH = @LENGTH + 9

            INSERT @TEMPDATA2
                (
                    ID2
                  ,IDFROM2
                  ,IDTO2
                )
                VALUES
                (
                    @ID
                  ,@IDFROM
                  ,@IDTO
                )

        END
    FETCH NEXT
    FROM csr
    INTO @ID
      ,@IDFROM
	  ,@ModFlag
END
CLOSE csr
DEALLOCATE csr
SELECT *
FROM @TEMPDATA2

Open in new window

0
 
8080_DiverCommented:
That would be because of the type that changed AS to ASW. :-/
0
 
SharathData EngineerCommented:
I see the issue with my code. check this now.
DECLARE @TEMPDATA TABLE
	(ID VARCHAR(20)
	,IDFROM FLOAT
	,IDTO FLOAT
	,LENGTH FLOAT
	)
INSERT @TEMPDATA VALUES ('A','0','5','5')
INSERT @TEMPDATA VALUES ('A','5','10','15')
INSERT @TEMPDATA VALUES ('A','10','20','10')
INSERT @TEMPDATA VALUES ('A','20','35','15')
INSERT @TEMPDATA VALUES ('A','35','65','30')

DECLARE @TEMPDATA2 TABLE
	(ID2 VARCHAR(20)
	,IDFROM2 FLOAT
	,IDTO2 FLOAT
	)

;with cte as (
select ID,MIN(IDFROM) IDFROM, MIN(IDFROM) + 10 NextID,MAX(IDTO) MAX_IDTO from @TEMPDATA group by ID union all
select ID,IDFROM + 10,NextID+10, MAX_IDTO from CTE where IDFROM + 10 <= MAX_IDTO)
INSERT @TEMPDATA2
select ID,IDFROM,IDTO from (
select ID,IDFROM,case when IDTO < MAX_IDTO then IDTO else MAX_IDTO end IDTO,MAX_IDTO from (
select 1 rn,ID,IDFROM,IDFROM + 1 as IDTO,MAX_IDTO from cte union all
select 2,ID,NextID-9,NextID IDTO,MAX_IDTO from cte) t1 )t2
 where IDFROM < IDTO
 order by IDFROM

SELECT * FROM @TEMPDATA2
/*
ID2	IDFROM2	IDTO2
A	0	1
A	1	10
A	10	11
A	11	20
A	20	21
A	21	30
A	30	31
A	31	40
A	40	41
A	41	50
A	50	51
A	51	60
A	60	61
A	61	65
*/

Open in new window

0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now