Numbering Records? - SQL Server Forums

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 2000 Forums

SQL Server Development (2000)

Numbering Records?

Author

Topic

phoenix22
Starting Member

20 Posts

Posted - 2006-02-12 : 21:19:55

I have a DATE_VALUE table containing dates, and in particular, a text column containing a pay period (e.g. 2/5/2006 - 2/18/2006)and fiscal year (e.g. 2006) which may or may not correspond to the pay period year.

The problem is that I need to write a query that numbers the pay periods. So the very first pay period in the 2006 fiscal year(which is usually in June or July) would be 1, the next would be 2, etc. Any ideas/suggestions on how I could do this?

Thanks for your time

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-02-12 : 21:24:41

post your table structure, sample data and expected result. Refer to here

----------------------------------
'KH'
everything that has a beginning has an end

phoenix22
Starting Member

20 Posts

Posted - 2006-02-12 : 22:33:18

Table structure/brief sample data:

PAY_PERIOD FISCAL_YEAR
6/1/1990 - 6/14/1990 1989
6/15/1990 - 6/19/1990 1990
6/16/1991 - 6/30/1991 1990
7/1/1991 - 7/14/1991 1991

Desired Result:
PAY_PERIOD_NUM PAY_PERIOD FISCAL_YEAR
52 6/1/1990 - 6/14/1990 1989
1 6/15/1990 - 6/19/1990 1990
52 6/16/1991 - 6/30/1991 1990
1 7/1/1991 - 7/14/1991 1991

Please note that pay period nums 2 to 51 will be all the dates in between. Thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-02-12 : 22:40:30

>> So the very first pay period in the 2006 fiscal year(which is usually in June or July) would be 1, the next would be 2, etc
So how do you determine which period is 1 which period is 2, which is 52 ?

----------------------------------
'KH'
everything that has a beginning has an end

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-02-12 : 23:09:06

quote:
I have a DATE_VALUE table containing dates, and in particular, a text column containing a pay period (e.g. 2/5/2006 - 2/18/2006)and fiscal year (e.g. 2006) which may or may not correspond to the pay period year.

You should use 2 datetime columns to store the pay period rather than in a text / varchar column.


pay_period_start    datetime,
pay_period_end      datetime

----------------------------------
'KH'
everything that has a beginning has an end

phoenix22
Starting Member

20 Posts

Posted - 2006-02-12 : 23:36:17

The first pay period is already determined by the organization's pay roll calendar (not the same every day). Database wise, it's the first date with the new fiscal year.

I agree that it would be easier if the pay period were dates, but unfortunately that's the table structure that I have to work with.

Thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-02-12 : 23:43:07

>> The first pay period is already determined by the organization's pay roll calendar (not the same every day)
every day or every month / year ?

So there should be another table that some info that will determine 6/1/1990 - 6/14/1990 1989 = Pay Period 52, right ?

----------------------------------
'KH'
everything that has a beginning has an end

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2006-02-13 : 00:55:33

would this help
http://www.sqlteam.com/item.asp?ItemID=1491

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources