Prairie Valley bus planner export

PVbusplanner

declare @command varchar(1000) 

	IF OBJECT_ID('tempdb..#temptable') IS NOT NULL DROP TABLE #temptable
	
	select distinct
		S.cStudentNumber,
		s.cGovernmentNumber as 'Government Number',
		s.cLegalLastName as 'Legal Last Name',
		s.cLegalFirstName as 'Legal First Name',
		us.UF_1590,
		'"' + (select CONVERT(VARCHAR(12), s.dBirthdate, 107) AS [Mon DD, YYYY]) + '"' as 'BirthDate',
		isnull(cF.cLastName,'') as 'Father Last Name',
		isnull(cF.cFirstName,'') as 'Father First Name',
		isnull(cM.cLastName,'') as 'Mother Last Name',
		isnull(cM.cFirstName,'') as 'Mother First Name',
		isnull(cG.clastname,'') as 'Guardian Last Name',
		isnull(cG.cfirstname,'') as 'Guardian First Name',
		'(' + substring(sl.cPhone,1,3) + ')' + substring(sl.cPhone,4,3) + '-' + SUBSTRING(sl.cPhone,7,4) as 'Phone',
		isnull(dbo.GetGradesName(s.iGradesID),'') as 'Grade',
		isnull(sl.cHouseNo,'') + ' ' + isnull(sl.cStreet,'') as 'Mailing Address Street',
		isnull(dbo.GetLookupValue(sl.iLV_CityID),'') as 'Mailing Address City',
		isnull(sl.cPostalCode,'') as 'Postal Code',
		--isnull(sal.cHouseNo,'') as 'House',
		case
				when ((isnull(sal.cStreet,'') like '') and (isnull(sal.cHouseNo,'') like '') and (isnull(dbo.getlookupvalue(sal.iLV_CityID),'') like '') and (isnull((dbo.GetLookupValue(us.UF_2189) + '-' + dbo.GetLookupValue(us.UF_2193) + '-' + dbo.GetLookupValue(us.UF_2194) + '-' + dbo.GetLookupValue(us.UF_2190) + '-' + dbo.GetLookupValue(us.UF_2188) ),'') like ''))
				then isnull(sl.cHouseNo,'')
				else isnull(sal.cHouseNo,'')
				end as 'House',
		--isnull(sal.cStreet,'') as 'Street',
		case
				when ((isnull(sal.cStreet,'') like '') and (isnull(sal.cHouseNo,'') like '') and (isnull(dbo.getlookupvalue(sal.iLV_CityID),'') like '') and (isnull((dbo.GetLookupValue(us.UF_2189) + '-' + dbo.GetLookupValue(us.UF_2193) + '-' + dbo.GetLookupValue(us.UF_2194) + '-' + dbo.GetLookupValue(us.UF_2190) + '-' + dbo.GetLookupValue(us.UF_2188) ),'') like ''))
				then isnull(sl.cStreet,'')
				else isnull(sal.cStreet,'')
				end as 'Street',
		--isnull(dbo.GetLookupValue(sal.iLV_CityID),'') as 'City',
			case
				when ((isnull(sal.cStreet,'') like '') and (isnull(sal.cHouseNo,'') like '') and (isnull(dbo.getlookupvalue(sal.iLV_CityID),'') like '') and (isnull((dbo.GetLookupValue(us.UF_2189) + '-' + dbo.GetLookupValue(us.UF_2193) + '-' + dbo.GetLookupValue(us.UF_2194) + '-' + dbo.GetLookupValue(us.UF_2190) + '-' + dbo.GetLookupValue(us.UF_2188) ),'') like ''))
				then isnull(dbo.GetLookupValue(sl.iLV_CityID),'')
				else isnull(dbo.GetLookupValue(sal.iLV_CityID),'')
				end as 'City',
		isnull((dbo.GetLookupValue(us.UF_2189) + '-' + dbo.GetLookupValue(us.UF_2193) + '-' + dbo.GetLookupValue(us.UF_2194) + '-' + dbo.GetLookupValue(us.UF_2190) + '-' + dbo.GetLookupValue(us.UF_2188) ),'') as 'Land Location',
		case 
				when ((select UF_1656 from userstudent where iStudentID = s.istudentid) like 45506) 
				then 'French'
				else ''
				end as 'Program',
		ss.cCode
			into #temptable
	from student s
	left join School ss
		on ss.iSchoolID = s.iSchoolID 
	left join Location sl
		on sl.iLocationID = s.iLocationID 
--------
	left join ContactRelation crF
		on crF.iStudentID = s.iStudentID and dbo.getLookupValue(crF.iLV_RelationID) like 'Father'
	left join  Contact cF
		on cF.iContactID = crF.iContactID
	left join  Location clF
		on clF.iLocationID = cF.iLocationID
--------
	left join  ContactRelation crM
		on crM.iStudentID = s.iStudentID and dbo.getLookupValue(crM.iLV_RelationID) like 'Mother'
	left join  Contact cM
		on cM.iContactID = crM.iContactID
	left join  Location clM
		on clM.iLocationID = cM.iLocationID 
--------
	left join  
				(select * from (
				select 
					row_number() over (partition by cr.istudentid,cphone order by cr.istudentid,cphone) as 'rowid',
					cr.*
				from student s
				join ContactRelation cr
					on cr.iStudentID = s.iStudentID
				join Contact c
					on c.icontactid = cr.icontactid
				join Location cl
					on cl.iLocationID = c.ilocationid
				where dbo.getlookupvalue(cr.iLV_RelationID) like 'Guardian') a
				where rowid = 1) crG
		on crG.iStudentID = s.iStudentID 
		and dbo.getLookupValue(crG.iLV_RelationID) like 'Guardian' 
		--and (crG.iContactPriority = ( 
		--			select min(iContactPriority) from contactrelation 
		--			where iStudentID = s.iStudentID 
		--			and dbo.getLookupValue(crG.iLV_RelationID) like 'Guardian') 
		--	or
		--	(crG.iContactPriority is null))
	left join Contact cG
			on cG.iContactID = crG.iContactID 
	left join  Location clG
		on clG.iLocationID = cG.iLocationID 		
--------
	left join  StudentAddress sa
		on sa.istudentid = s.istudentid
		and sa.iStudentAddressID = (select MAX(istudentaddressid) from StudentAddress where iStudentID = s.istudentid and dbo.getlookupvalue(sa.iLV_AddressTypeID) like 'Physical%')
	left join  Location sal
		on sal.iLocationID = sa.iLocationID
	left join  UserStudent us
		on us.iStudentID = s.iStudentID 
	where s.lcurrent = 1
	and s.iStatusType = 1
	--and us.UF_1590 = 1
	--and s.cgovernmentnumber like '117725845'
	order by 2,3



--------------------------------------------------------
--------------------------------------------------------
--post processing 

--remove blank phone numbers
update #temptable set Phone = '' where Phone = '()-'

--remove anything from physical address with the word 'box'
update #temptable set Street = '' where Street like '% Box %' or street like 'Box %'



select * from #temptable