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
 General SQL Server Forums
 New to SQL Server Programming
 Row Data in One Row with Logic

Author  Topic 

asif372
Posting Yak Master

100 Posts

Posted - 2013-04-19 : 15:37:16
I am Working On Attendance System my Data is like this
ID Describing EmployeeID CheckTime Data describe the timings and Type Describing its type of IN or Out.

ID--------CheckTime---------------Type
11--------2013-01-01 09:00:00-----I
11--------2013-01-01 09:10:00-----I
11--------2013-01-01 09:11:00-----I
11--------2013-01-01 12:00:00-----O
11--------2013-01-01 12:02:00-----O
11--------2013-01-01 14:15:00-----I
11--------2013-01-01 14:45:00-----O
11--------2013-01-01 15:00:00-----I
11--------2013-01-01 15:03:00-----I
11--------2013-01-01 18:00:00-----O
11--------2013-01-01 18:00:00-----O
11--------2013-01-02 09:10:00-----I
11--------2013-01-02 09:11:00-----I
11--------2013-01-02 18:00:00-----O
11--------2013-01-02 18:05:00-----O

Basically i want first in second out third in fourth out and so on Concept like this

ID----DATE---------TimeIN-----TimeOut---
11--2013-01-01----09:00:00---12:02:00---
11--2013-01-01----14:15:00---14:45:00---
11--2013-01-01----15:00:00---18:00:00---
11--2013-01-02----09:10:00---18:05:00---

how can it be possible
Thanks in Advance

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-04-19 : 17:56:23
quote:
Originally posted by asif372

I am Working On Attendance System my Data is like this
ID Describing EmployeeID CheckTime Data describe the timings and Type Describing its type of IN or Out.

ID--------CheckTime---------------Type
11--------2013-01-01 09:00:00-----I
11--------2013-01-01 09:10:00-----I
11--------2013-01-01 09:11:00-----I
11--------2013-01-01 12:00:00-----O
11--------2013-01-01 12:02:00-----O
11--------2013-01-01 14:15:00-----I
11--------2013-01-01 14:45:00-----O
11--------2013-01-01 15:00:00-----I
11--------2013-01-01 15:03:00-----I
11--------2013-01-01 18:00:00-----O
11--------2013-01-01 18:00:00-----O
11--------2013-01-02 09:10:00-----I
11--------2013-01-02 09:11:00-----I
11--------2013-01-02 18:00:00-----O
11--------2013-01-02 18:05:00-----O

Basically i want first in second out third in fourth out and so on Concept like this

ID----DATE---------TimeIN-----TimeOut---
11--2013-01-01----09:00:00---12:02:00---
11--2013-01-01----14:15:00---14:45:00---
11--2013-01-01----15:00:00---18:00:00---
11--2013-01-02----09:10:00---18:05:00---

how can it be possible
Thanks in Advance


I am sure there is a simpler way to do this than what I am showing below (which Visakh will post soon )
CREATE TABLE #tmp (id INT, checktime DATETIME, TYPE CHAR(1));
INSERT INTO #tmp VALUES
('11','2013-01-01 09:00:00','I'),
('11','2013-01-01 09:10:00','I'),
('11','2013-01-01 09:11:00','I'),
('11','2013-01-01 12:00:00','O'),
('11','2013-01-01 12:02:00','O'),
('11','2013-01-01 14:15:00','I'),
('11','2013-01-01 14:45:00','O'),
('11','2013-01-01 15:00:00','I'),
('11','2013-01-01 15:03:00','I'),
('11','2013-01-01 18:00:00','O'),
('11','2013-01-01 18:00:00','O'),
('11','2013-01-02 09:10:00','I'),
('11','2013-01-02 09:11:00','I'),
('11','2013-01-02 18:00:00','O'),
('11','2013-01-02 18:05:00','O')


;WITH cte AS
(
SELECT
*,
ROW_NUMBER() OVER (PARTITION BY id ORDER BY checktime) AS N,
ROW_NUMBER() OVER (PARTITION BY id ORDER BY checktime DESC ) AS N2
FROM
#tmp
),
Ins AS
(
SELECT c1.*, ROW_NUMBER() OVER (PARTITION BY c1.id ORDER BY c1.checktime) AS nn FROM cte c1
LEFT JOIN cte c2 ON (c1.n=c2.n+1 AND c1.id = c2.id AND c1.type = 'I' AND c2.type = 'O')
WHERE c1.n = 1 OR c2.n IS NOT NULL

),
Outs AS
(
SELECT c1.*, ROW_NUMBER() OVER (PARTITION BY c1.id ORDER BY c1.checktime) AS nn FROM cte c1
LEFT JOIN cte c2 ON (c1.n=c2.n-1 AND c1.id = c2.id AND c1.type = 'O' AND c2.type = 'I')
WHERE c1.n2 = 1 OR c2.n IS NOT NULL
)
SELECT
i.id,
CAST(i.checktime AS DATE) AS Date,
CAST(i.checktime AS TIME) AS TimeIN,
CAST(o.checktime AS TIME) AS TIMEOUT
FROM
Ins i
LEFT JOIN Outs o ON i.nn = o.nn

DROP TABLE #tmp
Go to Top of Page

asif372
Posting Yak Master

100 Posts

Posted - 2013-04-20 : 02:40:47
Thanks for your Responce
it really works up to the point but this procedure is quite Leanthy kindly suggest any Simple way for this work
thanks
Go to Top of Page

asif372
Posting Yak Master

100 Posts

Posted - 2013-04-20 : 05:00:28
my data is Like this

ID------CheckTime------------------Type
9000----2013-01-09 09:00:00.000-----I
9000----2013-01-09 09:01:00.000-----O
9000----2013-01-09 09:10:00.000-----O
9000----2013-01-09 10:00:00.000-----I
9000----2013-01-09 10:01:00.000-----I
9000----2013-01-09 12:00:00.000-----O
9000----2013-01-09 13:00:00.000-----I
9000----2013-01-09 13:01:00.000-----I
9000----2013-01-09 13:01:00.000-----I
9000----2013-01-09 15:00:00.000-----O
9000----2013-01-09 15:01:00.000-----O


i have Query like this

SELECT
T .ID,
DATEADD(dd, 0, DATEDIFF(dd, 0, T .CheckTime)) Date,
T .CheckTime AS TimeIn,
(SELECT TOP 1 CASE WHEN T1.Type = 'O' THEN CHECKTIME ELSE NULL END
FROM AttendLog T1
WHERE T1.ID = T .ID AND T1.CHECKTIME > T .CHECKTIME
ORDER BY CHECKTIME) AS TimeOut
FROM AttendLog T
WHERE
Type = 'I'

but getiing false data like this

ID------Date----------TimeIN----------TimeOut
9000----2013-01-09----09:00:00.000----09:01:00.000
9000----2013-01-09----10:00:00.000----NULL
9000----2013-01-09----10:01:00.000----12:00:00.000
9000----2013-01-09----13:00:00.000----NULL
9000----2013-01-09----13:01:00.000----15:00:00.000
9000----2013-01-09----13:01:00.000----15:00:00.000


My Required Data is like this
ID------Date----------TimeIN----------TimeOut-----
9000----2013-01-09----09:00:00.000----09:10:00.000
9000----2013-01-09----10:01:00.000----12:00:00.000
9000----2013-01-09----13:01:00.000----15:01:00.000

thanks in Advance
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-03 : 09:53:17
[code]
declare @test table
(
ID int,
CheckTime datetime,
[Type] char(1)
)
insert @test
values(9000,'2013-01-09 09:00:00.000','I'),
(9000,'2013-01-09 09:01:00.000','O'),
(9000,'2013-01-09 09:10:00.000','O'),
(9000,'2013-01-09 10:00:00.000','I'),
(9000,'2013-01-09 10:01:00.000','I'),
(9000,'2013-01-09 12:00:00.000','O'),
(9000,'2013-01-09 13:00:00.000','I'),
(9000,'2013-01-09 13:01:00.000','I'),
(9000,'2013-01-09 13:01:00.000','I'),
(9000,'2013-01-09 15:00:00.000','O'),
(9000,'2013-01-09 15:01:00.000','O')

SELECT DISTINCT t.ID,CONVERT(date,t.CheckTime) AS [Date], CONVERT(time,t.CheckTime) AS TimeIN,CONVERT(time,t2.MaxOut) AS TimeOUT
FROM @test t
CROSS APPLY (SELECT MIN(CASE WHEN Type='O' THEN CheckTime END) AS NextOut,
MIN(CASE WHEN Type='I' THEN CheckTime END) AS NextIn
FROM @test
WHERE ID = t.ID
AND CheckTime > t.CheckTime
)t1
OUTER APPLY (SELECT MAX(CheckTime) AS MaxOut
FROM @test
WHERE type='O'
AND ID = t.ID
AND CheckTime < COALESCE(NextIn,'99991231')
)t2
WHERE t.Type='I'
AND COALESCE(t1.NextIn,'99991231') > t1.NextOut


output
--------------------------------------
ID Date TimeIN TimeOUT
----------------------------------------------------------------
9000 2013-01-09 09:00:00.0000000 09:10:00.0000000
9000 2013-01-09 10:01:00.0000000 12:00:00.0000000
9000 2013-01-09 13:01:00.0000000 15:01:00.0000000

[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-05-03 : 11:04:29
quote:
Originally posted by visakh16


declare @test table
(
ID int,
CheckTime datetime,
[Type] char(1)
)
insert @test
values(9000,'2013-01-09 09:00:00.000','I'),
(9000,'2013-01-09 09:01:00.000','O'),
(9000,'2013-01-09 09:10:00.000','O'),
(9000,'2013-01-09 10:00:00.000','I'),
(9000,'2013-01-09 10:01:00.000','I'),
(9000,'2013-01-09 12:00:00.000','O'),
(9000,'2013-01-09 13:00:00.000','I'),
(9000,'2013-01-09 13:01:00.000','I'),
(9000,'2013-01-09 13:01:00.000','I'),
(9000,'2013-01-09 15:00:00.000','O'),
(9000,'2013-01-09 15:01:00.000','O')

SELECT DISTINCT t.ID,CONVERT(date,t.CheckTime) AS [Date], CONVERT(time,t.CheckTime) AS TimeIN,CONVERT(time,t2.MaxOut) AS TimeOUT
FROM @test t
CROSS APPLY (SELECT MIN(CASE WHEN Type='O' THEN CheckTime END) AS NextOut,
MIN(CASE WHEN Type='I' THEN CheckTime END) AS NextIn
FROM @test
WHERE ID = t.ID
AND CheckTime > t.CheckTime
)t1
OUTER APPLY (SELECT MAX(CheckTime) AS MaxOut
FROM @test
WHERE type='O'
AND ID = t.ID
AND CheckTime < COALESCE(NextIn,'99991231')
)t2
WHERE t.Type='I'
AND COALESCE(t1.NextIn,'99991231') > t1.NextOut


output
--------------------------------------
ID Date TimeIN TimeOUT
----------------------------------------------------------------
9000 2013-01-09 09:00:00.0000000 09:10:00.0000000
9000 2013-01-09 10:01:00.0000000 12:00:00.0000000
9000 2013-01-09 13:01:00.0000000 15:01:00.0000000



------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs


This is not quite the result OP wanted to get if I understood him/her correctly as per the original posting (but it is, per the second posting). So it looks like either he/she is not sure what is required, or there is a mistake in one or the other.

His expected results are these as per the original posting
output
--------------------------------------
ID Date TimeIN TimeOUT
----------------------------------------------------------------
9000 2013-01-09 09:00:00.0000000 09:10:00.0000000
9000 2013-01-09 10:00:00.0000000 12:00:00.0000000
9000 2013-01-09 13:00:00.0000000 15:01:00.0000000
Go to Top of Page

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2013-05-03 : 14:10:18
Alternative to James' sql:

select a.id
,a.itime
,case when max(b.checktime)>a.itime then max(b.checktime) else null end as otime
from (select a.id
,min(a.itime) as itime
,a.otime
,min(b.checktime) as itime2
from (select a.id
,a.checktime as itime
,min(b.checktime) as otime
from asif372.thetable as a
left outer join asif372.thetable as b
on b.id=a.id
and b.checktime>a.checktime
and b.type='O'
where a.type='I'
group by a.id
,a.checktime
) as a
left outer join asif372.thetable as b
on b.id=a.id
and b.checktime>a.otime
and b.type='I'
group by id
,otime
) as a
left outer join asif372.thetable as b
on b.id=a.id
and b.checktime<isnull(a.itime2,'21991231')
and b.type='O'
group by a.id
,a.itime


I would use James' sql myself as his sql is faster (mine uses 2-layered subselect, which would drain db power = bad). Just posted this sql as an alternative.
Go to Top of Page

erikhaselhofer
Starting Member

30 Posts

Posted - 2013-05-04 : 03:26:38
This uses James temporary table.

WITH cte AS
(
SELECT a.id, a.checktime AS timein, MIN(b.checktime) AS timeout
FROM #Tmp a
LEFT JOIN #Tmp b
ON a.type <> b.type
AND a.checktime < b.checktime
WHERE a.type = 'I'
GROUP BY a.id, a.checktime
)
SELECT id, MIN(timein) AS timein, timeout
FROM CTE
GROUP BY id, timeout


id timein timeout
11 2013-01-01 09:00:00.000 2013-01-01 12:00:00.000
11 2013-01-01 14:15:00.000 2013-01-01 14:45:00.000
11 2013-01-01 15:00:00.000 2013-01-01 18:00:00.000
11 2013-01-02 09:10:00.000 2013-01-02 18:00:00.000
Go to Top of Page

erikhaselhofer
Starting Member

30 Posts

Posted - 2013-05-04 : 15:47:49
I just realized something while on a bike ride. My code should probably be,

WITH cte AS
(
SELECT a.id, a.checktime AS timein, MIN(b.checktime) AS timeout
FROM #Tmp a
LEFT JOIN #Tmp b
ON a.type <> b.type
AND a.id = b.id
AND a.checktime < b.checktime
WHERE a.type = 'I'
GROUP BY a.id, a.checktime
)
SELECT id, MIN(timein) AS timein, timeout
FROM CTE
GROUP BY id, timeout

I don't think it matters in terms of the final result, but it would make for a smaller join, which is a good thing.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-06 : 02:06:23
quote:
Originally posted by James K

quote:
Originally posted by visakh16


declare @test table
(
ID int,
CheckTime datetime,
[Type] char(1)
)
insert @test
values(9000,'2013-01-09 09:00:00.000','I'),
(9000,'2013-01-09 09:01:00.000','O'),
(9000,'2013-01-09 09:10:00.000','O'),
(9000,'2013-01-09 10:00:00.000','I'),
(9000,'2013-01-09 10:01:00.000','I'),
(9000,'2013-01-09 12:00:00.000','O'),
(9000,'2013-01-09 13:00:00.000','I'),
(9000,'2013-01-09 13:01:00.000','I'),
(9000,'2013-01-09 13:01:00.000','I'),
(9000,'2013-01-09 15:00:00.000','O'),
(9000,'2013-01-09 15:01:00.000','O')

SELECT DISTINCT t.ID,CONVERT(date,t.CheckTime) AS [Date], CONVERT(time,t.CheckTime) AS TimeIN,CONVERT(time,t2.MaxOut) AS TimeOUT
FROM @test t
CROSS APPLY (SELECT MIN(CASE WHEN Type='O' THEN CheckTime END) AS NextOut,
MIN(CASE WHEN Type='I' THEN CheckTime END) AS NextIn
FROM @test
WHERE ID = t.ID
AND CheckTime > t.CheckTime
)t1
OUTER APPLY (SELECT MAX(CheckTime) AS MaxOut
FROM @test
WHERE type='O'
AND ID = t.ID
AND CheckTime < COALESCE(NextIn,'99991231')
)t2
WHERE t.Type='I'
AND COALESCE(t1.NextIn,'99991231') > t1.NextOut


output
--------------------------------------
ID Date TimeIN TimeOUT
----------------------------------------------------------------
9000 2013-01-09 09:00:00.0000000 09:10:00.0000000
9000 2013-01-09 10:01:00.0000000 12:00:00.0000000
9000 2013-01-09 13:01:00.0000000 15:01:00.0000000



------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs


This is not quite the result OP wanted to get if I understood him/her correctly as per the original posting (but it is, per the second posting). So it looks like either he/she is not sure what is required, or there is a mistake in one or the other.

His expected results are these as per the original posting
output
--------------------------------------
ID Date TimeIN TimeOUT
----------------------------------------------------------------
9000 2013-01-09 09:00:00.0000000 09:10:00.0000000
9000 2013-01-09 10:00:00.0000000 12:00:00.0000000
9000 2013-01-09 13:00:00.0000000 15:01:00.0000000



Let us wait until OP comes back with what exactly is requirement then

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

asif372
Posting Yak Master

100 Posts

Posted - 2013-05-21 : 08:33:50
thanks all of you for your Interest.
can my requirement be fullfill while using Pivot Table if yes than what will be the query because all suggested Queries are taking very much time
Thanks in Advance
Regards
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-22 : 00:34:18
please dont post over multiple threads. that will create unnecessary confusion

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
   

- Advertisement -