Meaning of a sql query [Resolved]

Posted by Cibin under Sql Server on 8/12/2013 | Points: 10 | Views : 1796 | Status : [Member] | Replies : 48
Hi,

I Have a SQL Query for calculating the CheckIn and CheckOut of an Employee.. I want to modify that code when if there is Two Checkouts in database , I want to take the largest value..

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 Up.CHECKTYPE = 'O'
ORDER BY Up.CHECKTIME ASC) UpTime
FROM sample Down
WHERE Down.CHECKTYPE = 'I' ) X
GROUP BY UpTime
ORDER BY UpTime


Thanks In Regards,




Responses

Posted by: Bandi on: 8/13/2013 [Member] [MVP] Platinum | Points: 50

Up
0
Down

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

Posted by: Bandi on: 8/13/2013 [Member] [MVP] Platinum | Points: 50

Up
0
Down

Resolved
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

Posted by: Bandi on: 8/13/2013 [Member] [MVP] Platinum | Points: 50

Up
0
Down

Resolved
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

Posted by: Bandi on: 8/13/2013 [Member] [MVP] Platinum | Points: 50

Up
0
Down

Resolved
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

Posted by: Bandi on: 8/13/2013 [Member] [MVP] Platinum | Points: 50

Up
0
Down

Resolved
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

Posted by: Bandi on: 8/13/2013 [Member] [MVP] Platinum | Points: 50

Up
0
Down

Resolved
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

Posted by: Bandi on: 8/14/2013 [Member] [MVP] Platinum | Points: 50

Up
0
Down

Resolved
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

Posted by: Bandi on: 8/19/2013 [Member] [MVP] Platinum | Points: 50

Up
0
Down

Resolved
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

Posted by: Bandi on: 8/19/2013 [Member] [MVP] Platinum | Points: 50

Up
0
Down

Resolved
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

Posted by: Bandi on: 8/19/2013 [Member] [MVP] Platinum | Points: 50

Up
0
Down

Resolved
>>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

Posted by: Bandi on: 8/12/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
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

Posted by: Bandi on: 8/12/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
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

Posted by: Cibin on: 8/13/2013 [Member] Starter | Points: 25

Up
0
Down

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

Posted by: Cibin on: 8/13/2013 [Member] Starter | Points: 25

Up
0
Down

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

Posted by: Bandi on: 8/13/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
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

Posted by: Cibin on: 8/13/2013 [Member] Starter | Points: 25

Up
0
Down


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

Posted by: Cibin on: 8/13/2013 [Member] Starter | Points: 25

Up
0
Down

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

Posted by: Cibin on: 8/13/2013 [Member] Starter | Points: 25

Up
0
Down

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

Posted by: Bandi on: 8/13/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
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

Posted by: Cibin on: 8/13/2013 [Member] Starter | Points: 25

Up
0
Down

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

Posted by: Cibin on: 8/13/2013 [Member] Starter | Points: 25

Up
0
Down

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

Posted by: Cibin on: 8/13/2013 [Member] Starter | Points: 25

Up
0
Down

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

Posted by: Cibin on: 8/13/2013 [Member] Starter | Points: 25

Up
0
Down

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

Posted by: Bandi on: 8/13/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
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

Posted by: Cibin on: 8/13/2013 [Member] Starter | Points: 25

Up
0
Down

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

Posted by: Bandi on: 8/13/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
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

Posted by: Cibin on: 8/13/2013 [Member] Starter | Points: 25

Up
0
Down
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

Posted by: Cibin on: 8/13/2013 [Member] Starter | Points: 25

Up
0
Down


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

Posted by: Bandi on: 8/13/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
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

Posted by: Cibin on: 8/13/2013 [Member] Starter | Points: 25

Up
0
Down
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

Posted by: Cibin on: 8/13/2013 [Member] Starter | Points: 25

Up
0
Down


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

Posted by: Cibin on: 8/14/2013 [Member] Starter | Points: 25

Up
0
Down

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

Posted by: Bandi on: 8/14/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
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

Posted by: Cibin on: 8/14/2013 [Member] Starter | Points: 25

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

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

Posted by: Bandi on: 8/14/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
--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

Posted by: Cibin on: 8/14/2013 [Member] Starter | Points: 25

Up
0
Down

Hi, Chandu,

I need Only the time in CheckIn and CheckOut field

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

Posted by: Cibin on: 8/14/2013 [Member] Starter | Points: 25

Up
0
Down

Thanks Very much Chandu

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

Posted by: Bandi on: 8/14/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
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

Posted by: Cibin on: 8/14/2013 [Member] Starter | Points: 25

Up
0
Down

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

Posted by: Cibin on: 8/15/2013 [Member] Starter | Points: 25

Up
0
Down

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

Posted by: Bandi on: 8/15/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
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

Posted by: Cibin on: 8/15/2013 [Member] Starter | Points: 25

Up
0
Down

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

Posted by: Bandi on: 8/15/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
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

Posted by: Cibin on: 8/17/2013 [Member] Starter | Points: 25

Up
0
Down

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

Posted by: Cibin on: 8/19/2013 [Member] Starter | Points: 25

Up
0
Down


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

Posted by: Cibin on: 8/19/2013 [Member] Starter | Points: 25

Up
0
Down

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

Posted by: Cibin on: 8/19/2013 [Member] Starter | Points: 25

Up
0
Down

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

Posted by: Cibin on: 8/19/2013 [Member] Starter | Points: 25

Up
0
Down


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

Login to post response