SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Difference between Times
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

asif372
Posting Yak Master

Pakistan
100 Posts

Posted - 04/25/2013 :  08:27:34  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 04/25/2013 :  08:37:27  Show Profile  Reply with Quote

SELECT [Date],TimeIN,TimeOUT,
CONVERT(varchar(8),DATEADD(ss,DATEDIFF(ss,timeIN,TimeOUT),0),108) AS SPENDTIME
FROM YourTable


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

mmkrishna1919
Yak Posting Veteran

India
91 Posts

Posted - 04/25/2013 :  09:36:01  Show Profile  Reply with Quote
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

Edited by - mmkrishna1919 on 04/25/2013 09:44:42
Go to Top of Page

MuMu88
Aged Yak Warrior

547 Posts

Posted - 04/25/2013 :  10:20:52  Show Profile  Reply with Quote
Murali Krishna,
Here is your code modified to make the output look pretty

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;
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 04/25/2013 :  10:52:14  Show Profile  Reply with Quote
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

547 Posts

Posted - 04/25/2013 :  13:52:20  Show Profile  Reply with Quote
Visakh, your latest query doesn't work...mostly syntactical problems...


Here is a modified query that does work (with better output format...)

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;


Edited by - MuMu88 on 04/25/2013 14:21:26
Go to Top of Page

asif372
Posting Yak Master

Pakistan
100 Posts

Posted - 04/25/2013 :  16:09:49  Show Profile  Reply with Quote
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

547 Posts

Posted - 04/25/2013 :  16:20:49  Show Profile  Reply with Quote
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

Pakistan
100 Posts

Posted - 04/25/2013 :  16:53:48  Show Profile  Reply with Quote
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

547 Posts

Posted - 04/25/2013 :  21:05:37  Show Profile  Reply with Quote
This should give you what you are looking for:



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;



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

India
52317 Posts

Posted - 04/26/2013 :  00:15:12  Show Profile  Reply with Quote
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...)

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;




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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000