USE [live_45]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[custSP_EnrolledYear]
	-- Add the parameters for the stored procedure here
	@en_year varchar(10)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	create table #temp_table(
		StudentName varchar(50),
		StuNum varchar(50),
		SchoolStatus varchar(50),
		AdEnrollID varchar(50),
		EnrollNum varchar(50),
		ProgramDescrip varchar(50),
		ExpStartDate datetime,
		LDA datetime,
		GradDate datetime,
	)

	declare @begin_date varchar(10)
	declare @end_date varchar(10)
	declare @t_en_year varchar(10) = trim(@en_year)
	declare @first_two_letters varchar(2) = left(@t_en_year,2)
	declare @txt_year varchar(4) = right(@t_en_year, 4)
	declare @int_year int = cast(@txt_year as int)

	if @first_two_letters = 'AY'
		BEGIN
			set @begin_date = cast((@int_year - 1) as varchar) + '-07-01'
			set @end_date = @txt_year + '-06-30'
		END
	else if @first_two_letters = 'FY'
		BEGIN
			set @begin_date = @txt_year + '-01-01'
			set @end_date = @txt_year + '-12-31'
		END
	else
		BEGIN 
			set @begin_date = ''
			set @end_date = ''
		END

	declare @sql varchar(5000) = ''
	if @begin_date <> '' and @end_date <> ''
		BEGIN
			delete from #temp_table
			set @sql = 'insert into #temp_table(StudentName, StuNum, SchoolStatus,  AdEnrollID, EnrollNum, ProgramDescrip, ExpStartDate, LDA, GradDate)	
			            Select StudentName, StuNum, SchoolStatus,  AdEnrollID, EnrollNum, ProgramDescrip, ExpStartDate, LDA, GradDate
			            from dbo.MSLS where LDA >= ''' +  @begin_date + ''' and ExpStartDate <= ''' + @end_date + ''''
			exec(@sql)
		END

select ProgramDescrip, StudentName, StuNum, SchoolStatus, AdEnrollID, EnrollNum, ExpStartDate, LDA, GradDate
from #temp_table
order by ProgramDescrip, StudentName, StuNum

select ProgramDescrip, count(distinct StuNum) as Number_of_Students
from #temp_table
group by ProgramDescrip

drop table #temp_table 

END