SQL Feather and Quill

Here’s a script I created to determine when I was going to be at specific miles for a marathon. It’ll give you your miles per hour and your minutes per mile. Then it’ll calculate at what time you will be at different mile marks.

Set nocount on
Declare @FTime datetime,
	@Dist float,
	@RStartTime datetime,
	@BDown float

-----------------------------------
----Change these for you event-----
-----------------------------------

Set @FTime = '3:10:00'
Set @Dist = 26.4
Set @RStartTime = '6/19/10 7:30 AM'
Set @BDown = 1  --this is the breakdown desired

-----------------------------------
-----------------------------------
-----------------------------------
Declare @Htime int,
	@Mtime int,
	@STime int,
	@TSec int,
	@SPM float,
	@iCounter float

Declare	@TheList table (Mile float,
				RTime datetime,
				CrossingTime time)

Set @Htime = Datepart(hh, @FTime)
Set @Mtime = Datepart(mi, @FTime)
Set @Stime = Datepart(ss, @FTime)
Set @TSec = (@Htime*60*60) + (@Mtime*60) + @Stime
Set @SPM = @TSec/@Dist

Set @iCounter = @BDown

While @icounter < @Dist
begin
	insert @TheList
	Select @iCounter,
		Dateadd(ss, @iCounter*@SPM, 0),
		DateAdd(ss, @iCounter*@SPM, @RStartTime)
	Set @iCounter = @iCounter + @BDown
End

---Insert Last one if not an even mile
IF Convert(int, 1000*@Dist) % Convert(int, 1000*@iCounter) <> 0
Begin
	insert @TheList values(@Dist, Dateadd(ss, @Dist*@SPM, 0), DateAdd(ss, @Dist*@SPM, @RStartTime))
End

Select Convert(varchar(20), Dateadd(ss, @SPM, 0), 8 ) as MPM,
			Round(3600/@SPM, 3) as MPH

Select Mile,
	Convert(varchar(20), RTime, 8 ) as Time,
	Convert(varchar(20), CrossingTime, 100) as 'SchedTime'
from @TheList