Grouping days into Biweekly payperiod

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

Transact-SQL (2005)

Grouping days into Biweekly payperiod

Author

Topic

satish15385
Starting Member

17 Posts

Posted - 2011-08-18 : 19:13:36

Hello,

I have a table with Dates,yearcalendar,Datepaystart and Datepayend. The Date has to exactly insert into the Biweekly period.

I am generating the future payroll based on the recent Datepaystart and Datepayend.....so is there a way i can generate future payperiods based on current payperiod and then insert that into the exact Date.

so from date 20110722 Datepaystart has to be 2011-07-22 and datepayend : 20110804 and this has to continue till Date becomes 20110804 and from then the next payperiod has to come

Ex:

Date DatepayStart Datepayend
20110721 2011-07-08 00:00:00 2011-07-21 00:00:00
20110722 2011-07-08 00:00:00 2011-07-21 00:00:00
20110723 2011-07-08 00:00:00 2011-07-21 00:00:00
20110724 2011-07-08 00:00:00 2011-07-21 00:00:00
20110725 2011-07-08 00:00:00 2011-07-21 00:00:00
20110726 2011-07-08 00:00:00 2011-07-21 00:00:00
20110728 2011-07-08 00:00:00 2011-07-21 00:00:00
20110729 2011-07-08 00:00:00 2011-07-21 00:00:00
20110730 2011-07-08 00:00:00 2011-07-21 00:00:00
20110731 2011-07-08 00:00:00 2011-07-21 00:00:00

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-08-18 : 21:16:04

how do you determine when is the datepaystart ?

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-19 : 02:20:57

you mean this?


;With Date_CTE([Date],Start,[End])
AS
(
SELECT CAST('20110721' as datetime),CAST('2011-07-08 00:00:00' as datetime),CAST('2011-07-21 00:00:00' as datetime)
UNION ALL
SELECT DATEADD(dd,1,[Date]),DATEADD(wk,2,Start),DATEADD(wk,2,[End])
FROM Date_CTE
WHERE DATEADD(dd,1,[Date])< '20110801'
)

select * from Date_CTE
OPTION (MAXRECURSION 0)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

satish15385
Starting Member

17 Posts

Posted - 2011-08-19 : 08:58:32

Datepaystart is determined something like this

Select cast(DATEADD(DD, 1, Datepayend ) as date ) from (select top 1 b.Datepayend from dbo.Master_FairHRFuturePayrollPayperiod_Staging b order by datepayend desc) a...even here i am getting an error
I declared @Startdate smalldatetime
and then when i set @Startdate = 'Select cast(DATEADD(DD, 1, Datepayend ) as date ) from (select top 1 b.Datepayend from dbo.Master_FairHRFuturePayrollPayperiod_Staging b order by datepayend desc) a'

it throws an error saying : Conversion failed when converting date and/or time from character string.

Also @ Visakh16 : The payperiod has to be same until it returns to next cycle;i.e it shouldn't change for every day

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-08-19 : 10:30:30

it is easier for others to help you if you can
- post the schema for the related tables
- sample data
- expected result

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-19 : 10:58:39

you alone knows what the rules are
so please post the details requested by KH if you need further help

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources