Group By 2 week periods overlapping months? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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)

Group By 2 week periods overlapping months?

Author

Topic

pwcphoto
Yak Posting Veteran

69 Posts

Posted - 2009-11-30 : 22:33:58

Trying to put together a time card system with payday on the first and 15th of each month. The payroll times must be submitted a few days earlier so we have two time periods to look at and total. The 11th though the 25th and 26th through the 10th. You can see it overlaps the month in the 26th to 10th.

The table is below:

CREATE TABLE [dbo].[Timecards] (
[Time_ID] [int] IDENTITY (1, 1) NOT NULL ,
[Time_Mem_ID] [int] NULL ,
[Time_Created] [datetime] NOT NULL ,
[Time_Created_2] [datetime] NULL ,
[Time_In] [datetime] NULL ,
[Time_Out] [datetime] NULL ,
[Time_IP_In] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Time_IP_Out] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL

The fields Time_In and Time_Out contain the datetime of the employee punching in and out.

I need to sum and group the time for each pay period, to summarize completed pay periods and keep running totals on current pay periods based on each Mem_ID (the employee).

Any Help would be appreciated.

Phil

-----------------------
I used to be a rocket scientist. Now I'm just a space cadet...

toddbkc
Starting Member

9 Posts

Posted - 2009-12-01 : 12:26:56

Hi pwcphoto -

Try this - create a "Calendar" table that has the following columns:
FullDate - 5/1/2009, Year Number - 2009, Month Number - 5, Day Number - 1, PayPeriod - "PeriodID". You can add other columns as well, but the purpose is to give you the ability to group and sum on the PayPeriod column. You could also use this technique to indicate whether a specific day of the year is a paid holiday with a bit column IsHoliday.

So, querying would be by joining your Timecards table to Calendar like this:

select *
from Timecards
INNER Join Calendar
ON convert(varchar(10),Time_In,101) = Calendar.FullDate

TODD

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-12-01 : 16:09:32

Something like this?

DECLARE	@Sample TABLE
	(
		dt DATETIME
	)

INSERT	@Sample
SELECT	ABS(CHECKSUM(NEWID())) % 25000 + 35000
FROM	master..spt_values
WHERE	Type = 'P'

SELECT		dt,
		CASE
			WHEN DATEPART(DAY, dt) BETWEEN 11 AND 25 THEN '2nd'
			ELSE '1st'
		END
FROM		@Sample
ORDER BY	dt

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources