ESP - Create Financial Year and Period

/*
Script to auto add finacial year and its periods if they are missing
Created By	: Steve Ling 2024/03/02

You may have to change the stating year depending 
on your finacial year and periods

*/

GO

Declare @y nvarchar(4), @yid int, @ly nvarchar(4), @ny nvarchar(4)

Set @y = '2030'

Set @ny = (select @y+1)
print @ny

Set @ly = (Select CASE WHEN @ny & 3 = 0 AND (@ny % 25 <> 0 OR @ny & 15 = 0) THEN '0229' else '0228' end)
print @ly

INSERT INTO [dbo].[orgfinancialyear]
           ([mainttime]
           ,[userid]
           ,[name]
           ,[startdate]
           ,[enddate])
     VALUES
           (getdate()
           ,'Script'
           ,@y
           ,Convert(datetime,@y+'0601 00:00:00')
           ,Convert(datetime,@ny+'0530 23:59:59')
		   )

set @yid = (select @@IDENTITY )

INSERT INTO [dbo].[orgfinancialperiod]
           ([mainttime]
           ,[userid]
           ,[endDate]
           ,[periodnumber]
           ,[name]
           ,[startdate]
           ,[financialyearID])
     VALUES
		   (GETDATE(),'Script',Convert(datetime,@y+'0628 23:59:59'),1,'June',Convert(datetime,@y+'0601 00:00:00'),@yid),
		   (GETDATE(),'Script',Convert(datetime,@y+'0802 23:59:59'),2,'July',Convert(datetime,@y+'0629 00:00:00'),@yid),
		   (GETDATE(),'Script',Convert(datetime,@y+'0830 23:59:59'),3,'August',Convert(datetime,@y+'0803 00:00:00'),@yid),
		   (GETDATE(),'Script',Convert(datetime,@y+'0927 23:59:59'),4,'September',Convert(datetime,@y+'0831 00:00:00'),@yid),
		   (GETDATE(),'Script',Convert(datetime,@y+'1101 23:59:59'),5,'October',Convert(datetime,@y+'0928 00:00:00'),@yid),
		   (GETDATE(),'Script',Convert(datetime,@y+'1129 23:59:59'),6,'November',Convert(datetime,@y+'1102 00:00:00'),@yid),
		   (GETDATE(),'Script',Convert(datetime,@ny+'0103 23:59:59'),7,'December',Convert(datetime,@y+'1130 00:00:00'),@yid),
		   (GETDATE(),'Script',Convert(datetime,@ny+'0131 23:59:59'),8,'January',Convert(datetime,@ny+'0104 00:00:00'),@yid),
		   (GETDATE(),'Script',Convert(datetime,@ny+@ly+' 23:59:59'),9,'February',Convert(datetime,@ny+'0201 00:00:00'),@yid),
		   (GETDATE(),'Script',Convert(datetime,@ny+'0328 23:59:59'),10,'March',Convert(datetime,@ny+'0301 00:00:00'),@yid),
		   (GETDATE(),'Script',Convert(datetime,@ny+'0502 23:59:59'),11,'April',Convert(datetime,@ny+'0329 00:00:00'),@yid),
		   (GETDATE(),'Script',Convert(datetime,@ny+'0530 23:59:59'),12,'May',Convert(datetime,@ny+'0503 00:00:00'),@yid)

select * from orgfinancialyear where id=@yid;select * from orgfinancialperiod where financialyearID =@yid
GO

/*
Script to auto add finacial year and its periods if they are missing
Created By	: Steve Ling 2024/03/02

You may have to change the stating year depending 
on your finacial year and periods

This one is January to December

*/

GO

Declare @y nvarchar(4), @yid int, @ly nvarchar(4)

Set @y = '2016'

Set @ly = (Select CASE WHEN @y & 3 = 0 AND (@y % 25 <> 0 OR @y & 15 = 0) THEN '0229' else '0228' end)

INSERT INTO [dbo].[orgfinancialyear]
           ([mainttime]
           ,[userid]
           ,[name]
           ,[startdate]
           ,[enddate])
     VALUES
           (getdate()
           ,'Script'
           ,@y
           ,Convert(datetime,@y+'0101 00:00:00')
           ,Convert(datetime,@y+'1231 23:59:59')
		   )

set @yid = (select @@IDENTITY )

INSERT INTO [dbo].[orgfinancialperiod]
           ([mainttime]
           ,[userid]
           ,[endDate]
           ,[periodnumber]
           ,[name]
           ,[startdate]
           ,[financialyearID])
     VALUES
           (GETDATE(),'Script',Convert(datetime,@y+'0131 23:59:59'),1,'January',Convert(datetime,@y+'0101 00:00:00'),@yid),
		   (GETDATE(),'Script',Convert(datetime,@y+@ly+' 23:59:59'),2,'February',Convert(datetime,@y+'0201 00:00:00'),@yid),
		   (GETDATE(),'Script',Convert(datetime,@y+'0331 23:59:59'),3,'March',Convert(datetime,@y+'0301 00:00:00'),@yid),
		   (GETDATE(),'Script',Convert(datetime,@y+'0430 23:59:59'),4,'April',Convert(datetime,@y+'0401 00:00:00'),@yid),
		   (GETDATE(),'Script',Convert(datetime,@y+'0531 23:59:59'),5,'May',Convert(datetime,@y+'0501 00:00:00'),@yid),
		   (GETDATE(),'Script',Convert(datetime,@y+'0630 23:59:59'),6,'June',Convert(datetime,@y+'0601 00:00:00'),@yid),
		   (GETDATE(),'Script',Convert(datetime,@y+'0731 23:59:59'),7,'July',Convert(datetime,@y+'0701 00:00:00'),@yid),
		   (GETDATE(),'Script',Convert(datetime,@y+'0831 23:59:59'),8,'August',Convert(datetime,@y+'0801 00:00:00'),@yid),
		   (GETDATE(),'Script',Convert(datetime,@y+'0930 23:59:59'),9,'September',Convert(datetime,@y+'0901 00:00:00'),@yid),
		   (GETDATE(),'Script',Convert(datetime,@y+'1031 23:59:59'),10,'October',Convert(datetime,@y+'1001 00:00:00'),@yid),
		   (GETDATE(),'Script',Convert(datetime,@y+'1130 23:59:59'),11,'November',Convert(datetime,@y+'1101 00:00:00'),@yid),
		   (GETDATE(),'Script',Convert(datetime,@y+'1231 23:59:59'),12,'December',Convert(datetime,@y+'1201 00:00:00'),@yid)

--select * from orgfinancialyear where id=@yid;select * from orgfinancialperiod where financialyearID =@yid
GO