SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 SQL Stored Procedure HELP with YEAR
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

TashiDuks
Starting Member

Bhutan
3 Posts

Posted - 01/07/2014 :  03:51:51  Show Profile  Reply with Quote
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...

Edited by - TashiDuks on 01/07/2014 03:54:43

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 01/07/2014 :  06:27:02  Show Profile  Reply with Quote
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
Go to Top of Page

TashiDuks
Starting Member

Bhutan
3 Posts

Posted - 01/08/2014 :  02:02:22  Show Profile  Reply with Quote
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...
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 01/08/2014 :  06:44:10  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000