SQL Stored Procedure HELP with YEAR

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

SQL Stored Procedure HELP with YEAR

Author

Topic

TashiDuks
Starting Member

3 Posts

Posted - 2014-01-07 : 03:51:51

Hi,

I am using SQL SERVER 2008 and I have following tables :

Table Name : tblParameter
Columns : FYear = stores Current Financial Year
CurrentYearYN = 0 for No, 1 for Yes
MaxCL = Maximum Casual Leave value
MinCL = Minimum Casual Leave Value

I need a stored procedure which will store next financial year in new row and set CurrentYearYN column value of previous year to 0 and set new financial year to 1 and copy the rest column value. After the stored procedure is run the table should have following value for example:

Let say I am setting financial year 2014. After running stored procedure the table should look like this:

FYear|CurrentYearYN|MaxCL|MinCL
------------------------------
2012 |0 |10 |0
------------------------------
2013 |0 |10 |0
------------------------------
2014 |1 |10 |0

when ever I run the store procedure it should keep on adding new financial year and change the CurrentYearYN of previous year to 0 and set CurrentYearYN of new year to 1 and copy the rest.

Sorry for the poor English. Hope the concept which I am looking for is clear.

Need Help.

Thanks

TashiDuks

Learn till you die...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-01-07 : 06:27:02

something like this


CREATE PROC SetNewFY
AS

UPDATE t
SET CurrentYearYN = 0
FROM Table t
WHERE CurrentYearYN = 1


INSERT Table
SELECT TOP 1 FYear + 1,
1,
MaxCL,
MinCL
FROM Table
ORDER BY Fyear DESC
GO

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

TashiDuks
Starting Member

3 Posts

Posted - 2014-01-08 : 02:02:22

Hi,

Thanks its working. I need some more additional features. For example:

Lets say the Current Year is Opened i.e. 2014. Can we add the validation if the current year is already opened. Since if there is not validation then whenever the Stored Procedure is run it keeps on adding new year.

If yes then HOW? Please.

Thanks

Learn till you die...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-01-08 : 06:44:10

Add a check as below


CREATE PROC SetNewFY
AS
IF NOT EXISTS (SELECT 1
FROM Table
WHERE FYear = YEAR(GETDATE())
AND CurrentYearYN = 1
)
BEGIN
UPDATE t
SET CurrentYearYN = 0
FROM Table t
WHERE CurrentYearYN = 1


INSERT Table
SELECT TOP 1 FYear + 1,
1,
MaxCL,
MinCL
FROM Table
ORDER BY Fyear DESC
END
GO

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources