sheldonhull sheldonhull

👋

Hi! Who keeps their github status up to date? You get a 🌮 just for reading this

Staff Software Engineer with an obsession to automate all things https://www.sheldonhull.com

sheldonhull / dateSnippets.sql

Last active December 16, 2015 06:19

common date manipulation to get EOM/BOM values

	declare @Date date = getdate()
	select
	previous_month_bom = dateadd(month, datediff(month, 0, @Date) - 1, 0) --previous_month_bom
	,previous_month_eom = dateadd(day, -1, dateadd(month, datediff(month, 0, @Date), 0)) -- previous_month_eom
	,bom = dateadd(month, datediff(month, 0, @Date), 0) -- bom
	,eom = dateadd(month, datediff(month, 0, @Date) + 1, -1) -- eom
	,next_bom = dateadd(month, datediff(month, 0, @Date) + 1, 0) -- next_bom
	,next_eom = dateadd(day, -1, dateadd(month, datediff(month, 0, @Date) + 2, 0)) -- next_eom

sheldonhull / TutorialEliminateOverlappingDueDates.sql

Last active March 12, 2019 05:52

Eliminate Overlapping Dates, with subgroupings (such as overlap only eliminated at process > task level, instead of just process level)

	if object_id('tempdb..#Awesome') is not null
	drop table #awesome;

	create table #awesome
	(
	unique_join_k int
	,process varchar(10)
	,start_date date
	,end_date date
	,grouper_id as convert(uniqueidentifier, hashbytes('sha1',

sheldonhull / IslandsOfConsecutivePerf.sql

Created October 20, 2014 21:09

Finding Islands of Consecutive Performance - long winded version (without the row-row in single statement)

	/*******************************************************
	identifying gaps on a month
	*******************************************************/
	if object_id('tempdb..#accounts') is not null
	drop table #accounts;

	-- Starting Point
	select
	'Starting Result Set' as query_description
	,x.account

sheldonhull / createColumns.sql

Created October 23, 2014 15:47

create_date and create_by_user columns for tables

	,create_date datetime2(3) not null default getdate()
	,create_by_user varchar(50) default suser_sname() not null

sheldonhull / obtainTraceInformation

Created December 2, 2014 14:49

obtain information on traces running

	------------------------- obtain information on traces running -------------------------
	select
	trace_id = id
	,is_rowset
	,trace_name = reverse(left(reverse(path),
	charindex('\', reverse(path),
	1) - 1))
	,trace_duration =
	case
	when stop_time is not null then 'Duration (min): ' + convert(varchar(10), datediff(minute, start_time, stop_time), 120)

sheldonhull / create_or_proc_originalProposedSolution

Last active August 29, 2015 14:11

OR_test_step1

	create procedure test2
	@id int = 0,
	@col1 int = 0,
	@col2 int = 0
	as
	begin
	set nocount on
	declare @sql nvarchar(255), @cond nvarchar(255)
	set @sql = 'select id, col1, col2 from or_test '
	set @cond = ''

sheldonhull / or_index_to_add

Created December 11, 2014 16:15

OR test research and response

create unique nonclustered index ix_nc_CoveringIndex on or_test (id, col1, col2) with drop_existing;

sheldonhull / Detecting and reducing VLFs in SQL Server 2008.sql

Last active August 29, 2015 14:13

	-- Detecting and reducing VLFs in SQL Server 2008
	-- Glenn Berry
	-- June 2010
	-- http://glennberrysqlperformance.spaces.live.com/
	-- Twitter: GlennAlanBerry

	--select db_name(), * from sys.database_files

	declare @DbName sysname = db_name()
	,@Dt varchar(30) = convert(varchar(30), getdate(), 121)

sheldonhull / StatisticsParser.ahk

Created January 21, 2015 19:32

Monitors clipboard for ssms statistics IO being copied and opens URL, pastes, and submits so the results can be easily used

sheldonhull / queryingXMLDataWithSQL.sql

Created June 24, 2015 15:13

Querying XML Data Examples

	/*******************************************************
	test data to work with
	*******************************************************/
	if object_id('tempdb..#sample') is not null
	drop table #sample;
	select
	*
	into #sample
	from
	(