How do I link 2 records in the same table with no apparent link?

Question

How do I link 2 records in the same table with no apparent link?

craigalaniz

SSC Veteran

Points: 281
More actions
May 22, 2012 at 9:24 am

#392313

Hi All
Here is my problem. I have a table that is used for time clock punches, the IN and OUT punches do not have any way of linking the two together. The only way to see that an IN goes with an OUT is the order. Date won't work. There is a transaction id that is incremented after each punch.
Here is some sample data.
TC_EMPNODate\Time Punch Typetc_typeTC_ID
8058802012-05-20 22:41:00.000IN 1 8831916
8058802012-05-21 07:15:00.000OUT 11 8837290
8058802012-05-21 22:46:00.000IN 1 8850645
8058802012-05-22 07:15:00.000OUT 11 8855954
The only thing I can think of is to use the TC_ID to connect the two.
Any ideas would be appreciated.
Thanks so much
Craig

Viewing 12 posts - 1 through 11 (of 11 total)

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

craigalaniz (5/22/2012)
Hi All
Here is my problem. I have a table that is used for time clock punches, the IN and OUT punches do not have any way of linking the two together. The only way to see that an IN goes with an OUT is the order. Date won't work. There is a transaction id that is incremented after each punch.
Here is some sample data.
TC_EMPNODate\Time Punch Typetc_typeTC_ID
8058802012-05-20 22:41:00.000IN 1 8831916
8058802012-05-21 07:15:00.000OUT 11 8837290
8058802012-05-21 22:46:00.000IN 1 8850645
8058802012-05-22 07:15:00.000OUT 11 8855954
The only thing I can think of is to use the TC_ID to connect the two.
Any ideas would be appreciated.
Thanks so much
Craig

Please read the first article I reference below in my signature block. It will walk you through the steps on what you need to post and how to do it to get the best answers possible.

Also, for the sample data, could you add one or two more employees, and make sure the data looks like it would in your database (i.e. interleave the data).

I am pretty sure this won't be too difficult to solve.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

Also, please be sure to include the expected output based on the sample data. Right now I am not quite sure what output you are expecting from your initial post.

Lowell SSC Guru Points: 323518 More actions · Answer 3

the data presented formatted as select statements:

SELECT '805880' AS TC_EMPNO,'2012-05-20 22:41:00.000' AS DateTime,'IN' AS PunchType,'1' AS tc_type,'8831916' AS TC_ID UNION ALL

SELECT '805880','2012-05-21 07:15:00.000','OUT','11','8837290' UNION ALL

SELECT '805880','2012-05-21 22:46:00.000','IN','1','8850645' UNION ALL

SELECT '805880','2012-05-22 07:15:00.000','OUT','11','8855954'

even this is only partially helpful, because it doesn't have the datatypes(everytthings a string), as Lynn was requesting.

having the CREATE TABLE and then INSERT INTO statements are what is really going to help get you answers.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

Here is a best guess (with a little extra) without additional information.

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE FUNCTION [dbo].[itvf_FormatTimeHHMM] (

@Date1 DATETIME,

@Date2 DATETIME

)

RETURNS TABLE WITH SCHEMABINDING

AS RETURN(

SELECT CASE SIGN(ISNULL(DATEDIFF(n, @Date1, @Date2), -1)) WHEN -1 THEN '-' ELSE '' END +

CAST(ABS(ISNULL(DATEDIFF(n, @Date1, @Date2), -1)) / 60 AS VARCHAR) + ':' +

RIGHT('0' + CAST(ABS(ISNULL(DATEDIFF(n, @Date1, @Date2), -1)) % 60 AS VARCHAR),2) AS FormatTime

);

GO

WITH TestData AS (

SELECT '805880' AS TC_EMPNO,'2012-05-20 22:41:00.000' AS PunchDateTime,'IN' AS PunchType,'1' AS tc_type,'8831916' AS TC_ID UNION ALL

SELECT '805880','2012-05-21 07:15:00.000','OUT','11','8837290' UNION ALL

SELECT '805880','2012-05-21 22:46:00.000','IN','1','8850645' UNION ALL

SELECT '805880','2012-05-22 07:15:00.000','OUT','11','8855954'

),

BaseData AS (

SELECT

TC_EMPNO,

PunchDateTime,

PunchType,

tc_type,

TC_ID,

ROW_NUMBER() OVER (PARTITION BY TC_EMPNO ORDER BY TC_ID) AS RowNum,

ROW_NUMBER() OVER (PARTITION BY TC_EMPNO ORDER BY TC_ID) - CASE WHEN PunchType = 'IN' THEN 0 WHEN PunchType = 'OUT' THEN 1 END PunchPair

FROM

TestData

)

SELECT

bd1.TC_EMPNO,

bd1.PunchDateTime,

bd1.PunchType,

bd1.tc_type,

bd1.TC_ID,

bd2.PunchDateTime,

bd2.PunchType,

bd2.tc_type,

bd2.TC_ID,

ft.FormatTime

FROM

BaseData bd1

INNER JOIN BaseData bd2

ON (bd1.TC_EMPNO = bd2.TC_EMPNO

AND bd1.PunchPair = bd2.PunchPair

AND bd1.RowNum = bd2.RowNum -1)

CROSS APPLY dbo.itvf_FormatTimeHHMM(bd1.PunchDateTime,bd2.PunchDateTime) ft

;

GO

craigalaniz SSC Veteran Points: 281 More actions · Answer 5

Hi Lynn

Thanks for the tips.

Here is the table structure.

CREATE TABLE [dbo].[E_TimeCards](

[TC_ID] [bigint] IDENTITY(1,1) NOT NULL,

[TC_EMPNO] [nvarchar](9) NOT NULL,

[TC_ADATE] [datetime] NOT NULL,

[TC_TYPE] [smallint] NULL

GO

Sample Data: [TC_TYPE] = 1 (Out Punch) , [TC_TYPE] = 11 (In Punch)

SELECT '805880' AS [TC_EMPNO], '2012-05-20 22:41:00.000' AS [TC_ADATE], '1' AS [TC_TYPE] , 8831916 AS [TC_ID] UNION ALL

SELECT '805880' AS [TC_EMPNO],'2012-05-21 07:15:00.000' AS [TC_ADATE],'11' AS [TC_TYPE] ,8837290 AS [TC_ID] UNION ALL

SELECT '805880' AS [TC_EMPNO],'2012-05-21 22:46:00.000' AS [TC_ADATE],'1' AS [TC_TYPE] ,8850645 AS [TC_ID] UNION ALL

SELECT '805880' AS [TC_EMPNO],'2012-05-22 07:15:00.000' AS [TC_ADATE],'11' AS [TC_TYPE] ,8855954 AS [TC_ID] UNION ALL

SELECT '845690' AS [TC_EMPNO],'2012-05-20 07:00:00.000'AS [TC_ADATE],'1' AS [TC_TYPE] ,8841271 AS [TC_ID] UNION ALL

SELECT '845690' AS [TC_EMPNO],'2012-05-20 12:00:00.000' AS [TC_ADATE], '11' AS [TC_TYPE] ,8841272 AS [TC_ID] UNION ALL

SELECT '845690' AS [TC_EMPNO],'2012-05-21 06:53:00.000'AS [TC_ADATE],'1' AS [TC_TYPE] ,8837876 AS [TC_ID] UNION ALL

SELECT '845690' AS [TC_EMPNO],'2012-05-21 16:35:00.000'AS [TC_ADATE],'11' AS [TC_TYPE] ,8847550 AS [TC_ID] UNION ALL

SELECT '845690' AS [TC_EMPNO],'2012-05-22 06:47:00.000'AS [TC_ADATE],'1' AS [TC_TYPE] ,8856222 AS [TC_ID] UNION ALL

SELECT '845690' AS [TC_EMPNO],'2012-05-22 06:47:00.000'AS [TC_ADATE],'11' AS [TC_TYPE] ,8857328 AS [TC_ID] UNION ALL

SELECT '845750' AS [TC_EMPNO],'2012-05-21 14:56:00.000'AS [TC_ADATE],'1' AS [TC_TYPE] ,8845405 AS [TC_ID] UNION ALL

SELECT '845750' AS [TC_EMPNO],'2012-05-21 23:00:00.000'AS [TC_ADATE],'11' AS [TC_TYPE] ,8850918 AS [TC_ID]

Basically I wan the output data to look like the following.

SELECT '805880' AS [TC_EMPNO], '2012-05-20 22:41:00.000' AS [TC_ADATE], '1' AS [TC_TYPE] , 8831916 AS [TC_ID], '805880' AS [TC_EMPNO],'2012-05-21 07:15:00.000' AS [TC_ADATE],'11' AS [TC_TYPE] ,8837290 AS [TC_ID] UNION ALL

SELECT '805880' AS [TC_EMPNO],'2012-05-21 22:46:00.000' AS [TC_ADATE],'1' AS [TC_TYPE] ,8850645 AS [TC_ID], '805880' AS [TC_EMPNO],'2012-05-22 07:15:00.000' AS [TC_ADATE],'11' AS [TC_TYPE] ,8855954 AS [TC_ID] UNION ALL

SELECT '845690' AS [TC_EMPNO],'2012-05-20 07:00:00.000'AS [TC_ADATE],'1' AS [TC_TYPE] ,8841271 AS [TC_ID], '845690' AS [TC_EMPNO],'2012-05-20 12:00:00.000' AS [TC_ADATE], '11' AS [TC_TYPE] ,8841272 AS [TC_ID] UNION ALL

SELECT '845690' AS [TC_EMPNO],'2012-05-21 06:53:00.000'AS [TC_ADATE],'1' AS [TC_TYPE] ,8837876 AS [TC_ID], '845690' AS [TC_EMPNO],'2012-05-21 16:35:00.000'AS [TC_ADATE],'11' AS [TC_TYPE] ,8847550 AS [TC_ID] UNION ALL

SELECT '845690' AS [TC_EMPNO],'2012-05-22 06:47:00.000'AS [TC_ADATE],'1' AS [TC_TYPE] ,8856222 AS [TC_ID], '845690' AS [TC_EMPNO],'2012-05-22 06:47:00.000'AS [TC_ADATE],'11' AS [TC_TYPE] ,8857328 AS [TC_ID] UNION ALL

SELECT '845750' AS [TC_EMPNO],'2012-05-21 14:56:00.000'AS [TC_ADATE],'1' AS [TC_TYPE] ,8845405 AS [TC_ID], '845750' AS [TC_EMPNO],'2012-05-21 23:00:00.000'AS [TC_ADATE],'11' AS [TC_TYPE] ,8850918 AS [TC_ID]

Thanks a lot

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

Not quite what you asked for, but is this close? You can modify as needed and I kept the in-line function I added above as well.

CREATE TABLE [dbo].[E_TimeCards](

[TC_EMPNO] [nvarchar](9) NOT NULL,

[TC_ADATE] [datetime] NOT NULL,

[TC_TYPE] [smallint] NULL,

[TC_ID] [bigint] NOT NULL

)

GO

--Sample Data: [TC_TYPE] = 1 (Out Punch) , [TC_TYPE] = 11 (In Punch)

INSERT INTO dbo.E_TimeCards (

TC_EMPNO,

TC_ADATE,

TC_TYPE,

TC_ID

)

SELECT '805880' AS [TC_EMPNO], '2012-05-20 22:41:00.000' AS [TC_ADATE], '1' AS [TC_TYPE] , 8831916 AS [TC_ID] UNION ALL

SELECT '805880' AS [TC_EMPNO], '2012-05-21 07:15:00.000' AS [TC_ADATE], '11' AS [TC_TYPE] , 8837290 AS [TC_ID] UNION ALL

SELECT '805880' AS [TC_EMPNO], '2012-05-21 22:46:00.000' AS [TC_ADATE], '1' AS [TC_TYPE] , 8850645 AS [TC_ID] UNION ALL

SELECT '805880' AS [TC_EMPNO], '2012-05-22 07:15:00.000' AS [TC_ADATE], '11' AS [TC_TYPE] , 8855954 AS [TC_ID] UNION ALL

SELECT '845690' AS [TC_EMPNO], '2012-05-20 07:00:00.000' AS [TC_ADATE], '1' AS [TC_TYPE] , 8841271 AS [TC_ID] UNION ALL

SELECT '845690' AS [TC_EMPNO], '2012-05-20 12:00:00.000' AS [TC_ADATE], '11' AS [TC_TYPE] , 8841272 AS [TC_ID] UNION ALL

SELECT '845690' AS [TC_EMPNO], '2012-05-21 06:53:00.000' AS [TC_ADATE], '1' AS [TC_TYPE] , 8837876 AS [TC_ID] UNION ALL

SELECT '845690' AS [TC_EMPNO], '2012-05-21 16:35:00.000' AS [TC_ADATE], '11' AS [TC_TYPE] , 8847550 AS [TC_ID] UNION ALL

SELECT '845690' AS [TC_EMPNO], '2012-05-22 06:47:00.000' AS [TC_ADATE], '1' AS [TC_TYPE] , 8856222 AS [TC_ID] UNION ALL

SELECT '845690' AS [TC_EMPNO], '2012-05-22 06:47:00.000' AS [TC_ADATE], '11' AS [TC_TYPE] , 8857328 AS [TC_ID] UNION ALL

SELECT '845750' AS [TC_EMPNO], '2012-05-21 14:56:00.000' AS [TC_ADATE], '1' AS [TC_TYPE] , 8845405 AS [TC_ID] UNION ALL

SELECT '845750' AS [TC_EMPNO], '2012-05-21 23:00:00.000' AS [TC_ADATE], '11' AS [TC_TYPE] , 8850918 AS [TC_ID];

GO

WITH BaseData AS (

SELECT

TC_EMPNO,

TC_ADATE,

tc_type,

TC_ID,

ROW_NUMBER() OVER (PARTITION BY TC_EMPNO ORDER BY TC_ID) AS RowNum,

ROW_NUMBER() OVER (PARTITION BY TC_EMPNO ORDER BY TC_ID) - CASE WHEN TC_TYPE = 1 THEN 0 WHEN TC_TYPE = 11 THEN 1 END PunchPair

FROM

dbo.E_TimeCards

)

SELECT

bd1.TC_EMPNO,

bd1.TC_ADATE,

bd1.tc_type,

bd1.TC_ID,

bd2.TC_ADATE,

bd2.tc_type,

bd2.TC_ID,

ft.FormatTime

FROM

BaseData bd1

INNER JOIN BaseData bd2

ON (bd1.TC_EMPNO = bd2.TC_EMPNO

AND bd1.PunchPair = bd2.PunchPair

AND bd1.RowNum = bd2.RowNum -1)

CROSS APPLY dbo.itvf_FormatTimeHHMM(bd1.TC_ADATE,bd2.TC_ADATE) ft

;

GO

DROP TABLE dbo.E_TimeCards;

GO

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 7

Please note, the create and drop of the sample data table is there so that I would cleanup my Sandbox database.

craigalaniz SSC Veteran Points: 281 More actions · Answer 8

Hi Lynn

Thanks so much for your help and your tips on submitting a question. I will be sure follow this format in the future.

I noticed that you included ft.FormatTime. Is there a way to calculate the hours difference between OUT and the next IN?

Again, thank you

Craig

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 9

craigalaniz (5/22/2012)
Hi Lynn
Thanks so much for your help and your tips on submitting a question. I will be sure follow this format in the future.
I noticed that you included ft.FormatTime. Is there a way to calculate the hours difference between OUT and the next IN?
Again, thank you
Craig

Possibly, but there won't always be a next IN in the data. Also, not sure what value that would provide.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 10

Lynn Pettis (5/22/2012)
craigalaniz (5/22/2012)
Hi Lynn
Thanks so much for your help and your tips on submitting a question. I will be sure follow this format in the future.
I noticed that you included ft.FormatTime. Is there a way to calculate the hours difference between OUT and the next IN?
Again, thank you
Craig
Possibly, but there won't always be a next IN in the data. Also, not sure what value that would provide.

Here is the code: