보미네

if object_id ( 'tblx') is not null 
drop table tblx 
go
 
select 
	c1, c2, c3, isnull(sign(c3-lag(c3, 1) over (partition by c1 order by (c2))),0) c4 
into tblx 
from 
	(
	select char(a.idx % 26 + 65) c1
		, cast ('20180101' as datetime) + b.idx c2
		, cast(abs(checksum(newid())) % 100000 as int) c3
	from 
		(
		select top 26 cast(row_number() over (order by (select 1)) as int) -1 idx
		from sys.objects a1
		) a, 
		(
		select top 365 cast(row_number() over (order by (select 1)) as int) -1 idx
		from sys.objects a1
			, sys.objects a2
		) b 
	) a 
where 
   datepart(weekday, c2 ) not in (1,7)

--
select * from tblx 

-- 1 
; with a as
(
select row_number() over ( partition by c1, c order by c2) rn 
	, * 
from tblx 
	, 
	(
	select 0 c union all 
	select 1 union all 
	select 2 union all
	select 3 union all
	select 4 union all
	select 5
	) b
) 
select 
	  max(c1) c1, min(c2) c2
	, max(case when c = 0 then c3 else null end ) d0
	, max(case when c = 1 then c3 else null end ) d1
	, max(case when c = 2 then c3 else null end ) d2 
	, max(case when c = 3 then c3 else null end ) d3
	, max(case when c = 4 then c3 else null end ) d4 
	, max(case when c = 5 then c3 else null end ) d5 
from a 
group by c1, rn-c
having max(case when c = 0 then c3 else null end ) is not null
	and sign (max(case when c = 1 then c3 else null end ) - max(case when c = 0 then c3 else null end )) 
	  + sign (max(case when c = 2 then c3 else null end ) - max(case when c = 1 then c3 else null end )) 
	  + sign (max(case when c = 3 then c3 else null end ) - max(case when c = 2 then c3 else null end )) 
	  + sign (max(case when c = 4 then c3 else null end ) - max(case when c = 3 then c3 else null end )) 
	  + sign (max(case when c = 5 then c3 else null end ) - max(case when c = 4 then c3 else null end )) 
	  = 5
order by c1, c2 

-- 2 
; with a 
as
(
select *
	, lag(c3, 1) over (partition by c1 order by (c2)) dm1
	, lag(c3, 2) over (partition by c1 order by (c2)) dm2
	, lag(c3, 3) over (partition by c1 order by (c2)) dm3
	, lag(c3, 4) over (partition by c1 order by (c2)) dm4
	, lag(c3, 5) over (partition by c1 order by (c2)) dm5
from tblx
)
select * 
from a 
where 
	sign(dm4-dm5)
	+ sign(dm3-dm4)
	+ sign(dm2-dm3)
	+ sign(dm1-dm2)
	+ sign(c3-dm1)  = 5

-- 3 
select a.c1, a.c2, a.c3
from 
	(
	select a.* , count(*) over (partition by c1, rn2) as cn
	from 
		(
			select a.*
				, row_number() over (partition by c1 order by c2) 
				  - row_number() over (partition by c1, c4 order by c2) as rn2
			from tblx a
		) a
	where c4 = 1
	) a
where cn >= 5 
order by c1, c2

function SqlQuery 
{
    param (
        [parameter(Mandatory=$true)]  [string] $server, 
        [parameter(Mandatory=$true)]  [string] $port,
        [parameter(Mandatory=$true)]  [string] $database, 
        [parameter(Mandatory=$true)]  [string] $id, 
        [parameter(Mandatory=$true)]  [string] $pass, 
        [parameter(Mandatory=$true)]  [string] $query,
        [parameter(Mandatory=$false)] [switch] $isRead = $false , 
        [parameter(Mandatory=$false)] [int]    $queryTimeout = 0 ,
        [parameter(Mandatory=$false)] [int]    $connTimeout = 5 ,

        [parameter(Mandatory=$false)] [string] $appName = "PowerShell"  
    ) 
    try
    {
        $substringLen = 30
        if ($query.Length -lt $substringLen)
        {
            $substringLen = $query.Length
        }
        $querySubstring = $query.substring(0, $substringLen)
        
        $conn = New-Object System.Data.SQLClient.SQLConnection
        $conn.ConnectionString = "server=$($server),$($port);database=$($database);User Id=$($id);Password=$($pass);Connect Timeout=$($connTimeout);Application Name=$($appName)"
        $conn.Open()
        $cmd = New-Object System.Data.SQLClient.SQLCommand
        $cmd.Connection = $conn
        $cmd.CommandText = $query
        $cmd.CommandTimeout=$queryTimeout
        if($isRead)
        {
            $ds=New-Object system.Data.DataSet
            $da=New-Object system.Data.SqlClient.SqlDataAdapter($cmd)
            $da.fill($ds) | Out-Null
            Write-Host ($ds.Tables | Format-Table | Out-String)
        }
        else
        {
            $cmd.ExecuteNonQuery() | Out-Null
        }
        $conn.Close()

        Write-Host "SUCCESS, SERVER : $($server), QUERY : $($querySubstring) ..."
    }
    catch 
    {
        Write-Host "FAILED, SERVER : $($server), QUERY : $($querySubstring) ..."
        throw "FAILED SERVER : $($server), ERROR MESSAGE : $($_)"
    }
}



[string] $port = "1433"
[string] $id = "id"
[string] $pass = "pass"
[string] $query = 
@"
select @@version
"@

SqlQuery -server "ip" -port "1433" -database "master" -id  $id -pass $pass -query $query -isRead:$true

using System;
using System.Collections.Generic;
using System.IO.Ports;
using System.Linq;
using System.Text;
using System.Threading;
using System.Threading.Tasks;

namespace TaskTest2
{
    // Parallel execution can be performed as much as the specified number.
    // Job can be canceled.
    // You can set the task timeout.
    // When a task exception occurs, all workers can be stopped.

    internal class Program
    {
        static readonly CancellationTokenSource cancellationTokenSource = new CancellationTokenSource();

        public static void Main(string[] args)
        {

            // Simulation of job cancellation time (external factor)
            DateTime endTime = DateTime.Now.Add(new TimeSpan(0, 0, 1000));

            Manager manager = new Manager(cancellationTokenSource);
            var task = manager.Run(maxDegreeOfParallelism: 10, workerCnt: 10, dummyWorkerIterationCnt: 10, errorPosition: 1);
            try
            {
                // Work cancellation The internal work must be completed within this time.
                task = AsyncExtensions.TimeoutAfter(task, TimeSpan.FromSeconds(100), cancellationTokenSource);
                while (!task.Wait(50))
                {
                    Console.Write(".");
                    if (DateTime.Now > endTime)
                        manager.Stop();
                }
            }
            catch (AggregateException aex)
            {
                foreach (var ex in aex.InnerExceptions)
                {
                    Console.WriteLine($@"main inner exception {ex.Message}");
                }
            }
            catch (TimeoutException ex)
            {
                Console.WriteLine($@"main timeout {ex.Message}");
            }
            catch (Exception ex)
            {
                Console.WriteLine($@"main exception ex {ex.Message}");
            }
            Console.WriteLine("main program end");
        }
    }

    internal class Manager
    {
        CancellationTokenSource cancellationTokenSource;

        public Manager(CancellationTokenSource cancellationTokenSource)
        {
            this.cancellationTokenSource = cancellationTokenSource;
        }

        public void Stop()
        {
            cancellationTokenSource.Cancel();
            Console.WriteLine("cancel requested");
        }

        public async Task Run(int maxDegreeOfParallelism, int workerCnt, int dummyWorkerIterationCnt, int? errorPosition)
        {

            CancellationToken token = cancellationTokenSource.Token;
            List<Func<Task>> actionList = new List<Func<Task>>();

            for (int i = 0; i < workerCnt; i++)
            {
                int j = i;
                actionList.Add(() => new DummyWorker(token).DoWork(iteration: dummyWorkerIterationCnt, errorPosition: errorPosition));
                if (token.IsCancellationRequested)
                {
                    Console.WriteLine($@"run token cancel requested");
                    break;
                }
            }



            try
            {
                await AsyncExtensions.InvokeAsync(actionList, maxDegreeOfParallelism: maxDegreeOfParallelism, token);
            }
            catch (Exception ex)
            {
                Console.WriteLine($@"run method exception {ex.Message}");
            }
            Console.WriteLine("end");

        }
    }

    internal class DummyWorker
    {
        CancellationToken token;

        public DummyWorker(CancellationToken token)
        {
            this.token = token;
        }

        public Task DoWork(int iteration, int? errorPosition)
        {
            var task = Task.Run(async () =>
            {
                for (int i = 0; i < iteration; i++)
                {
                    if (token.IsCancellationRequested)
                    {
                        Console.WriteLine($@"{GetType()} cancel requested");
                        break;
                    }
                    if (i == errorPosition)
                        throw new Exception("simulation exception : fire!");

                    // working...
                    await Task.Delay(500);

                    Console.WriteLine($@"working...iteration {i}");
                }
            }, token);
            return task;
        }
    }

    public static class AsyncExtensions
    {
        public static async Task<T> TimeoutAfter<T>(this Task<T> task, TimeSpan timeout, CancellationTokenSource cancellationTokenSource = null)
        {
            if (task == await Task.WhenAny(task, Task.Delay(timeout)))
                return await task;
            else
            {
                if (cancellationTokenSource != null)
                    cancellationTokenSource.Cancel();

                throw new TimeoutException();
            }
        }
        public static async Task TimeoutAfter(this Task task, TimeSpan timeout, CancellationTokenSource cancellationTokenSource = null)
        {
            if (task == await Task.WhenAny(task, Task.Delay(timeout)))
                await task;
            else
            {
                if (cancellationTokenSource != null)
                    cancellationTokenSource.Cancel();

                throw new TimeoutException();
            }
        }
        public static async Task InvokeAsync(IEnumerable<Func<Task>> taskFactories, int maxDegreeOfParallelism, CancellationToken token)
        {
            if (taskFactories == null) throw new ArgumentNullException(nameof(taskFactories));
            if (maxDegreeOfParallelism <= 0) throw new ArgumentException(nameof(maxDegreeOfParallelism));

            // Defensive copy. Similar to what Task.WhenAll/WhenAny does.
            Func<Task>[] queue = taskFactories.ToArray();

            if (queue.Length == 0)
            {
                return;
            }

            List<Task> tasksInFlight = new List<Task>(maxDegreeOfParallelism);
            int index = 0;

            do
            {
                while (tasksInFlight.Count < maxDegreeOfParallelism && index < queue.Length)
                {
                    if (token.IsCancellationRequested)
                        return;

                    Func<Task> taskFactory = queue[index++];

                    tasksInFlight.Add(taskFactory());
                }

                if (token.IsCancellationRequested)
                    return;

                Task completedTask = await Task.WhenAny(tasksInFlight).ConfigureAwait(false);

                try
                {
                    await completedTask.ConfigureAwait(false); // Check result.
                }
                catch
                {
                    throw;
                }

                tasksInFlight.Remove(completedTask);
            }
            while (index < queue.Length || tasksInFlight.Count != 0);
        }
    }
}