July 20, 2011 at 7:52 am
The daycare where our kids go recently lost the husband/wife couple that owned and operated the center in a tragic plane crash. The husband was the IT guy. The electronic check-in/check-out system used by parents to sign the kids in and out, as well as by the staff for punch-in and out feeds a SQL Server 2008 database (yeah!). I'm helping them get the data out for payroll right now. Believe it or not, they basically are running queries from SSMS and cuting/pasting into Excel.
The query they need currently returns results like the following. The "datAdded" is the date/time of the punch-in/out event and the "strAction" is "I" for punch-in, "O" for punch-out.
strLastName strFirstName datAdded strAction
Apple Alison 2011-07-01 08:30:00 I
Apple Alison 2011-07-01 11:45:00 O
Apple Alison 2011-07-01 12:17:00 I
Apple Alison 2011-07-01 17:17:00 O
Apple Alison 2011-07-02 08:37:00 I
Apple Alison 2011-07-02 11:43:00 O
Apple Alison 2011-07-02 12:14:00 I
Apple Alison 2011-07-02 17:15:00 O
Banana Brenda 2011-07-01 08:00:00 I
Banana Brenda 2011-07-01 16:32:00 O
Banana Brenda 2011-07-02 07:57:00 I
Banana Brenda 2011-07-02 16:31:00 O
Cherry Christy 2011-07-01 07:30:00 I
Cherry Christy 2011-07-01 09:35:00 I
Cherry Christy 2011-07-01 11:02:00 O
Cherry Christy 2011-07-01 12:02:00 I
Cherry Christy 2011-07-01 16:33:00 O
Grape Glenda 2011-07-01 09:00:00 I
Grape Glenda 2011-07-01 12:02:00 O
Grape Glenda 2011-07-01 12:58:00 I
Orange Olivia 2011-07-01 07:58:00 I
Orange Olivia 2011-07-01 11:58:00 I
Orange Olivia 2011-07-01 16:33:00 O
There are some issues to work around. As you can see Alison Apple had good punch-in/out actions. There is an "O" for every "I" event; one "I/O" for the morning with a break for lunch then another "I/O" for the afternoon. Brenda Banana also has good punch-in/out actions, but there's just one for the day (no lunch break). However, the rest of the staff have issues. Christy Cherry has an "I" at 7:30am and another "I" at 9:35am. This happens if the staff leaves the building for any reason without punching out. Happens a LOT. They have to punch-in in order to open the electronic lock that lets them into the building. Glenda Grape has a good punch-in/out for the morning, but she punched back in after lunch (12:58pm) but forgot to punch-out (happens occasionally). Likewise Olivia Orange forgot to punch-out at lunchtime, resulting in two consecutive "I" events.
I have a couple questions. I know I can handle a lot of this if I were to write an app to work with this data, but that won't happen until way down the road (if ever).
First... is there any easy way to highlight the consecutive "I" or consecutive "O" events using a simple SQL query? I'm sure there is a cursor-based solution that would work, but I'd like to avoid that.
Second... the goal is to get the output like shown below, with the punch-in/punch-out times as one row and an hours worked calculation.
strLastName strFirstName datPunchIn datPunchOut decHoursWorked
Apple Alison 2011-07-01 08:30:00 2011-07-01 11:45:00 (calc)
Apple Alison 2011-07-01 12:17:00 2011-07-01 17:17:00 (calc)
Apple Alison 2011-07-02 08:37:00 2011-07-02 11:43:00 (calc)
Apple Alison 2011-07-02 12:14:00 2011-07-02 17:15:00 (calc)
Banana Brenda 2011-07-01 08:00:00 2011-07-01 16:32:00 (calc)
Banana Brenda 2011-07-02 07:57:00 2011-07-02 16:31:00 (calc)
Cherry Christy 2011-07-01 07:30:00 NULL
Cherry Christy 2011-07-01 09:35:00 2011-07-01 11:02:00 (calc)
Cherry Christy 2011-07-01 12:02:00 2011-07-01 16:33:00 (calc)
Grape Glenda 2011-07-01 09:00:00 2011-07-01 12:02:00 (calc)
Grape Glenda 2011-07-01 12:58:00 NULL
Orange Olivia 2011-07-01 07:58:00 NULL
Orange Olivia 2011-07-01 11:58:00 2011-07-01 16:33:00 (calc)
A format like this would make it much easier to find the records that need to be reconciled. This will also cut-and-paste into their Excel worksheet much better since Excel remains their short-term solution.
I'm sure I can figure this out over time, but due to the tragic circumstances, time is of the essence -- they need to be able to pay their staff pronto.
Any assistance on this is greatly appreciated.... Steve
July 20, 2011 at 9:01 am
This gets you the second requirement. Sure it could also be used to back into the first. Best of luck with this.
DECLARE @Tmp TABLE (
strLastName VARCHAR(50)
, strFirstName VARCHAR(50)
, datAdded DATETIME
, strAction CHAR(1)
)
INSERT INTO @Tmp VALUES ('Cherry','Christy','2011-07-01 07:30:00','I');
INSERT INTO @Tmp VALUES ('Cherry','Christy','2011-07-01 09:35:00','I');
INSERT INTO @Tmp VALUES ('Cherry','Christy','2011-07-01 11:02:00','O');
INSERT INTO @Tmp VALUES ('Cherry','Christy','2011-07-01 12:02:00','I');
INSERT INTO @Tmp VALUES ('Cherry','Christy','2011-07-01 16:33:00','O');
WITH tmp_cte (strLastName, strFirstName, datAdded, strAction, RowRank)
AS (
SELECT strLastName, strFirstName, datAdded, strAction, RANK() OVER (PARTITION BY strLastName ORDER BY datAdded)
FROM @Tmp
)
SELECT t1.strLastName
, t1.strFirstName
, t1.datAdded AS datPunchIn
, t2.datAdded AS datPunchOut
, DATEDIFF(HH,t1.datAdded,t2.datAdded) AS decHoursWorked
FROM tmp_cte t1
LEFT OUTER JOIN tmp_cte t2 ON t1.strLastName = t2.strLastName AND t2.RowRank = t1.RowRank + 1 AND t2.strAction = 'O'
WHERE t1.strAction = 'I';
_____________________________________________________________________
- Nate
July 20, 2011 at 1:10 pm
Here's a shot at your first requirement. For a high-volume table I'd probably use a cursor, or even let the calling application highlight the consecutive rows. It would be a simple comparison in Excel for instance.
declare @sample table (strLastName varchar(10), strFirstName varchar(10), datAdded datetime, strAction char(1))
insert into @sample
SELECT 'Apple', 'Alison', '2011-07-01 08:30:00', 'I' UNION ALL
SELECT 'Apple', 'Alison', '2011-07-01 11:45:00', 'O' UNION ALL
SELECT 'Apple', 'Alison', '2011-07-01 12:17:00', 'I' UNION ALL
SELECT 'Apple', 'Alison', '2011-07-01 17:17:00', 'O' UNION ALL
SELECT 'Apple', 'Alison', '2011-07-02 08:37:00', 'I' UNION ALL
SELECT 'Apple', 'Alison', '2011-07-02 11:43:00', 'O' UNION ALL
SELECT 'Apple', 'Alison', '2011-07-02 12:14:00', 'I' UNION ALL
SELECT 'Apple', 'Alison', '2011-07-02 17:15:00', 'O' UNION ALL
SELECT 'Banana', 'Brenda', '2011-07-01 08:00:00', 'I' UNION ALL
SELECT 'Banana', 'Brenda', '2011-07-01 16:32:00', 'O' UNION ALL
SELECT 'Banana', 'Brenda', '2011-07-02 07:57:00', 'I' UNION ALL
SELECT 'Banana', 'Brenda', '2011-07-02 16:31:00', 'O' UNION ALL
SELECT 'Cherry', 'Christy', '2011-07-01 07:30:00', 'I' UNION ALL
SELECT 'Cherry', 'Christy', '2011-07-01 09:35:00', 'I' UNION ALL
SELECT 'Cherry', 'Christy', '2011-07-01 11:02:00', 'O' UNION ALL
SELECT 'Cherry', 'Christy', '2011-07-01 12:02:00', 'I' UNION ALL
SELECT 'Cherry', 'Christy', '2011-07-01 16:33:00', 'O' UNION ALL
SELECT 'Grape', 'Glenda', '2011-07-01 09:00:00', 'I' UNION ALL
SELECT 'Grape', 'Glenda', '2011-07-01 12:02:00', 'O' UNION ALL
SELECT 'Grape', 'Glenda', '2011-07-01 12:58:00', 'I' UNION ALL
SELECT 'Orange', 'Olivia', '2011-07-01 07:58:00', 'I' UNION ALL
SELECT 'Orange', 'Olivia', '2011-07-01 11:58:00', 'I' UNION ALL
SELECT 'Orange', 'Olivia', '2011-07-01 16:33:00', 'O'
select *,(select case when s1.strAction = (select top 1 strAction
from @sample s2
where s2.datAdded < s1.datAdded
order by s2.datAdded desc) then '*'
--when s1.strAction = (select top 1 strAction
-- from @sample s2
-- where s2.datAdded > s1.datAdded
-- order by s2.datAdded) then '*'
else '' end) as consecutive
from @sample s1
order by datAdded
P.S. An index on [datAdded] which also included [strAction] would do a world of good towards the performance of the query above.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
July 20, 2011 at 1:55 pm
Thanks guys. These seem to be working just fine for me. That saves me a lot of trial and error. I really need to delve into CTEs one of these days...
Steve
July 20, 2011 at 2:16 pm
You're welcome.
CTEs are just like views, except you define them for the duration of a single query.
Alternatively, think of them as subqueries that you read from top to bottom, instead of inside-out.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
July 21, 2011 at 3:09 am
Try this - IN and appropriate OUT on the same row:
;WITH SequencedData AS (
SELECT *,
PersonID = DENSE_RANK() OVER(ORDER BY strLastName, strFirstName),
rn = ROW_NUMBER() OVER (ORDER BY strLastName, strFirstName, datAdded)
FROM #Logger),
Calculator AS (
SELECT tr.*,
ClockIn = CASE WHEN tr.strAction = 'I' THEN tr.datAdded ELSE NULL END,
ClockOut = CASE WHEN tr.strAction = 'I' AND nr.strAction = 'O' AND tr.PersonID = nr.PersonID
THEN nr.datAdded ELSE NULL END
FROM SequencedData tr
INNER JOIN SequencedData nr ON nr.rn = tr.rn+1
WHERE tr.rn = 1
UNION ALL
SELECT tr.*,
ClockIn = CASE WHEN tr.strAction = 'I' THEN tr.datAdded ELSE NULL END,
ClockOut = CASE WHEN tr.strAction = 'I' AND nr.strAction = 'O' AND tr.PersonID = nr.PersonID
THEN nr.datAdded ELSE NULL END
FROM Calculator lr
INNER JOIN SequencedData tr ON tr.rn = lr.rn+1
INNER JOIN SequencedData nr ON nr.rn = lr.rn+2
)
SELECT *
FROM Calculator
WHERE strAction = 'I'
ORDER BY PersonID, rn
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply