two weeks payroll - 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 2005 Forums

Transact-SQL (2005)

two weeks payroll

Author

Topic

osupratt
Posting Yak Master

238 Posts

Posted - 2009-04-06 : 14:37:29

I have a database that users are entering work ticket information into. Included in this is payroll hours for day. Payroll runs every two weeks.

How can I from the date of entry know if it is in the last two weeks of payroll; I mean our weeks are from Saturday through Friday?

So how do I establish the pay weeks and then from GETDATE() know whether I'm in the period or not?

Thanks for any help.

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2009-04-06 : 14:43:39

[code]
select * from
mytable
where mydate >= dateadd(w,-2,getdate())
[/code]

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

osupratt
Posting Yak Master

238 Posts

Posted - 2009-04-06 : 15:29:56

isn't the normal week setup in the system as Sunday through Saturday? as we go through the year you could see how this would not work in my query. i'm trying to logically figure out if i'm running my report on Thursday of week number 2 of payroll how do I only calculate the hours of week number 1 of payroll (let's say March 28 start of first week and April 4 start of second week) and add in that of week 2?

thanks for your response.

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2009-04-06 : 15:50:42

In the below query, you would set your startdate to the first day of a pay period. The query will then calculate the following pay periods and check the dates provided (MYTable) to show what period it falls in.

Let me know if you need further clarification.



Declare @Startdate datetime
set @Startdate = '12/01/2008'

Declare @MyTable Table(mydate datetime)
Insert Into @MyTable(Mydate)
select '01/2/2009' Union all
select '01/4/2009' Union all
select '01/5/2009' Union all
select '01/9/2009' Union all
select '01/22/2009' Union all
select '01/25/2009' Union all
select '02/2/2009' Union all
select '02/3/2009' Union all
select '03/1/2009' Union all
select '03/21/2009' Union all
select '04/12/2009' 


;With Mycte(d) as
(
select @StartDate
Union All
Select dateadd(week,2,d)
from mycte
where d <=getdate()
) 
select a.d as PayperiodStart,dateadd(week,2,dateadd(day,-1,d)) as PayPeriodEnd,b.Mydate as Mydatetocheck from 
MYcte a
Inner Join
@mytable b
on b.Mydate >= d and b.Mydate < dateadd(week,2,d)

In your live database keep a table of the payperiods as long as they will not be changing. If they do change, then you can always use a CTE as illustrated.

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

osupratt
Posting Yak Master

238 Posts

Posted - 2009-04-07 : 09:19:04

I am somewhat new to this so yes if you could clarify a bit it would go a long way. I would rather not keep a table of start dates, but would like to programically show a start and end date and then see if the record date is in those dates.

Can this be done. CTE is that common table expression? What is going on here? Thanks again for your help.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-04-07 : 10:08:12

I think it's easier than that. If you consider that the first Saturday since sql date 0 (1/1/1900) is 5, then the first payroll period since date 0 (1/1/1900) is: date 5 through date 5 + 13 days. so:


select convert(datetime, 5), datename(weekday, 5)
select convert(datetime, 5+13), datename(weekday, 5+13)

OUTPUT:
----------------------- ------------------------------
1900-01-06 00:00:00.000 Saturday

----------------------- ------------------------------
1900-01-19 00:00:00.000 Friday

So your pay period groupings can be derived by using DATEADD(day, 5, <yourDate>) / 14. every date within a pay period will have the same value.
so you can use this WHERE clause (below) to only get the dates in the current (or previous) payperiod. (Based on Vinnie881's data)


select myDate
       ,datediff(day, 5, myDate) / 14 as PayPeriodGroup
from   @myTable

--Previous period
where  datediff(day, 5, myDate) / 14 = (datediff(day, 5, getdate()) / 14) - 1 

--current period
--where  datediff(day, 5, myDate) / 14 = (datediff(day, 5, getdate()) / 14)

Be One with the Optimizer
TG

osupratt
Posting Yak Master

238 Posts

Posted - 2009-04-07 : 14:24:31

sorry, but now i am confused.

my date column is 'Date' and my table is 'PayrollDetail'

i have never declared a variable and or set the values. how would this look with my column and table? am also unsure on how you are setting dates? randomly? sorry i'm not as versed and am really stuck. thanks.

osupratt
Posting Yak Master

238 Posts

Posted - 2009-04-07 : 17:01:22

ok. i'll have to test this more thoroughly, but here is what i have. it seems to be working correctly. i appreciate your help on this. thank you very much.

Declare @Startdate datetime
set @Startdate = '01/03/2009'

;
With Mycte(d) as
(
select @StartDate
Union All
Select dateadd(week,4,d)
from mycte
where d <=getdate()
)
select a.d as PayPeriodStart,dateadd(week,2,dateadd(day,-1,d)) as PayPeriodEnd,b.Date as PayEntryDate,
b.EmployeeNumber,b.EmployeeName,b.DailyWorkTicketNumber,

CASE WHEN b.Date BETWEEN a.d AND dateadd(week,2,dateadd(day,-1,d)) THEN ISNULL(b.TravelTime,0) ELSE 0 END AS TravelTime,
CASE WHEN b.Date BETWEEN a.d AND dateadd(week,2,dateadd(day,-1,d)) THEN ISNULL(b.WorkTimeOnJob,0) ELSE 0 END AS WorkTimeOnJob,
CASE WHEN b.Date BETWEEN a.d AND dateadd(week,2,dateadd(day,-1,d)) THEN ISNULL(b.CoTime,0) ELSE 0 END AS CoTime,
CASE WHEN b.Date BETWEEN a.d AND dateadd(week,2,dateadd(day,-1,d)) THEN ISNULL(b.Hours,0) ELSE 0 END AS Hours

from MYcte a
Inner Join
dbo.PayrollDetail b
on b.Date < dateadd(week,2,d) and b.Date >= d

osupratt
Posting Yak Master

238 Posts

Posted - 2009-04-07 : 17:44:23

trying to use this as a VIEW and i get an error because of the DECLARE.

would someone know how to put the query as a View? thanks.

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2009-04-07 : 18:00:57

show us what you are trying to do exactly copy paste in here :)

<><><><><><><><><><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2009-04-07 : 18:14:56

[code]
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET NOCOUNT ON;
GO

IF OBJECT_ID('dbo.VPayPeriod) IS NOT NULL
DROP VIEW dbo.VPayPeriod;
GO
CREATE VIEW [dbo].[VPayPeriod]
AS
/*
Object Name: VPayPeriod

* History
By Date Description
-----------------------------------------
osupratt 04/07/2009 Created

*/
-----------------------------------------
Declare @Startdate datetime
set @Startdate = '01/03/2009'

;
With Mycte(d) as
(
select @StartDate
Union All
Select dateadd(week,4,d)
from mycte
where d <=getdate()
)
select a.d as PayPeriodStart,
dateadd(week,2,dateadd(day,-1,d)) as PayPeriodEnd,
b.Date as PayEntryDate,
b.EmployeeNumber,
b.EmployeeName,
b.DailyWorkTicketNumber,
CASE WHEN b.Date BETWEEN a.d AND dateadd(week,2,dateadd(day,-1,d))
THEN ISNULL(b.TravelTime,0)
ELSE 0
END AS TravelTime,
CASE WHEN b.Date BETWEEN a.d AND dateadd(week,2,dateadd(day,-1,d))
THEN ISNULL(b.WorkTimeOnJob,0)
ELSE 0
END AS WorkTimeOnJob ,
CASE WHEN b.Date BETWEEN a.d AND dateadd(week,2,dateadd(day,-1,d))
THEN ISNULL(b.CoTime,0)
ELSE 0
END AS CoTime,
CASE WHEN b.Date BETWEEN a.d AND dateadd(week,2,dateadd(day,-1,d))
THEN ISNULL(b.Hours,0)
ELSE 0
END AS Hours
from MYcte a
Inner JOIN dbo.PayrollDetail b
on b.Date < dateadd(week,2,d) and b.Date >= d

GO
[/code]

osupratt
Posting Yak Master

238 Posts

Posted - 2009-04-07 : 18:27:33

here is what i have (hopefully this isn't too far off):

USE [MercerDailyWorkTicket]
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET NOCOUNT ON;
GO

IF OBJECT_ID([dbo].[VW_Well_Service_PayrollView]) IS NOT NULL
DROP VIEW [dbo].[VW_Well_Service_PayrollView];
GO
CREATE VIEW [dbo].[VW_Well_Service_PayrollView]
AS

Declare @Startdate datetime
set @Startdate = '01/03/2009'
GO
/****** Object: View [dbo].[VW_Well_Service_PayrollView] Script Date: 04/07/2009 15:57:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [dbo].[VW_Well_Service_PayrollView]
AS

;
With Mycte(d) as
(
select @StartDate
Union All
Select dateadd(week,4,d)
from mycte
where d <=getdate()
)
select a.d as PayPeriodStart,dateadd(week,2,dateadd(day,-1,d)) as PayPeriodEnd,b.Date as PayEntryDate,
b.EmployeeNumber,b.EmployeeName,b.DailyWorkTicketNumber,b.TableID,b.JobTitle,b.CostCenter,b.PayCategory,b.Misc_Text_Field_1 AS PayWeek,
b.Misc_Text_Field_2 AS UserName,b.Misc_Text_Field_3 AS WorkLocation,

CASE WHEN b.Date BETWEEN a.d AND dateadd(week,2,dateadd(day,-1,d)) THEN ISNULL(b.TravelTime,0) ELSE 0 END AS TravelTime,
CASE WHEN b.Date BETWEEN a.d AND dateadd(week,2,dateadd(day,-1,d)) THEN ISNULL(b.WorkTimeOnJob,0) ELSE 0 END AS WorkTimeOnJob,
CASE WHEN b.Date BETWEEN a.d AND dateadd(week,2,dateadd(day,-1,d)) THEN ISNULL(b.CoTime,0) ELSE 0 END AS CoTime,
CASE WHEN b.Date BETWEEN a.d AND dateadd(week,2,dateadd(day,-1,d)) THEN ISNULL(b.Hours,0) ELSE 0 END AS Hours

from MYcte a
Inner Join
dbo.PayrollDetail b
on b.Date < dateadd(week,2,d) and b.Date >= d

GO

gives the following errors:

Msg 4104, Level 16, State 1, Line 2
The multi-part identifier "dbo.VW_Well_Service_PayrollView" could not be bound.
Msg 156, Level 15, State 1, Procedure VW_Well_Service_PayrollView, Line 5
Incorrect syntax near the keyword 'Declare'.
Msg 102, Level 15, State 1, Procedure VW_Well_Service_PayrollView, Line 5
Incorrect syntax near ';'.
Msg 137, Level 15, State 2, Procedure VW_Well_Service_PayrollView, Line 8
Must declare the scalar variable "@StartDate".
Msg 102, Level 15, State 1, Procedure VW_Well_Service_PayrollView, Line 13
Incorrect syntax near ')'.

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2009-04-07 : 18:29:46

need to add
Declare @Startdate datetime
set @Startdate = '01/03/2009'

before
;
With Mycte(d) as

why are you doing

Declare @Startdate datetime
set @Startdate = '01/03/2009'

Will startdate always be '01/03/2009'? I would put that in the stored procedure that call this view.

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2009-04-07 : 19:48:20

[code]
IF OBJECT_ID([dbo].[VW_Well_Service_PayrollView]) IS NOT NULL
DROP VIEW [dbo].[VW_Well_Service_PayrollView];
GO

;
CREATE VIEW [dbo].[VW_Well_Service_PayrollView]
AS
With Mycte(d) as
(
select '01/03/2009'
Union All
Select dateadd(week,4,d)
from mycte
where d <=getdate()
)
select a.d as PayPeriodStart
,dateadd(week,2,dateadd(day,-1,d)) as PayPeriodEnd
,b.Date as PayEntryDate
,b.EmployeeNumber
,b.EmployeeName
,b.DailyWorkTicketNumber
,b.TableID
,b.JobTitle
,b.CostCenter
,b.PayCategory
,b.Misc_Text_Field_1 AS PayWeek
,b.Misc_Text_Field_2 AS UserName
,b.Misc_Text_Field_3 AS WorkLocation
,coalesce(b.TravelTime,0) AS TravelTime
,coalesce(b.WorkTimeOnJob,0) AS WorkTimeOnJob
,coalesce(b.CoTime,0) AS CoTime
,coalesce(b.Hours,0) AS Hours
from MYcte a
Inner Join
dbo.PayrollDetail b
on b.Date < dateadd(week,2,d) and b.Date >= d

go

/*
Select *
from
VW_Well_Service_PayrollView a
where
a.Payweek = '4/4/2009' -- You can filter accordingly
*/
[/code]

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

osupratt
Posting Yak Master

238 Posts

Posted - 2009-04-08 : 09:25:34

Still does not work. Do you see anything that would cause the errors at the end?

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET NOCOUNT ON;
GO

IF OBJECT_ID(dbo.VPayPeriod) IS NOT NULL
DROP VIEW dbo.VPayPeriod;
GO
CREATE VIEW [dbo].[VPayPeriod]
AS
/*
Object Name: VPayPeriod

* History
By Date Description
-----------------------------------------
osupratt 04/07/2009 Created

*/
-----------------------------------------
Declare @Startdate datetime
set @Startdate = '01/03/2009'

;
With Mycte(d) as
(
select @StartDate
Union All
Select dateadd(week,4,d)
from mycte
where d <=getdate()
)
select a.d as PayPeriodStart,
dateadd(week,2,dateadd(day,-1,d)) as PayPeriodEnd,
b.Date as PayEntryDate,
b.EmployeeNumber,
b.EmployeeName,
b.DailyWorkTicketNumber,
CASE WHEN b.Date BETWEEN a.d AND dateadd(week,2,dateadd(day,-1,d))
THEN ISNULL(b.TravelTime,0)
ELSE 0
END AS TravelTime,
CASE WHEN b.Date BETWEEN a.d AND dateadd(week,2,dateadd(day,-1,d))
THEN ISNULL(b.WorkTimeOnJob,0)
ELSE 0
END AS WorkTimeOnJob ,
CASE WHEN b.Date BETWEEN a.d AND dateadd(week,2,dateadd(day,-1,d))
THEN ISNULL(b.CoTime,0)
ELSE 0
END AS CoTime,
CASE WHEN b.Date BETWEEN a.d AND dateadd(week,2,dateadd(day,-1,d))
THEN ISNULL(b.Hours,0)
ELSE 0
END AS Hours
from MYcte a
Inner JOIN dbo.PayrollDetail b
on b.Date < dateadd(week,2,d) and b.Date >= d

GO

ERRORS:

Msg 4104, Level 16, State 1, Line 2
The multi-part identifier "dbo.VPayPeriod" could not be bound.
Msg 156, Level 15, State 1, Procedure VPayPeriod, Line 13
Incorrect syntax near the keyword 'Declare'.

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2009-04-08 : 10:21:20

do as Vinni suggetsed. try this

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET NOCOUNT ON;
GO

IF OBJECT_ID(dbo.VPayPeriod) IS NOT NULL
DROP VIEW dbo.VPayPeriod;
GO
CREATE VIEW [dbo].[VPayPeriod]
AS
/*
Object Name: VPayPeriod

* History
By Date Description
-----------------------------------------
osupratt 04/07/2009 Created

*/
-----------------------------------------
;
With Mycte(d) as
(
select '01/03/2009'
Union All
Select dateadd(week,4,d)
from mycte
where d <=getdate()
)
select a.d as PayPeriodStart,
dateadd(week,2,dateadd(day,-1,d)) as PayPeriodEnd,
b.Date as PayEntryDate,
b.EmployeeNumber,
b.EmployeeName,
b.DailyWorkTicketNumber,
CASE WHEN b.Date BETWEEN a.d AND dateadd(week,2,dateadd(day,-1,d))
THEN ISNULL(b.TravelTime,0)
ELSE 0
END AS TravelTime,
CASE WHEN b.Date BETWEEN a.d AND dateadd(week,2,dateadd(day,-1,d))
THEN ISNULL(b.WorkTimeOnJob,0)
ELSE 0
END AS WorkTimeOnJob ,
CASE WHEN b.Date BETWEEN a.d AND dateadd(week,2,dateadd(day,-1,d))
THEN ISNULL(b.CoTime,0)
ELSE 0
END AS CoTime,
CASE WHEN b.Date BETWEEN a.d AND dateadd(week,2,dateadd(day,-1,d))
THEN ISNULL(b.Hours,0)
ELSE 0
END AS Hours
from MYcte a
Inner JOIN dbo.PayrollDetail b
on b.Date < dateadd(week,2,d) and b.Date >= d

GO

<><><><><><><><><><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

osupratt
Posting Yak Master

238 Posts

Posted - 2009-04-08 : 10:33:16

ran and same errors.

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2009-04-08 : 10:42:49

sorry boss. try this

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET NOCOUNT ON;
GO

IF OBJECT_ID('dbo.VPayPeriod') IS NOT NULL
DROP VIEW dbo.VPayPeriod;
GO
CREATE VIEW [dbo].[VPayPeriod]
AS
/*
Object Name: VPayPeriod

* History
By Date Description
-----------------------------------------
osupratt 04/07/2009 Created

*/
-----------------------------------------

With Mycte(d) as
(
select CAST('01/03/2009' AS DATETIME)
Union All
Select dateadd(week,4,d)
from mycte
where d <=getdate()
)
select a.d as PayPeriodStart,
dateadd(week,2,dateadd(day,-1,d)) as PayPeriodEnd,
b.Date as PayEntryDate,
b.EmployeeNumber,
b.EmployeeName,
b.DailyWorkTicketNumber,
CASE WHEN b.Date BETWEEN a.d AND dateadd(week,2,dateadd(day,-1,d))
THEN ISNULL(b.TravelTime,0)
ELSE 0
END AS TravelTime,
CASE WHEN b.Date BETWEEN a.d AND dateadd(week,2,dateadd(day,-1,d))
THEN ISNULL(b.WorkTimeOnJob,0)
ELSE 0
END AS WorkTimeOnJob ,
CASE WHEN b.Date BETWEEN a.d AND dateadd(week,2,dateadd(day,-1,d))
THEN ISNULL(b.CoTime,0)
ELSE 0
END AS CoTime,
CASE WHEN b.Date BETWEEN a.d AND dateadd(week,2,dateadd(day,-1,d))
THEN ISNULL(b.Hours,0)
ELSE 0
END AS Hours
from MYcte a
Inner JOIN dbo.PayrollDetail b
on b.Date < dateadd(week,2,d) and b.Date >= d
GO

<><><><><><><><><><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2009-04-08 : 11:03:01

Also since you are joining o
on b.Date < dateadd(week,2,d) and b.Date >= d

you do not need the case statements because the will always be true, that is why I swiched them just

,coalesce(b.TravelTime,0) AS TravelTime
,coalesce(b.WorkTimeOnJob,0) AS WorkTimeOnJob
,coalesce(b.CoTime,0) AS CoTime
,coalesce(b.Hours,0) AS Hours

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

osupratt
Posting Yak Master

238 Posts

Posted - 2009-04-08 : 11:38:43

thanks... that seems to have done the trick. one question i have though is will this query build a NEW two week payroll period (start to end) every two weeks or am i in for trouble down the road?

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-04-08 : 11:46:08

The query you ended up with seems to have incorrect payPeriods. Here is the distinct list:


PayPeriodStart          PayPeriodEnd
----------------------- -----------------------
2009-01-03 00:00:00.000 2009-01-16 00:00:00.000
2009-01-31 00:00:00.000 2009-02-13 00:00:00.000
2009-02-28 00:00:00.000 2009-03-13 00:00:00.000
2009-03-28 00:00:00.000 2009-04-10 00:00:00.000
2009-04-25 00:00:00.000 2009-05-08 00:00:00.000

This will give 2 week periods (Sat-Fri). No CTE, no Case statements:


select dateadd(week, (datediff(day, 5, Date)/14)*2, 12)    as PayPeriodStart
       ,dateadd(week, (datediff(day, 5, Date)/14)*2, 25)  as PayPeriodEnd
       ,b.Date                                            as PayEntryDate
       ,b.EmployeeNumber
       ,b.EmployeeName
       ,b.DailyWorkTicketNumber
       ,isNull(b.TravelTime,0)                            as travelTime
       ,isNull(b.WorkTimeOnJob,0)                         as WorkTimeOnJob 
       ,ISNULL(b.CoTime,0)                                as CoTime
       ,ISNULL(b.Hours,0)                                 as Hours
from   PayrollDetail b

EDIT:
Removed the WHERE clause since this will be a view. You should apply the date range criteria when selecting from view.

Also shifted the period to start 1 week later so that 1/3/2009 is the start period instead of 1 week earlier.

Be One with the Optimizer
TG

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources