A few useful Microsoft SQL Server T-SQL function samples

a-common.sql

-- A few useful Microsoft SQL Server T-SQL function samples

cursor.sql

-- Procedure using a cursor to gather date-range data from another procedure that takes a date as parameter
CREATE PROCEDURE gatherdates
	@StartDate DATE = '20210401', @EndDate DATE = '20210501'
AS

SET NOCOUNT ON;
DECLARE @reportDates as CURSOR;
DECLARE @selDate as DATE;
--DECLARE @lastDateValue as decimal(19,4);

-- Temp table to hold the results
DECLARE @breakdownMoney TABLE (
	ViewDate DATE,
	[Payouts] decimal(19,4),
	[Revenue] decimal(19,4)
)

-- Generate a cursor (list) of selected date range
SET @reportDates = CURSOR FAST_FORWARD FOR
	SELECT distinct DATEADD(DAY, nbr - 1, @StartDate) as ReportDate
	FROM    ( SELECT    ROW_NUMBER() OVER ( ORDER BY c.object_id ) AS Nbr
			  FROM      sys.columns c -- just a (funny) way to generate a running sequence
			) nbrs
	WHERE   nbr - 1 <= DATEDIFF(DAY, @StartDate, @EndDate)

-- Loop through dates, getting totals per day
OPEN @reportDates;
FETCH NEXT FROM @reportDates INTO @selDate;
WHILE @@FETCH_STATUS = 0
BEGIN

	 --PRINT ('Executing gatherDailyValues for ' + CAST(@selDate AS VARCHAR(MAX)))
	 INSERT INTO @breakdownMoney EXEC [gatherDailyValues] @selDate
	 FETCH NEXT FROM @reportDates INTO @selDate;
END
CLOSE @reportDates;
DEALLOCATE @reportDates;

-- All
select * from @breakdownMoney
where [Payouts] is not null
order by ViewDate
GO

group-sums.sql

-- Group by with multiple counts and coalesce (isnull) and datediff
select
	Username
	, sum(case Accept when 1 then 1 else 0 end) as Accepted /* int */
	, sum(case Accept when 0 then 1 else 0 end) as Rejected /* int */
	, max(isNull(datediff(day, Created, Removed),0)) as ActiveAge /* int null */
from logtable
group by
	Username

median.sql

-- Calculate Median via SQL Window function:
SELECT ItemId, AVG(1.0 * X.DaysBetweenMedian) AS DaysBetweenMedian
 FROM ( -- Find the 1 middle (or 0 and 2 middle) dates, then select their avg. 
	SELECT ItemId, DaysBetweenMedian, -- per item
		(2 * ROW_NUMBER() OVER (PARTITION BY ItemId ORDER BY DaysBetweenMedian)
		 - COUNT(*) OVER (PARTITION BY ItemId)) AS middle_subset
	FROM (
		select ItemId, DueDate,
		datediff(day,lag(DueDate) over (partition by ItemId order by DueDate),DueDate) as DaysBetweenMedian
		from scheduletable
	) X
	where DaysBetweenMedian is not null

) AS X
WHERE X.middle_subset BETWEEN 0 AND 2
group by ItemId

missing-indexes.sql

-- Missing indexes query by Pinal Dave
-- https://blog.sqlauthority.com/2011/01/03/sql-server-2008-missing-index-script-download/

SELECT TOP 25
dm_mid.database_id AS DatabaseID,
dm_migs.avg_user_impact*(dm_migs.user_seeks+dm_migs.user_scans) Avg_Estimated_Impact,
dm_migs.last_user_seek AS Last_User_Seek,
OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id) AS [TableName],
'CREATE INDEX [IX_' + OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id) + '_'
+ REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.equality_columns,''),', ','_'),'[',''),']','') 
+ CASE
WHEN dm_mid.equality_columns IS NOT NULL
AND dm_mid.inequality_columns IS NOT NULL THEN '_'
ELSE ''
END
+ REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.inequality_columns,''),', ','_'),'[',''),']','')
+ ']'
+ ' ON ' + dm_mid.statement
+ ' (' + ISNULL (dm_mid.equality_columns,'')
+ CASE WHEN dm_mid.equality_columns IS NOT NULL AND dm_mid.inequality_columns 
IS NOT NULL THEN ',' ELSE
'' END
+ ISNULL (dm_mid.inequality_columns, '')
+ ')'
+ ISNULL (' INCLUDE (' + dm_mid.included_columns + ')', '') AS Create_Statement
FROM sys.dm_db_missing_index_groups dm_mig
INNER JOIN sys.dm_db_missing_index_group_stats dm_migs
ON dm_migs.group_handle = dm_mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details dm_mid
ON dm_mig.index_handle = dm_mid.index_handle
WHERE dm_mid.database_ID = DB_ID()
ORDER BY Avg_Estimated_Impact DESC
GO

pivot.sql

-- A transpose to pivot table:
select *
from 
	(select role.[userid], role.[name] 
	 from user_roles role) r
pivot
(
	max ([name])
	for [Name] in (manager,analyst,normal)		
) as x