SQL code help to get one row for each sequential entry

Question

Post reply

SQL code help to get one row for each sequential entry

sql5322

Old Hand

Points: 381

August 23, 2023 at 10:05 pm

Go to Answer

#4269510

Hi,

I am looking for a sql help to generate one row per each sequental door_area, for a given employee, based on the sample data that looks like this:

Current Output:

current_output

Desired Output:

desired_output

Here is my code to test for sample data.

CREATE TABLE dbo.emp_seq
        (
  emp_id int,
  product_code int,
  clock_in datetime,
  clock_out datetime,
  door_area varchar(25)
);

INSERT INTO dbo.emp_seq (emp_id, product_code, clock_in, clock_out, door_area)
VALUES   (101, 323, '9/25/16 7:12 PM','10/12/16 6:34 PM','A1')
       , (101, 323, '10/12/16 6:34 PM', '10/24/16 8:52 AM', 'A1')
   , (101, 323, '2/13/17 5:25 PM', '3/12/17 3:23 PM', 'B3')
   , (101, 323, '3/12/17 3:23 PM', '3/12/17 3:25 PM', 'B3')
   , (101, 323, '3/12/17 3:25 PM', '3/16/17 12:03 PM', 'B3')
   , (101, 323, '1/4/18 8:47 PM', '1/4/18 9:27 PM', 'A1')
   , (101, 323, '1/4/18 9:27 PM', '1/13/18 8:45 AM', 'A1')
   , (101, 323, '1/13/18 12:06 PM', '1/30/18 4:30 PM', 'A5')

      
SELECT *
FROM dbo.emp_seq;

Thanks for your help in advance.

Emperor100

SSCrazy

Points: 2173

August 23, 2023 at 10:49 pm

Answer

#4269520

Try this:

WITH CTE
AS
(SELECT
  emp_id
 ,product_code
 ,clock_in
 ,clock_out
 ,door_area
 ,ROW_NUMBER() OVER (PARTITION BY emp_id ORDER BY clock_in) -
  ROW_NUMBER() OVER (PARTITION BY emp_id, door_area ORDER BY clock_in) AS grp
  FROM dbo.emp_seq)
SELECT
emp_id, product_code, MIN(clock_in) AS clock_in, MAX(clock_out) AS clock_out, door_area
FROM CTE
GROUP BY emp_id, product_code, door_area, grp
ORDER BY clock_in;

=======================================================================

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 1

sql5322 wrote:

Hi,

I am looking for a sql help to generate one row per each sequental door_area, for a given employee, based on the sample data that looks like this:

Current Output:

current_output

Desired Output:

desired_output

Here is my code to test for sample data.

CREATE TABLE dbo.emp_seq
        (
  emp_id int,
  product_code int,
  clock_in datetime,
  clock_out datetime,
  door_area varchar(25)
);

INSERT INTO dbo.emp_seq (emp_id, product_code, clock_in, clock_out, door_area)
VALUES   (101, 323, '9/25/16 7:12 PM','10/12/16 6:34 PM','A1')
       , (101, 323, '10/12/16 6:34 PM', '10/24/16 8:52 AM', 'A1')
   , (101, 323, '2/13/17 5:25 PM', '3/12/17 3:23 PM', 'B3')
   , (101, 323, '3/12/17 3:23 PM', '3/12/17 3:25 PM', 'B3')
   , (101, 323, '3/12/17 3:25 PM', '3/16/17 12:03 PM', 'B3')
   , (101, 323, '1/4/18 8:47 PM', '1/4/18 9:27 PM', 'A1')
   , (101, 323, '1/4/18 9:27 PM', '1/13/18 8:45 AM', 'A1')
   , (101, 323, '1/13/18 12:06 PM', '1/30/18 4:30 PM', 'A5')

      
SELECT *
FROM dbo.emp_seq;

Thanks for your help in advance.

Can you please explain why an employee would punch out and then punch back in in the same second?
Why would an employee be punched in at the same door area for almost a month?

A large part of why I'm asking these questions is because the current posted solution seems to work but only because it's following some very strict rules which may not actually exist in real life.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

drew.allen SSC Guru Points: 76985 More actions · Answer 2

This looks like a Packing Intervals problem. Based on your expected output, I am using closed intervals. That is, both endpoints are considered to be included in the interval, so if the clock_out of one interval is the same as the clock_in of another interval, the two intervals are considered to overlap instead of being two separate intervals.

NOTE: I changed your permanent table to a temporary table. You should always create temp tables when providing sample data, so that people who want to help don't have to remember to clean up afterward.

WITH clocks AS
(
    SELECT e.emp_id
         , e.product_code
         , e.clock_in
         , e.clock_out
         , e.door_area
         , CASE WHEN e.clock_in <= LAG(e.clock_out) OVER(PARTITION BY e.emp_id, e.product_code, e.door_area ORDER BY e.clock_out) THEN 0 ELSE 1 END AS is_start
    FROM #emp_seq AS e
)
, clock_groups AS
(
    SELECT c.emp_id
         , c.product_code
         , c.clock_in
         , c.clock_out
         , c.door_area
         , SUM(c.is_start) OVER(PARTITION BY c.emp_id, c.product_code, c.door_area ORDER BY c.clock_in, c.clock_out ROWS UNBOUNDED PRECEDING) AS clock_grp
    FROM clocks AS c
)
SELECT c.emp_id
     , c.product_code
     , c.door_area
     , MIN(c.clock_in) AS clock_in
     , MAX(c.clock_out) AS clock_out
FROM clock_groups AS c
GROUP BY c.emp_id
       , c.product_code
       , c.door_area
       , c.clock_grp
ORDER BY c.emp_id, c.product_code, MIN(c.clock_in)
;

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA