Categories
Script SQL The Arcane

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;

Categories
C# Tasks

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() );
				}
			}

		}

	}
Categories
Numbers Script SQL

SQL Generate Tally (Number) Table

-- select top 10 [n] from [dbo].[GetNumbers]( 1024 ) order by [n];
create or alter function [dbo].[GetNumbers]( @n bigint )
returns table with schemabinding as
return
-- declare @n bigint = 1024;
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)) as n from l5)
  select top (@n) n
  from nums
  order by n;
Categories
Script SQL Strings

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;
Categories
Date & Times Script SQL

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;
Categories
Date & Times Script SQL

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;
Categories
Date & Times Script SQL

SQL Function to Generate Date Table

Update: Please try the Improved version of this script.

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

	with [cte]( [Date] ) AS (
		select @Start
		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
		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] )
	select [Date]
	from [cte]
	option (maxrecursion 32767);

	return;
end;
Categories
Articles C# Developers

The 4 pillars of OOP (Object Oriented Programming) summed up in my own words.

Encapsulation – The process of hiding internal data away from other classes.

Abstraction – The process of hiding irrelevant details while providing a feature.
(with the side effect of reducing the code complexity.)

Inheritance – The process of providing properties and methods to derived classes.

Polymorphism – The ability of a derived class to take on different behaviors than the parent class provides.

Categories
Utility

RoboCopy – Exclude existing files.cmd

robocopy.exe d:\Sourcepath e:\Destpath /E /XC /XN /XO
The command line switches:

/E makes Robocopy recursively copy subdirectories, including empty ones.

/XC excludes existing files with the same timestamp, but different file sizes. Robocopy normally overwrites those.

/XN excludes existing files newer than the copy in the source directory. Robocopy normally overwrites those.

/XO excludes existing files older than the copy in the source directory. Robocopy normally overwrites those.

With the Changed, Older, and Newer classes excluded, Robocopy will exclude files already existing in the destination directory.
Categories
C#

Magical Methods in C#

Here is a very well written article about various capabilities in C# written by CEZARY PIÄ„TEK.
The Magical Methods in C#