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
 Difference between Times

Author  Topic 

asif372
Posting Yak Master

100 Posts

Posted - 2013-04-25 : 08:27:34
I am working on Attendance System My Data is Like this

ID---Date---------TimeIN--------------------TimeOUT
1----2012-01-06---2012-01-06 08:54:09.000---2012-01-07 01:15:01
2----2012-01-06---2012-01-06 14:00:21.000---2012-01-07 01:12:01
3----2012-01-06---2012-01-06 08:00:21.000---2012-01-06 18:00:01
4----2012-01-06---2012-01-06 18:20:21.000---2012-01-07 08:00:30

i want Spend Time between TimeIn and TimeOUT one thing more to consider is timein Date and Timeout Date some time is not same
Resultant Data Should be like this
ID--Date--------TimeIN---------------TimeOUT--------------SPENDTIME---
1---2012-01-06--2012-01-06 08:54:09--2012-01-07 01:15:01--16:39:08---
2---2012-01-06--2012-01-06 14:00:21--2012-01-07 01:00:21--11:00:00---
3---2012-01-06--2012-01-06 08:00:21--2012-01-06 18:00:01--10:00:20---
4---2012-01-06--2012-01-06 18:20:21--2012-01-07 08:20:30--14:00:09---

how can it be possible
Thanks
Regards,

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-04-25 : 08:37:27
[code]
SELECT [Date],TimeIN,TimeOUT,
CONVERT(varchar(8),DATEADD(ss,DATEDIFF(ss,timeIN,TimeOUT),0),108) AS SPENDTIME
FROM YourTable
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

mmkrishna1919
Yak Posting Veteran

95 Posts

Posted - 2013-04-25 : 09:36:01
Hi Visakh,

What is the case if
timein='2012-01-06 08:54:09'
timeout='2012-01-07 08:54:09'

Then Spend time should be 24:00:00,But the below query is showing 00:00:00 as spend time may be because of it will increase the day.

we can write like this for spendtime.

SELECT cast(((DATEDIFF(ss,@timeIN,@TimeOUT))/3600) as varchar)+':'+
cast((DATEDIFF(ss,@timeIN,@TimeOUT)%3600)/60 as varchar)+':'+
cast((DATEDIFF(ss,@timeIN,@TimeOUT)%3600)%60 as varchar) AS spendtime

Thanks,


M.MURALI kRISHNA
Go to Top of Page

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-04-25 : 10:20:52
Murali Krishna,
Here is your code modified to make the output look pretty
[CODE]
SELECT RIGHT('0' + cast(((DATEDIFF(ss,timeIN,TimeOUT))/3600) as varchar), 2)+':'+
RIGHT('0' + cast((DATEDIFF(ss,timeIN,TimeOUT)%3600)/60 as varchar), 2)+':'+
RIGHT('0' + cast((DATEDIFF(ss,timeIN,TimeOUT)%3600)%60 as varchar), 2) AS spendtime from #tmp1;
[/CODE]
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-04-25 : 10:52:14
quote:
Originally posted by mmkrishna1919

Hi Visakh,

What is the case if
timein='2012-01-06 08:54:09'
timeout='2012-01-07 08:54:09'

Then Spend time should be 24:00:00,But the below query is showing 00:00:00 as spend time may be because of it will increase the day.

we can write like this for spendtime.

SELECT cast(((DATEDIFF(ss,@timeIN,@TimeOUT))/3600) as varchar)+':'+
cast((DATEDIFF(ss,@timeIN,@TimeOUT)%3600)/60 as varchar)+':'+
cast((DATEDIFF(ss,@timeIN,@TimeOUT)%3600)%60 as varchar) AS spendtime

Thanks,


M.MURALI kRISHNA


you mean you've people working for more than 24 hrs?

anyways you can do this in case you've such workaholic employees


SELECT ID,[Date],TimeIN,TimeOUT,
CONVERT(varchar(4),DATEDIFF(dd,0,DATEADD(ss,TimeDiffInSec,0)) * 24 + (TimeDIffInSec/3600) + ':' + RIGHT(CONVERT(varchar(8),DATEADD(ss,TimeDIffInSec,0),108),5) AS timediff
FROM
(
SELECT *,DATEDIFF(ss,timeIN,TimeOUT),0) AS TimeDIffInSec
FROM YourTable
)t


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-04-25 : 13:52:20
Visakh, your latest query doesn't work...mostly syntactical problems...


Here is a modified query that does work (with better output format...)
[CODE]
SELECT ID, [date], TimeIN,TimeOUT,
RIGHT('0' + CONVERT(varchar(4),DATEDIFF(dd,0,DATEADD(ss,TimeDiffInSec,0)) * 24 + (TimeDIffInSec/3600)), 2) + ':' +
RIGHT(CONVERT(varchar(8),DATEADD(ss,TimeDIffInSec,0),108),5) AS timediff
FROM
(
SELECT *,DATEDIFF(ss,timeIN,TimeOUT) AS TimeDIffInSec
FROM YOURTABLE
) T;

[/CODE]
Go to Top of Page

asif372
Posting Yak Master

100 Posts

Posted - 2013-04-25 : 16:09:49
thanks Every one
i also want to group result by ID,Date
how can it will be possible

SELECT ID, [date], TimeIN,TimeOUT,
RIGHT('0' + CONVERT(varchar(4),DATEDIFF(dd,0,DATEADD(ss,TimeDiffInSec,0)) * 24 + (TimeDIffInSec/3600)), 2) + ':' +
RIGHT(CONVERT(varchar(8),DATEADD(ss,TimeDIffInSec,0),108),5) AS timediff
FROM
(
SELECT *,DATEDIFF(ss,timeIN,TimeOUT) AS TimeDIffInSec
FROM YOURTABLE
Group by ID,Date
) T;
but it didnot works
and give following result
TimeIN' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

TimeOUT' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Go to Top of Page

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-04-25 : 16:20:49
What do you want the output to be?
Can you show some data?
You should be getting what you originally posted.
Go to Top of Page

asif372
Posting Yak Master

100 Posts

Posted - 2013-04-25 : 16:53:48
My Data
ID---Date---------TimeIN--------------------TimeOUT
1----2012-01-06---2012-01-06 08:15:09.000---2012-01-06 09:15:01
1----2012-01-06---2012-01-06 10:15:00.000---2012-01-06 11:15:01
1----2012-01-06---2012-01-06 11:20:00.000---2012-01-07 2:20:01

Required Data

ID--Date--------SPENDTIME--
1---2012-01-06--05:00:00---

Thanks
Go to Top of Page

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-04-25 : 21:05:37
This should give you what you are looking for:

[CODE]

SELECT ID, [Date],
RIGHT('0' + CONVERT(varchar(4),DATEDIFF(dd,0,DATEADD(ss,TimeDiffInSec,0)) * 24 + (TimeDIffInSec/3600)), 2) + ':' +
RIGHT(CONVERT(varchar(8),DATEADD(ss,TimeDIffInSec,0),108),5) AS timediff
FROM
(SELECT ID, [Date], SUM(DATEDIFF(ss,timeIN,TimeOUT)) as TimeDiffInSec from #tmp1 group by ID, [Date]) as T order by ID;

[/CODE]

BTW I am not a big fan of naming columns with reserved words. For clarity you may want to call the Date column something else more descriptive in your environment.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-04-26 : 00:15:12
quote:
Originally posted by MuMu88

Visakh, your latest query doesn't work...mostly syntactical problems...


Here is a modified query that does work (with better output format...)
[CODE]
SELECT ID, [date], TimeIN,TimeOUT,
RIGHT('0' + CONVERT(varchar(4),DATEDIFF(dd,0,DATEADD(ss,TimeDiffInSec,0)) * 24 + (TimeDIffInSec/3600)), 2) + ':' +
RIGHT(CONVERT(varchar(8),DATEADD(ss,TimeDIffInSec,0),108),5) AS timediff
FROM
(
SELECT *,DATEDIFF(ss,timeIN,TimeOUT) AS TimeDIffInSec
FROM YOURTABLE
) T;

[/CODE]


I dont have a sql box here to check. Hence it was untested

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
   

- Advertisement -