Author |
Topic |
asif372
Posting Yak Master
100 Posts |
Posted - 2013-03-11 : 03:43:38
|
Alternate of QueryI HAVE Data Like thisID------Date-------------------------CheckTime-----------------RowNumber1116----2012-12-03 00:00:00.000-----2012-12-03 14:46:46.000----11116----2012-12-03 00:00:00.000-----2012-12-03 18:12:17.000----21116----2012-12-04 00:00:00.000-----2012-12-04 09:06:54.000----11116----2012-12-04 00:00:00.000-----2012-12-04 17:46:08.000----21116----2012-12-05 00:00:00.000-----2012-12-05 09:09:36.000----11116----2012-12-05 00:00:00.000-----2012-12-05 17:04:33.000----2Required DataID----Date---------------------TimeIN-------------------TimeOut1116--2012-12-03 00:00:00.000--2012-12-03 14:46:46.000--2012-12-03 18:12:17.0001116--2012-12-04 00:00:00.000--2012-12-04 09:06:54.000--2012-12-04 17:46:08.0001116--2012-12-05 00:00:00.000--2012-12-05 09:09:36.000--2012-12-05 17:04:33.000I have Executed this Query working fine but works very slow i want alternate of this querySELECTT.EID,T.Date,T.CHECKTIME AS TIMEIN,(SELECT TOP 1 CASE WHEN T1.RowNumber IN (2,4,6,8,10,12,14,16,18,20,22,24) THEN T1.CHECKTIME ELSE NULL ENDFROM ATTENDANCE T1WHERE T1.EID = T.EIDAND DATEADD(dd, 0, DATEDIFF(dd, 0, T.CheckTime)) = DATEADD(dd, 0, DATEDIFF(dd, 0, T1.CheckTime)) AND T1.CHECKTIME > T.CHECKTIMEGROUP BY T1.RowNumber,T1.CheckTimeORDER BY T1.CHECKTIME) AS TimeOutFROM ATTENDANCE TWHERE T.ROWNUMBER IN (1,3,5,7,9,11,13,15,17,19,21,23,25)GROUP BY T.EID,T.Date,T.CheckTime,(T.RowNumber-1) /2I want Alternate of following Query Kindly HelpThanks in AdvanceRegards |
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-03-11 : 05:39:45
|
[code]DECLARE @ATTENDANCE TABLE( EID INT, Date DATETIMe, CheckTime DATETIMe, RowNumber INT)INSERT INTO @ATTENDANCESELECT 1116, '2012-12-03 00:00:00.000', '2012-12-03 14:46:46.000', 1UNION ALLSELECT 1116,'2012-12-03 00:00:00.000', '2012-12-03 18:12:17.000', 2UNION ALLSELECT 1116,'2012-12-04 00:00:00.000', '2012-12-04 09:06:54.000', 1 UNION ALLSELECT 1116,'2012-12-04 00:00:00.000', '2012-12-04 17:46:08.000', 2 UNION ALLSELECT 1116,'2012-12-05 00:00:00.000', '2012-12-05 09:09:36.000', 1 UNION ALLSELECT 1116, '2012-12-05 00:00:00.000', '2012-12-05 17:04:33.000', 2/*Required DataID----Date---------------------TimeIN-------------------TimeOut1116--2012-12-03 00:00:00.000--2012-12-03 14:46:46.000--2012-12-03 18:12:17.0001116--2012-12-04 00:00:00.000--2012-12-04 09:06:54.000--2012-12-04 17:46:08.0001116--2012-12-05 00:00:00.000--2012-12-05 09:09:36.000--2012-12-05 17:04:33.000I have Executed this Query working fine but works very slow i want alternate of this query*/SELECTT.EID,T.Date,T.CHECKTIME AS TIMEIN,(SELECT TOP 1 CASE WHEN T1.RowNumber IN (2,4,6,8,10,12,14,16,18,20,22,24) THEN T1.CHECKTIME ELSE NULL ENDFROM @ATTENDANCE T1WHERE T1.EID = T.EIDAND DATEADD(dd, 0, DATEDIFF(dd, 0, T.CheckTime)) = DATEADD(dd, 0, DATEDIFF(dd, 0, T1.CheckTime)) AND T1.CHECKTIME > T.CHECKTIMEGROUP BY T1.RowNumber,T1.CheckTimeORDER BY T1.CHECKTIME) AS TimeOutFROM @ATTENDANCE TWHERE T.ROWNUMBER IN (1,3,5,7,9,11,13,15,17,19,21,23,25)GROUP BY T.EID,T.Date,T.CheckTime,(T.RowNumber-1) /2--AlternateSELECT EID, Date, MIN(CheckTime) AS Start_Time,MAX(CheckTime) AS End_TimeFROM @ATTENDANCE tOUTER APPLY (SELECT MIN(CheckTime) AS MinDate FROM @ATTENDANCE WHERE CheckTime> t.CheckTime AND Date <> t.Date AND EID = t.EID )t1GROUP BY EID,DATE,MinDate[/code]--Chandu |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-03-11 : 06:27:26
|
I think this is enough ( atleast as per posted sample data)SELECT EID, Date, MIN(CASE WHEN RowNumber =1 THEN CheckTime END) AS Start_Time,MIN(CASE WHEN RowNumber =2 THEN CheckTime END) AS End_TimeFROM @ATTENDANCE tGROUP BY EID,DATE ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-03-11 : 06:36:49
|
Yes Visakh.. My alternate solution is independent of RowNumber...--Chandu |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-03-11 : 06:41:44
|
quote: Originally posted by bandi Yes Visakh.. My alternate solution is independent of RowNumber...--Chandu
Whats the issue in depending on rownumber if the field already exists?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-03-11 : 07:03:11
|
He is checking for alternate RowNumbers (means Even and Odd Numbering), not for specific 1 and 2SeeCASE WHEN T1.RowNumber IN (2,4,6,8,10,12,14,16,18,20,22,24) THEN T1.CHECKTIME ELSE NULL ENDWHERE T.ROWNUMBER IN (1,3,5,7,9,11,13,15,17,19,21,23,25)....Instead of these conditions, we can do like this1) RowNumber%2 = 0 instead of CASE2) RowNumber%2 != 0 instead of WHERE--Chandu |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-03-11 : 07:14:08
|
his sample data sshowed rownumber value as 1 and 2. If thats not the case, he can use logic using modulo operator to check for odd and even values. in nay case there's no need for correlated subquery using APPLY------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
asif372
Posting Yak Master
100 Posts |
Posted - 2013-03-11 : 08:01:25
|
thanks Bandi and visak for your interesti dont want group by clause in this query how can it be possiblethanks |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-03-11 : 10:01:08
|
SELECT DISTINCT EID, Date, MIN(CASE WHEN RowNumber%2 != 0 THEN CheckTime END) OVER(PARTITION BY EID, DATE) AS Start_Time,MIN(CASE WHEN RowNumber%2 =0 THEN CheckTime END) OVER(PARTITION BY EID, DATE) AS End_TimeFROM @ATTENDANCE t--Chandu |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-03-11 : 12:16:36
|
quote: Originally posted by asif372 thanks Bandi and visak for your interesti dont want group by clause in this query how can it be possiblethanks
why wats the issue with GROUP BY?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
asif372
Posting Yak Master
100 Posts |
Posted - 2013-03-12 : 01:15:01
|
Sir I have some Issues on your Previous Recommendations of Query Kindly See Belowthis is my Table DataEID-----Date-------------------CheckTIME-----------------RowNumber1116----2013-01-14 00:00:00.000 2013-01-14 09:29:43.000--1 1116----2013-01-14 00:00:00.000 2013-01-14 13:08:23.000--21116----2013-01-14 00:00:00.000 2013-01-14 13:44:21.000--31116----2013-01-14 00:00:00.000 2013-01-14 17:09:46.000--4i have Work on this QuerySELECTT.EID,T.Date,T.CHECKTIME AS TIMEIN,T.RowNumber RN,T1.CHECKTIME TimeOut,T1.RowNumber RN1FROM View6 TLEFT OUTER JOIN View6 T1 ON T.EID = T1.EIDAND T.DATE = T1.DATEAND T1.RowNumber IN (2,4,6,8,10,12,14,16,18,20,22,24)AND (T.CHECKTIME < T1.CHECKTIME)WHERE T.ROWNUMBER IN (1,3,5,7,9,11,13,15,17,19,21,23,25)AND T.EID = 1116AND T.Date = '20130114'and Receive Following ResultEID---Date---------TimeIn------------RN---TimeOut--------------RN11116--2013-01-14---2013-01-14 09:29:43---1--2013-01-14 13:08:23--21116--2013-01-14---2013-01-14 09:29:43---1--2013-01-14 17:09:46--41116--2013-01-14---2013-01-14 13:44:21---3--2013-01-14 17:09:46--4My Required Result is as FollowEID---Date---------TimeIn------------RN---TimeOut--------------RN11116--2013-01-14---2013-01-14 09:29:43---1--2013-01-14 13:08:23--21116--2013-01-14---2013-01-14 13:44:21---3--2013-01-14 17:09:46--4kindly Help me to sort this ProblemThanks |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-03-12 : 01:35:21
|
where's our suggestion used? the current query you posted has neither mine nor Bandi's suggestionsdeclare @test table(EID int,[Date] datetime,CheckTIME datetime,RowNumber int)insert @testvalues(1116,'2013-01-14 00:00:00.000','2013-01-14 09:29:43.000',1),(1116,'2013-01-14 00:00:00.000','2013-01-14 13:08:23.000',2),(1116,'2013-01-14 00:00:00.000','2013-01-14 13:44:21.000',3),(1116,'2013-01-14 00:00:00.000','2013-01-14 17:09:46.000',4),(1116,'2013-01-14 00:00:00.000','2013-01-14 18:22:34.000',5),(1116,'2013-01-14 00:00:00.000','2013-01-14 19:19:06.000',6),(1116,'2013-01-14 00:00:00.000','2013-01-14 20:03:16.000',7),(1116,'2013-01-14 00:00:00.000','2013-01-14 20:16:46.000',8),(1118,'2013-02-22 00:00:00.000','2013-02-22 11:19:43.000',1),(1118,'2013-02-22 00:00:00.000','2013-02-22 12:08:23.000',2),(1118,'2013-02-22 00:00:00.000','2013-02-22 13:44:21.000',3),(1118,'2013-02-22 00:00:00.000','2013-02-22 16:44:21.000',4)SELECT EID,[Date],MAX(CASE WHEN GrpSeq=1 THEN CHECKTIME END) AS TimeIn,MAX(CASE WHEN GrpSeq=1 THEN RowNumber END) AS RN,MAX(CASE WHEN GrpSeq=2 THEN CHECKTIME END) AS TimeOut,MAX(CASE WHEN GrpSeq=2 THEN RowNumber END) AS RN1FROM(SELECTT.EID,T.Date,T.CHECKTIME,T.RowNumber,(T.RowNumber-1)/2 AS Grp,ROW_NUMBER() OVER (PARTITION BY Date,(T.RowNumber-1)/2 ORDER BY RowNumber) AS GrpSeqFROM @test T)tGROUP BY EID,[Date],Grpoutput------------------------------------------------------------------------------------------------EID Date TimeIn RN TimeOut RN1------------------------------------------------------------------------------------------------1116 2013-01-14 00:00:00.000 2013-01-14 09:29:43.000 1 2013-01-14 13:08:23.000 21116 2013-01-14 00:00:00.000 2013-01-14 13:44:21.000 3 2013-01-14 17:09:46.000 41116 2013-01-14 00:00:00.000 2013-01-14 18:22:34.000 5 2013-01-14 19:19:06.000 61116 2013-01-14 00:00:00.000 2013-01-14 20:03:16.000 7 2013-01-14 20:16:46.000 81118 2013-02-22 00:00:00.000 2013-02-22 11:19:43.000 1 2013-02-22 12:08:23.000 21118 2013-02-22 00:00:00.000 2013-02-22 13:44:21.000 3 2013-02-22 16:44:21.000 4 ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
asif372
Posting Yak Master
100 Posts |
Posted - 2013-03-12 : 02:40:57
|
Thanks a lot it works |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-03-12 : 03:14:49
|
Check for this sample data once.......insert @testvalues(1116,'2013-01-14 00:00:00.000','2013-01-14 09:29:43.000',1),(1116,'2013-01-14 00:00:00.000','2013-01-14 13:08:23.000',2),(1116,'2013-01-14 00:00:00.000','2013-01-14 13:44:21.000',3),(1116,'2013-01-14 00:00:00.000','2013-01-14 17:09:46.000',4),(1116,'2013-01-14 00:00:00.000','2013-01-14 18:22:34.000',5),(1116,'2013-01-14 00:00:00.000','2013-01-14 19:19:06.000',6),(1116,'2013-01-14 00:00:00.000','2013-01-14 20:03:16.000',7),(1116,'2013-01-14 00:00:00.000','2013-01-14 20:16:46.000',8),(1118,'2013-02-22 00:00:00.000','2013-02-22 11:19:43.000',1),(1118,'2013-02-22 00:00:00.000','2013-02-22 12:08:23.000',2),(1118,'2013-02-22 00:00:00.000','2013-02-22 13:44:21.000',3),(1118,'2013-02-22 00:00:00.000','2013-02-22 16:44:21.000',4),(1115,'2013-01-14 00:00:00.000','2013-01-14 18:22:34.000',1),(1115,'2013-01-14 00:00:00.000','2013-01-14 19:19:06.000',2)--Chandu |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-03-12 : 05:27:37
|
there was a typo..here's the suggestion with Bandis datadeclare @test table(EID int,[Date] datetime,CheckTIME datetime,RowNumber int)insert @testvalues(1116,'2013-01-14 00:00:00.000','2013-01-14 09:29:43.000',1),(1116,'2013-01-14 00:00:00.000','2013-01-14 13:08:23.000',2),(1116,'2013-01-14 00:00:00.000','2013-01-14 13:44:21.000',3),(1116,'2013-01-14 00:00:00.000','2013-01-14 17:09:46.000',4),(1116,'2013-01-14 00:00:00.000','2013-01-14 18:22:34.000',5),(1116,'2013-01-14 00:00:00.000','2013-01-14 19:19:06.000',6),(1116,'2013-01-14 00:00:00.000','2013-01-14 20:03:16.000',7),(1116,'2013-01-14 00:00:00.000','2013-01-14 20:16:46.000',8),(1118,'2013-02-22 00:00:00.000','2013-02-22 11:19:43.000',1),(1118,'2013-02-22 00:00:00.000','2013-02-22 12:08:23.000',2),(1118,'2013-02-22 00:00:00.000','2013-02-22 13:44:21.000',3),(1118,'2013-02-22 00:00:00.000','2013-02-22 16:44:21.000',4),(1115,'2013-01-14 00:00:00.000','2013-01-14 18:22:34.000',1),(1115,'2013-01-14 00:00:00.000','2013-01-14 19:19:06.000',2)SELECT EID,[Date],MAX(CASE WHEN GrpSeq=1 THEN CHECKTIME END) AS TimeIn,MAX(CASE WHEN GrpSeq=1 THEN RowNumber END) AS RN,MAX(CASE WHEN GrpSeq=2 THEN CHECKTIME END) AS TimeOut,MAX(CASE WHEN GrpSeq=2 THEN RowNumber END) AS RN1FROM(SELECTT.EID,T.Date,T.CHECKTIME,T.RowNumber,(T.RowNumber-1)/2 AS Grp,ROW_NUMBER() OVER (PARTITION BY EID,Date,(T.RowNumber-1)/2 ORDER BY RowNumber) AS GrpSeqFROM @test T)tGROUP BY EID,[Date],GrpORDER BY EID,[Date],Grpoutput--------------------------------------------------------------------------------------------EID Date TimeIn RN TimeOut RN1--------------------------------------------------------------------------------------------1115 2013-01-14 00:00:00.000 2013-01-14 18:22:34.000 1 2013-01-14 19:19:06.000 21116 2013-01-14 00:00:00.000 2013-01-14 09:29:43.000 1 2013-01-14 13:08:23.000 21116 2013-01-14 00:00:00.000 2013-01-14 13:44:21.000 3 2013-01-14 17:09:46.000 41116 2013-01-14 00:00:00.000 2013-01-14 18:22:34.000 5 2013-01-14 19:19:06.000 61116 2013-01-14 00:00:00.000 2013-01-14 20:03:16.000 7 2013-01-14 20:16:46.000 81118 2013-02-22 00:00:00.000 2013-02-22 11:19:43.000 1 2013-02-22 12:08:23.000 21118 2013-02-22 00:00:00.000 2013-02-22 13:44:21.000 3 2013-02-22 16:44:21.000 4 ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|