T-SQL Tuesday #143 – Short code examples

It’s this time of the month again! T-SQL Tuesday is a monthly blogging party on the second Tuesday. This month’s topic is “What are your go to handy short scripts?” brought to you by John McCormack.

What are those little short bits of code that you can’t live without? I’m talking about little snippets that are only a few lines, that you may even have memorised. It could be T-SQL, PowerShell, Python or anything else you use day to day.

One piece of T-SQL snippet I use a lot is one to generate a tally table (aka numbers table). It’s based upon the Itzik Ben-Gang style of generating a virtual table using cross joins:

WITH T0 AS (SELECT N	 FROM (VALUES (1),(1)) AS tmp(N))
	,T1 AS (SELECT N = 1 FROM T0 AS a CROSS JOIN T0 AS b)
	,T2 AS (SELECT N = 1 FROM T1 AS a CROSS JOIN T1 AS b)
	,T3 AS (SELECT N = 1 FROM T2 AS a CROSS JOIN T2 AS b)
	,T4 AS (SELECT N = 1 FROM T3 AS a CROSS JOIN T3 AS b)
SELECT RID = ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM T4
ORDER BY RID;

For those of you who don’t know what a tally table is, it’s a construct that allows you to mimic looping behavior in SQL, instead of writing an explicit WHILE loop or a cursor. The advantage is that it is set-based which SQL Server loves and thus wicked fast in many situations. More info here and here. With some slight modifications, we can use it to generate dates as well:

WITH T0 AS (SELECT N	 FROM (VALUES (1),(1)) AS tmp(N))
	,T1 AS (SELECT N = 1 FROM T0 AS a CROSS JOIN T0 AS b)
	,T2 AS (SELECT N = 1 FROM T1 AS a CROSS JOIN T1 AS b)
	,T3 AS (SELECT N = 1 FROM T2 AS a CROSS JOIN T2 AS b)
	,T4 AS (SELECT N = 1 FROM T3 AS a CROSS JOIN T3 AS b)
SELECT DATEADD(DAY,ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) -1,'2000-01-01')
FROM T4;

Speaking of dates, another script I use from time to time is one that generates a date table. Pretty useful at the start of a BI project or for a proof of concept. And yes, it uses a tally table 🙂

DECLARE @datefrom DATE;
DECLARE @dateto DATE;
    
SET DATEFIRST 1;  -- Set monday as first day of week
    
-- Insert date Records
SELECT @datefrom = '2015-01-01',@dateto = '2035-12-31';
    
WITH cte_tally AS
(
    SELECT Num = ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1
    FROM		sys.columns c
    CROSS JOIN	sys.columns c1
)
,	cte_dates AS
(
    SELECT
    		[date] = DATEADD(day, Num, @datefrom)
    	,Num
    FROM cte_tally
    WHERE	Num >= 0
        AND Num <= DATEDIFF(day, @datefrom, @dateto)
)
SELECT
     SK_Date                = d.[date]
    ,[DateDesc]				= CONVERT(CHAR(11),d.[date],120)
    ,[WeekNbr]				= DATEPART(WEEK,d.[date])
    ,[MonthNbr]				= DATEPART(MONTH,d.[date])
    ,[QuarterNbr]			= DATEPART(QUARTER,d.[date])
    ,[SemesterNbr]			= CASE WHEN DATEPART(MONTH, d.[date]) <= 6 THEN 1 ELSE 2 END
    ,[Year]					= DATEPART(YEAR, d.[date])
    ,[DayName]				= DATENAME(WEEKDAY, d.[date])
    ,[WeekName]				= 'W' + CAST(DATEPART(WEEK,d.[date]) AS VARCHAR(2))
    ,[MonthName]			= DATENAME(MONTH,d.[date])
    ,[QuarterName]			= 'Q' + CAST(DATEPART(QUARTER,d.[date]) AS CHAR(1))
    ,[SemesterName]			= 'S' + CAST(CASE WHEN DATEPART(m, d.[date]) <= 6 THEN 1 ELSE 2 END AS CHAR(1))
    ,[YearWeek]				= CAST(DATEPART(YEAR, d.[date]) AS CHAR(4)) + RIGHT('0' + CAST(DATEPART(WEEK, d.[date]) AS VARCHAR(2)),2)
    ,[YearWeekDesc]			= CAST(DATEPART(YEAR, d.[date]) AS CHAR(4)) + ' W' + CAST(DATEPART(WEEK,d.[date]) AS VARCHAR(2))
    ,[YearMonth]			= CAST(DATEPART(YEAR, d.[date]) AS CHAR(4)) + SUBSTRING(CONVERT(VARCHAR(6),d.[date],112),5,2)
    ,[YearMonthDesc]        = CAST(DATEPART(YEAR, d.[date]) AS CHAR(4)) + '-' + RIGHT('0' + CAST(DATEPART(MONTH, d.[date]) AS VARCHAR(2)),2)
    ,[YearMonthDescFull]	= DATENAME(MONTH,d.[date]) + ' ' + CAST(DATEPART(YEAR, d.[date]) AS CHAR(4))
    ,[YearQuarter]			= CAST(DATEPART(YEAR, d.[date]) AS CHAR(4)) + CAST(DATEPART(QUARTER, d.[date]) AS CHAR(1))
    ,[YearQuarterDesc]		= CAST(DATEPART(YEAR, d.[date]) AS CHAR(4)) + ' ' + 'Q' + CAST(DATEPART(QUARTER,d.[date]) AS CHAR(1))
    ,[YearSemester]			= CAST(DATEPART(YEAR, d.[date]) AS CHAR(4)) + CASE WHEN DATEPART(MONTH, d.[date]) <= 6 THEN '1' ELSE '2' END
    ,[YearSemesterDesc]		= CAST(DATEPART(YEAR, d.[date]) AS CHAR(4)) + ' ' + 'S' + CAST(CASE WHEN DATEPART(m, d.[date]) <= 6 THEN 1 ELSE 2 END AS CHAR(1))
    ,[IsWeekDay]			= CASE WHEN DATEPART(WEEKDAY, d.[date]) < 6 THEN 'yes' ELSE 'no' END
    ,[IsMonthFirstDay]		= CASE WHEN d.[date] = DATEADD(MONTH, DATEDIFF(MONTH, 0, d.[date]), 0) THEN 'yes' ELSE 'no' END
    ,[IsMonthLastDay]		= CASE WHEN d.[date] = EOMONTH(d.[date],0) THEN 'yes' ELSE 'no' END
    ,[MonthFirstDay]		= DATEADD(DAY,1,EOMONTH(d.[date],-1))
    ,[MonthLastDay]			= EOMONTH(d.[date],0)
    ,[DayOfWeek]			= DATEPART(WEEKDAY, d.[date])
    ,[DayOfMonth]			= DATEPART(DAY, d.[date])
    ,[DayOfQuarter]			= DATEDIFF(DAY,DATEADD(QUARTER,DATEDIFF(QUARTER,0,d.[date]),0),d.[date]) + 1
    ,[DayOfSemester]		= CASE	WHEN DATEPART(QUARTER,d.[date]) IN (1,3) -- day of semester is equal to day of quarter in the quarters 1 and 3
    									THEN DATEDIFF(DAY,DATEADD(QUARTER,DATEDIFF(QUARTER,0,d.[date]),0),d.[date]) + 1
    									ELSE CASE WHEN DATEPART(QUARTER,d.[date]) = 2 -- take the last day of quarter of quarter 1 + day of quarter of quarter 2
    												THEN DATEDIFF(DAY,DATEADD(QUARTER,DATEDIFF(QUARTER,0,DATEFROMPARTS(YEAR(d.[date]),3,31)),0),DATEFROMPARTS(YEAR(d.[date]),3,31)) + 1 + DATEDIFF(DAY,DATEADD(QUARTER,DATEDIFF(QUARTER,0,d.[date]),0),d.[date]) + 1
    												-- take the last day of quarter of quarter 3 + day of quarter of quarter 4
    												ELSE DATEDIFF(DAY,DATEADD(QUARTER,DATEDIFF(QUARTER,0,DATEFROMPARTS(YEAR(d.[date]),9,30)),0),DATEFROMPARTS(YEAR(d.[date]),9,30)) + 1 + DATEDIFF(DAY,DATEADD(QUARTER,DATEDIFF(QUARTER,0,d.[date]),0),d.[date]) + 1
    											END
    							END
    ,[DayOfYear]			= DATEPART(DAYOFYEAR, d.[date])
    ,[DaysAgo]				= DATEDIFF(DAY,d.[date],CAST(GETDATE() AS DATE))
    ,[WeeksAgo]				= DATEDIFF(WEEK,d.[date],CAST(GETDATE() AS DATE))
    ,[MonthsAgo]			= DATEDIFF(MONTH,d.[date],CAST(GETDATE() AS DATE))
    ,[QuartersAgo]			= DATEDIFF(QUARTER,d.[date],CAST(GETDATE() AS DATE))
    ,[SemestersAgo]			= CASE WHEN DATEDIFF(QUARTER,d.[date],CAST(GETDATE() AS DATE)) % 2 = 0 -- even number of quarters ago
    									THEN DATEDIFF(QUARTER,d.[date],CAST(GETDATE() AS DATE)) / 2 -- there are 2 quarters in a semester
    									ELSE (DATEDIFF(QUARTER,d.[date],CAST(GETDATE() AS DATE)) + 1) / 2
    							END
    ,[YearsAgo]				= DATEDIFF(YEAR,d.[date],CAST(GETDATE() AS DATE))
    ,IsCurrentDate			= CASE WHEN d.[date] = CAST(GETDATE() AS DATE) THEN 'yes' ELSE 'no' END
    ,IsCurrentWeek			= CASE WHEN DATEPART(WEEK,d.[date])		= DATEPART(WEEK,CAST(GETDATE() AS DATE))	AND YEAR(d.[date]) = YEAR(GETDATE()) THEN 'yes' ELSE 'no' END
    ,IsCurrentMonth			= CASE WHEN DATEPART(MONTH,d.[date])		= DATEPART(MONTH,CAST(GETDATE() AS DATE))	AND YEAR(d.[date]) = YEAR(GETDATE()) THEN 'yes' ELSE 'no' END
    ,IsCurrentQuarter		= CASE WHEN DATEPART(QUARTER,d.[date])	= DATEPART(QUARTER,CAST(GETDATE() AS DATE)) AND YEAR(d.[date]) = YEAR(GETDATE()) THEN 'yes' ELSE 'no' END
    ,IsCurrentSemester		= CASE WHEN CAST(DATEPART(yyyy, d.[date]) AS CHAR(4)) + CASE WHEN DATEPART(m, d.[date]) <= 6 THEN '1' ELSE '2' END
    										= CAST(DATEPART(yyyy,GETDATE()) AS CHAR(4)) + CASE WHEN DATEPART(m, GETDATE()) <= 6 THEN '1' ELSE '2' END
    									THEN 'yes' ELSE 'no' END
    ,IsCurrentYear			= CASE WHEN YEAR(d.[date]) = YEAR(GETDATE()) THEN 'yes' ELSE 'no' END
    ,[Last12Months]			= CASE WHEN DATEDIFF(MONTH,d.[date],CAST(GETDATE() AS DATE)) < 13 AND DATEDIFF(MONTH,d.[date],CAST(GETDATE() AS DATE)) > 0
    									THEN 'yes'
    									ELSE 'no'
    								END
    ,[PreviousMonth]		= CASE WHEN DATEDIFF(MONTH,d.[date],CAST(GETDATE() AS DATE)) < 2 AND DATEDIFF(MONTH,d.[date],CAST(GETDATE() AS DATE)) > 0
    									THEN 'yes'
    									ELSE 'no'
    								END
    ,[PreviousWeek]			= CASE WHEN DATEDIFF(WEEK,DATEADD(dd,-1,d.[date]),DATEADD(dd,-1,CAST(GETDATE() AS DATE))) < 2 AND DATEDIFF(WEEK,DATEADD(dd,-1,d.[date]),DATEADD(dd,-1,CAST(GETDATE() AS DATE))) > 0
    									THEN 'yes'
    									ELSE 'no'
    								END   
FROM cte_dates d;

You can find the scripts (and a few others) on my GitHub repo. Using such a repo is a good way to ensure you access to your handy scripts wherever you are.