Variables and Logic Flow

use ReportBuilderTraining

---
--- Explore variable delcarations, if/then/else, while loops and BEGIN/END blocks
---

---
--- get table of first mondays of Month of a year
---

---
--- declaring variables, and (optionally) assigning default values
---
declare @count int = 0
declare @date datetime = '20170101'
declare @first_mondays table ([Date] datetime)

--- tell SQL not to report row counts on teh Messages tab. Can be useful when calling scripts or stored procedures
--- from an client non-SQL program (powershell, etc) which can sometimes terminate a data stream when it recieves
--- the first row count
set nocount on

--- loop through each day of the year
while @count <= 366
begin

	-- check if this day is a Monday
	if datepart(dw, @date) = 2
	begin
		
		print 'Monday: ' + convert(varchar, @date, 101)
		insert into @first_mondays ([Date]) values (@date)

		-- quit if we found the first Monday of December
		if month(@date) = 12
			break

		-- Now that we found the first Monday of this month, set to the first day of next month
		set @date = cast(year(@date) as char(4))  + right('00' + cast(month(@date)+1 as varchar), 2) + '01'
		print 'Next Month: ' + convert(varchar, @date, 101)

	end
	else 

		-- increment to next day
		set @date = dateadd(dd, 1, @date)

	-- increment counter
	set @count = @count+1

end

set nocount off

select * from @first_mondays



go


---
--- While loops are almost always the wrong solution. SQL performs much better handling
--- set functions. The following is a preferred method of solving the above problem
---

--- create a Numbers utility table, often used to solve problems like this one

if OBJECT_ID('dbo.Numbers') is not null
	drop table dbo.Numbers
go

create table dbo.Numbers (n int not null)
go

insert into dbo.Numbers (n)
select top 1000000 row_number() over (order by a.number) n
	from master.dbo.spt_values a
	cross join master.dbo.spt_values b

ALTER TABLE dbo.Numbers ADD CONSTRAINT PK_Numbers PRIMARY KEY CLUSTERED (n)
go 



---
--- Now find first Mondays a more preferred way
---

select dateadd(dd, n, '20161231')
	from dbo.Numbers
	where year(dateadd(dd, n, '20161231')) = 2017
	and datepart(dw, dateadd(dd, n, '20161231')) = 2
	and datepart(dd, dateadd(dd, n, '20161231')) <= 7
go