Meaning of a sql query [Resolved]

--Sample Data

DECLARE @Log TABLE( CHECKTIME DATETIME2, CHECKTYPE CHAR(1),  Badgenumber int, Name  VARCHAR(40))

INSERT INTO @Log

SELECT '2013-08-11 08:13:54.0000000', 'I', 40172, 'MOHD MOMINUL ISLAM' union all

SELECT '2013-08-11 10:07:06.0000000', 'O', 40172, 'MOHD MOMINUL ISLAM' union all

SELECT '2013-08-11 10:07:43.0000000', 'I', 40172, 'MOHD MOMINUL ISLAM' union all

SELECT '2013-08-11 10:10:23.0000000', 'O', 40172, 'MOHD MOMINUL ISLAM' union all

SELECT '2013-08-11 10:11:20.0000000', 'I', 40172, 'MOHD MOMINUL ISLAM' union all

SELECT '2013-08-11 10:28:53.0000000', 'O', 40172, 'MOHD MOMINUL ISLAM' union all

SELECT '2013-08-11 10:30:28.0000000', 'I', 40172, 'MOHD MOMINUL ISLAM' union all

SELECT '2013-08-11 10:35:37.0000000', 'O', 40172, 'MOHD MOMINUL ISLAM' union all

SELECT '2013-08-11 10:39:19.0000000', 'I', 40172, 'MOHD MOMINUL ISLAM' union all

SELECT '2013-08-11 15:30:10.0000000', 'O', 40172, 'MOHD MOMINUL ISLAM' union all

SELECT '2013-08-11 15:30:53.0000000', 'I', 40172, 'MOHD MOMINUL ISLAM' union all

SELECT '2013-08-11 15:33:04.0000000', 'O', 40172, 'MOHD MOMINUL ISLAM' union all

SELECT '2013-08-11 15:33:52.0000000', 'I', 40172, 'MOHD MOMINUL ISLAM' union all

SELECT '2013-08-11 15:35:51.0000000', 'I', 40172, 'MOHD MOMINUL ISLAM' union all

SELECT '2013-08-11 15:38:09.0000000', 'O', 40172, 'MOHD MOMINUL ISLAM' union all

SELECT '2013-08-11 18:35:17.0000000', 'O', 40172, 'MOHD MOMINUL ISLAM'



/*Output of the Query Is : 

StartTime EndTime (No Column Name) 

2013-08-11 08:13:54.0000000 2013-08-11 18:35:17.0000000 622

*/

--Query1

SELECT NAME, Badgenumber

	,MIN(CASE WHEN CHECKTYPE='I' THEN CHECKTIME END) StartTime 

	,MAX(CASE WHEN CHECKTYPE='O' THEN CHECKTIME END) EndTime 

	,DateDiff(MI,Min(CASE WHEN CHECKTYPE='I' THEN CHECKTIME END),MAX(CASE WHEN CHECKTYPE='O' THEN CHECKTIME END))

FROM @Log

GROUP BY NAME, Badgenumber



-- Query2

SELECT NAME, Badgenumber, MIN(CheckTime) AS Start_Time, MinDate

	,DateDiff(MI,Min(CHECKTIME),MinDate) TimeDifferenceinMin

FROM @Log t

OUTER APPLY (SELECT MIN(CheckTime) AS MinDate

			 FROM @Log

			 WHERE CheckTime > t.CheckTime

			 AND Badgenumber= t.Badgenumber

			 AND NAME = t.NAME

			 )t1

GROUP BY NAME,Badgenumber,MinDate

output :
--Query1:

NAME	Badgenumber	StartTime	EndTime	(No column name)

MOHD MOMINUL ISLAM	40172	2013-08-11 08:13:54.0000000	2013-08-11 18:35:17.0000000	622

--query2:

NAME	Badgenumber	Start_Time	MinDate	TimeDifferenceinMin

MOHD MOMINUL ISLAM	40172	2013-08-11 18:35:17.0000000	NULL	NULL

MOHD MOMINUL ISLAM	40172	2013-08-11 08:13:54.0000000	2013-08-11 10:07:06.0000000	114

MOHD MOMINUL ISLAM	40172	2013-08-11 10:07:06.0000000	2013-08-11 10:07:43.0000000	0

MOHD MOMINUL ISLAM	40172	2013-08-11 10:07:43.0000000	2013-08-11 10:10:23.0000000	3

MOHD MOMINUL ISLAM	40172	2013-08-11 10:10:23.0000000	2013-08-11 10:11:20.0000000	1

MOHD MOMINUL ISLAM	40172	2013-08-11 10:11:20.0000000	2013-08-11 10:28:53.0000000	17

MOHD MOMINUL ISLAM	40172	2013-08-11 10:28:53.0000000	2013-08-11 10:30:28.0000000	2

MOHD MOMINUL ISLAM	40172	2013-08-11 10:30:28.0000000	2013-08-11 10:35:37.0000000	5

MOHD MOMINUL ISLAM	40172	2013-08-11 10:35:37.0000000	2013-08-11 10:39:19.0000000	4

MOHD MOMINUL ISLAM	40172	2013-08-11 10:39:19.0000000	2013-08-11 15:30:10.0000000	291

MOHD MOMINUL ISLAM	40172	2013-08-11 15:30:10.0000000	2013-08-11 15:30:53.0000000	0

MOHD MOMINUL ISLAM	40172	2013-08-11 15:30:53.0000000	2013-08-11 15:33:04.0000000	3

MOHD MOMINUL ISLAM	40172	2013-08-11 15:33:04.0000000	2013-08-11 15:33:52.0000000	0

MOHD MOMINUL ISLAM	40172	2013-08-11 15:33:52.0000000	2013-08-11 15:35:51.0000000	2

MOHD MOMINUL ISLAM	40172	2013-08-11 15:35:51.0000000	2013-08-11 15:38:09.0000000	3

MOHD MOMINUL ISLAM	40172	2013-08-11 15:38:09.0000000	2013-08-11 18:35:17.0000000	177

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Cibin, if this helps please login to Mark As Answer. | Alert Moderator

Add WHERE Condition to check the date ranges.....

-- Query1

SELECT NAME, Badgenumber

	,MIN(CASE WHEN CHECKTYPE='I' THEN CHECKTIME END) StartTime 

	,MAX(CASE WHEN CHECKTYPE='O' THEN CHECKTIME END) EndTime 

	,DateDiff(MI,Min(CASE WHEN CHECKTYPE='I' THEN CHECKTIME END),MAX(CASE WHEN CHECKTYPE='O' THEN CHECKTIME END))

FROM @Log

WHERE CAST( CHECKTIME AS Date ) BEtween '20130801' AND '20130830' 

GROUP BY NAME, Badgenumber



-- Query2

SELECT NAME, Badgenumber, MIN(CheckTime) AS Start_Time, MinDate

	,DateDiff(MI,Min(CHECKTIME),MinDate) TimeDifferenceinMin

FROM @Log t

OUTER APPLY (SELECT MIN(CheckTime) AS MinDate

			 FROM @Log

			 WHERE CheckTime > t.CheckTime

			 AND Badgenumber= t.Badgenumber

			 AND NAME = t.NAME

			 AND CAST( CHECKTIME AS Date ) BEtween '20130801' AND '20130830'   --- 1st Aug, 2013 to 30th Aug, 2013

			 )t1

WHERE CAST( CHECKTIME AS Date ) BEtween '20130801' AND '20130830'

GROUP BY NAME,Badgenumber,MinDate 

ORDER BY Start_Time

NOTE: You can pass date range values by passing variables

DECLARE @startDate DATE = '20130801', @EndDate DATE = '20130830'

SELECT ....

WHERE CAST( CHECKTIME AS Date ) BEtween @startDate AND @EndDate 

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Cibin, if this helps please login to Mark As Answer. | Alert Moderator

Hi Cibin,
--Query:

SELECT NAME, Badgenumber, MIN(CheckTime) AS Start_Time, EndTime, CHECKTYPE

	,DateDiff(MI,Min(CASE WHEN CHECKTYPE = 'I' THEN CHECKTIME END ),EndTime) InTimeDifference

	,DateDiff(MI,Min(CASE WHEN CHECKTYPE = 'O' THEN CHECKTIME END ),EndTime) OutTimeDifference

FROM @Log t

OUTER APPLY (SELECT MIN(CheckTime) AS EndTime

			 FROM @Log

			 WHERE CheckTime > t.CheckTime

			 AND Badgenumber= t.Badgenumber

			 AND NAME = t.NAME 

			 AND CAST( CHECKTIME AS Date ) BEtween '20130801' AND '20130830'

			 )t1 

WHERE (CAST( CHECKTIME AS Date ) BEtween '20130801' AND '20130830') 

GROUP BY NAME, Badgenumber, EndTime, CHECKTYPE 

ORDER BY Start_Time

--OUTPUT

NAME   Badgenumber  Start_Time      EndTime	CHECKTYPE	InTimeDiff	OutTimeDiff

ISLAM	40172	2013-08-11 08:13:54	2013-08-11 10:07:06	I	114	NULL

ISLAM	40172	2013-08-11 10:07:06	2013-08-11 10:07:43	O	NULL	0

ISLAM	40172	2013-08-11 10:07:43	2013-08-11 10:10:23	I	3	NULL

ISLAM	40172	2013-08-11 10:10:23	2013-08-11 10:11:20	O	NULL	1

ISLAM	40172	2013-08-11 10:11:20	2013-08-11 10:28:53	I	17	NULL

ISLAM	40172	2013-08-11 10:28:53	2013-08-11 10:30:28	O	NULL	2

ISLAM	40172	2013-08-11 10:30:28	2013-08-11 10:35:37	I	5	NULL

ISLAM	40172	2013-08-11 10:35:37	2013-08-11 10:39:19	O	NULL	4

ISLAM	40172	2013-08-11 10:39:19	2013-08-11 15:30:10	I	291	NULL

ISLAM	40172	2013-08-11 15:30:10	2013-08-11 15:30:53	O	NULL	0

ISLAM	40172	2013-08-11 15:30:53	2013-08-11 15:33:04	I	3	NULL

ISLAM	40172	2013-08-11 15:33:04	2013-08-11 15:33:52	O	NULL	0

ISLAM	40172	2013-08-11 15:33:52	2013-08-11 15:35:51	I	2	NULL

ISLAM	40172	2013-08-11 15:35:51	2013-08-11 15:38:09	I	3	NULL

ISLAM	40172	2013-08-11 15:38:09	2013-08-11 18:35:17	O	NULL	177

ISLAM	40172	2013-08-11 18:35:17	NULL		O	NULL	NULL

Note: if NOT the above result you want, please post exact output for your sample data......
Do not forget to explain the output in the case of last 4 rows of your data

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Cibin, if this helps please login to Mark As Answer. | Alert Moderator

Have you tested query by passing correct Date ranges?
DECLARE @startDate DATE = '20130701', @EndDate DATE = '20130731'

SELECT NAME, Badgenumber, MIN(CheckTime) AS Start_Time, EndTime, CHECKTYPE

	,DateDiff(MI,Min(CASE WHEN CHECKTYPE = 'I' THEN CHECKTIME END ),EndTime) InTimeDifference

	,DateDiff(MI,Min(CASE WHEN CHECKTYPE = 'O' THEN CHECKTIME END ),EndTime) OutTimeDifference

FROM @Log t

OUTER APPLY (SELECT MIN(CheckTime) AS EndTime

			 FROM @Log

			 WHERE CheckTime > t.CheckTime

			 AND Badgenumber= t.Badgenumber

			 AND NAME = t.NAME 

			 AND CAST( CHECKTIME AS Date ) BEtween @startDate AND @EndDate 

			 )t1 

WHERE (CAST( CHECKTIME AS Date ) BEtween @startDate AND @EndDate ) 

GROUP BY NAME, Badgenumber, EndTime, CHECKTYPE 

ORDER BY Start_Time

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Cibin, if this helps please login to Mark As Answer. | Alert Moderator

DECLARE @startDate DATE = '20130701', @EndDate DATE = '20130731' 



SELECT Badgenumber, NAME As Employeename, CAST( MIN(CHECKTIME) AS Date) As Checkindate

	,MIN(CheckTime) AS CheckIn, CheckOut, CHECKTYPE

	,DateDiff(MI,Min(CASE WHEN CHECKTYPE = 'I' THEN CHECKTIME END ),CheckOut) TotalIn

	,DateDiff(MI,Min(CASE WHEN CHECKTYPE = 'O' THEN CHECKTIME END ),CheckOut) TotalOut

	FROM @Log t

OUTER APPLY (SELECT MIN(CheckTime) AS CheckOut

			 FROM @Log

			 WHERE CheckTime > t.CheckTime

			 AND Badgenumber= t.Badgenumber

			 AND NAME = t.NAME 

			 AND CAST( CHECKTIME AS Date ) BEtween @startDate AND @EndDate

			 )t1 

WHERE (CAST( CHECKTIME AS Date ) BEtween @startDate AND @EndDate) 

GROUP BY NAME, Badgenumber, CheckOut, CHECKTYPE 

ORDER BY Checkindate

Cibin,
Please look into the following result. here CheckInDate is 11th, July and CheckOutDate is 14th July... Is that TotalOut value correct in your case...?

Badgenumber	Employeename	Checkindate	CheckIn	CheckOut	CHECKTYPE	TotalIn	TotalOut

Checkindate	CheckIn	CheckOut	CHECKTYPE	TotalIn	TotalOut

2013-07-11	2013-07-11 17:11:10.0000000	2013-07-14 08:53:44.0000000 	O	NULL	3822

2013-07-14	2013-07-14 08:53:44.0000000	2013-07-14 09:43:30.0000000	I	50	NULL

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Cibin, if this helps please login to Mark As Answer. | Alert Moderator

Note: Highlighted part is for storing result set into another table called #TempTable

DECLARE @startDate DATE = '20130701', @EndDate DATE = '20130731' ;

SELECT Badgenumber, NAME As Employeename, CAST( MIN(CHECKTIME) AS Date) As Checkindate

	,MIN(CheckTime) AS CheckIn, CheckOut, CHECKTYPE

	,ISNULL(CAST(DateDiff(MI,Min(CASE WHEN CHECKTYPE = 'I' THEN CHECKTIME END ),CheckOut)/60 AS VARCHAR(10)) + ':' + 

	CAST(DateDiff(MI,Min(CASE WHEN CHECKTYPE = 'I' THEN CHECKTIME END ),CheckOut)%60  AS VARCHAR(10)), '0')  [TotalInHH:MI]

	,ISNULL(CAST(DateDiff(MI,Min(CASE WHEN CHECKTYPE = 'O' THEN CHECKTIME END ),CheckOut)/60 AS VARCHAR(10)) + ':' + 

	CAST(DateDiff(MI,Min(CASE WHEN CHECKTYPE = 'O' THEN CHECKTIME END ),CheckOut)%60  AS VARCHAR(10)), '0') [TotalOutHH:MI]

INTO #TempTable 

FROM @Log t

OUTER APPLY (SELECT MIN(CheckTime) AS CheckOut

			 FROM @Log

			 WHERE CheckTime > t.CheckTime

			 AND Badgenumber= t.Badgenumber

			 AND NAME = t.NAME 

			 AND CAST( CHECKTIME AS Date ) BEtween @startDate AND @EndDate

			 )t1 

WHERE (CAST( CHECKTIME AS Date ) BEtween @startDate AND @EndDate) 

GROUP BY NAME, Badgenumber, CheckOut, CHECKTYPE 

ORDER BY Checkindate;

-- To check the result

SELECT * FROM #TempTable

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Cibin, if this helps please login to Mark As Answer. | Alert Moderator

I understood your requirement now... Highlighted code is the modification....

DECLARE @startDate DATE = '20130701', @EndDate DATE = '20130731' 



SELECT Badgenumber, NAME As Employeename, CAST( MIN(CHECKTIME) AS Date) As Checkindate

	,CONVERT (char(5),MIN(CheckTime), 108) CheckInTime

	,CONVERT (char(5),CheckOut, 108) CheckOutTime 

	,CONVERT(char(5),DATEADD(Minute,DateDiff(MI,Min(CASE WHEN CHECKTYPE = 'I' THEN CHECKTIME END ),CheckOut), '00:00'), 108) [TotalInHH:MI]

	,CONVERT(char(5), DATEADD(Minute, DateDiff(MI,Min(CASE WHEN CHECKTYPE = 'O' THEN CHECKTIME END ),CheckOut), '00:00'), 108) [TotalOutHH:MI]  

FROM @Log t

OUTER APPLY (SELECT MIN(CheckTime) AS CheckOut

			 FROM @Log

			 WHERE CheckTime > t.CheckTime

			 AND Badgenumber= t.Badgenumber

			 AND NAME = t.NAME 

			 --AND CAST( CHECKTIME AS Date ) = CAST( t.CHECKTIME AS Date )

			 AND CAST( CHECKTIME AS Date ) BEtween @startDate AND @EndDate

			 )t1 

WHERE (CAST( CHECKTIME AS Date ) BEtween @startDate AND @EndDate) 

GROUP BY NAME, Badgenumber, CheckOut, CHECKTYPE 

ORDER BY Checkindate 

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Cibin, if this helps please login to Mark As Answer. | Alert Moderator

Hi cibin,
This is the latest code as per your requirement....

DECLARE @startDate DATE = '20130701', @EndDate DATE = '20130731' 



;WITH cte AS

	(  SELECT Badgenumber, NAME As Employeename, CAST( MIN(CHECKTIME) AS Date) As Checkindate

		,CONVERT (char(5),MIN(CheckTime), 108) CheckInTime

		,CONVERT (char(5),CheckOut, 108) CheckOutTime 

		,CONVERT(char(5),DATEADD(Minute,DateDiff(MI,Min(CHECKTIME),CheckOut), '00:00'), 108) [TotalInHH:MI]

		, ROW_NUMBER() OVER(ORDER BY CAST(MIN(CHECKTIME) AS Date), CONVERT (char(5),MIN(CheckTime), 108)) RN 	FROM @Log t

	OUTER APPLY (SELECT MIN(CheckTime) AS CheckOut

				 FROM @Log

				 WHERE CheckTime > t.CheckTime

				 AND Badgenumber= t.Badgenumber

				 AND NAME = t.NAME 

				 AND CHECKTYPE = 'O' 

				 --AND CAST( CHECKTIME AS Date ) = CAST( t.CHECKTIME AS Date )

				 AND CAST( CHECKTIME AS Date ) BEtween @startDate AND @EndDate

				 )t1 

	WHERE (CAST( CHECKTIME AS Date ) BEtween @startDate AND @EndDate) 

	AND CHECKTYPE= 'I'

	GROUP BY NAME, Badgenumber, CheckOut, CHECKTYPE 

	--ORDER BY Checkindate

	)

SELECT c1.Badgenumber, c1.Employeename, c1.Checkindate, c1.CheckInTime, c1.CheckOutTime, c1.[TotalInHH:MI]

	,CONVERT(char(5),DATEADD(Minute,DATEDIFF( MI, c1.CheckOutTime, c2.CheckInTime), '00:00'), 108) [TotalOutHH:MI]

FROM cte c1 

LEFT JOIN cte c2 ON c1.RN = c2.RN-1 

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Cibin, if this helps please login to Mark As Answer. | Alert Moderator

WITH TempResultName AS ( SELECT .... ) Any SELECT/DML

The above syntax is mainly used for result set based manipulation... Here you need to access first row's OutTime and Next row's InTime at a time.. So we need Self JOIN operation on earlier result(output).... That's why I modified previoud query as follows:
;WITH AnyNameYouCanGiveForResultSet
AS
(
-- Here place previous SELECT statement
)
-- followed by Self Join of above result (i.e. AnyNameYouCanGiveForResultSet )

for accessing first row & next row we have ROW_NUMBER() OVER(ORDER BY ColumnName) AS RN and join based on c1.RN = c2.RN-1

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Cibin, if this helps please login to Mark As Answer. | Alert Moderator

>>I want also copy these output values to a Table

SELECT c1.Badgenumber, c1.Employeename, c1.Checkindate, c1.CheckInTime, c1.CheckOutTime, c1.[TotalInHH:MI]

	,CONVERT(char(5),DATEADD(Minute,DATEDIFF( MI, c1.CheckOutTime, c2.CheckInTime), '00:00'), 108) [TotalOutHH:MI]

INTO #TempTable 

FROM cte c1 

LEFT JOIN cte c2 ON c1.RN = c2.RN-1 

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Cibin, if this helps please login to Mark As Answer. | Alert Moderator

try this once.. let me know the result... i havn`t tested below code....

SELECT Min(CHECKTIME) StartTime, UpTime EndTime,DateDiff(MI,Min(CHECKTIME),UpTime) FROM (SELECT Down.CHECKTIME,(SELECT Top 1 Up.CHECKTIME FROM sample Up WHERE  Up.CHECKTIME > Down.CHECKTIME  AND CAST(Up.checktime as date)  = CAST(Down.checktime as date) AND Up.CHECKTYPE = 'O' ORDER BY Up.CHECKTIME DESC) UpTime FROM sample Down WHERE Down.CHECKTYPE = 'I' ) X GROUP BY UpTime ORDER BY UpTime

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Cibin, if this helps please login to Mark As Answer. | Alert Moderator

have you checked above query?
>> if there is Two Checkouts in database , I want to take the largest value..
means there must be corresponding CheckIns also.... can you post some sample data how your table data looks like?

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Cibin, if this helps please login to Mark As Answer. | Alert Moderator

Hi Chandu,

Am Attaching the Sample data with this post , Please check it out .
 Download source file

Cibin, if this helps please login to Mark As Answer. | Alert Moderator

Hi , Chandu,

Am attaching the result of above query with this post .
 Download source file

Cibin, if this helps please login to Mark As Answer. | Alert Moderator

I'm sorry. I don't have permissions to download files... can you send in post....

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Cibin, if this helps please login to Mark As Answer. | Alert Moderator

Hi, Chandu ,

This is the Sample Data....,.


CHECKTIME CHECKTYPE Badgenumber Name
2013-08-11 08:13:54.0000000 I 40172 MOHD MOMINUL ISLAM
2013-08-11 10:07:06.0000000 O 40172 MOHD MOMINUL ISLAM
2013-08-11 10:07:43.0000000 I 40172 MOHD MOMINUL ISLAM
2013-08-11 10:10:23.0000000 O 40172 MOHD MOMINUL ISLAM
2013-08-11 10:11:20.0000000 I 40172 MOHD MOMINUL ISLAM
2013-08-11 10:28:53.0000000 O 40172 MOHD MOMINUL ISLAM
2013-08-11 10:30:28.0000000 I 40172 MOHD MOMINUL ISLAM
2013-08-11 10:35:37.0000000 O 40172 MOHD MOMINUL ISLAM
2013-08-11 10:39:19.0000000 I 40172 MOHD MOMINUL ISLAM
2013-08-11 15:30:10.0000000 O 40172 MOHD MOMINUL ISLAM
2013-08-11 15:30:53.0000000 I 40172 MOHD MOMINUL ISLAM
2013-08-11 15:33:04.0000000 O 40172 MOHD MOMINUL ISLAM
2013-08-11 15:33:52.0000000 I 40172 MOHD MOMINUL ISLAM
2013-08-11 15:35:51.0000000 I 40172 MOHD MOMINUL ISLAM
2013-08-11 15:38:09.0000000 O 40172 MOHD MOMINUL ISLAM
2013-08-11 18:30:55.0000000 O 40172 MOHD MOMINUL ISLAM

Cibin, if this helps please login to Mark As Answer. | Alert Moderator

Hi, Chandu,

Output of the Query Is :
StartTime EndTime (No Column Name)
2013-08-11 08:13:54.0000000 2013-08-11 18:35:17.0000000 622

Cibin, if this helps please login to Mark As Answer. | Alert Moderator

Hi, Chandu ,

This is the Sample Data....,.


CHECKTIME CHECKTYPE Badgenumber Name
2013-08-11 08:13:54.0000000 I 40172 MOHD MOMINUL ISLAM
2013-08-11 10:07:06.0000000 O 40172 MOHD MOMINUL ISLAM
2013-08-11 10:07:43.0000000 I 40172 MOHD MOMINUL ISLAM
2013-08-11 10:10:23.0000000 O 40172 MOHD MOMINUL ISLAM
2013-08-11 10:11:20.0000000 I 40172 MOHD MOMINUL ISLAM
2013-08-11 10:28:53.0000000 O 40172 MOHD MOMINUL ISLAM
2013-08-11 10:30:28.0000000 I 40172 MOHD MOMINUL ISLAM
2013-08-11 10:35:37.0000000 O 40172 MOHD MOMINUL ISLAM
2013-08-11 10:39:19.0000000 I 40172 MOHD MOMINUL ISLAM
2013-08-11 15:30:10.0000000 O 40172 MOHD MOMINUL ISLAM
2013-08-11 15:30:53.0000000 I 40172 MOHD MOMINUL ISLAM
2013-08-11 15:33:04.0000000 O 40172 MOHD MOMINUL ISLAM
2013-08-11 15:33:52.0000000 I 40172 MOHD MOMINUL ISLAM
2013-08-11 15:35:51.0000000 I 40172 MOHD MOMINUL ISLAM
2013-08-11 15:38:09.0000000 O 40172 MOHD MOMINUL ISLAM
2013-08-11 18:30:55.0000000 O 40172 MOHD MOMINUL ISLAM


In Our Old System , We manually delete the last Checktype " I " and Second last " O ", So that he doesn't loss his time..

Cibin, if this helps please login to Mark As Answer. | Alert Moderator

For your output simple SQL query is enough...

SELECT NAME, Badgenumber

	,MIN(CASE WHEN CHECKTYPE='I' THEN CHECKTIME END) StartTime 

	,MAX(CASE WHEN CHECKTYPE='O' THEN CHECKTIME END) EndTime 

	,DateDiff(MI,Min(CASE WHEN CHECKTYPE='I' THEN CHECKTIME END),MAX(CASE WHEN CHECKTYPE='O' THEN CHECKTIME END)) "TotalTimeIrrespectiveOfGaps"

FROM TableNAme

GROUP BY NAME, Badgenumber

What about the time gaps in between each CheckIn & CheckOut....
Ex: Employee " MOHD MOMINUL ISLAM " took 8 checkins & Checkouts.... what about gap time between each checkin & checkout...

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Cibin, if this helps please login to Mark As Answer. | Alert Moderator

Hi , Chandu,

ya, I want also the details of totalout time , and also i need the details of all checkin and checkout details. the above query only give the total time .

Cibin, if this helps please login to Mark As Answer. | Alert Moderator

Thanks Chandu,

This Query is for One Day ,, I Want it between a range of dates, may be from 1st May to 31st May, like that

Cibin, if this helps please login to Mark As Answer. | Alert Moderator

Hi, Chandu

Query 2 is Working , But can you please explain its working

Cibin, if this helps please login to Mark As Answer. | Alert Moderator

Hi, Chandu ,,

in this query

-- Query2

SELECT NAME, Badgenumber, MIN(CheckTime) AS Start_Time, MinDate

,DateDiff(MI,Min(CHECKTIME),MinDate) TimeDifferenceinMin

FROM @Log t

OUTER APPLY (SELECT MIN(CheckTime) AS MinDate

FROM @Log

WHERE CheckTime > t.CheckTime

AND Badgenumber= t.Badgenumber

AND NAME = t.NAME

)t1

GROUP BY NAME,Badgenumber,MinDate



both checkintotal and checkout total is displaying ...


I want The output format as

Badgenumber Employeename Checkindate CheckIn CheckOut TotalIn TotalOut

40172 MOHD MOMINUL ISLAM 2013-08-11 08:13:54.0000000 2013-08-11 10:07:06.0000000 114 0

for each entry

Cibin, if this helps please login to Mark As Answer. | Alert Moderator

SELECT NAME, Badgenumber, MIN(CheckTime) AS Start_Time, MinDate

	,DateDiff(MI,Min(CHECKTIME),MinDate) TimeDifferenceinMin

FROM @Log t

OUTER APPLY (SELECT MIN(CheckTime) AS MinDate

			 FROM @Log

			 WHERE CheckTime > t.CheckTime

			 AND Badgenumber= t.Badgenumber

			 AND NAME = t.NAME

			 AND CAST( CHECKTIME AS Date ) BEtween '20130801' AND '20130830'

			 )t1 

WHERE (CAST( CHECKTIME AS Date ) BEtween '20130801' AND '20130830')

GROUP BY NAME, Badgenumber, MinDate

ORDER BY Start_Time

Inner query in the OUTER APPLY is for getting next Checkout time (greater than the previous row CheckIn time) per day...
OUTER APPLY is same as JOIN operation... Based on that CheckOut time (here MinDate) we are calculating number of minutes between checlin & CheckOut time...

I have one doubt.. why you are manually deleting last CheckIn & checkout times?

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Cibin, if this helps please login to Mark As Answer. | Alert Moderator

Hi, Chandu


When am executing the Query , The output is like this :

NAME Badgenumber Start_Time EndTime CHECKTYPE InTimeDifference OutTimeDifference
------------------------ ------------------------ ---------------------- ---------------------- --------- ---------------- -----------------
CIBIN KANNANAYAKKAL 40214 2013-07-10 08:52:55.00 NULL I NULL NULL
CIBIN KANNANAYAKKAL 40214 2013-07-10 10:50:54.00 NULL O NULL NULL
Warning: Null value is eliminated by an aggregate or other SET operation.

(2 row(s) affected)

This is not am expecting output

Chandu can u send me a blank mail to this id : cibinlouis01@gmail.com

i'll Scan and mail you the desired report format

Cibin, if this helps please login to Mark As Answer. | Alert Moderator

Hi Cibin,
I don't have access to gmail/ outside networks...
Post your data for employee CIBIN KANNANAYAKKAL & Badgetnumber = 40214.. and expected exact output
I will try to understand the data....

I understood the reason for NULL values....
because of this condition WHERE (CAST( CHECKTIME AS Date ) BEtween '20130801' AND '20130830') you got NULL values for EndTime.. that led to get NULL for TimeDifferences too...
Fix: Change Date Ranges in the WHERE condition ( Date Format is YYYYMMDD)

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Cibin, if this helps please login to Mark As Answer. | Alert Moderator

Hi Chandu ,

CHECKTIME CHECKTYPE Badgenumber Name
2013-07-10 08:52:55.0000000 I 40214 CIBIN KANNANAYAKKAL
2013-07-10 10:50:54.0000000 O 40214 CIBIN KANNANAYAKKAL
2013-07-10 10:57:09.0000000 I 40214 CIBIN KANNANAYAKKAL
2013-07-10 11:35:30.0000000 O 40214 CIBIN KANNANAYAKKAL
2013-07-10 11:42:48.0000000 I 40214 CIBIN KANNANAYAKKAL
2013-07-10 12:54:44.0000000 O 40214 CIBIN KANNANAYAKKAL
2013-07-10 13:02:57.0000000 I 40214 CIBIN KANNANAYAKKAL
2013-07-10 13:34:14.0000000 O 40214 CIBIN KANNANAYAKKAL
2013-07-10 14:39:17.0000000 I 40214 CIBIN KANNANAYAKKAL
2013-07-10 15:16:00.0000000 O 40214 CIBIN KANNANAYAKKAL
2013-07-10 15:22:06.0000000 I 40214 CIBIN KANNANAYAKKAL
2013-07-10 16:43:20.0000000 O 40214 CIBIN KANNANAYAKKAL
2013-07-11 08:49:00.0000000 I 40214 CIBIN KANNANAYAKKAL
2013-07-11 10:02:17.0000000 O 40214 CIBIN KANNANAYAKKAL
2013-07-11 10:15:10.0000000 I 40214 CIBIN KANNANAYAKKAL
2013-07-11 10:51:47.0000000 O 40214 CIBIN KANNANAYAKKAL
2013-07-11 10:58:07.0000000 I 40214 CIBIN KANNANAYAKKAL
2013-07-11 12:09:29.0000000 O 40214 CIBIN KANNANAYAKKAL
2013-07-11 12:16:25.0000000 I 40214 CIBIN KANNANAYAKKAL
2013-07-11 13:08:14.0000000 O 40214 CIBIN KANNANAYAKKAL
2013-07-11 14:29:32.0000000 I 40214 CIBIN KANNANAYAKKAL
2013-07-11 15:55:02.0000000 O 40214 CIBIN KANNANAYAKKAL
2013-07-11 15:59:28.0000000 I 40214 CIBIN KANNANAYAKKAL
2013-07-11 16:46:59.0000000 O 40214 CIBIN KANNANAYAKKAL
2013-07-11 17:10:32.0000000 I 40214 CIBIN KANNANAYAKKAL
2013-07-11 17:11:10.0000000 O 40214 CIBIN KANNANAYAKKAL
2013-07-14 08:53:44.0000000 I 40214 CIBIN KANNANAYAKKAL
2013-07-14 09:43:30.0000000 O 40214 CIBIN KANNANAYAKKAL
2013-07-14 09:52:50.0000000 I 40214 CIBIN KANNANAYAKKAL
2013-07-14 10:56:43.0000000 O 40214 CIBIN KANNANAYAKKAL
2013-07-14 11:00:07.0000000 I 40214 CIBIN KANNANAYAKKAL
2013-07-14 12:00:18.0000000 O 40214 CIBIN KANNANAYAKKAL
2013-07-14 12:08:06.0000000 I 40214 CIBIN KANNANAYAKKAL
2013-07-14 13:04:48.0000000 O 40214 CIBIN KANNANAYAKKAL
2013-07-14 14:24:25.0000000 I 40214 CIBIN KANNANAYAKKAL
2013-07-14 15:37:08.0000000 O 40214 CIBIN KANNANAYAKKAL
2013-07-14 15:41:16.0000000 I 40214 CIBIN KANNANAYAKKAL
2013-07-14 18:48:35.0000000 O 40214 CIBIN KANNANAYAKKAL
2013-07-15 09:16:41.0000000 I 40214 CIBIN KANNANAYAKKAL
2013-07-15 10:50:07.0000000 O 40214 CIBIN KANNANAYAKKAL
2013-07-15 10:53:57.0000000 I 40214 CIBIN KANNANAYAKKAL
2013-07-15 12:14:20.0000000 O 40214 CIBIN KANNANAYAKKAL
2013-07-15 12:23:06.0000000 I 40214 CIBIN KANNANAYAKKAL
2013-07-15 13:06:15.0000000 O 40214 CIBIN KANNANAYAKKAL
2013-07-15 14:38:20.0000000 I 40214 CIBIN KANNANAYAKKAL
2013-07-15 15:57:15.0000000 O 40214 CIBIN KANNANAYAKKAL
2013-07-15 16:01:23.0000000 I 40214 CIBIN KANNANAYAKKAL
2013-07-15 17:57:10.0000000 O 40214 CIBIN KANNANAYAKKAL
2013-07-15 18:00:16.0000000 I 40214 CIBIN KANNANAYAKKAL
2013-07-15 19:59:31.0000000 O 40214 CIBIN KANNANAYAKKAL
2013-07-15 20:00:02.0000000 I 40214 CIBIN KANNANAYAKKAL
2013-07-15 20:00:49.0000000 O 40214 CIBIN KANNANAYAKKAL
2013-07-16 09:00:17.0000000 I 40214 CIBIN KANNANAYAKKAL
2013-07-16 11:12:18.0000000 O 40214 CIBIN KANNANAYAKKAL
2013-07-16 11:15:22.0000000 I 40214 CIBIN KANNANAYAKKAL
2013-07-16 12:54:21.0000000 O 40214 CIBIN KANNANAYAKKAL
2013-07-16 12:57:11.0000000 I 40214 CIBIN KANNANAYAKKAL
2013-07-16 13:16:22.0000000 O 40214 CIBIN KANNANAYAKKAL
2013-07-16 14:29:53.0000000 I 40214 CIBIN KANNANAYAKKAL
2013-07-16 20:06:34.0000000 O 40214 CIBIN KANNANAYAKKAL
2013-07-17 08:56:55.0000000 I 40214 CIBIN KANNANAYAKKAL
2013-07-17 10:34:40.0000000 O 40214 CIBIN KANNANAYAKKAL
2013-07-17 10:38:12.0000000 I 40214 CIBIN KANNANAYAKKAL
2013-07-17 11:42:12.0000000 O 40214 CIBIN KANNANAYAKKAL
2013-07-17 11:48:34.0000000 I 40214 CIBIN KANNANAYAKKAL
2013-07-17 12:23:10.0000000 O 40214 CIBIN KANNANAYAKKAL
2013-07-17 12:26:58.0000000 I 40214 CIBIN KANNANAYAKKAL
2013-07-17 13:17:35.0000000 O 40214 CIBIN KANNANAYAKKAL
2013-07-17 14:27:07.0000000 I 40214 CIBIN KANNANAYAKKAL
2013-07-17 15:51:56.0000000 O 40214 CIBIN KANNANAYAKKAL
2013-07-17 16:07:29.0000000 I 40214 CIBIN KANNANAYAKKAL
2013-07-17 18:04:07.0000000 O 40214 CIBIN KANNANAYAKKAL
2013-07-17 18:12:04.0000000 I 40214 CIBIN KANNANAYAKKAL
2013-07-17 19:39:36.0000000 O 40214 CIBIN KANNANAYAKKAL
2013-07-17 19:39:56.0000000 I 40214 CIBIN KANNANAYAKKAL
2013-07-17 19:40:14.0000000 O 40214 CIBIN KANNANAYAKKAL
2013-07-18 08:41:21.0000000 I 40214 CIBIN KANNANAYAKKAL
2013-07-18 09:36:55.0000000 O 40214 CIBIN KANNANAYAKKAL
2013-07-18 09:40:24.0000000 I 40214 CIBIN KANNANAYAKKAL
2013-07-18 10:13:37.0000000 O 40214 CIBIN KANNANAYAKKAL
2013-07-18 10:17:04.0000000 I 40214 CIBIN KANNANAYAKKAL
2013-07-18 11:22:00.0000000 O 40214 CIBIN KANNANAYAKKAL
2013-07-18 11:30:22.0000000 I 40214 CIBIN KANNANAYAKKAL
2013-07-18 15:23:01.0000000 O 40214 CIBIN KANNANAYAKKAL
2013-07-18 15:26:57.0000000 I 40214 CIBIN KANNANAYAKKAL
2013-07-18 16:16:35.0000000 O 40214 CIBIN KANNANAYAKKAL
2013-07-18 16:20:09.0000000 I 40214 CIBIN KANNANAYAKKAL
2013-07-18 16:41:01.0000000 O 40214 CIBIN KANNANAYAKKAL
2013-07-20 15:10:02.0000000 I 40214 CIBIN KANNANAYAKKAL
2013-07-20 15:10:28.0000000 O 40214 CIBIN KANNANAYAKKAL
2013-07-20 15:10:48.0000000 I 40214 CIBIN KANNANAYAKKAL
2013-07-20 20:48:05.0000000 O 40214 CIBIN KANNANAYAKKAL
2013-07-21 08:26:03.0000000 I 40214 CIBIN KANNANAYAKKAL
2013-07-21 09:00:32.0000000 O 40214 CIBIN KANNANAYAKKAL
2013-07-21 09:05:13.0000000 I 40214 CIBIN KANNANAYAKKAL
2013-07-21 10:49:49.0000000 O 40214 CIBIN KANNANAYAKKAL
2013-07-21 10:53:55.0000000 I 40214 CIBIN KANNANAYAKKAL
2013-07-21 11:26:26.0000000 O 40214 CIBIN KANNANAYAKKAL
2013-07-21 11:31:14.0000000 I 40214 CIBIN KANNANAYAKKAL
2013-07-21 12:10:35.0000000 O 40214 CIBIN KANNANAYAKKAL
2013-07-21 12:14:43.0000000 I 40214 CIBIN KANNANAYAKKAL
2013-07-21 13:18:02.0000000 O 40214 CIBIN KANNANAYAKKAL
2013-07-21 14:15:14.0000000 I 40214 CIBIN KANNANAYAKKAL
2013-07-21 15:04:37.0000000 O 40214 CIBIN KANNANAYAKKAL
2013-07-21 15:12:04.0000000 I 40214 CIBIN KANNANAYAKKAL
2013-07-21 15:52:44.0000000 O 40214 CIBIN KANNANAYAKKAL
2013-07-21 15:59:36.0000000 I 40214 CIBIN KANNANAYAKKAL
2013-07-21 18:06:11.0000000 O 40214 CIBIN KANNANAYAKKAL
2013-07-21 18:11:29.0000000 I 40214 CIBIN KANNANAYAKKAL
2013-07-21 18:26:25.0000000 O 40214 CIBIN KANNANAYAKKAL
2013-07-22 08:54:11.0000000 I 40214 CIBIN KANNANAYAKKAL
2013-07-22 10:02:12.0000000 O 40214 CIBIN KANNANAYAKKAL
2013-07-22 10:08:17.0000000 I 40214 CIBIN KANNANAYAKKAL
2013-07-22 11:18:13.0000000 O 40214 CIBIN KANNANAYAKKAL
2013-07-22 11:23:04.0000000 I 40214 CIBIN KANNANAYAKKAL
2013-07-22 12:21:51.0000000 O 40214 CIBIN KANNANAYAKKAL
2013-07-22 12:26:29.0000000 I 40214 CIBIN KANNANAYAKKAL
2013-07-22 12:58:15.0000000 O 40214 CIBIN KANNANAYAKKAL
2013-07-22 14:00:58.0000000 I 40214 CIBIN KANNANAYAKKAL
2013-07-22 15:14:03.0000000 O

Cibin, if this helps please login to Mark As Answer. | Alert Moderator

Hi, Chandu

Now all the Query Working properly ,But Chandu can you Help me to produce the report in the desired format ..



Badgenumber Employeename Checkindate CheckIn CheckOut TotalIn TotalOut

40172 MOHD MOMINUL ISLAM 2013-08-11 08:13:54.0000000 2013-08-11 10:07:06.0000000 114 0

Cibin, if this helps please login to Mark As Answer. | Alert Moderator

in which format you want the result?

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Cibin, if this helps please login to Mark As Answer. | Alert Moderator

Hi, Chandu

Now all the Query Working properly ,But Chandu can you Help me to produce the report in the desired format ..



Badgenumber Employeename Checkindate CheckIn CheckOut TotalIn TotalOut

40172 MOHD MOMINUL ISLAM 2013-08-11 08:13:54.0000000 2013-08-11 10:07:06.0000000 114 0

Cibin, if this helps please login to Mark As Answer. | Alert Moderator

Thanks Chandu,

And One More question : can you make the format in mins to hour ,, which means ,,, the total checkin and checkout are in mins like (72, 120, 130 etc...) can change it into hours like ( 1:12 , 2:00 , 2:10 etc...)


Chandu One more thing i want also save these details into a table

Cibin, if this helps please login to Mark As Answer. | Alert Moderator

Thanks Chandu ,

But after executing the query the checkIn and checkOut value will be 08:12:25.0000000, i want only the time .

Cibin, if this helps please login to Mark As Answer. | Alert Moderator

with the above query we will get following format time...

(No column name)	TotalOutHH:MI

8	0:8

NULL	0

65	1:5

NULL	0

6	0:6

NULL	0

966	16:6

NULL	0

13	0:13

NULL	0

81	1:21

NULL	0

3822	63:42

NULL	0

80	1:20

NULL	0

4	0:4

NULL	0

868	14:28

NULL	0

92	1:32

NULL	0

770	12:50

NULL	0

781	13:1

NULL	0

4	0:4

NULL	0

2789	46:29

NULL	0

0	0:0

NULL	0

698	11:38

NULL	0

5	0:5

NULL	0

4	0:4

NULL	0

5	0:5

NULL	0

4	0:4

NULL	0

57	0:57

NULL	0

8	0:8

NULL	0

7	0:7

NULL	0

5	0:5

NULL	0

868	14:28

NULL	0

6	0:6

NULL	0

5	0:5

NULL	0

5	0:5

NULL	0

62	1:2

NULL	0

NULL	0

>> checkIn and checkOut value will be 08:12:25.0000000, i want only the time .
This ([b]08:12:25.0000000[/b] ) is TIME part with format HH:MI:SS:NNNNNNN.... But you want only HH:MI format.. right?

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Cibin, if this helps please login to Mark As Answer. | Alert Moderator

yes, i want it in that format HH:MM .

Cibin, if this helps please login to Mark As Answer. | Alert Moderator

--Check the below query

DECLARE @startDate DATE = '20130701', @EndDate DATE = '20130731' 



SELECT Badgenumber, NAME As Employeename, CAST( MIN(CHECKTIME) AS Date) As Checkindate

	,MIN(CheckTime) AS CheckIn, CheckOut, CHECKTYPE

	,DateDiff(MI,Min(CASE WHEN CHECKTYPE = 'O' THEN CHECKTIME END ),CheckOut)  -- This is just for CheckOut Total Minutes

	,CONVERT(char(5), DATEADD(Minute, DateDiff(MI,Min(CASE WHEN CHECKTYPE = 'I' THEN CHECKTIME END ),CheckOut), '0:00:00'), 108) [TotalInHH:MI]	

	,CONVERT(char(5), DATEADD(Minute, DateDiff(MI,Min(CASE WHEN CHECKTYPE = 'O' THEN CHECKTIME END ),CheckOut), '0:00:00'), 108) [TotalOutHH:MI]  --gives incorrect result if HH exceeds 24

FROM @Log t

OUTER APPLY (SELECT MIN(CheckTime) AS CheckOut

			 FROM @Log

			 WHERE CheckTime > t.CheckTime

			 AND Badgenumber= t.Badgenumber

			 AND NAME = t.NAME 

			 --AND CAST( CHECKTIME AS Date ) = CAST( t.CHECKTIME AS Date )

			 AND CAST( CHECKTIME AS Date ) BEtween @startDate AND @EndDate

			 )t1 

WHERE (CAST( CHECKTIME AS Date ) BEtween @startDate AND @EndDate) 

GROUP BY NAME, Badgenumber, CheckOut, CHECKTYPE 

ORDER BY Checkindate, CheckIn

Explanation:
You can make use of CONVERT ( char(5), ColumnNameofTotalMin, 108) for HH:MI format..... But there is one limitation i.e. if the total number of minutes exceeds 24*60 (means 1 day) the result is remaining hours and minutes....

Badgenumber	Employeename	Checkindate	CheckIn	CheckOut	CHECKTYPE  TotalMinutes TotalOutHH:MI

40214 CIBIN	2013-07-11	2013-07-11 17:11:10 2013-07-14 08:53:44 O	3822  15:42

CheckOut minutes are 3822 means 1 day + 15 hours + 42 Minutes.. But above function will only give HH:MI (15:42)

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Cibin, if this helps please login to Mark As Answer. | Alert Moderator

Hi, Chandu,

I need Only the time in CheckIn and CheckOut field

Cibin, if this helps please login to Mark As Answer. | Alert Moderator

Thanks Very much Chandu

Cibin, if this helps please login to Mark As Answer. | Alert Moderator

You are welcome...
Any way you got some knowledge so that you can help others too........

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Cibin, if this helps please login to Mark As Answer. | Alert Moderator

Ya Sure , Am Very much Happy To help Others , Everyday i'll check for new posts, But almost all of the questions in C# and ASP.net ,,, am not much familiar with that. That's the main problem. If I know the answer surely i'll share with them . Anyway thanks chandu being with me . Thanks ..

Cibin, if this helps please login to Mark As Answer. | Alert Moderator

Hi, Chandu,

Now am facing another Problem that is , the query execution is fine . . But we want to make a small change in the output format ... I'll Show you ....


OUTPUT :

Badgenumber	Employeename	               Checkindate	CheckInTime	CheckOutTime	TotalInHH:MI	TotalOutHH:MI

40214	                CIBIN KANNANAYAKKAL 	2013-08-04                 	11:08	            12:53	                 01:45	NULL

40214          	CIBIN KANNANAYAKKAL 	2013-08-04                  12:53	            13:02	                  NULL	00:09                           //we don't need this full entry 

40214         	CIBIN KANNANAYAKKAL 	2013-08-04              	13:02	            13:27	                  00:25	NULL

40214	                CIBIN KANNANAYAKKAL 	2013-08-04	                13:27	            15:14	                  NULL	01:47                           //we don't need this full entry 

I want the OUTPUT like this :

Badgenumber	Employeename                  	Checkindate	CheckInTime	CheckOutTime	TotalInHH:MI	TotalOutHH:MI

40214	                CIBIN KANNANAYAKKAL 	2013-08-04	                 11:08	              12:53	           01:45	00:09

40214         	CIBIN KANNANAYAKKAL 	2013-08-04                   13:02	              13:27	           00:25	01:47

Hope You understand the correction ?

Can you Help Me Out Please

Cibin, if this helps please login to Mark As Answer. | Alert Moderator

hi cibin, can u tell the requirement in words.. i'm posting via mobile. i can't able to figure out the format

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Cibin, if this helps please login to Mark As Answer. | Alert Moderator

Hi Chandu,

Happy to see you, I'll try to explain, In our Query there is two entries, one for totalIn and other for totalOut, My requirement is There is one entry for both totalout and totalIn.That is we need the result for TotalOut and totalIn From one entry.

Cibin, if this helps please login to Mark As Answer. | Alert Moderator

Hi Cibin,
Highlighted code is the modification.... check and let me know the result....

DECLARE @startDate DATE = '20130701', @EndDate DATE = '20130731' 



SELECT Badgenumber, NAME As Employeename, CAST( MIN(CHECKTIME) AS Date) As Checkindate

	,CONVERT (char(5),MIN(CheckTime), 108) CheckInTime

	,CONVERT (char(5),CheckOut, 108) CheckOutTime 

	,[b]CONVERT(char(5),DATEADD(Minute,DateDiff(MI,Min(CHECKTIME),CheckOut), '00:00'), 108) [TotalInHH:MI][/b]

FROM @Log t

OUTER APPLY (SELECT MIN(CheckTime) AS CheckOut

			 FROM @Log

			 WHERE CheckTime > t.CheckTime

			 AND Badgenumber= t.Badgenumber

			 AND NAME = t.NAME 

			 [b]AND CHECKTYPE = 'O' [/b]

			 --AND CAST( CHECKTIME AS Date ) = CAST( t.CHECKTIME AS Date )

			 AND CAST( CHECKTIME AS Date ) BEtween @startDate AND @EndDate

			 )t1 

WHERE (CAST( CHECKTIME AS Date ) BEtween @startDate AND @EndDate) 

[b]AND CHECKTYPE= 'I'[/b]

GROUP BY NAME, Badgenumber, CheckOut, CHECKTYPE 

ORDER BY Checkindate

Note: In this case you do need [TotalOutHH:MI] column... That's why I have removed that part too

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Cibin, if this helps please login to Mark As Answer. | Alert Moderator

Hi, Chandu,

I need this column too [TotalOutHH:MI] column], you got my desired format. ITt is calculated By : Difference between value of Checkout in First row and value of CheckIn in second Row.This difference gives the value for TotalOut In the first row

Thanks In Advance

Cibin, if this helps please login to Mark As Answer. | Alert Moderator

Thanks Chandu , Thank You Very much, If you don't mind will you please explain this code for me . Use of cte, Use of Outer Apply etc....

Cibin, if this helps please login to Mark As Answer. | Alert Moderator

Thanks Chandu ,

But now I have another doubt, if this code will resolve the error which was occurred from Two consecutive CheckIn s and Checkout s ...

Regards,
Cibin Louis

Cibin, if this helps please login to Mark As Answer. | Alert Moderator

Hi, Chandu

I want also copy these output values to a Table
Regards ,

Cibin Louis

Cibin, if this helps please login to Mark As Answer. | Alert Moderator

Thanks Chandu , Thank you Very much for helping me. Hope will see you soon with another question

Cibin, if this helps please login to Mark As Answer. | Alert Moderator