creating table for fiscal year

Question

creating table for fiscal year

softwareeng2010

SSC Veteran

Points: 246
More actions
May 20, 2013 at 6:10 am

#394807

hi all
i want to create table for fiscal year in sql server 2012.
the fields are :
1-ID
2-StartDate
3-EndDate
but i have some problems with ID column ,i want to set it for the first year(2013)
Id int identity(2013,1)
when i insert the first records in 2013, ID for that record is 20131, and for the second one 20132, and ...
next year it must be set to id int identity (2014,1)
how can i do that?changing values of identity column ?

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323504 More actions · Answer 1

softwareeng2010 (5/20/2013)
hi all
i want to create table for fiscal year in sql server 2012.
the fields are :
1-ID
2-StartDate
3-EndDate
but i have some problems with ID column ,i want to set it for the first year(2013)
Id int identity(2013,1)
when i insert the first records in 2013, ID for that record is 20131, and for the second one 20132, and ...
how can i do that?changing values of identity column ?

20131/20132? so, you are sticking quarters in a year table? maybe you need a quarters table RELATED to a year table instead?

for me, years are self defining, so the PK of the table should be an integer for the year: 2013 for example;

i might have the fiscal range in the table, like you plan,\.

next year it must be set to id int identity (2014,1)

you don't HAVE to have an identity,a dn if you doo, you cna isnert any value by using SET IDENTITY_INSERT [TalbeName] ON

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

geoff5 Hall of Fame Points: 3560 More actions · Answer 2

Be aware that sometimes fiscal years can shift. A company may decide to move the first day of the fiscal year from January 1 to June 1. Or they may shift from June to October. My point is that it is theoretically possible to have two fiscal years on record that start in the same calendar year, due to such shifts. So an identity column that equals a calendar year may not be the best choice for a fiscal year table. I would recommend a standard surrogate key that does not necessarily carry meaning in itself. Instead you could add a calculated column based on YEAR(StartDate) to give a shorthand reference to the fiscal year value. That would provide the desired value without having to INSERT two values into the table each time you create a new fiscal year.

ssimmons 2102 SSCommitted Points: 1634 More actions · Answer 3

an identity field is not what you want to use. What we have created is the following

columns for the following -

Date YYYYMMDD - type int ex 20130521

Date - type datetime ex 05/21/2013

Calendar YYYYMM - type int ex 201305

Calendar YYYY - type int ex 2013

Fiscal YYYYMM - type int ex 201309

Fiscal YYYY - type int ex 2013

You could also add columns for Quarters

all of are fields are type int, makes it easy to do math

We also have fields if the date is a work day (either a 1 or 0) then you can easily figure out how many work days in MTD or YTD calculations, etc

We more fields, but this is the basic concept.