Time calculation - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Time calculation

Author

Topic

amodi
Yak Posting Veteran

83 Posts

Posted - 2009-07-06 : 05:06:21

Hello Friends,
I have a table named "UserTimeInOut" with the following columns:
EmpId,
LoginTime,
LogoutTime,
DepartmentId,
diffSeconds, -- Deifference between LoginTime and LogoutTime by using
(DateDiff(second, @LoginTime, @LogoutTime))

I want to write a stored procedure that take three input parameters( say @DepartmentId, @FromDate and ToDate) and return the list of Employees in a Department(@DepartmentId), with total time for each Employee

Data in the table is as follows:
EmpId--LoginTime---------------LogoutTime-------------DepartmentId---diffSeconds
2400----6/8/2009 11:00:00 AM----6/8/2009 12:09:11 PM----1----------------4151
2400----6/9/2009 10:17:55 AM----6/9/2009 11:09:11 AM----1----------------3076
2411----6/8/2009 07:07:55 AM----6/8/2009 12:59:11 PM----1----------------21076
2411----6/9/2009 11:07:55 AM----6/9/2009 11:09:11 AM----1----------------76
2400----6/12/2009 09:07:55 AM---6/12/2009 11:49:11 AM---1----------------9676

I want output to be:
EmpId---TotalTime
2400-----04:41:43
2411-----05:52:32

Thanks.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-06 : 05:11:13

SELECT EmpID, CONVERT(CHAR(8), DATEADD(SECOND, SUM(DATEDIFF(SECOND, LoginTime, LogoutTime)), 0), 108)
FROM UserTimeInOut
GROUP BY EmpID

Microsoft SQL Server MVP

N 56°04'39.26"
E 12°55'05.63"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-06 : 05:20:56

Also see
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=107275

Microsoft SQL Server MVP

N 56°04'39.26"
E 12°55'05.63"

amodi
Yak Posting Veteran

83 Posts

Posted - 2009-07-06 : 05:23:21

Thanks Peso.

amodi
Yak Posting Veteran

83 Posts

Posted - 2009-07-06 : 05:56:53

Hello Peso.

I want the result between date range. How to do that?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-06 : 05:59:44

[code]
WHERE LoginTime >= @FromDate
AND LogoutTime <= @ToDate
[/code]

KH
[spoiler]Time is always against us[/spoiler]

amodi
Yak Posting Veteran

83 Posts

Posted - 2009-07-06 : 06:08:39

Thanks khtan.

amodi
Yak Posting Veteran

83 Posts

Posted - 2009-08-03 : 07:31:10

Hello friends,

The below query is working absolutely fine:

SELECT EmpID, CONVERT(CHAR(8), DATEADD(SECOND, SUM(DATEDIFF(SECOND, LoginTime, LogoutTime)), 0), 108)
FROM LogIns
GROUP BY EmpID

but when i want the selection between date range. as follows, its not working:

SELECT EmpID, CONVERT(CHAR(8), DATEADD(SECOND, SUM(DATEDIFF(SECOND, LoginTime, LogoutTime)), 0), 108)
FROM LogIns
WHERE LoginTime between 06-22-2009 and 07-18-2009
GROUP BY EmpID

its not showing even a single record.

Note: LoginTime is of type datetime contains records as follows:
22-06-2009 10:42:02 AM
22-06-2009 10:48:52 AM
22-06-2009 12:35:13 PM
22-06-2009 12:36:18 PM
22-06-2009 12:38:15 PM

Mangal Pardeshi
Posting Yak Master

110 Posts

Posted - 2009-08-03 : 07:56:05

pass the dates in language independant format, and see whther thts u help u or not
WHERE LoginTime >='20090622' and loginformat < '20090719'

see http://www.karaszi.com/SQLServer/info_datetime.asp

Mangal Pardeshi
http://mangalpardeshi.blogspot.com

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-08-03 : 07:59:03

[code]
SELECT EmpID, CONVERT(CHAR(8), DATEADD(SECOND, SUM(DATEDIFF(SECOND, LoginTime, LogoutTime)), 0), 108)
FROM LogIns
WHERE LoginTime between 06-22-2009 and 07-18-2009
GROUP BY EmpID [/code]

That is not a date, but integer subtraction 6 minus 22 minus 2009.

Specify the date in ISO format YYYYMMDD and enclosed in single quote as what Mangal as suggested

KH
[spoiler]Time is always against us[/spoiler]

Mangal Pardeshi
Posting Yak Master

110 Posts

Posted - 2009-08-03 : 08:08:32

I also forget to add this, be careful with these date calculations and BETWEEN

If you are looking for getting the rows from 07-18-2009 also then your condition should look like this

WHERE LoginTime >='20090622' and loginformat <= '20090718 23:59:59 997'

or add one day and do LESS THAN
WHERE LoginTime >='20090622' and loginformat < '20090719'

And whenever possible avoid using BETWEEN, instead use >= AND < operators

Mangal Pardeshi
http://mangalpardeshi.blogspot.com

amodi
Yak Posting Veteran

83 Posts

Posted - 2009-08-03 : 08:25:29

Thank you sir, i will try that and get back to you(both).

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources