Driving Around Town

While driving around I pay attention to the way the other drivers behave.

I notice who lets who in, and who cuts who off.

I notice who speeds up to (& sometimes through!) red lights or stop signs.

And you know what the common denominator is among all of these bad drivers? The trump flags, the trump stickers.

Go figure..

ALL, ANY, and SOME: 3 SQL operators you do NOT want to use…

http://bradsruminations.blogspot.com/2009/08/all-any-and-some-three-stooges.html

-- 4 questions with BoxOf3Coins
with BoxOf3Coins(CoinValue) as (select  5 union all select 10 union all select 25)
	,EmptyBox as (select CoinValue from BoxOf3Coins where 1 = 2)
select 'Is twenty-five cents worth more than ANY of the coins in BoxOf3Coins?',case when 25 > any (select CoinValue from BoxOf3Coins) then 'Yes' else 'No' end;

with BoxOf3Coins(CoinValue) as (select  5 union all select 10 union all select 25)
	,EmptyBox as (select CoinValue from BoxOf3Coins where 1 = 2)
select 'Is twenty-five cents worth more than ALL (EACH) of the coins in BoxOf3Coins?',case when 25 > all (select CoinValue from BoxOf3Coins) then 'Yes' else 'No' end;

with BoxOf3Coins(CoinValue) as (select  5 union all select 10 union all select 25)
	,EmptyBox as (select CoinValue from BoxOf3Coins where 1 = 2)
select 'Is twenty-five cents worth the same as ANY of the coins in BoxOf3Coins?',case when 25 = any (select CoinValue from BoxOf3Coins) then 'Yes' else 'No' end;

with BoxOf3Coins(CoinValue) as (select  5 union all select 10 union all select 25)
	,EmptyBox as (select CoinValue from BoxOf3Coins where 1 = 2)
select 'Is twenty-five cents worth the same as ALL (EACH) of the coins in BoxOf3Coins?',case when 25 = all (select CoinValue from BoxOf3Coins) then 'Yes' else 'No' end;

-- Now answer the same question with the EmptyBox
with BoxOf3Coins(CoinValue) as (select  5 union all select 10 union all select 25)
	,EmptyBox as (select CoinValue from BoxOf3Coins where 1 = 2)
select 'Is twenty-five cents worth more than ANY of the coins in EmptyBox?',case when 25 > any (select CoinValue from EmptyBox) then 'Yes' else 'No' end;

with BoxOf3Coins(CoinValue) as (select  5 union all select 10 union all select 25)
	,EmptyBox as (select CoinValue from BoxOf3Coins where 1 = 2)
select 'Is twenty-five cents worth more than ALL (EACH) of the coins in EmptyBox?',case when 25 > all (select CoinValue from EmptyBox) then 'Yes' else 'No' end;

with BoxOf3Coins(CoinValue) as (select  5 union all select 10 union all select 25)
	,EmptyBox as (select CoinValue from BoxOf3Coins where 1 = 2)
select 'Is twenty-five cents worth the same as ANY of the coins in EmptyBox?',case when 25 = any (select CoinValue from EmptyBox) then 'Yes' else 'No' end;

with BoxOf3Coins(CoinValue) as (select  5 union all select 10 union all select 25)
	,EmptyBox as (select CoinValue from BoxOf3Coins where 1 = 2)
select 'Is twenty-five cents worth the same as ALL (EACH) of the coins in EmptyBox?',case when 25 = all (select CoinValue from EmptyBox) then 'Yes' else 'No' end;

Processing Tasks in Order of Completion

Disclaimer: I acknowledge this method was obtained from another blog; I’m pretty sure it was Stephen Toub’s, but I cannot seem to find his post again.

But here are my modified versions of his Interleaved() method.

#nullable enable

	using System;
	using System.Collections.Concurrent;
	using System.Collections.Generic;
	using System.Diagnostics;
	using System.Linq;
	using System.Threading;
	using System.Threading.Tasks;
	using JetBrains.Annotations;

	public static class TaskExtensions {

		/// <summary>
		/// Return tasks in order of completion.
		/// </summary>
		/// <param name="tasks"></param>
		/// <returns></returns>
		public static IEnumerable<Task<Task>> InOrderOfCompletion( this IEnumerable<Task> tasks ) {
			var inputTasks = tasks.ToList();

			var buckets = new TaskCompletionSource<Task>[ inputTasks.Count ];
			var results = new Task<Task>[ buckets.Length ];

			for ( var i = 0; i < buckets.Length; i++ ) {
				buckets[ i ] = new TaskCompletionSource<Task>();
				results[ i ] = buckets[ i ].Task;
			}

			var nextTaskIndex = -1;

			void Continuation( Task completed ) {
				var bucket = buckets[ Interlocked.Increment( ref nextTaskIndex ) ];
				bucket.TrySetResult( completed );
			}

			foreach ( var inputTask in inputTasks ) {
				inputTask?.ContinueWith( Continuation, CancellationToken.None, TaskContinuationOptions.ExecuteSynchronously, TaskScheduler.Default );
			}

			return results;
		}

		/// <summary>
		/// Return tasks in order of completion.
		/// </summary>
		/// <typeparam name="T"></typeparam>
		/// <param name="tasks"></param>
		/// <returns></returns>
		[ItemNotNull]
		[NotNull]
		public static IEnumerable<Task<Task<T>>> InOrderOfCompletion<T>( this IEnumerable<Task<T>> tasks ) {
			var inputTasks = tasks.ToList();

			var buckets = new TaskCompletionSource<Task<T>>[ inputTasks.Count ];
			var results = new Task<Task<T>>[ buckets.Length ];

			for ( var i = 0; i < buckets.Length; i++ ) {
				buckets[ i ] = new TaskCompletionSource<Task<T>>();
				results[ i ] = buckets[ i ].Task;
			}

			var nextTaskIndex = -1;

			void Continuation( Task<T> completed ) {
				var bucket = buckets[ Interlocked.Increment( ref nextTaskIndex ) ];
				bucket.TrySetResult( completed );
			}

			foreach ( var inputTask in inputTasks ) {
				inputTask?.ContinueWith( Continuation, CancellationToken.None, TaskContinuationOptions.ExecuteSynchronously, TaskScheduler.Default );
			}

			return results;
		}

		/// <summary>
		/// Return tasks in order of completion.
		/// </summary>
		/// <typeparam name="T"></typeparam>
		/// <param name="tasks"></param>
		/// <returns></returns>
		[ItemNotNull]
		[NotNull]
		public static IEnumerable<Task<Task<T>>> InOrderOfCompletion<T>( this IDictionary<TimeSpan, Task<T>> tasks ) {
			var inputTasks = tasks.ToList();

			var buckets = new TaskCompletionSource<Task<T>>[ inputTasks.Count ];
			var results = new Task<Task<T>>[ buckets.Length ];

			for ( var i = 0; i < buckets.Length; i++ ) {
				buckets[ i ] = new TaskCompletionSource<Task<T>>();
				results[ i ] = buckets[ i ].Task;
			}

			var nextTaskIndex = -1;

			void Continuation( Task<T> completed ) {
				var bucket = buckets[ Interlocked.Increment( ref nextTaskIndex ) ];
				bucket.TrySetResult( completed );
			}

			foreach ( var inputTask in inputTasks ) {
				inputTask.Value?.ContinueWith( Continuation, CancellationToken.None, TaskContinuationOptions.ExecuteSynchronously, TaskScheduler.Default );
			}

			return results;
		}

	}

And here is my test code.

	public static class Examples {

		public static async Task TestsInOrderOfCompletion( CancellationToken token ) {
			var rng = new Random();

			//Add to the list, out of "order"..
			var tasks = new ConcurrentDictionary<TimeSpan, Task<String>> {
				[ TimeSpan.FromSeconds( 3 ) ] = Task.Delay( TimeSpan.FromSeconds( 3 ), token ).ContinueWith( _ => "3 seconds", token ),
				[ TimeSpan.FromSeconds( 1 ) ] = Task.Delay( TimeSpan.FromSeconds( 1 ), token ).ContinueWith( _ => "1 second", token ),
				[ TimeSpan.FromSeconds( 2 ) ] = Task.Delay( TimeSpan.FromSeconds( 2 ), token ).ContinueWith( _ => "2 seconds", token ),
				[ TimeSpan.FromSeconds( 5 ) ] = Task.Delay( TimeSpan.FromSeconds( 5 ), token ).ContinueWith( _ => "5 seconds", token ),
				[ TimeSpan.FromSeconds( 8 ) ] = Task.Delay( TimeSpan.FromSeconds( 8 ), token ).ContinueWith( _ => "8 seconds", token ),
				[ TimeSpan.FromSeconds( 7 ) ] = Task.Delay( TimeSpan.FromSeconds( 7 ), token ).ContinueWith( _ => "7 seconds", token ),
				[ TimeSpan.FromSeconds( 9 ) ] = Task.Delay( 1000, token ).ContinueWith( _ => "9 seconds", token ),
				[ TimeSpan.FromSeconds( 10 ) ] = Task.Delay( 1000, token ).ContinueWith( _ => "10 seconds", token ),
				[ TimeSpan.FromSeconds( 4 ) ] = Task.Delay( 1000, token ).ContinueWith( _ => "4 seconds", token )
			};

			//Add a few more to the list, also in "random" order..
			for ( var i = 0; i < 25; i++ ) {
				var millisecondsDelay = rng.Next( 10000 );
				var task = Task.Delay( millisecondsDelay, token ).ContinueWith( _ => $"{millisecondsDelay / 1000.0:F4}", token );   //return how many milliseconds we just delayed
				tasks[ TimeSpan.FromMilliseconds( millisecondsDelay ) ] = task;
			}

			foreach ( var bucket in tasks.InOrderOfCompletion() ) {
				try {
					var task = await bucket.ConfigureAwait( false );
					var result = await task.ConfigureAwait( false );
					Console.WriteLine( $"{DateTime.Now:hh:mm:ss}, TaskId #{task.Id:N0}, {result} ms" );

				}
				catch ( OperationCanceledException ) { }

				catch ( Exception exception ) {
					Debug.WriteLineIf( Debugger.IsAttached, exception.ToString() );
				}
			}

		}

	}

Jeff Moden’s Script for Splitting CSV Strings up to 8000 Length

Pulled from https://www.sqlservercentral.com/articles/tally-oh-an-improved-sql-8k-%E2%80%9Ccsv-splitter%E2%80%9D-function, I think.

CREATE OR ALTER FUNCTION [dbo].[DelimitedSplit8K](
	@pString VARCHAR(8000),
	@pDelimiter CHAR(1)
) --WARNING!!! DO NOT USE MAX DATA-TYPES HERE!  IT WILL KILL PERFORMANCE!
RETURNS TABLE WITH SCHEMABINDING AS
RETURN
--===== "Inline" CTE Driven "Tally Table" produces values from 1 up to 10,000... enough to cover VARCHAR(8000)
  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
                ),                          --10E+1 or 10 rows
       E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
       E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
 cteTally(N) AS (--==== This provides the "base" CTE and limits the number of rows right up front
                     -- for both a performance gain and prevention of accidental "overruns"
                 SELECT TOP (ISNULL(DATALENGTH(@pString),0)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
                ),
cteStart(N1) AS (--==== This returns N+1 (starting position of each "element" just once for each delimiter)
                 SELECT 1 UNION ALL
                 SELECT t.N+1 FROM cteTally t WHERE SUBSTRING(@pString,t.N,1) = @pDelimiter
                ),
cteLen(N1,L1) AS(--==== Return start and length (for use in substring)
                 SELECT s.N1,
                        ISNULL(NULLIF(CHARINDEX(@pDelimiter,@pString,s.N1),0)-s.N1,8000)
                   FROM cteStart s
                )
--===== Do the actual split. The ISNULL/NULLIF combo handles the length for the final element when no delimiter is found.
 SELECT ItemNumber = ROW_NUMBER() OVER(ORDER BY l.N1),
        Item       = SUBSTRING(@pString, l.N1, l.L1)
   FROM cteLen l;

Improved SQL Function to Generate DateTimes

-- select * from [dbo].[GenDateTimes]( 'year', '1/1/0001', '12/12/9999' ) order by [Date];
-- select * from [dbo].[GenDateTimes]( 'month', '1/1/0001', '12/12/9999' ) order by [Date];
-- select * from [dbo].[GenDateTimes]( 'week', '1/1/0001', '12/12/9999' ) order by [Date];
-- select * from [dbo].[GenDateTimes]( 'day', '1/1/0001', '12/12/9999' ) order by [Date];
-- select * from [dbo].[GenDateTimes]( 'hour', '1/1/0001', '12/12/9999' ) order by [Date];
-- select * from [dbo].[GenDateTimes]( 'minute', '1/1/1999', '12/12/2999' ) order by [Date];
-- select * from [dbo].[GenDateTimes]( 'second', '1/1/1999', '12/12/1999' ) order by [Date];
-- select * from [dbo].[GenDateTimes]( 'millisecond', '1/1/1999', '1/12/1999' ) order by [Date];
create or alter function [dbo].[GenDateTimes](
	@Increment varchar(11),
	@Start datetime2(3),
	@End datetime2(3)
)
returns	@Range table( [Date] datetime2(3) )
as
begin;
	declare @units bigint = 
				case
					when @Increment = 'ms' THEN datediff_big( ms, @Start, @End )
					when @Increment = 'millisecond' THEN datediff_big( millisecond, @Start, @End )
					when @Increment = 's' THEN datediff_big( s, @Start, @End )
					when @Increment = 'second' THEN datediff_big( second, @Start, @End )
					when @Increment = 'mi' THEN datediff_big( mi, @Start, @End )
					when @Increment = 'minute' THEN datediff_big( minute, @Start, @End )
					when @Increment = 'hh' THEN datediff_big( hh, @Start, @End )
					when @Increment = 'hour' THEN datediff_big( hour, @Start, @End )
					when @Increment = 'd' THEN datediff( d, @Start, @End )
					when @Increment = 'day' THEN datediff( day, @Start, @End )
					when @Increment = 'wk' THEN datediff( wk, @Start, @End )
					when @Increment = 'week' THEN datediff( week, @Start, @End )
					when @Increment = 'mm' THEN datediff( mm, @Start, @End )
					when @Increment = 'month' THEN datediff( month, @Start, @End )
					when @Increment = 'y' THEN datediff( y, @Start, @End )
					when @Increment = 'year' THEN datediff( year, @Start, @End )
				end;

	set @Increment = lower( @Increment );

	with
		[l0] as ( select 0 [c] union all select 1 ),
		[l1] as ( select 1 [c] from [l0] [a] cross join [l0] [b] ),
		[l2] as ( select 1 [c] from [l1] [a] cross join [l1] [b] ),
		[l3] as ( select 1 [c] from [l2] [a] cross join [l2] [b] ),
		[l4] as ( select 1 [c] from [l3] [a] cross join [l3] [b] ),
		[l5] as ( select 1 [c] from [l4] [a] cross join [l4] [b] ),
		[nums] as ( select row_number() over( order by ( select null ) ) [n] from [l5] ),
		[cte]( [Date] ) AS (
			select @Start [Date]
			union all
			select
				case
					when @Increment = 'ms' THEN dateadd( ms, [n], @Start )
					when @Increment = 'millisecond' THEN dateadd( millisecond, [n], @Start )
					when @Increment = 's' THEN dateadd( s, [n], @Start )
					when @Increment = 'second' THEN dateadd( second, [n], @Start )
					when @Increment = 'mi' THEN dateadd( mi, [n], @Start )
					when @Increment = 'minute' THEN dateadd( minute, [n], @Start )
					when @Increment = 'hh' THEN dateadd( hh, [n], @Start )
					when @Increment = 'hour' THEN dateadd( hour, [n], @Start )
					when @Increment = 'd' THEN dateadd( d, [n], @Start )
					when @Increment = 'day' THEN dateadd( day, [n], @Start )
					when @Increment = 'wk' THEN dateadd( wk, [n], @Start )
					when @Increment = 'week' THEN dateadd( week, [n], @Start )
					when @Increment = 'mm' THEN dateadd( mm, [n], @Start )
					when @Increment = 'month' THEN dateadd( month, [n], @Start )
					when @Increment = 'y' THEN dateadd( y, [n], @Start )
					when @Increment = 'year' THEN dateadd( year, [n], @Start )
				end
			from [nums] [t]
			where [t].[n] <= @units
	)
	insert into @Range( [Date] )
	select [Date]
	from [cte];

	return;
end;

SQL Function to Generate Date Table with Count

-- select * from [dbo].[GenDates2]( 'd', getdate()-16383, getdate()+16384 );
-- select * from [dbo].[GenDates2]( 'w', getdate()-16383, getdate()+16384 );
-- select * from [dbo].[GenDates2]( 'm', getdate()-16383, getdate()+16384 );
-- select * from [dbo].[GenDates2]( 'y', getdate()-1, getdate()-16384 );
create or alter function [dbo].[GenDates2](
	@Increment char(1),
	@Start datetime2(3),
	@End datetime2(3)
)
returns	@Range table( [Date] datetime2(3), [Count] int )
as
begin;
	set @Increment = lower( @Increment );

	with cte( [Date], [Count] ) AS (
		select @Start [Date], 0 [Count]
		union all
		select
			case
				when @Increment = 'd' THEN dateadd(day, 1, [Date])
				when @Increment = 'w' THEN dateadd(week, 1, [Date])
				when @Increment = 'm' THEN dateadd(month, 1, [Date])
				when @Increment = 'y' THEN dateadd(year, 1, [Date])
			end,
		case
			when @Increment = 'd' THEN datediff( day, @Start, dateadd(day, 1, [Date]))
			when @Increment = 'w' THEN datediff(week, @Start, dateadd(week, 1, [Date]))
			when @Increment = 'm' THEN datediff(month, @Start, dateadd(month, 1, [Date]))
			when @Increment = 'y' THEN datediff(year, @Start, dateadd(year, 1, [Date]))
		end [Count]
		from [cte]
		where [Date] <= 
			case
				when @Increment = 'd' THEN dateadd(day, -1, @End)
				when @Increment = 'w' THEN dateadd(week, -1, @End)
				when @Increment = 'm' THEN dateadd(month, -1, @End)
				when @Increment = 'y' THEN dateadd(year, -1, @End)
			end
	)
	insert into @Range( [Date], [Count] )
	select [Date], [Count]
	from [cte]
	option (maxrecursion 32767);

	return;
end;