Continue to Use SQL Server 2019 CTP After Trial Period Ends

  
  
$currentDate = Get-Date  

$pastTime = $currentDate.AddDays(-179)  

set-date $pastTime  

Start-Service -Name "*sql*" -ErrorAction SilentlyContinue  

$areServicesStopped = $true  

while($areServicesStopped){  

    $serviceStatus = get-service -Name "*sql*" | select status

    if ($serviceStatus -notlike "Stopped")    {

        $areServicesStopped = $false

    }

    Start-Sleep -Seconds 1

}

$currentDate = $pastTime.AddDays(179)  

set-date $currentDate

 

https://sysxnull.blogspot.com/2017/09/solved-restart-sql-service-after-trial.html

Advertisements

Dynamic Alias Name

  
 DECLARE @sql NVARCHAR(MAX)

DECLARE @Month as Int
SET @Month = 2

SELECT @sql = N'Select CAST(@Month as nvarchar(10)) as '+ QUOTENAME('MTD' + CAST(@Month as nvarchar(2)) )   ;

EXEC sp_executesql @SQL,N'@Month int', @Month

 

Recursive Solution to Implement Excel Formula

  


CREATE TABLE #ResultsTable 
(
    Datum DATETIME,
    window CHAR(10),
    countersIN INT,--countersOUT INT,
    countersOUT INT,--countersIN INT,
    RESULT INT,
    RESULT_by_Cursor INT,
    countersIN_corrected INT
);


INSERT INTO #ResultsTable 
VALUES ('20180104 08:30:00.000', '08:30', 0, 0, 0, NULL, NULL),
       ('20180104 09:00:00.000', '09:00', 2, 1, 1, NULL, NULL),
       ('20180104 09:30:00.000', '09:30', 1, 0, 2, NULL, NULL),
       ('20180104 10:00:00.000', '10:00', 25, 9, 18, NULL, NULL),
       ('20180104 10:30:00.000', '10:30', 45, 41, 22, NULL, NULL),
       ('20180104 11:00:00.000', '11:00', 38, 37, 23, NULL, NULL),
       ('20180104 11:30:00.000', '11:30', 50, 51, 22, NULL, NULL),
       ('20180104 12:00:00.000', '12:00', 21, 24, 19, NULL, NULL),
       ('20180104 12:30:00.000', '12:30', 12, 19, 12, NULL, NULL),
       ('20180104 13:00:00.000', '13:00', 25, 18, 19, NULL, NULL),
       ('20180104 13:30:00.000', '13:30', 35, 27, 27, NULL, NULL),
       ('20180104 14:00:00.000', '14:00', 81, 9, 52, NULL, NULL),
       ('20180104 14:30:00.000', '14:30', 113, 18, 70, NULL, NULL),
       ('20180104 15:00:00.000', '15:00', 116, 34, 71, NULL, NULL),
       ('20180104 15:30:00.000', '15:30', 123, 36, 54, NULL, NULL),
       ('20180104 16:00:00.000', '16:00', 127, 35, 50, NULL, NULL),
       ('20180104 16:30:00.000', '16:30', 103, 19, 47, NULL, NULL),
       ('20180104 17:00:00.000', '17:00', 79, 31, 27, NULL, NULL),
       ('20180104 17:30:00.000', '17:30', 50, 16, 26, NULL, NULL),
       ('20180104 18:00:00.000', '18:00', 28, 11, 17, NULL, NULL),
       ('20180104 18:30:00.000', '18:30', 16, 15, 2, NULL, NULL),
       ('20180104 19:00:00.000', '19:00', 0, 2, 0, NULL, NULL),
       ('20180104 19:30:00.000', '19:30', 0, 0, 0, NULL, NULL),
       ('20180104 20:00:00.000', '20:00', 0, 0, 0, NULL, NULL),
       ('20180104 20:30:00.000', '20:30', 0, 0, 0, NULL, NULL),
       ('20180104 21:00:00.000', '21:00', 0, 0, 0, NULL, NULL),
       ('20180104 21:30:00.000', '21:30', 0, 0, 0, NULL, NULL),
       ('20180104 22:00:00.000', '22:00', 0, 0, 0, NULL, NULL)



 ;with mycte as (
select Datum,window,countersIN,countersOUT,  RESULT,
Lead(countersOUT,1)  Over(Order by Datum) countersOUT_next,
Lead(countersOUT,2)  Over(Order by Datum)  countersOUT_nextnext,
row_number() over(order by Datum) rn
from  #ResultsTable 
  )
  ,recursiveCte as (
  select Datum,window,countersIN,countersOUT,  RESULT
  ,countersOUT_next
  ,countersOUT_nextnext
  ,rn  
 , 0 as countersIN_corrected 
 , 0 as RESULT_by_Recursive 
  from mycte
  where Datum='20180104 08:30:00.000'

  Union all

  Select  t.Datum,t.window,t.countersIN,t.countersOUT,  t.RESULT
  ,t.countersOUT_next
  , t.countersOUT_nextnext
  ,t.rn
  ,countersIN_corrected=
  IIF(isnull(r.RESULT_by_Recursive,0) + t.countersIN - t.countersOUT  (t.countersOUT_next + t.countersOUT_nextnext)    ,
  t.countersOUT_next + t.countersOUT_nextnext + t.countersOUT -isnull(r.RESULT_by_Recursive,0) ,
 t.countersIN )  )  
 ,
 RESULT_by_Recursive= 
  r.RESULT_by_Recursive  + 
 (
 IIF( r.RESULT_by_Recursive  + t.countersIN - t.countersOUT  (t.countersOUT_next + t.countersOUT_nextnext)    ,
  t.countersOUT_next + t.countersOUT_nextnext + t.countersOUT - r.RESULT_by_Recursive ,
 t.countersIN )  ) 
 
 )
 - t.countersOUT  

  from  recursiveCte r join mycte t on t.rn=r.rn+1
  )
  
Select Datum, window, countersIN, countersOUT,RESULT,RESULT_by_Recursive,countersIN_corrected	
 
from recursiveCte
 
 
  

DROP TABLE #ResultsTable;

/*
Datum	window	countersIN	countersOUT	RESULT	RESULT_by_Recursive	countersIN_corrected
2018-01-04 08:30:00.000	08:30     	0	0	0	0	0
2018-01-04 09:00:00.000	09:00     	2	1	1	1	2
2018-01-04 09:30:00.000	09:30     	1	0	2	2	1
2018-01-04 10:00:00.000	10:00     	25	9	18	18	25
2018-01-04 10:30:00.000	10:30     	45	41	22	22	45
2018-01-04 11:00:00.000	11:00     	38	37	23	23	38
2018-01-04 11:30:00.000	11:30     	50	51	22	22	50
2018-01-04 12:00:00.000	12:00     	21	24	19	19	21
2018-01-04 12:30:00.000	12:30     	12	19	12	12	12
2018-01-04 13:00:00.000	13:00     	25	18	19	19	25
2018-01-04 13:30:00.000	13:30     	35	27	27	27	35
2018-01-04 14:00:00.000	14:00     	81	9	52	52	34
2018-01-04 14:30:00.000	14:30     	113	18	70	70	36
2018-01-04 15:00:00.000	15:00     	116	34	71	71	35
2018-01-04 15:30:00.000	15:30     	123	36	54	54	19
2018-01-04 16:00:00.000	16:00     	127	35	50	50	31
2018-01-04 16:30:00.000	16:30     	103	19	47	47	16
2018-01-04 17:00:00.000	17:00     	79	31	27	27	11
2018-01-04 17:30:00.000	17:30     	50	16	26	26	15
2018-01-04 18:00:00.000	18:00     	28	11	17	17	2
2018-01-04 18:30:00.000	18:30     	16	15	2	2	0
2018-01-04 19:00:00.000	19:00     	0	2	0	0	0
2018-01-04 19:30:00.000	19:30     	0	0	0	0	0
2018-01-04 20:00:00.000	20:00     	0	0	0	0	0
2018-01-04 20:30:00.000	20:30     	0	0	0	0	0
2018-01-04 21:00:00.000	21:00     	0	0	0	0	0
2018-01-04 21:30:00.000	21:30     	0	0	0	0	0
2018-01-04 22:00:00.000	22:00     	0	0	0	0	0
*/



  

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/e4145994-c9d0-4b16-9fa6-10a2655f3ad5/translate-excel-formula-logic-to-tsql?forum=transactsql


Quarter Calculation T-SQL

  

\CREATE TABLE mytable(
   Qtr         VARCHAR(20) NOT NULL  
  ,CurrentQtr  VARCHAR(2) NOT NULL
  ,CurrentYear INTEGER  NOT NULL
 
);
INSERT INTO mytable(Qtr,CurrentQtr,CurrentYear) VALUES
 ('CurrentQuarter','Q1',2019 )
,('CurrentQuarterMinus1','Q1',2019 )
,('CurrentQuarterMinus2','Q1',2019 )
,('CurrentQuarterMinus3','Q1',2019 )
,('CurrentQuarterMinus4','Q1',2019 )
,('CurrentQuarterMinus5','Q1',2019 )
,('CurrentQuarterMinus6','Q1',2019 )
,('CurrentQuarterMinus7','Q1',2019 )
,('CurrentQuarterMinus8','Q1',2019 );


;with myctedate as (
SELECT  Qtr,CurrentQtr,CurrentYear,
	Dateadd(Quarter,ISNULL(-try_cast(right(Qtr ,1) as int),0),
	Cast(CAST(CurrentYear AS CHAR(4))+'-'+
CASE WHEN CurrentQtr = 'Q1' THEN '01' 
WHEN CurrentQtr = 'Q2' THEN '04'
WHEN CurrentQtr = 'Q3' THEN '07'
WHEN CurrentQtr = 'Q4' THEN '10' Else NULL END +'-01' as Date) ) QuarterDate
From mytable
 )

 Select Qtr,CurrentQtr,CurrentYear,
 Concat(Format(QuarterDate,'yy'),'Q', Datepart(QUARTER,QuarterDate)) ans
 from myctedate

 drop table mytable

  

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/c5b3dd56-5781-41ad-82e5-89a9ab9d7050/tsql-query-help-to-compute-quarteryear?forum=transactsql


Find Monday’s Date in a Week

  
declare @dt datetime=getdate()

select DATEADD([day], ((DATEDIFF([day], 0,@dt ) / 7) * 7) , 0) --Monday's Date
,Dateadd(week,datediff(week,0,@dt),0)  --Monday's Date

, DATEADD([day], ((DATEDIFF([day], 0, @dt ) / 7) * 7) +7, 0) --Next Monday's Date
,Dateadd(week,datediff(week,0, @dt)+1,0)  --Next Monday's Date

  

Multiple Ways to Get End of Month for a Date

  
declare @dt datetime='2016-03-02'

Select 
 Eomonth(@dt)
,Dateadd(month,month(@dt)-12,Datefromparts(Year(@dt),12,31) )
,DateAdd(DAY, -1, dateadd(MONTH, 1, Datefromparts(Year(@dt),Month(@dt),1)))
,DateAdd(DAY, -1, dateadd(MONTH, 1, convert(char(6), @dt, 112) + '01'))
,DateAdd(Month, 1 + DateDiff(month,0,@dt), -1)
,DateAdd(Month,DateDiff(month,0,@dt)+1, 0)-1
,Datefromparts(Year(@dt),Month(@dt)
,
Case  
when Month(@dt) in (4,6,9,11) then 30  
when Month(@dt) in (1,3,5,7,8,10,12) then 31  
else 
  case when Month(@dt) =2 
 and Try_Parse(Concat('02','/29/',Year(@dt))  as datetime) is not NULL 
 then 29  else 28 
 end
 END ) 

  

Check the User Who Started a Long Running Job Query

  

SELECT top 1  msdb.dbo.agent_datetime(run_date, run_time) as 'Job_RunDateTime',
	  sj.name
	, sh.run_date, sh.run_duration
	, sh.step_name
	,Substring(sh.message,charindex('The Job was invoked by User',sh.message)
	,charindex('The last step to run',sh.message)-charindex('The Job was invoked by User',sh.message))
	[The Job was invoked by User]
	 , sh.message 
	
FROM msdb.dbo.sysjobs sj
JOIN msdb.dbo.sysjobhistory sh ON sj.job_id = sh.job_id
join msdb.dbo.sysjobs_view job on job.job_id=sj.job_id
inner join msdb.dbo.sysjobactivity activity on job.job_id = activity.job_id
where activity.run_Requested_date is not null and activity.stop_execution_date is null  
and  sj.name = N'my agent job name'	AND step_id = 0 
Order by   msdb.dbo.agent_datetime(sh.run_date, sh.run_time) desc ;