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.
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 thisID Describing EmployeeID CheckTime Data describe the timings and Type Describing its type of IN or Out.ID--------CheckTime---------------Type11--------2013-01-01 09:00:00-----I11--------2013-01-01 09:10:00-----I11--------2013-01-01 09:11:00-----I11--------2013-01-01 12:00:00-----O11--------2013-01-01 12:02:00-----O11--------2013-01-01 14:15:00-----I11--------2013-01-01 14:45:00-----O11--------2013-01-01 15:00:00-----I11--------2013-01-01 15:03:00-----I11--------2013-01-01 18:00:00-----O11--------2013-01-01 18:00:00-----O11--------2013-01-02 09:10:00-----I11--------2013-01-02 09:11:00-----I11--------2013-01-02 18:00:00-----O11--------2013-01-02 18:05:00-----OBasically i want first in second out third in fourth out and so on Concept like thisID----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 possibleThanks 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 thisID Describing EmployeeID CheckTime Data describe the timings and Type Describing its type of IN or Out.ID--------CheckTime---------------Type11--------2013-01-01 09:00:00-----I11--------2013-01-01 09:10:00-----I11--------2013-01-01 09:11:00-----I11--------2013-01-01 12:00:00-----O11--------2013-01-01 12:02:00-----O11--------2013-01-01 14:15:00-----I11--------2013-01-01 14:45:00-----O11--------2013-01-01 15:00:00-----I11--------2013-01-01 15:03:00-----I11--------2013-01-01 18:00:00-----O11--------2013-01-01 18:00:00-----O11--------2013-01-02 09:10:00-----I11--------2013-01-02 09:11:00-----I11--------2013-01-02 18:00:00-----O11--------2013-01-02 18:05:00-----OBasically i want first in second out third in fourth out and so on Concept like thisID----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 possibleThanks 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 TIMEOUTFROM Ins i LEFT JOIN Outs o ON i.nn = o.nn DROP TABLE #tmp |
|
|
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 workthanks |
|
|
asif372
Posting Yak Master
100 Posts |
Posted - 2013-04-20 : 05:00:28
|
my data is Like thisID------CheckTime------------------Type9000----2013-01-09 09:00:00.000-----I9000----2013-01-09 09:01:00.000-----O9000----2013-01-09 09:10:00.000-----O9000----2013-01-09 10:00:00.000-----I9000----2013-01-09 10:01:00.000-----I9000----2013-01-09 12:00:00.000-----O9000----2013-01-09 13:00:00.000-----I9000----2013-01-09 13:01:00.000-----I9000----2013-01-09 13:01:00.000-----I9000----2013-01-09 15:00:00.000-----O9000----2013-01-09 15:01:00.000-----Oi have Query like thisSELECT 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 ENDFROM AttendLog T1WHERE T1.ID = T .ID AND T1.CHECKTIME > T .CHECKTIMEORDER BY CHECKTIME) AS TimeOutFROM AttendLog TWHERE Type = 'I'but getiing false data like thisID------Date----------TimeIN----------TimeOut9000----2013-01-09----09:00:00.000----09:01:00.0009000----2013-01-09----10:00:00.000----NULL9000----2013-01-09----10:01:00.000----12:00:00.0009000----2013-01-09----13:00:00.000----NULL9000----2013-01-09----13:01:00.000----15:00:00.0009000----2013-01-09----13:01:00.000----15:00:00.000My Required Data is like thisID------Date----------TimeIN----------TimeOut-----9000----2013-01-09----09:00:00.000----09:10:00.0009000----2013-01-09----10:01:00.000----12:00:00.0009000----2013-01-09----13:01:00.000----15:01:00.000thanks in Advance |
|
|
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 @testvalues(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 TimeOUTFROM @test tCROSS 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 )t1OUTER APPLY (SELECT MAX(CheckTime) AS MaxOut FROM @test WHERE type='O' AND ID = t.ID AND CheckTime < COALESCE(NextIn,'99991231') )t2WHERE t.Type='I'AND COALESCE(t1.NextIn,'99991231') > t1.NextOutoutput--------------------------------------ID Date TimeIN TimeOUT----------------------------------------------------------------9000 2013-01-09 09:00:00.0000000 09:10:00.00000009000 2013-01-09 10:01:00.0000000 12:00:00.00000009000 2013-01-09 13:01:00.0000000 15:01:00.0000000[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
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 @testvalues(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 TimeOUTFROM @test tCROSS 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 )t1OUTER APPLY (SELECT MAX(CheckTime) AS MaxOut FROM @test WHERE type='O' AND ID = t.ID AND CheckTime < COALESCE(NextIn,'99991231') )t2WHERE t.Type='I'AND COALESCE(t1.NextIn,'99991231') > t1.NextOutoutput--------------------------------------ID Date TimeIN TimeOUT----------------------------------------------------------------9000 2013-01-09 09:00:00.0000000 09:10:00.00000009000 2013-01-09 10:01:00.0000000 12:00:00.00000009000 2013-01-09 13:01:00.0000000 15:01:00.0000000 ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://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 postingoutput--------------------------------------ID Date TimeIN TimeOUT----------------------------------------------------------------9000 2013-01-09 09:00:00.0000000 09:10:00.00000009000 2013-01-09 10:00:00.0000000 12:00:00.00000009000 2013-01-09 13:00:00.0000000 15:01:00.0000000 |
|
|
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. |
|
|
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 timeoutFROM #Tmp aLEFT JOIN #Tmp bON a.type <> b.typeAND a.checktime < b.checktimeWHERE a.type = 'I'GROUP BY a.id, a.checktime)SELECT id, MIN(timein) AS timein, timeoutFROM CTEGROUP BY id, timeoutid timein timeout11 2013-01-01 09:00:00.000 2013-01-01 12:00:00.00011 2013-01-01 14:15:00.000 2013-01-01 14:45:00.00011 2013-01-01 15:00:00.000 2013-01-01 18:00:00.00011 2013-01-02 09:10:00.000 2013-01-02 18:00:00.000 |
|
|
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 timeoutFROM #Tmp aLEFT JOIN #Tmp bON a.type <> b.typeAND a.id = b.idAND a.checktime < b.checktimeWHERE a.type = 'I'GROUP BY a.id, a.checktime)SELECT id, MIN(timein) AS timein, timeoutFROM CTEGROUP BY id, timeoutI don't think it matters in terms of the final result, but it would make for a smaller join, which is a good thing. |
|
|
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 @testvalues(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 TimeOUTFROM @test tCROSS 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 )t1OUTER APPLY (SELECT MAX(CheckTime) AS MaxOut FROM @test WHERE type='O' AND ID = t.ID AND CheckTime < COALESCE(NextIn,'99991231') )t2WHERE t.Type='I'AND COALESCE(t1.NextIn,'99991231') > t1.NextOutoutput--------------------------------------ID Date TimeIN TimeOUT----------------------------------------------------------------9000 2013-01-09 09:00:00.0000000 09:10:00.00000009000 2013-01-09 10:01:00.0000000 12:00:00.00000009000 2013-01-09 13:01:00.0000000 15:01:00.0000000 ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://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 postingoutput--------------------------------------ID Date TimeIN TimeOUT----------------------------------------------------------------9000 2013-01-09 09:00:00.0000000 09:10:00.00000009000 2013-01-09 10:00:00.0000000 12:00:00.00000009000 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 MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
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 timeThanks in Advance Regards |
|
|
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 MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
|
|
|
|