Advent of Code- Contains spoilers!

If you haven’t seen https://adventofcode.com/2016 I can recommend checking it out for a bit of coding fun!

Being slightly stubborn I wanted to do it in SQL (why not!), I’ve only managed time to do the first day so far and below are my workings:

--Create a simple function to split a comma string

CREATE FUNCTION dbo.fnSplit(
@sInputList VARCHAR(8000) -- List of delimited items
, @sDelimiter VARCHAR(8000) = ',' -- delimiter that separates items
) RETURNS @List TABLE (item VARCHAR(8000))

BEGIN
DECLARE @sItem VARCHAR(8000)
WHILE CHARINDEX(@sDelimiter,@sInputList,0) <> 0
BEGIN
SELECT
@sItem=RTRIM(LTRIM(SUBSTRING(@sInputList,1,CHARINDEX(@sDelimiter,@sInputList,0)-1))),
@sInputList=RTRIM(LTRIM(SUBSTRING(@sInputList,CHARINDEX(@sDelimiter,@sInputList,0)+LEN(@sDelimiter),LEN(@sInputList))))

IF LEN(@sItem) > 0
INSERT INTO @List SELECT @sItem
END

IF LEN(@sInputList) > 0
INSERT INTO @List SELECT @sInputList -- Put the last item in
RETURN
END
GO

--create our working table

drop table [dbo].[aoc_day1_1];

CREATE TABLE [dbo].[aoc_day1_1](
[item] [varchar](8000) NULL,
[direction] [varchar](1) NULL,
[x] [int] NULL,
[y] [int] NULL,
[rowno] [int] NULL
) ON [PRIMARY]

GO
-- insert the instructions to the table
declare @input varchar (max);

set @input='R3, L5, R1, R2, L5, R2, R3, L2, L5, R5, L4, L3, R5, L1, R3, R4, R1, L3, R3, L2, L5, L2, R4, R5, R5, L4, L3, L3, R4, R4, R5, L5, L3, R2, R2, L3, L4, L5, R1, R3, L3, R2, L3, R5, L194, L2, L5, R2, R1, R1, L1, L5, L4, R4, R2, R2, L4, L1, R2, R53, R3, L5, R72, R2, L5, R3, L4, R187, L4, L5, L2, R1, R3, R5, L4, L4, R2, R5, L5, L4, L3, R5, L2, R1, R1, R4, L1, R2, L3, R5, L4, R2, L3, R1, L4, R4, L1, L2, R3, L1, L1, R4, R3, L4, R2, R5, L2, L3, L3, L1, R3, R5, R2, R3, R1, R2, L1, L4, L5, L2, R4, R5, L2, R4, R4, L3, R2, R1, L4, R3, L3, L4, L3, L1, R3, L2, R2, L4, L4, L5, R3, R5, R3, L2, R5, L2, L1, L5, L1, R2, R4, L5, R2, L4, L5, L4, L5, L2, L5, L4, R5, R3, R2, R2, L3, R3, L2, L5';
insert into aoc_day1_1 (item,rowno) select *,ROW_NUMBER() over (order by (select 200)) from dbo.fnSplit(@input, ',');

--set out first line

update aoc_day1_1
set direction = 'E', x = 3, y=0 where rowno =1;

--now loop through all the others to walk the path

declare @maxrow int;
set @maxrow= (select count(*) from aoc_day1_1);
declare @currrow int;
set @currrow =2;

while @currrow <= @maxrow BEGIN update aoc_day1_1 set direction = (SELECT CASE WHEN p.direction = 'N' AND left(n.item,1) = 'L' THEN 'W' WHEN p.direction = 'N' AND left(n.item,1) = 'R' THEN 'E' WHEN p.direction = 'E' AND left(n.item,1) = 'L' THEN 'N' WHEN p.direction = 'E' AND left(n.item,1) = 'R' THEN 'S' WHEN p.direction = 'S' AND left(n.item,1) = 'R' THEN 'W' WHEN p.direction = 'S' AND left(n.item,1) = 'L' THEN 'E' WHEN p.direction = 'W' AND left(n.item,1) = 'L' THEN 'S' ELSE 'N' END FROM aoc_day1_1 n JOIN aoc_day1_1 p ON n.rowno =@currrow AND p.rowno =@currrow -1) WHERE rowno = @currrow; update aoc_day1_1 set x = (SELECT CASE WHEN p.direction = 'N' AND left(n.item,1) = 'L' THEN p.x - rtrim(substring(n.item,2,99)) WHEN p.direction = 'N' AND left(n.item,1) = 'R' THEN p.x + rtrim(substring(n.item,2,99)) WHEN p.direction = 'S' AND left(n.item,1) = 'L' THEN p.x + rtrim(substring(n.item,2,99)) WHEN p.direction = 'S' AND left(n.item,1) = 'R' THEN p.x - rtrim(substring(n.item,2,99)) ELSE p.x END FROM aoc_day1_1 n JOIN aoc_day1_1 p ON n.rowno =@currrow AND p.rowno =@currrow -1) WHERE rowno = @currrow; update aoc_day1_1 set y = (SELECT CASE WHEN p.direction = 'E' AND left(n.item,1) = 'L' THEN p.y + rtrim(substring(n.item,2,99)) WHEN p.direction = 'E' AND left(n.item,1) = 'R' THEN p.y - rtrim(substring(n.item,2,99)) WHEN p.direction = 'W' AND left(n.item,1) = 'L' THEN p.y - rtrim(substring(n.item,2,99)) WHEN p.direction = 'W' AND left(n.item,1) = 'R' THEN p.y + rtrim(substring(n.item,2,99)) ELSE p.y END FROM aoc_day1_1 n JOIN aoc_day1_1 p ON n.rowno =@currrow AND p.rowno =@currrow -1) WHERE rowno = @currrow; set @currrow = @currrow+1 END; /***************************************** Day1 part2 *****************************************/ --create our working table drop table aoc_day1_2; CREATE TABLE [dbo].[aoc_day1_2]( [line] [int] NULL, [coordinates] [varchar](max) NULL, [geo] [geometry] NULL ) ON [PRIMARY] GO --insert our first row insert into aoc_day1_2 SELECT 1, '0 0,'+cast(x as varchar)+' '+cast(y as varchar), geometry::STGeomFromText('LINESTRING(0 0,'+cast(x as varchar)+' '+cast(y as varchar)+')', 0) from aoc_day1_1 where rowno =1; --loop through all the others declare @maxrow int; set @maxrow= (select count(*) from aoc_day1_1); declare @currrow int; set @currrow =2; while @currrow <= @maxrow BEGIN Insert into aoc_day1_2 select @currrow as line, cast(p.x as varchar)+' '+cast(p.y as varchar)+','+cast(n.x as varchar)+' '+cast(n.y as varchar), geometry::STGeomFromText('LINESTRING('+cast(p.x as varchar)+' '+cast(p.y as varchar)+','+cast(n.x as varchar)+' '+cast(n.y as varchar)+')', 0) FROM aoc_day1_1 n JOIN aoc_day1_1 p ON n.rowno =@currrow AND p.rowno =@currrow -1 WHERE n.rowno = @currrow; set @currrow = @currrow+1 END; --bring in our data ;with dataset as ( SELECT a.line as a, b.line as b, a.line+b.line as minline, a.geo.STIntersection(b.geo).ToString() as flag from aoc_day1_2 a JOIN aoc_day1_2 b ON a.line != b.line and b.line !=a.line+1 --because its not possible for a line to cross the one immediately before it and a.line !=b.line+1 --because its not possible for a line to cross the one immediately after it WHERE a.geo.STIntersection(b.geo).ToString() !='GEOMETRYCOLLECTION EMPTY' ) --finally select our lowest intersection SELECT a,b,flag FROM dataset where minline = (select min(minline) from dataset)

Leave a Reply