multiple time punches remove duplicates

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

multiple time punches remove duplicates

Author

Topic

yeskay2008
Starting Member

7 Posts

Posted - 2012-05-21 : 23:30:00

I have table like this

CREATE TABLE [dbo].[punch](
[empid] [int] NOT NULL,
[puchtime] [datetime] NOT NULL,
[InOUt] [varchar](10) NOT NULL
)

data
INSERT [dbo].[punch] ([empid], [puchtime], [InOUt]) VALUES (1, CAST(0x0000A0260083D600 AS DateTime), N'IN')
INSERT [dbo].[punch] ([empid], [puchtime], [InOUt]) VALUES (1, CAST(0x0000A02600AD08E0 AS DateTime), N'OUT')
INSERT [dbo].[punch] ([empid], [puchtime], [InOUt]) VALUES (1, CAST(0x0000A02600B12790 AS DateTime), N'IN')
INSERT [dbo].[punch] ([empid], [puchtime], [InOUt]) VALUES (1, CAST(0x0000A02600B16DE0 AS DateTime), N'IN')
INSERT [dbo].[punch] ([empid], [puchtime], [InOUt]) VALUES (1, CAST(0x0000A02600D63BC0 AS DateTime), N'OUT')
INSERT [dbo].[punch] ([empid], [puchtime], [InOUt]) VALUES (1, CAST(0x0000A02600D68210 AS DateTime), N'OUT')
INSERT [dbo].[punch] ([empid], [puchtime], [InOUt]) VALUES (1, CAST(0x0000A02600E6B680 AS DateTime), N'IN')
INSERT [dbo].[punch] ([empid], [puchtime], [InOUt]) VALUES (1, CAST(0x0000A02600E6FCD0 AS DateTime), N'IN')
INSERT [dbo].[punch] ([empid], [puchtime], [InOUt]) VALUES (1, CAST(0x0000A02600E7CFC0 AS DateTime), N'IN')
INSERT [dbo].[punch] ([empid], [puchtime], [InOUt]) VALUES (1, CAST(0x0000A0260128A180 AS DateTime), N'OUT')

I need to get paired "IN" and "OUT" on each date for each employee. Each IN needs to have matching "OUT".
Cases where mutiple "IN" are there one after another latest "IN" should be used. Cases where multiple "OUT" is there one after another first "OUT" needs to be used. Using sqlserver 2010.

Thanks in advance

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-22 : 00:10:59

so what should be output for above data?

Also FYI you dont have sql server 2010 it should be sql 2008 or 2012

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-22 : 00:23:02

[code]

With Corrected_Punches
AS
(
SELECT p.*
FROM punch p
OUTER APPLY (SELECT TOP 1 InOUt
FROM punch
WHERE empid = p.empid
AND puchtime > p.puchtime
ORDER BY puchtime) p1
WHERE p.InOUt='IN'
AND p.InOUt <>COALESCE(p1.InOUt,'OUT')

UNION ALL
SELECT p.*
FROM punch p
OUTER APPLY (SELECT TOP 1 InOUt
FROM punch
WHERE empid = p.empid
AND puchtime < p.puchtime
ORDER BY puchtime DESC) p1
WHERE p.InOUt='OUT'
AND p.InOUt <>COALESCE(p1.InOUt,'IN')
)

SELECT empid,MAX(CASE WHEN InOUt='IN' THEN puchtime END) AS InTime,
MAX(CASE WHEN InOUt='OUT' THEN puchtime END) AS OutTime
FROM (SELECT ROW_NUMBER() OVER (PARTITION BY empid,InOUt ORDER BY puchtime) AS Rn,*
FROM Corrected_Punches) c1
GROUP BY empid,Rn
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

yeskay2008
Starting Member

7 Posts

Posted - 2012-05-22 : 03:15:19

Works like a charm.

Excellent Thanks for your help.

(yes as you said it is sqlserver 2012).

yeskay2008
Starting Member

7 Posts

Posted - 2012-05-22 : 22:57:54

One more validation.. can this be added to the same query.

First record on any day cannot be a "OUT". unless the previous record is a "IN" on the previous day (night shift).

Iam from oldschool and tending towards using cursors . im getting back to speed on sqlserver, just now getting to know Cross APPLy and outer apply.

Aprreciate all your help.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-05-22 : 23:23:13

quote:
Cases where mutiple "IN" are there one after another latest "IN" should be used. Cases where multiple "OUT" is there one after another first "OUT" needs to be used

So basically it is "Last IN" and "First OUT". Which means the IN and OUT should be one after another


; with cte as
(
	select	*, rn = row_number() over (partition by empid order by puchtime)
	from	punch
)
select	i.empid, i.puchtime, o.puchtime
from	cte i
	inner join cte o	on	i.empid	= o.empid
				and	i.rn 	= o.rn - 1
where	i.InOUt	= 'IN'
and	o.InOUt = 'OUT'
order by i.empid, i.rn

KH
[spoiler]Time is always against us[/spoiler]

yeskay2008
Starting Member

7 Posts

Posted - 2012-05-22 : 23:36:11

Modified data

truncate table punch;
GO
INSERT [dbo].[punch] ([empid], [puchtime], [InOUt]) VALUES (1, CAST(0x0000A0260083D600 AS DateTime), N'IN')
INSERT [dbo].[punch] ([empid], [puchtime], [InOUt]) VALUES (1, CAST(0x0000A02600AD08E0 AS DateTime), N'OUT')
INSERT [dbo].[punch] ([empid], [puchtime], [InOUt]) VALUES (1, CAST(0x0000A02600B12790 AS DateTime), N'IN')
INSERT [dbo].[punch] ([empid], [puchtime], [InOUt]) VALUES (1, CAST(0x0000A02600B16DE0 AS DateTime), N'IN')
INSERT [dbo].[punch] ([empid], [puchtime], [InOUt]) VALUES (1, CAST(0x0000A02600D63BC0 AS DateTime), N'OUT')
INSERT [dbo].[punch] ([empid], [puchtime], [InOUt]) VALUES (1, CAST(0x0000A02600D68210 AS DateTime), N'OUT')
INSERT [dbo].[punch] ([empid], [puchtime], [InOUt]) VALUES (1, CAST(0x0000A02600E6B680 AS DateTime), N'IN')
INSERT [dbo].[punch] ([empid], [puchtime], [InOUt]) VALUES (1, CAST(0x0000A02600E6FCD0 AS DateTime), N'IN')
INSERT [dbo].[punch] ([empid], [puchtime], [InOUt]) VALUES (1, CAST(0x0000A02600E7CFC0 AS DateTime), N'IN')
INSERT [dbo].[punch] ([empid], [puchtime], [InOUt]) VALUES (1, CAST(0x0000A0260128A180 AS DateTime), N'OUT')
INSERT [dbo].[punch] ([empid], [puchtime], [InOUt]) VALUES (3, CAST(0x0000A027005265C0 AS DateTime), N'OUT')
INSERT [dbo].[punch] ([empid], [puchtime], [InOUt]) VALUES (3, CAST(0x0000A02700D63BC0 AS DateTime), N'IN')
INSERT [dbo].[punch] ([empid], [puchtime], [InOUt]) VALUES (3, CAST(0x0000A0270128A180 AS DateTime), N'OUT')
INSERT [dbo].[punch] ([empid], [puchtime], [InOUt]) VALUES (1, CAST(0x0000A026016A8C80 AS DateTime), N'IN')
INSERT [dbo].[punch] ([empid], [puchtime], [InOUt]) VALUES (1, CAST(0x0000A0270041EB00 AS DateTime), N'OUT')

with the above data the query works fine with employee id 1 and not 3.

yeskay2008
Starting Member

7 Posts

Posted - 2012-05-22 : 23:40:47

Thanks khtan. Looks like it is working fine with your query.

(sorry i did not see your post, I was posting new data and you had updated in between)

Thanks again for your help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources