-1

In an effort to clarify I have added more information and removed the "simplified" code.

I have table with datetime punch information.

CREATE TABLE [dbo].[AT_Punches](
    [PunchID] [int] IDENTITY(1,1) NOT NULL,
    [Facility] [char](3) NULL,
    [person_code] [varchar](12) NULL,
    [TimeStamp] [datetime] NULL,
    [AltJob] [varchar](12) NULL,
    [INOUT] [char](3) NULL,
    [edit_user] [varchar](20) NULL,
    [edit_datetime] [datetime] NULL,
    [create_datetime] [datetime] NULL,
 CONSTRAINT [PK__AT_Punch] PRIMARY KEY CLUSTERED 
(
    [PunchID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 100) ON [PRIMARY]
) ON [PRIMARY]
GO
-- TEST DATA
INSERT INTO [dbo].[AT_Punches] ([Facility],[person_code],[TimeStamp],[AltJob],[INOUT],[edit_user],[edit_datetime],[create_datetime])
                        VALUES ('ABC','JDOE','2020-07-06 15:03:48.960','RN','In','tester',GETDATE(),GETDATE())
INSERT INTO [dbo].[AT_Punches] ([Facility],[person_code],[TimeStamp],[AltJob],[INOUT],[edit_user],[edit_datetime],[create_datetime])
                        VALUES ('ABC','JDOE','2020-07-07 03:43:38.000','RN','Out','tester',GETDATE(),GETDATE())
INSERT INTO [dbo].[AT_Punches] ([Facility],[person_code],[TimeStamp],[AltJob],[INOUT],[edit_user],[edit_datetime],[create_datetime])
                        VALUES ('XYZ','KLO','2020-07-07 06:18:08.000','RN','In','tester',GETDATE(),GETDATE())
INSERT INTO [dbo].[AT_Punches] ([Facility],[person_code],[TimeStamp],[AltJob],[INOUT],[edit_user],[edit_datetime],[create_datetime])
                        VALUES ('XYZ','JSMITH','2020-07-07 15:01:40.000','RN','In','tester',GETDATE(),GETDATE())
INSERT INTO [dbo].[AT_Punches] ([Facility],[person_code],[TimeStamp],[AltJob],[INOUT],[edit_user],[edit_datetime],[create_datetime])
                        VALUES ('XYZ','JSMITH','2020-07-08 03:43:38.000','RN','Out','tester',GETDATE(),GETDATE())
INSERT INTO [dbo].[AT_Punches] ([Facility],[person_code],[TimeStamp],[AltJob],[INOUT],[edit_user],[edit_datetime],[create_datetime])
                        VALUES ('XYZ','ABARR','2020-07-08 04:12:33.000','RN','In','tester',GETDATE(),GETDATE())
INSERT INTO [dbo].[AT_Punches] ([Facility],[person_code],[TimeStamp],[AltJob],[INOUT],[edit_user],[edit_datetime],[create_datetime])
                        VALUES ('XYZ','ABARR','2020-07-08 07:19:22.000','RN','Out','tester',GETDATE(),GETDATE())

I need to turn those records into a report that pairs up punches while making note of the "missing" punches. It would look something like this.

Facility Person              Vendor Job In               Out              Hours 
ABC      Jane Doe (JDOE)     12345  RN  7/6/2020 3:00 PM 7/7/2020 3:45 AM 12.75 
XYZ      Kim Lo (KLO)        34567  RN  7/7/2020 6:15 AM *** MISSING ***  0.00 
XYZ      Jon Smith (JSMITH)  34567  RN  7/7/2020 3:00 PM 7/8/2020 3:45 AM 12.75 
XYZ      Angela Barr (ABARR) 34567  RN  7/8/2020 4:15 AM 7/8/2020 7:15 AM 3.00 

I am currently using a cursor in an sp to work this out but it takes too long to run. To process 700ish records takes about 45 seconds. I feel it be done faster. Here is that sp.

CREATE PROCEDURE [dbo].[sp_Missing_Punch_Formatter]
@StartDate date, @EndDate date, @faccode varchar(4), @userID varchar(20)
WITH EXEC AS CALLER
AS
DECLARE @boolIN   BIT

DECLARE @PunchesCursor AS CURSOR

DECLARE @strLastStamp   DATETIME
DECLARE @strLastOut   VARCHAR (25)
DECLARE @strLastPerson   VARCHAR (12)

DECLARE @strLastRecNo   INT
DECLARE @counter   INT

DECLARE @facility   CHAR (3)
DECLARE @vendorid   VARCHAR (8)
DECLARE @person_code   VARCHAR (12)
DECLARE @altjob   VARCHAR (12)
DECLARE @Inout   CHAR (3)
DECLARE @timestamp   DATETIME
DECLARE @InPunch   VARCHAR (25)
DECLARE @OutPunch   VARCHAR (25)
DECLARE @is_round  char(1)

BEGIN
   /********************* delete temp work table data *********************/

   DELETE FROM atrax.dbo.at_punchwork
    WHERE userid = @userid AND faccode = @faccode;

   /********************* reformat and calc punches *********************/
   SET @PunchesCursor =
          CURSOR FOR
             SELECT facility,
                    vendorid,
                    person_code,
                    altjob,
                    Inout,
                    timestamp,
                    InPunch,
                    OutPunch, is_round
               FROM (SELECT p.facility,
                            a.vendorid,
                            p.person_code,
                            p.altjob,
                            p.timestamp,
                            p.INOUT,
                            isnull (format (p.timestamp, 'g'), '****')
                               AS InPunch,
                            '****' AS OutPunch, 'Y' as is_round
                       FROM atrax.dbo.AT_Punches p
                            JOIN atrax.dbo.AT_person e
                               ON     p.Facility = e.Facility
                                  AND p.person_code = e.Person_code
                            JOIN atrax.dbo.AT_agency a
                               ON e.VendorID = a.VendorID
                      WHERE p.INOUT = 'In'
                     UNION ALL
                     SELECT p.facility,
                            a.vendorid,
                            p.person_code,
                            p.altjob,
                            p.timestamp,
                            p.INOUT,
                            '****' AS InPunch,
                            isnull (format (timestamp, 'g'), '****')
                               AS OutPunch, 'Y' as is_round
                       FROM atrax.dbo.AT_Punches p
                            JOIN atrax.dbo.AT_person e
                               ON     p.Facility = e.Facility
                                  AND p.person_code = e.Person_code
                            JOIN atrax.dbo.AT_agency a
                               ON e.VendorID = a.VendorID
                      WHERE p.INOUT = 'Out') AS t
              WHERE     timestamp >= @startdate
                    AND timestamp <= dateadd(DD,1,@enddate)
                    AND facility = @faccode
             ORDER BY facility,
                      VendorID,
                      person_code,
                      timestamp;

   SET @strLastPerson = '';
   SET @strLastStamp = '';
   SET @strLastOut = '';

   SET @boolIN = 0;                                              

   SET @strLastRecNo = 0;
   SET @counter = 0;
   
-- Open PUNCHES for Processing 
   
   OPEN @PunchesCursor
   FETCH NEXT FROM @PunchesCursor
        INTO @facility,
             @vendorid,
             @person_code,
             @altjob,
             @Inout,
             @timestamp,
             @InPunch,
             @OutPunch,
             @is_round

   WHILE @@FETCH_STATUS = 0
   BEGIN
      SET @counter += 1;                                 -- Record # = counter

      -- start new record for new person if not first record in
      IF @person_code <> @strLastPerson AND @counter <> 1
         BEGIN
            -- if record already pending finish last record
            IF @boolIN = 1
               BEGIN
                  -- if record has been started then finish with it, save out data from in punch (missing)
                  BEGIN TRANSACTION;

                  UPDATE atrax.dbo.at_punchwork
                     SET timestampout = @strLastStamp, OutPunch = @strLastOut
                   WHERE     recno = @strLastRecNo
                         AND userid = @userid
                         AND faccode = @faccode;

                  COMMIT;
                  -- Reset Vars
                  SET @boolIN = 0                                      --False

                  SET @strLastStamp = ''
                  SET @strLastOut = ''
               END
         END                                                 -- end new person

      IF @Inout = 'In'
         BEGIN
            -- Process the in record
            IF @boolIN = 1
               BEGIN
                  -- if record has been started then finish with it, save out data from in punch (missing)
                  BEGIN TRANSACTION;

                  UPDATE atrax.dbo.at_punchwork
                     SET timestampout = @strLastStamp, OutPunch = @strLastOut
                   WHERE     recno = @strLastRecNo
                         AND userid = @userid
                         AND faccode = @faccode;

                  COMMIT;

                  SET @boolIN = 0                                      --False

                  SET @strLastStamp = ''
                  SET @strLastOut = ''
               END                                                   -- boolin

            -- Start a new IN for current record
            SET @boolIN = 1                                             --true

            INSERT INTO atrax.dbo.at_punchwork (recno,
                                                faccode,
                                                vendorid,
                                                person_code,
                                                WorkedJob,
                                                timestamp,
                                                Inout,
                                                InPunch,
                                                userid,
                                                is_round)
            VALUES (@Counter,
                    @faccode,
                    @vendorid,
                    @person_code,
                    @Altjob,
                    @timestamp,
                    @Inout,
                    @inpunch,
                    @userid,
                    @is_round);


            -- save out data from this record
            SET @strLastStamp = @timestamp
            SET @strLastOut = @outpunch
         END
      ELSE                                                      -- INout = Out
         BEGIN
            -- process out record

            IF @boolIN = 1
               BEGIN
                  -- Record all ready started -- Add Out part from curr record
                  UPDATE atrax.dbo.at_punchwork
                     SET timestampout = @timestamp, OutPunch = @outpunch
                   WHERE     recno = @strLastRecNo
                         AND userid = @userid
                         AND faccode = @faccode;

                  SET @strLastStamp = ''
                  SET @strLastOut = ''

                  SET @boolIN = 0
               END
            ELSE
               BEGIN
                  -- add whole out Record (no in found).
                  INSERT INTO atrax.dbo.at_punchwork (recno,
                                                      faccode,
                                                      vendorid,
                                                      person_code,
                                                      WorkedJob,
                                                      timestamp,
                                                      Inout,
                                                      InPunch,
                                                      timestampout,
                                                      OutPunch,
                                                      userid, 
                                                      is_round)
                  VALUES (@counter,
                          @faccode,
                          @vendorid,
                          @person_code,
                          @Altjob,
                          @timestamp,
                          @inout,
                          @inpunch,
                          @timestamp,
                          @outpunch,
                          @userid,
                          @is_round);

                  SET @strLastStamp = ''
                  SET @strLastOut = ''

                  SET @boolIN = 0
               END                                                  -- bool IN
         END                                                          -- INOUT

      SET @strLastRecNo = @counter;
      SET @strLastPerson = @person_code;

      FETCH NEXT FROM @PunchesCursor
           INTO @facility,
                @vendorid,
                @person_code,
                @altjob,
                @Inout,
                @timestamp,
                @InPunch,
                @OutPunch,
                @is_round
   END                                                              --end loop
END
GO

Is there a faster way to do this kind of thing, maybe without a cursor?

  • how do you match IN and OUT? – Nikita Jul 9 at 23:01
  • @Nikita order by the punchDT for a user at a location. In theory, the first record should be an "In" punch. The next an "Out" punch. It should alternate like that until a missing punch is found. – WillG Jul 9 at 23:12
2

Revised answer, given clarifications in the question

The question is basically, for a given combination of person_code and facility, when you sort the records in [timestamp] order, you want to make sure every row showing an inout value of 'in' is followed by 'out' and vice versa - and that the last row shows 'out'.

Here's the solution using lead - which is a T-SQL function to read data values from rows further down the resultset than the current row. We use this basically to check: is the next row for the same person-facility combination? If so, then does the INOUT value on the next row differ from this row? If so, then great. If not, then there's a problem. However, if the next row is for some different person-facility combination, then on the current row, is the INOUT value 'out'? If not, then they haven't clocked out yet.

select 
    *,
case
    when lead(Facility,1,'') over (order by person_code, Facility, [TimeStamp]) = Facility
        and lead(person_code,1,'') over (order by person_code, Facility, [TimeStamp]) = person_code
        and lead(INOUT,1,'') over (order by person_code, Facility, [TimeStamp]) = INOUT
        then 'boohoo - next row is the same'
    when lead(Facility,1,'') over (order by person_code, Facility, [TimeStamp]) = Facility
        and lead(person_code,1,'') over (order by person_code, Facility, [TimeStamp]) = person_code
        and lead(INOUT,1,'') over (order by person_code, Facility, [TimeStamp]) <> INOUT
        then 'hooray - next row is different'
    when 
        (
            lead(Facility,1,'') over (order by person_code, Facility, [TimeStamp]) <> Facility
            or lead(person_code,1,'') over (order by person_code, Facility, [TimeStamp]) <> person_code
        )
        and INOUT = 'In'
        then 'boohoo - Not clocked out yet...'
     else 'hooray - last row for this person-location - and they clocked out :)' end
from at_punches 
order by person_code, Facility, [TimeStamp];

Initial answer

(This answer was when I thought they simply needed to show any rows where there weren't a matching number of clock-outs per person. I'll leave the answer here although it's trivial).

Try the following.

select * from tblPunches
where
    (select count(1) from tblPunches p2 where p2.userID = tblPunches.userID and p2.punchType = 'IN')
<> (select count(1) from tblPunches p3 where p3.userID = tblPunches.userID and p2.punchType = 'OUT')

Seems to me - if there is an unequal number of "IN" and "OUT" punches for a given user, there's an issue, no?

The above contains no logic to ensure an "OUT" punch correlates to a specific "IN" punch - for example, being on the same day, being within 24 hours or whatever - but neither did your question seem to imply that requirement.

PS. As I wrote this answer you posted a clarification comment that you want to iterate through the rows based on a timestamp. The above will return all rows for a given user if that user has any mismatch between IN and OUT punches.

If you want to return just the row where IN occurs twice in a row, or OUT occurs twice in a row (when ordered by a timestamp), then see my recent answer on a similar problem. I literally posted this five minutes ago and this approach will pull out just the single rows with issues for you.

| improve this answer | |
  • I've already updated the answer - seems we are cross-posting! :D Follow the link for an approach that will return the result you want. (PS - the question wasn't really clear about that requirement initially). – youcantryreachingme Jul 9 at 23:22
  • Thanks for the info, I need to go tinker with it for a bit and learn how to apply it to my needs. I'd like to make the CTE work but I haven't used that before. – WillG Jul 9 at 23:40
  • You're welcome. I think your scenario is simpler because you just need to look back at the prior row - not keep a running total over many rows. If you can prepare SQL to create the table and populate it with reasonable data for your use cases, I think I can knock out the SQL pretty quickly ... and it will be simpler. Same principle though - use lag. Probably only one CTE and that's to union with a dummy, future-dated record so we can move off your last row of data onto the dummy row then read back one row. – youcantryreachingme Jul 10 at 0:15
  • Ok, give me a few to update the question with more details. – WillG Jul 10 at 0:18
  • Took a little longer to get the test data than expected. – WillG Jul 10 at 0:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.