SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Row Data in One Row with Logic
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

asif372
Posting Yak Master

Pakistan
100 Posts

Posted - 04/19/2013 :  15:37:16  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3719 Posts

Posted - 04/19/2013 :  17:56:23  Show Profile  Reply with Quote
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

Pakistan
100 Posts

Posted - 04/20/2013 :  02:40:47  Show Profile  Reply with Quote
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

Edited by - asif372 on 04/20/2013 04:38:48
Go to Top of Page

asif372
Posting Yak Master

Pakistan
100 Posts

Posted - 04/20/2013 :  05:00:28  Show Profile  Reply with Quote
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

India
52325 Posts

Posted - 05/03/2013 :  09:53:17  Show Profile  Reply with Quote

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
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3719 Posts

Posted - 05/03/2013 :  11:04:29  Show Profile  Reply with Quote
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

Edited by - James K on 05/03/2013 15:38:13
Go to Top of Page

bitsmed
Constraint Violating Yak Guru

415 Posts

Posted - 05/03/2013 :  14:10:18  Show Profile  Reply with Quote
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 - 05/04/2013 :  03:26:38  Show Profile  Reply with Quote
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 - 05/04/2013 :  15:47:49  Show Profile  Reply with Quote
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

India
52325 Posts

Posted - 05/06/2013 :  02:06:23  Show Profile  Reply with Quote
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

Pakistan
100 Posts

Posted - 05/21/2013 :  08:33:50  Show Profile  Reply with Quote
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

India
52325 Posts

Posted - 05/22/2013 :  00:34:18  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000