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
 Alternate of following Query

Author  Topic 

asif372
Posting Yak Master

100 Posts

Posted - 2013-03-11 : 03:43:38
Alternate of Query

I HAVE Data Like this
ID------Date-------------------------CheckTime-----------------RowNumber
1116----2012-12-03 00:00:00.000-----2012-12-03 14:46:46.000----1
1116----2012-12-03 00:00:00.000-----2012-12-03 18:12:17.000----2
1116----2012-12-04 00:00:00.000-----2012-12-04 09:06:54.000----1
1116----2012-12-04 00:00:00.000-----2012-12-04 17:46:08.000----2
1116----2012-12-05 00:00:00.000-----2012-12-05 09:09:36.000----1
1116----2012-12-05 00:00:00.000-----2012-12-05 17:04:33.000----2

Required Data


ID----Date---------------------TimeIN-------------------TimeOut
1116--2012-12-03 00:00:00.000--2012-12-03 14:46:46.000--2012-12-03 18:12:17.000
1116--2012-12-04 00:00:00.000--2012-12-04 09:06:54.000--2012-12-04 17:46:08.000
1116--2012-12-05 00:00:00.000--2012-12-05 09:09:36.000--2012-12-05 17:04:33.000


I have Executed this Query working fine but works very slow i want alternate of this query
SELECT
T.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 END
FROM ATTENDANCE T1
WHERE T1.EID = T.EID
AND DATEADD(dd, 0, DATEDIFF(dd, 0, T.CheckTime)) = DATEADD(dd, 0, DATEDIFF(dd, 0, T1.CheckTime)) AND T1.CHECKTIME > T.CHECKTIME
GROUP BY T1.RowNumber,T1.CheckTime
ORDER BY T1.CHECKTIME
) AS TimeOut

FROM ATTENDANCE T
WHERE
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

I want Alternate of following Query Kindly Help
Thanks in Advance
Regards

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 @ATTENDANCE
SELECT 1116, '2012-12-03 00:00:00.000', '2012-12-03 14:46:46.000', 1UNION ALL
SELECT 1116,'2012-12-03 00:00:00.000', '2012-12-03 18:12:17.000', 2UNION ALL
SELECT 1116,'2012-12-04 00:00:00.000', '2012-12-04 09:06:54.000', 1 UNION ALL
SELECT 1116,'2012-12-04 00:00:00.000', '2012-12-04 17:46:08.000', 2 UNION ALL
SELECT 1116,'2012-12-05 00:00:00.000', '2012-12-05 09:09:36.000', 1 UNION ALL
SELECT 1116, '2012-12-05 00:00:00.000', '2012-12-05 17:04:33.000', 2
/*Required Data
ID----Date---------------------TimeIN-------------------TimeOut
1116--2012-12-03 00:00:00.000--2012-12-03 14:46:46.000--2012-12-03 18:12:17.000
1116--2012-12-04 00:00:00.000--2012-12-04 09:06:54.000--2012-12-04 17:46:08.000
1116--2012-12-05 00:00:00.000--2012-12-05 09:09:36.000--2012-12-05 17:04:33.000
I have Executed this Query working fine but works very slow i want alternate of this query*/
SELECT
T.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 END
FROM @ATTENDANCE T1
WHERE T1.EID = T.EID
AND DATEADD(dd, 0, DATEDIFF(dd, 0, T.CheckTime)) = DATEADD(dd, 0, DATEDIFF(dd, 0, T1.CheckTime)) AND T1.CHECKTIME > T.CHECKTIME
GROUP BY T1.RowNumber,T1.CheckTime
ORDER BY T1.CHECKTIME
) AS TimeOut

FROM @ATTENDANCE T
WHERE
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

--Alternate
SELECT EID, Date, MIN(CheckTime) AS Start_Time,MAX(CheckTime) AS End_Time
FROM @ATTENDANCE t
OUTER APPLY (SELECT MIN(CheckTime) AS MinDate
FROM @ATTENDANCE
WHERE CheckTime> t.CheckTime
AND Date <> t.Date
AND EID = t.EID
)t1
GROUP BY EID,DATE,MinDate
[/code]

--
Chandu
Go to Top of Page

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_Time
FROM @ATTENDANCE t
GROUP BY EID,DATE


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

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

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 MVP
http://visakhm.blogspot.com/

Go to Top of Page

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 2
See
CASE WHEN T1.RowNumber IN (2,4,6,8,10,12,14,16,18,20,22,24) THEN T1.CHECKTIME ELSE NULL END
WHERE T.ROWNUMBER IN (1,3,5,7,9,11,13,15,17,19,21,23,25)
....

Instead of these conditions, we can do like this
1) RowNumber%2 = 0 instead of CASE
2) RowNumber%2 != 0 instead of WHERE





--
Chandu
Go to Top of Page

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 MVP
http://visakhm.blogspot.com/

Go to Top of Page

asif372
Posting Yak Master

100 Posts

Posted - 2013-03-11 : 08:01:25
thanks Bandi and visak for your interest
i dont want group by clause in this query how can it be possible
thanks
Go to Top of Page

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_Time
FROM @ATTENDANCE t

--
Chandu
Go to Top of Page

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 interest
i dont want group by clause in this query how can it be possible
thanks



why wats the issue with GROUP BY?



------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

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 Below

this is my Table Data
EID-----Date-------------------CheckTIME-----------------RowNumber
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

i have Work on this Query

SELECT
T.EID,
T.Date,
T.CHECKTIME AS TIMEIN,
T.RowNumber RN,
T1.CHECKTIME TimeOut,
T1.RowNumber RN1
FROM View6 T
LEFT OUTER JOIN View6 T1 ON
T.EID = T1.EID
AND T.DATE = T1.DATE
AND 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 = 1116
AND T.Date = '20130114'

and Receive Following Result
EID---Date---------TimeIn------------RN---TimeOut--------------RN1
1116--2013-01-14---2013-01-14 09:29:43---1--2013-01-14 13:08:23--2
1116--2013-01-14---2013-01-14 09:29:43---1--2013-01-14 17:09:46--4
1116--2013-01-14---2013-01-14 13:44:21---3--2013-01-14 17:09:46--4

My Required Result is as Follow

EID---Date---------TimeIn------------RN---TimeOut--------------RN1
1116--2013-01-14---2013-01-14 09:29:43---1--2013-01-14 13:08:23--2
1116--2013-01-14---2013-01-14 13:44:21---3--2013-01-14 17:09:46--4

kindly Help me to sort this Problem
Thanks
Go to Top of Page

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 suggestions

declare @test table
(
EID int,[Date] datetime,
CheckTIME datetime,
RowNumber int
)
insert @test
values(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 RN1
FROM
(
SELECT
T.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 GrpSeq
FROM @test T
)t
GROUP BY EID,
[Date],
Grp



output
------------------------------------------------------------------------------------------------
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 2
1116 2013-01-14 00:00:00.000 2013-01-14 13:44:21.000 3 2013-01-14 17:09:46.000 4
1116 2013-01-14 00:00:00.000 2013-01-14 18:22:34.000 5 2013-01-14 19:19:06.000 6
1116 2013-01-14 00:00:00.000 2013-01-14 20:03:16.000 7 2013-01-14 20:16:46.000 8
1118 2013-02-22 00:00:00.000 2013-02-22 11:19:43.000 1 2013-02-22 12:08:23.000 2
1118 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 MVP
http://visakhm.blogspot.com/

Go to Top of Page

asif372
Posting Yak Master

100 Posts

Posted - 2013-03-12 : 02:40:57
Thanks a lot it works
Go to Top of Page

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-03-12 : 03:14:49
Check for this sample data once.......
insert @test
values(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
Go to Top of Page

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 data



declare @test table
(
EID int,[Date] datetime,
CheckTIME datetime,
RowNumber int
)

insert @test
values(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 RN1
FROM
(
SELECT
T.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 GrpSeq
FROM @test T
)t
GROUP BY EID,
[Date],
Grp
ORDER BY EID,
[Date],
Grp

output
--------------------------------------------------------------------------------------------
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 2
1116 2013-01-14 00:00:00.000 2013-01-14 09:29:43.000 1 2013-01-14 13:08:23.000 2
1116 2013-01-14 00:00:00.000 2013-01-14 13:44:21.000 3 2013-01-14 17:09:46.000 4
1116 2013-01-14 00:00:00.000 2013-01-14 18:22:34.000 5 2013-01-14 19:19:06.000 6
1116 2013-01-14 00:00:00.000 2013-01-14 20:03:16.000 7 2013-01-14 20:16:46.000 8
1118 2013-02-22 00:00:00.000 2013-02-22 11:19:43.000 1 2013-02-22 12:08:23.000 2
1118 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 MVP
http://visakhm.blogspot.com/

Go to Top of Page
   

- Advertisement -