Window Function

use ReportBuilderTraining

---
--- select term data for a student's last enrolled term
---

select *
	,row_number() over (partition by StudentId order by TermId desc) row
	from StudentHistory
	order by StudentId, TermId desc


select *
	from (
	
		select *
			,row_number() over (partition by StudentId order by TermId desc) row
			from StudentHistory

	) t
	where row=1
	-- 5,000 rows


---
--- row_number, rank and dense_rank
---

;with cte as (

	select *
		,case ClassCode
			when 'FR' then 1
			when 'SO' then 2
			when 'JR' then 3
			when 'SR' then 4
		 end ClassCodeSort
		from StudentHistory

)
select *
	,row_number() over (partition by StudentId order by ClassCodeSort) row
	,rank() over (partition by StudentId order by ClassCodeSort) rank
	,dense_rank() over (partition by StudentId order by ClassCodeSort) denserank
	from cte
	order by StudentId, ClassCodeSort, TermId


---
--- Majors in last term enrolled
---

;with cte as (

	select 
		 StudentId
		,TermId
		,MajorRank
		,rtrim(m.MajorCode) + ' - ' + m.MajorName Major
		,dense_rank() over (partition by StudentId order by TermId desc) row
		from StudentMajor sm
		join Major m
		on sm.MajorId=m.MajorId

)
select distinct
	 StudentId, TermId
	,(select Major from cte where StudentId=c.StudentId and row=1 and MajorRank=1) Major1
	,(select Major from cte where StudentId=c.StudentId and row=1 and MajorRank=2) Major2
	,(select Major from cte where StudentId=c.StudentId and row=1 and MajorRank=3) Major3
	from cte c
	where row=1
	-- 5,000 rows, after 14 minutes!

--create unique nonclustered index nc_StudentMajor_K1_K3_K4_2 on dbo.StudentMajor (StudentId, TermId desc, MajorRank) include (MajorId)
--create nonclustered index nc_StudentMajor_K1_K3_4_2 on dbo.StudentMajor (StudentId, TermId desc) include (MajorRank, MajorId)



---
--- often better to use temp tables over cte
---

if object_id('tempdb..#temp') is not null
	drop table #temp

go

select 
	 StudentId
	,TermId
	,MajorRank
	,rtrim(m.MajorCode) + ' - ' + m.MajorName Major
	,dense_rank() over (partition by StudentId order by TermId desc) row
	into #temp
	from StudentMajor sm
	join Major m
	on sm.MajorId=m.MajorId

select distinct
	 StudentId, TermId
	,(select Major from #temp where StudentId=c.StudentId and row=1 and MajorRank=1) Major1
	,(select Major from #temp where StudentId=c.StudentId and row=1 and MajorRank=2) Major2
	,(select Major from #temp where StudentId=c.StudentId and row=1 and MajorRank=3) Major3
	from #temp c
	where row=1
	-- 5,000 rows



---
--- students with address, permanent if we have it otherwise local
---

select *
	from (

		select 
			 s.FirstName, s.LastName
			,a.Street1, a.Street2
			,a.City, a.State, a.Zip
			,a.AddressTypeCode
			,row_number() over (partition by s.StudentId order by a.AddressTypeCode desc) row
			from Student s
			join Address a
			on s.StudentId=a.Studentid

	) t
	where row=1
	--4867 rows