Need some assistance with A SQL SELECT problem
Posted on 2010-01-03
So I have the following table:
CREATE TABLE [dbo].[tblTimeCard_Transaction](
[ID] [int] IDENTITY(1,1) NOT NULL,
[fkEmployee] [int] NULL,
[StartDateTime] [datetime] NULL,
[EndDateTime] [datetime] NULL,
[IPAddress] [varchar](15) NULL,
CONSTRAINT [PK_tblTimeCard_Entry] PRIMARY KEY CLUSTERED
My application adds records to this table when employees clock in and clock out for the day; i.e. timecard application. I need a query that will allow me to identify the first work date of a consecutive series of work dates for a given employee. For example if I want to run a time card report of employee 'Eric' I would specify a start and end date for a given pay period; in this example lets say 12/16/2009 thru 12/31/2009. In order to calculate overtime and the like correctly I need to determine the first date that Eric worked on or before 12/16/2009 which is consecutive with the start date of the pay period (12/16/2009).
If I have time records in the table for 12/7/2009, 12/8/2009, all the way thru 12/16/2009 and beyond AND Eric didn't work on 12/6/2009 then the correct answer to my question of "What was the first work date for Eric that affects the pay period beginning on 12/16/2009' is '12/7/2009'. I am however at a loss as to how to get this information out of a SQL query.
If it's not reasonably possible to acomplish this with SQL then can someone make a suggestion of a query in combination with some sort of 'Do....Loop' kind of loop?