Hi Everyone,
I need help.
I have this set of data:
EMPID |
Actual Time |
IN/OUT |
Count |
1 |
10:00 |
IN |
1 |
1 |
10:15 |
OUT |
2 |
1 |
12:00 |
IN |
3 |
1 |
12:30 |
OUT |
4 |
And I want this way:
EMPID |
IN |
OUT |
TotalMins |
1 |
10:00 |
10:15 |
0:15 |
1 |
12:00 |
12:30 |
0:30 |
How do I code this in sql?
Thank you in advance
hi
i tried to do this
hope it helps
drop create data ..
drop table #abc
go
create table #abc
(
EMPID int ,
ActualTime datetime ,
[IN/OUT] varchar(10),
aCount int
)
go
insert into #abc select 1,'10:00','IN', 1
insert into #abc select 1,'10:15','OUT', 2
insert into #abc select 1,'12:00','IN', 3
insert into #abc select 1,'12:30','OUT', 4
go
this SQL is long ... it can be made very short ... if you want
SQL
; WITH ctein
AS (SELECT *
FROM #abc
WHERE [in/out] = 'IN'),
cteout
AS (SELECT *
FROM #abc
WHERE [in/out] = 'OUT'),
finalcte
AS (SELECT a.empid,
a.actualtime AS [in],
Min(b.actualtime) AS [out]
FROM ctein a
JOIN cteout b
ON a.empid = b.empid
AND a.actualtime <= b.actualtime
GROUP BY a.empid,
a.actualtime)
SELECT a.empid,
Cast(a.[in] AS TIME),
Cast(a.out AS TIME),
Cast(a.out - a.[in] AS TIME)
FROM finalcte a
go
1 Like
Alternative:
,cte
as (select empid
,[actual time]
,[in/out]
,row_number() over(partition by empid
,[in/out]
order by [actual time]
)
as rn
from yourtable
)
select a.empid
,a.[actual time] as [in]
,b.[actual time] as [out]
,dateadd(minute
,datediff(minute
,a.[actual time]
,b.[actual time]
)
,cast('00:00' as time)
)
as totalmins
from cte as a
left outer join cte as b
on b.empid=a.empid
and b.[in/out]='OUT'
and b.rn=a.rn
where a.[in/out]='IN'
;
1 Like
Thank you @harishgg1 and @bitsmed for your help!
Once you've verified that no IN / OUT is missing in the data, it's very simple to get the output:
;WITH cte_sorted AS (
SELECT *, ROW_NUMBER() OVER(PARTITION BY EMPID ORDER BY ActualTime) AS row_num
FROM #abc
)
SELECT
EMPID,
CAST(MIN(ActualTime) AS time(0)) AS [IN],
CAST(MAX(ActualTime) AS time(0)) AS [OUT],
CAST(DATEADD(MINUTE, DATEDIFF(MINUTE, MIN(ActualTime), MAX(ActualTime)), 0) AS time(0)) AS TotalMins
FROM cte_sorted
GROUP BY EMPID, (row_num + 1) / 2
ORDER BY EMPID, [IN]
1 Like