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
 Alternate of following Query		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

asif372
Yak Posting Veteran

Pakistan
93 Posts
Posted - 03/11/2013 :  03:43:38  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
1423 Posts
Posted - 03/11/2013 :  05:39:45  Show Profile  Reply with Quote 

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


--
Chandu
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
47084 Posts
Posted - 03/11/2013 :  06:27:26  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
1423 Posts
Posted - 03/11/2013 :  06:36:49  Show Profile  Reply with Quote
Yes Visakh..
My alternate solution is independent of RowNumber...

--
Chandu
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
47084 Posts
Posted - 03/11/2013 :  06:41:44  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
1423 Posts
Posted - 03/11/2013 :  07:03:11  Show Profile  Reply with Quote
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

India
47084 Posts
Posted - 03/11/2013 :  07:14:08  Show Profile  Reply with Quote
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
Yak Posting Veteran

Pakistan
93 Posts
Posted - 03/11/2013 :  08:01:25  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
1423 Posts
Posted - 03/11/2013 :  10:01:08  Show Profile  Reply with Quote
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

India
47084 Posts
Posted - 03/11/2013 :  12:16:36  Show Profile  Reply with Quote
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
Yak Posting Veteran

Pakistan
93 Posts
Posted - 03/12/2013 :  01:15:01  Show Profile  Reply with Quote
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

India
47084 Posts
Posted - 03/12/2013 :  01:35:21  Show Profile  Reply with Quote
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
Yak Posting Veteran

Pakistan
93 Posts
Posted - 03/12/2013 :  02:40:57  Show Profile  Reply with Quote
Thanks a lot it works
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
1423 Posts
Posted - 03/12/2013 :  03:14:49  Show Profile  Reply with Quote
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

India
47084 Posts
Posted - 03/12/2013 :  05:27:37  Show Profile  Reply with Quote
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
  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.17 seconds. Powered By: Snitz Forums 2000