?
Solved

Need some assistance with A SQL SELECT problem

Posted on 2010-01-03
7
Medium Priority
?
309 Views
Last Modified: 2012-05-08
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?

Thanks!
0
Comment
Question by:DanLockwood
  • 3
  • 2
  • 2
7 Comments
 
LVL 81

Expert Comment

by:arnold
ID: 26168314
select top (1) * from [dbo].[tblTimeCard_Transaction] where startDatetime >='start of pay period' and StartDateTime <='end of range' and fkemplyee=<id of Eric> order by startDateTime asc

I am uncertain how 12/7/2009 affects the pay period on 12/16/2009 through 12/31/2009.

You can create the requisite logic to adjust the variable 'start of pay period' to meet your criteria.
I.e. when the selected range is 12/16/2009 through 12/31/2009 you really mean 12/1/2009 through 12/15/2009.

the problem gets further complicated depending on how your pay period is setup.  I.e. if I start my shift on the last day of a pay period and end it on the first day of the next pay period, does that effect the pay period on which the shift started or on which the shift ended? or do you split part of the time into the current pay period and the remainder into the next one?
 
0
 
LVL 9

Expert Comment

by:Karen
ID: 26168331
Always hard to do these things without sample data :)
I think the following will work?



Assuming Eric is fkEmployee = 123

SELECT * FROM tblTimeCard_Transaction
WHERE fkEmployee = 123
AND StartDateTime >=
(SELECT TOP 1 StartDateTime FROM tblTimeCard_Transaction WHERE fkEmployee = 123 AND StartDateTime BETWEEN '12/6/2009' AND '12/31/2009' ORDER BY StartDateTime)
AND StartDateTime <= '12/31/2009'
0
 

Author Comment

by:DanLockwood
ID: 26168371
Here's the relevant sample data:

695      9      2009-12-31 08:00:05.000      2009-12-31 21:48:45.000
693      9      2009-12-30 14:01:57.000      2009-12-30 19:32:17.000
689      9      2009-12-30 07:57:41.000      2009-12-30 13:04:35.000
684      9      2009-12-29 12:24:53.000      2009-12-29 17:23:20.000
683      9      2009-12-29 00:00:00.000      2009-12-29 11:40:01.000
677      9      2009-12-28 12:24:07.000      2009-12-28 23:59:59.000
675      9      2009-12-28 08:00:41.000      2009-12-28 11:24:54.000
668      9      2009-12-23 07:57:49.000      2009-12-23 16:06:33.000
665      9      2009-12-22 12:55:45.000      2009-12-22 17:11:31.000
661      9      2009-12-22 07:51:45.000      2009-12-22 12:03:51.000
658      9      2009-12-21 14:27:41.000      2009-12-21 17:17:20.000
650      9      2009-12-18 11:58:58.000      2009-12-18 21:55:55.000
647      9      2009-12-18 08:00:40.000      2009-12-18 10:52:22.000
646      9      2009-12-17 13:31:27.000      2009-12-17 17:35:15.000
641      9      2009-12-17 08:04:48.000      2009-12-17 12:38:02.000
638      9      2009-12-16 13:36:04.000      2009-12-16 17:29:22.000
620      9      2009-12-16 08:03:30.000      2009-12-16 12:09:02.000
616      9      2009-12-15 11:50:13.000      2009-12-15 17:35:36.000
613      9      2009-12-15 08:00:17.000      2009-12-15 10:32:33.000
610      9      2009-12-14 14:19:59.000      2009-12-14 16:30:08.000
606      9      2009-12-14 07:29:15.000      2009-12-14 13:23:07.000
634      9      2009-12-13 09:00:00.000      2009-12-13 15:30:00.000
601      9      2009-12-12 15:35:03.000      2009-12-12 18:35:54.000
597      9      2009-12-11 14:06:29.000      2009-12-11 21:18:35.000
593      9      2009-12-11 07:09:00.000      2009-12-11 13:26:45.000
592      9      2009-12-10 15:05:56.000      2009-12-10 17:18:55.000
585      9      2009-12-10 07:06:01.000      2009-12-10 14:07:41.000
580      9      2009-12-09 11:12:26.000      2009-12-09 17:30:30.000
576      9      2009-12-09 06:52:12.000      2009-12-09 10:21:09.000
574      9      2009-12-08 14:22:45.000      2009-12-08 20:49:25.000
568      9      2009-12-08 07:02:42.000      2009-12-08 13:48:58.000
633      9      2009-12-07 13:00:00.000      2009-12-07 17:30:00.000
632      9      2009-12-07 07:00:00.000      2009-12-07 12:00:00.000
631      9      2009-12-04 08:00:00.000      2009-12-04 16:00:00.000
630      9      2009-12-03 08:00:00.000      2009-12-03 16:00:00.000
629      9      2009-12-02 08:00:00.000      2009-12-02 16:00:00.000
628      9      2009-12-01 08:00:00.000      2009-12-01 16:00:00.000

Thank you both for the quick replys.  The reason that 12/7/2009 is significant in this problem is because in California an employee is entitled to overtime if they have worked more than 40 hours in a work week and/or they have worked more than 7 consecutive workdays without 24 hours off shift.  So just because a new pay period starts doesn't mean that there is a "clean overtime slate" if you know what I mean.  In this particular example Eric has been working consecutive days since 12/7/2009 so I need to know the first work day of the series of consecutive workdays immediately proceeding the pay period that i'm calculating for.  Hope that makes things more clear.

I welcome any additional feedback that you might have.  Thanks!
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 9

Expert Comment

by:Karen
ID: 26168572

Something like the following query would partially give what you want:

select ID, datediff(d, startdatetime, '2009-12-16') AS DaysPastStartDate from tblTimeCard_Transaction
where startdatetime < '2009-12-16'
and fkemployee = 9 ORDER BY StartDateTime desc

and then either programmatically, or in a stored procedure, you could move through these records until you find a gap in the days. i.e. DaysPastStartDate <> thelastvalue or DaysPastStartDate <> thelastvalue + 1.

If you want I could try and write a stored procedure which would do it?
0
 
LVL 9

Accepted Solution

by:
Karen earned 1000 total points
ID: 26168877
Does this do what you require? You could add another loop to input the employee ID values, and have the start date passed in as a parameter to the stored procedure. Hopefully this will give you something to work with anyhow.


SET NOCOUNT ON

DECLARE @PeriodStartDate       datetime
DECLARE @EmployeeID            integer
DECLARE @rec_days             integer
DECLARE @rec_startdate            datetime
DECLARE @LastDays            integer
DECLARE @LastDate            datetime
DECLARE @FoundIt            integer

SET @PeriodStartDate = '2009-12-31'
SET @EmployeeID = 9
SET @LastDays = 0
SET @LastDate = NULL
SET @FoundIt = 0

DECLARE record_cursor CURSOR READ_ONLY FOR
SELECT StartDateTime, datediff(d, StartDateTime, @PeriodStartDate) AS DaysPastStartDate FROM tblTimeCard_Transaction
WHERE StartDateTime < @PeriodStartDate
AND fkemployee = @EmployeeID ORDER BY StartDateTime DESC

OPEN record_cursor

FETCH NEXT FROM record_cursor
INTO @rec_startdate, @rec_days

WHILE @@FETCH_STATUS = 0
BEGIN
    IF @rec_days <> @LastDays AND @rec_days <> @LastDays + 1
      BEGIN
          SET @FoundIt = 1
          BREAK
      END

    SET @LastDays = @rec_days
    SET @LastDate = @rec_startdate
    FETCH NEXT FROM record_cursor
      INTO @rec_startdate, @rec_days
END

IF @FoundIt = 0
    -- Must be a new employee?
    SELECT NULL AS TheDateYouWant
ELSE
    SELECT @LastDate AS TheDateYouWant

CLOSE record_cursor
DEALLOCATE record_cursor
GO
0
 
LVL 81

Assisted Solution

by:arnold
arnold earned 1000 total points
ID: 26170418
The difficulty is setting the business rules.
The problem is how far back must you go to see whether the seven day rule applies. I.e. what if fkemployee with ID 9 has been working 12days straight since 12/2/2009?
Is the pay period a by weekly, or is it twice a month (15 and end of the month)?
Unless you have an intermediary table that keeps track of recent consecutive workdays on which you would run the modified cursor above.
i.e. on 12/4/2009, the table for 'Eric' would contain the 12/1/2009 entries, 12/2/2009 and 12/3/2009.
On 12/5/2009, this table for 'Eric' would contain the 12/1/2009, 12/2/2009, 12/3/2009 and 12/4/2009
On 12/6/2009 the courser will detect the 24 hour period and will clear this table.
note the pay period calculation should occur before the process that clears this "overtime" assist table.

I think you would need to use triggers on the main table to get the records to automatically be copied to the tracking table.

0
 

Author Closing Comment

by:DanLockwood
ID: 31674384
Thank you both for the posts.  While I didn't implement either solution directly it gave me some ideas to run with.  What I ended up doing was simply selecting the necessary rows and then sorting them in DESC order from a known date and then programatically looking for a "hole" in the sequential dates.  I then feed that "hole" back into my application so that it could begin the necessary calculations.  Thanks again.l
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question