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 thisID---Date---------TimeIN--------------------TimeOUT1----2012-01-06---2012-01-06 08:54:09.000---2012-01-07 01:15:012----2012-01-06---2012-01-06 14:00:21.000---2012-01-07 01:12:013----2012-01-06---2012-01-06 08:00:21.000---2012-01-06 18:00:014----2012-01-06---2012-01-06 18:20:21.000---2012-01-07 08:00:30i want Spend Time between TimeIn and TimeOUT one thing more to consider is timein Date and Timeout Date some time is not sameResultant Data Should be like thisID--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 possibleThanksRegards, |
|
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 SPENDTIMEFROM YourTable[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
mmkrishna1919
Yak Posting Veteran
95 Posts |
Posted - 2013-04-25 : 09:36:01
|
Hi Visakh,What is the case iftimein='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 spendtimeThanks,M.MURALI kRISHNA |
|
|
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] |
|
|
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 iftimein='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 spendtimeThanks,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 employeesSELECT 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 timediffFROM(SELECT *,DATEDIFF(ss,timeIN,TimeOUT),0) AS TimeDIffInSecFROM YourTable)t ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
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 timediffFROM(SELECT *,DATEDIFF(ss,timeIN,TimeOUT) AS TimeDIffInSecFROM YOURTABLE) T;[/CODE] |
|
|
asif372
Posting Yak Master
100 Posts |
Posted - 2013-04-25 : 16:09:49
|
thanks Every one i also want to group result by ID,Datehow 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 timediffFROM(SELECT *,DATEDIFF(ss,timeIN,TimeOUT) AS TimeDIffInSecFROM YOURTABLEGroup by ID,Date) T;but it didnot worksand give following resultTimeIN' 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. |
|
|
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. |
|
|
asif372
Posting Yak Master
100 Posts |
Posted - 2013-04-25 : 16:53:48
|
My DataID---Date---------TimeIN--------------------TimeOUT1----2012-01-06---2012-01-06 08:15:09.000---2012-01-06 09:15:011----2012-01-06---2012-01-06 10:15:00.000---2012-01-06 11:15:011----2012-01-06---2012-01-06 11:20:00.000---2012-01-07 2:20:01Required DataID--Date--------SPENDTIME--1---2012-01-06--05:00:00---Thanks |
|
|
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 timediffFROM(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. |
|
|
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 timediffFROM(SELECT *,DATEDIFF(ss,timeIN,TimeOUT) AS TimeDIffInSecFROM YOURTABLE) T;[/CODE]
I dont have a sql box here to check. Hence it was untested------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
|
|