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.
------------------------------------------------
Do you like this blog post? You can thank me by buying me a beer 🙂
