Calculate a 2-week 'Pay Period'

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

Transact-SQL (2000)

Calculate a 2-week 'Pay Period'

Author

Topic

mdelgado
Posting Yak Master

141 Posts

Posted - 2002-10-08 : 15:14:04

Hello all (again...)

I have a table that contains all the shipped info for our company going back 2 years, this table is updated nightly with the previous days data. The name of this table is [SH].

I need to calculate commissions for the 'Current' pay period and send out updated rankings daily. Pay Periods are 2 weeks long and always end on Friday; for example, today is 10/9/02 so this current pay period would be 9/28/02 thru 10/11/02. The next pay period would be 10/12/02 thru 10/25/02 etc...

My problem is that I need to query the [SH] table daily with shipped totals for the current pay period like so:

Declare @start smalldatetime
Declare @end smalldatetime

set @start = ????
set @end = ????

select sum([price]) as 'Revenue' from [SH]
where convert(smalldatetime,[SHIPDATE],1) between @start and @end

I need to find a way to calculate the beginning (@start) and most recent day (@end) so that I can automate this report.

Any help would be much appreciated...

thanks.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-10-08 : 15:34:56

You should take a look here:

http://www.sqlteam.com/item.asp?ItemID=3332

That's probably a good way to generate date ranges. I would suggest though that the best way for you to do this is to create a table listing the exact begin and end dates for each pay period, something like:

CREATE TABLE PayPeriods (startDate smalldatetime NOT NULL, endDate smalldatetime NOT NULL)

And populate that table with all of the actual dates you need to report on. It's pretty economical because 10 years worth of bi-weekly data adds up to about 260 rows...not bad at all, fits on one data page nicely. Then you could include it in a query like this:

SELECT Sum(SH.price) AS Revenue FROM SH, PayPeriods P
WHERE SH.ShipDate BETWEEN P.startDate AND P.EndDate
AND GetDate() BETWEEN P.startDate AND P.EndDate

It's also better to have a table with hard dates in order to accommodate things like holidays and other possible exceptions to the rule(s).

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2002-10-08 : 15:55:32

Why don't you create a table with all the pay periods and a status column

Then you could try something like this:

SELECT TOP 1 @start = StartDate
FROM
PayPeriods
WHERE StartDate <= GetDate() And Status = 'Not Processed'
ORDER BY StartDate DESC

SET @end = DateAdd(wk,2,@start)

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2002-10-08 : 15:56:33

You beat me to it Rob.

mdelgado
Posting Yak Master

141 Posts

Posted - 2002-10-08 : 15:59:20

thanks guys....

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources