Insert data. Do until

NRGUY · April 27, 2016, 8:54am

I have no idea how to do this but I'm sure its possible!

I have a data set like the below image. I want to create a new table with the same data but split by 22 yard sections within each 220 yards. e.g.( start at 29M 110Y - increase by 22 until 29M 220Y)

Most of the data I have is in 220 yard sections, but some may only be 50 yards.

JamesK · April 27, 2016, 1:11pm

What would your new table look like given the sample data that you posted?
In particular, what would be the rows generated in the new table by the row that has startyards = 1540 and and endyards = 0
If the difference between startyards and endyards is not an exact multiple of 22, what should be the output?

ScottPletcher · April 28, 2016, 3:02pm

WITH cte_tally10 AS (
    SELECT * FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0))v(N)
),
cte_tally1000 AS (
    SELECT ROW_NUMBER() OVER(ORDER BY t1.N) - 1 AS N
    FROM cte_tally10 t1, cte_tally10 t2, cte_tally10 t3
)
SELECT ot.TME, ot.TSM, ot.ELR, ot.TrackID,
    ot.StartMiles + ((ot.StartYards + (t.N * 22)) / 1760) AS StartMile,
    (ot.StartYards + (t.N * 22)) % 1760 AS StartYard,
    ot.StartMiles + ((ot.StartYards + ((t.N + 1) * 22)) / 1760) AS EndMile,
    (ot.StartYards + ((t.N + 1) * 22)) % 1760 AS EndYard
FROM #old_table ot
INNER JOIN cte_tally1000 t
ON t.N BETWEEN 0 AND 
    CASE WHEN ot.EndMiles > ot.StartMiles 
         THEN (ot.EndMiles - ot.StartMiles) * 1760 - ot.StartYards
         ELSE ot.EndYards - ot.StartYards END / 22 - 1
ORDER BY StartMile, StartYard

ScottPletcher · April 28, 2016, 3:07pm

--script to create the sample data

CREATE TABLE #old_table (
    TME varchar(100),
    TSM varchar(100),
    ELR char(3),
    TrackID int,
    StartMiles int,
    StartYards int,
    EndMiles int,
    EndYards int
    )
TRUNCATE TABLE #old_table
INSERT INTO #old_table SELECT 'TME Feltham', 'TSM Feltham', 'AAV', 1100, 29, 110, 29, 220
INSERT INTO #old_table SELECT 'TME Feltham', 'TSM Feltham', 'AAV', 1100, 29, 220, 29, 440
INSERT INTO #old_table SELECT 'TME Feltham', 'TSM Feltham', 'AAV', 1100, 29, 440, 29, 660
INSERT INTO #old_table SELECT 'TME Feltham', 'TSM Feltham', 'AAV', 1100, 29, 660, 29, 880
INSERT INTO #old_table SELECT 'TME Feltham', 'TSM Feltham', 'AAV', 1100, 29, 880, 29,1100
INSERT INTO #old_table SELECT 'TME Feltham', 'TSM Feltham', 'AAV', 1100, 29,1100, 29,1320
INSERT INTO #old_table SELECT 'TME Feltham', 'TSM Feltham', 'AAV', 1100, 29,1320, 29,1540
INSERT INTO #old_table SELECT 'TME Feltham', 'TSM Feltham', 'AAV', 1100, 29,1540, 30,   0
INSERT INTO #old_table SELECT 'TME Feltham', 'TSM Feltham', 'AAV', 1100, 30,   0, 30, 220
INSERT INTO #old_table SELECT 'TME Feltham', 'TSM Feltham', 'AAV', 1100, 30, 220, 30, 440
INSERT INTO #old_table SELECT 'TME Feltham', 'TSM Feltham', 'AAV', 1100, 30, 440, 30, 660
INSERT INTO #old_table SELECT 'TME Feltham', 'TSM Feltham', 'AAV', 1100, 30, 660, 30, 880
INSERT INTO #old_table SELECT 'TME Feltham', 'TSM Feltham', 'AAV', 1100, 30, 880, 30,1100
INSERT INTO #old_table SELECT 'TME Feltham', 'TSM Feltham', 'AAV', 1100, 30,1100, 30,1320
INSERT INTO #old_table SELECT 'TME Feltham', 'TSM Feltham', 'AAV', 1100, 30,1320, 31,   0
INSERT INTO #old_table SELECT 'TME Feltham', 'TSM Feltham', 'AAV', 1100, 31,   0, 31, 220

NRGUY · June 13, 2016, 2:11pm

Thanks for your help, but it doesn't quite do what I need it to. Sorry for not getting back sooner, I was dragged onto something else....
I already have a table of the main data (220 yard sections) and I do need this splitting into smaller chunks (e.g. 22 yards), however the data isn't always in 220 yard sections.

For example, the data on the first row starts at 117 yards and goes to 220, then it carries on the trend. The start and end points generally stick to the 220 yard table but can vary (this includes starting on minus figures such as -10 yards).

What I would like is to split this into 22 yard sections but with the understanding that if it starts or ends on a different figure (e.g. the 117 yards) then the 22 yard section becomes smaller (in this case 15 yards).

JeffModen · June 20, 2016, 12:48am

So for TrackID = 1100, what should the result look like for the above?

NRGUY · June 20, 2016, 8:19am

The result would look like the below with a shorter first and last section.

Normally the sections will start at 0 yards and end at 1760 (or 0 yards of the next mile), however like in this example they can start and end at different points.

JeffModen · June 20, 2016, 1:06pm

Got it. Thanks for taking the time. I'll be back after work tonight, unless someone else susses it for you before then.

JeffModen · June 21, 2016, 3:20am

Ok.. so I went to make test data from your second to previous post and it's a bloody graphic. Please help us help you. Provide the CREATE TABLE statement and provide the data in a readily consumable format that populates the table using code so we can help. I just don't have the time to manually copy from a graphic.

NRGUY · June 21, 2016, 7:52am

CREATE TABLE #old_table (
[Route] Nvarchar(100),
ELR char(3),
TrackID int,
StartMiles int,
StartYards int,
EndMiles int,
EndYards int,
LengthofSection INT
)
TRUNCATE TABLE #old_table
INSERT INTO #old_table SELECT 'Western West', 'ABW', 1100, 0, 117, 0, 220, 103
INSERT INTO #old_table SELECT 'Western West', 'ABW', 1100, 0, 220, 0, 440, 220
INSERT INTO #old_table SELECT 'Western West', 'ABW', 1100, 0, 440, 0, 660, 220
INSERT INTO #old_table SELECT 'Western West', 'ABW', 1100, 0, 660, 0, 880, 220
INSERT INTO #old_table SELECT 'Western West', 'ABW', 1100, 0, 880, 0,1100, 220
INSERT INTO #old_table SELECT 'Western West', 'ABW', 1100, 0, 1100, 0, 1320, 220
INSERT INTO #old_table SELECT 'Western West', 'ABW', 1100, 0, 1320, 0, 1399, 79
INSERT INTO #old_table SELECT 'Western West', 'ABW', 2100, 0, 117, 0, 220, 103
INSERT INTO #old_table SELECT 'Western West', 'ABW', 2100, 0, 220, 0, 440, 220
INSERT INTO #old_table SELECT 'Western West', 'ABW', 2100, 0, 440, 0, 660, 220
INSERT INTO #old_table SELECT 'Western West', 'ABW', 2100, 0, 660, 0, 880, 220
INSERT INTO #old_table SELECT 'Western West', 'ABW', 2100, 0, 880, 0, 1100, 220
INSERT INTO #old_table SELECT 'Western West', 'ABW', 2100, 0, 1100, 0, 1320, 220
INSERT INTO #old_table SELECT 'Western West', 'ABW', 2100, 0, 1320, 0, 1399, 79

JeffModen · June 21, 2016, 2:07pm

Excellent. Thanks for the readily consumable data. Makes life a whole lot easier for folks trying to help.

The following will do it for you with a caveat... you have to ensure the following index is available on whatever your source table is or you'll end up with a very nasty, CPU and IO intensive Cartesian Product. (I couldn't have found this problem without test data).

CREATE INDEX StartEndYards ON #old_table (StartYards,EndYards);

If you don't already have an fnTally function, it's time to build this Swiss Army Knife of T-SQL in your database(s). It makes life incredibly easy. Here's the code for that function. I don't normally prefix my function names with "fn" but I needed to because I also have a Tally table.

 CREATE FUNCTION [dbo].[fnTally]
/**********************************************************************************************************************
 Purpose:
 Return a column of BIGINTs from @ZeroOrOne up to and including @MaxN with a max value of 1 Trillion.

 As a performance note, it takes about 00:02:10 (hh:mm:ss) to generate 1 Billion numbers to a throw-away variable.

 Usage:
--===== Syntax example (Returns BIGINT)
 SELECT t.N
   FROM dbo.fnTally(@ZeroOrOne,@MaxN) t
;

 Notes:
 1. Based on Itzik Ben-Gan's cascading CTE (cCTE) method for creating a "readless" Tally Table source of BIGINTs.
    Refer to the following URLs for how it works and introduction for how it replaces certain loops. 
    http://www.sqlservercentral.com/articles/T-SQL/62867/
    http://sqlmag.com/sql-server/virtual-auxiliary-table-numbers
 2. To start a sequence at 0, @ZeroOrOne must be 0 or NULL. Any other value that's convertable to the BIT data-type
    will cause the sequence to start at 1.
 3. If @ZeroOrOne = 1 and @MaxN = 0, no rows will be returned.
 5. If @MaxN is negative or NULL, a "TOP" error will be returned.
 6. @MaxN must be a positive number from >= the value of @ZeroOrOne up to and including 1 Billion. If a larger
    number is used, the function will silently truncate after 1 Billion. If you actually need a sequence with
    that many values, you should consider using a different tool. ;-)
 7. There will be a substantial reduction in performance if "N" is sorted in descending order.  If a descending 
    sort is required, use code similar to the following. Performance will decrease by about 27% but it's still
    very fast especially compared with just doing a simple descending sort on "N", which is about 20 times slower.
    If @ZeroOrOne is a 0, in this case, remove the "+1" from the code.

    DECLARE @MaxN BIGINT; 
     SELECT @MaxN = 1000;
     SELECT DescendingN = @MaxN-N+1 
       FROM dbo.fnTally(1,@MaxN);

 8. There is no performance penalty for sorting "N" in ascending order because the output is explicity sorted by
    ROW_NUMBER() OVER (ORDER BY (SELECT NULL))

 Revision History:
 Rev 00 - Unknown     - Jeff Moden 
        - Initial creation with error handling for @MaxN.
 Rev 01 - 09 Feb 2013 - Jeff Moden 
        - Modified to start at 0 or 1.
 Rev 02 - 16 May 2013 - Jeff Moden 
        - Removed error handling for @MaxN because of exceptional cases.
 Rev 03 - 22 Apr 2015 - Jeff Moden
        - Modify to handle 1 Trillion rows for experimental purposes.
**********************************************************************************************************************/
        (@ZeroOrOne BIT, @MaxN BIGINT)
RETURNS TABLE WITH SCHEMABINDING AS 
 RETURN WITH
  E1(N) AS (SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL 
            SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL 
            SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL 
            SELECT 1)                                  --10E1 or 10 rows
, E4(N) AS (SELECT 1 FROM E1 a, E1 b, E1 c, E1 d)      --10E4 or 10 Thousand rows
,E12(N) AS (SELECT 1 FROM E4 a, E4 b, E4 c)            --10E12 or 1 Trillion rows                 
            SELECT N = 0 WHERE ISNULL(@ZeroOrOne,0)= 0 --Conditionally start at 0.
             UNION ALL 
            SELECT TOP(@MaxN) N = ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E12 -- Values from 1 to @MaxN
;

And, here's your solution. Like I said, the fnTally function makes life a breeze and the code becomes quite simple.

    WITH cte22YardSegments AS
    (--==== There are Eighty 22 Yard segments in a mile
         -- starting at 0 and ending at 79 inclusive.
     SELECT  StartYard = N*22+1
            ,EndYard   = N*22+22
       FROM dbo.fnTally(0,79)
    )
    ,cteExpandSegments AS
    (
     SELECT [Route]
            ,trk.ELR
            ,trk.TrackID
            ,trk.StartMiles
            ,StartYards = CASE WHEN trk.StartYards >= y22.StartYard-1 THEN trk.StartYards ELSE y22.StartYard-1 END
            ,trk.EndMiles
            ,EndYards   = CASE WHEN trk.EndYards >= y22.EndYard THEN y22.EndYard ELSE trk.EndYards END
       FROM #old_table        trk
       JOIN cte22YardSegments y22
         ON trk.StartYards <  y22.EndYard
        AND trk.EndYards   >= y22.StartYard
    )
     SELECT *, [Length] = EndYards-StartYards
       FROM cteExpandSegments
      ORDER BY [Route], ELR, TrackID, StartYards
    ;

For an in-depth explanation of how that magical little set of ON conditions work, please see the following article.
http://www.sqlservercentral.com/articles/T-SQL/105968/

NRGUY · June 21, 2016, 2:57pm

Thanks for this. It looks really good!
For some reason though it only goes up to 1540 yards in the mile not 1760 yards?

JeffModen · June 21, 2016, 3:06pm

1760 yards would be the next whole mile where the yards resets (should be) to 0.

JeffModen · June 21, 2016, 3:10pm

Actually, I just tried it with the following data and it appears to work correctly for end yards. Again, you should never have a start yards of 1760 because that would be the beginning of a new whole mile.

DROP TABLE #old_table
;

CREATE TABLE #old_table (
 [Route] Nvarchar(100),
 ELR char(3),
 TrackID int,
 StartMiles int,
 StartYards int,
 EndMiles int,
 EndYards int,
 LengthofSection INT
 )
TRUNCATE TABLE #old_table
INSERT INTO #old_table SELECT 'Western West', 'ABW', 2100, 0, 117, 0, 220, 103
INSERT INTO #old_table SELECT 'Western West', 'ABW', 2100, 0, 220, 0, 440, 220
INSERT INTO #old_table SELECT 'Western West', 'ABW', 2100, 0, 440, 0, 660, 220
INSERT INTO #old_table SELECT 'Western West', 'ABW', 2100, 0, 660, 0, 880, 220
INSERT INTO #old_table SELECT 'Western West', 'ABW', 2100, 0, 880, 0, 1100, 220
INSERT INTO #old_table SELECT 'Western West', 'ABW', 2100, 0, 1100, 0, 1320, 220
INSERT INTO #old_table SELECT 'Western West', 'ABW', 2100, 0, 1320, 0, 1760, 79   --< HERE!

CREATE INDEX StartEndYards ON #old_table (StartYards,EndYards);

NRGUY · June 21, 2016, 3:18pm

Ok that's probably where the issue has occurred. The data doesn't have an end of 1760, it has an end of 0 (for the next mile section). Like in this example, mile 139 ends at 1540 and then the next mile starts.

I can probably just change the data to reflect 1760 instead of 0

NRGUY · June 21, 2016, 3:29pm

Yep - Changed the data and it worked perfectly!

Thanks for your help

JeffModen · June 21, 2016, 8:41pm

Perfect. The next question would be, especially since you have to maintain it, do you understand the code?

NRGUY · June 22, 2016, 8:02am

I understand the concept of the main code but wouldn't be able to write it from scratch. I'm almost completely self taught and don't fully understand CTE's yet.

The tally function is above my head!

Luckily, the main code was required just to create some base data. It shouldn't have to be done again for a long time if at all.

Thanks for all your help