June 1, 2013 at 12:56 am
i have a problem kindly help me out.
this type of data i want:
Date------------Time in----Time out-----Spent time-------excess/short
2013-01-01-------09:14-----19:06-----------09:52-------------00:52
2013-01-02-------09:52-----18:36-----------08:44-------------00:16
2013-01-03-------09:15-----18:56-----------09:41-------------00:41
excess short comes from spent time mean a employee spent 9 hours 52 minute then its excess 00:52 minutes and if employee spent 8 hours 44 minutes then its short 00:16 minutes we have 9 to 6 hours timing shift.
immad
June 1, 2013 at 2:18 am
Hi!
Happy to help on this one however, in order to do this, can you provide details of the columns in the table(s) containing the data you wish to process.
There is an excellent article on how to do this provided by Jeff Moden which you can find here http://www.sqlservercentral.com/articles/Best+Practices/61537/
Kind regards,
James
June 1, 2013 at 2:26 am
i have this type of table
CREATE TABLE attendance
(
Date datetime,
Timein datetime,
Timeout datetime,
Spend nvarchar(50),
excessshort nvarchar(50)
)
my data look like this.
Date
2013-01-01 00:00:00.000
Timein
2013-01-01 09:14:00.000
Timeout
2013-01-01 19:06:00.000
SpendTime
09:52:00
excess/short
?
i want to calculate excesshort
excess short comes from spent time mean a employee spent 9 hours 52 minute then its excess 00:52 minutes and if employee spent 8 hours 44 minutes then its short 00:16 minutes we have 9 to 6 hours timing shift.
Thanks For Helping me
immad
June 1, 2013 at 3:03 am
Hi!
Hope this provides some help with your query. Comments are in the SQL
James
-- Used a temporary table for demonstration purposes
CREATE TABLE #attendance
(
CalDate datetime,-- Changed name slightly to avoid using DATE as a name as it is a type
Timein datetime,
Timeout datetime,
Spend nvarchar(50),
excessshort nvarchar(50)
)
-- Sample data as per your posting
insert into #attendance Values ( '2013-01-01 00:00:00.000', '2013-01-01 09:14:00.000', '2013-01-01 19:06:00.000', '09:52:00' , NULL )
insert into #attendance Values ( '2013-01-01 00:00:00.000', '2013-01-01 09:52:00.000', '2013-01-01 18:36:00.000', '08:44:00' , NULL )
insert into #attendance Values ( '2013-01-01 00:00:00.000', '2013-01-01 09:15:00.000', '2013-01-01 18:56:00.000', '09:41:00' , NULL )
-- Note: 540 is 9hrs in minutes, the code below converts the result to a varchar as per your table. I used the
-- Timein/TimeOut simply to avoid having to recast the 'spend' from nvarchar to a format datediff could use.
-- Answer is written back to the table as an update (it does not check to see if the calculation had alread
-- been done but this would be a simple modification.
update
#attendance
set
excessshort =
CAST ( ABS( 540 - DATEDIFF (MINUTE, Timein, timeout ) ) / 60 as varchar ) + ':' +-- Hours ( + ':' separator )
CAST ( ABS( 540 - DATEDIFF (MINUTE, Timein, timeout ) ) % 60 as varchar )-- Minutes
from
#attendance
-- Display the result
select * from #attendance
drop table #attendance
June 1, 2013 at 4:40 am
Thank you for your help your query is working fine
and thanks for explaining me.
please can u tell me one thing
this is my spendtime query
SELECT CalDate,[Timein],[Timeout],
CONVERT(varchar(8),DATEADD(ss,DATEDIFF(ss,[Timein],[Timeout]),0),108) AS SpendTime
FROM attendance
how i put this query in excess short qurey that u give me
immad
June 1, 2013 at 6:08 am
Hi!
Just off out for a bit - will look into a reply for you later. One thing, do you wish the query to set the spend time in the table as was the case before or just calculate & display it?
James
June 1, 2013 at 7:53 am
hi
Well my senior developer tell me that just calculate spend time and excess short and show it into report.for right now he didnt tell me to insert it.
immad
June 1, 2013 at 2:54 pm
Hi!
Three solutions which I hope cover all possibilities you may need.
James
-- Used a temporary table for demonstration purposes
CREATE TABLE #attendance
(
CalDate datetime,-- Changed name slightly to avoid using DATE as a name as it is a type
Timein datetime,
Timeout datetime,
Spend nvarchar(50),
excessshort nvarchar(50)
)
-- Sample data as per your posting
insert into #attendance Values ( '2013-01-01 00:00:00.000', '2013-01-01 09:14:00.000', '2013-01-01 19:06:00.000', '09:52:00' , NULL )
insert into #attendance Values ( '2013-01-01 00:00:00.000', '2013-01-01 09:52:00.000', '2013-01-01 18:36:00.000', '08:44:00' , NULL )
insert into #attendance Values ( '2013-01-01 00:00:00.000', '2013-01-01 09:15:00.000', '2013-01-01 18:56:00.000', '09:41:00' , NULL )
-- There are three possibilities as far as I can see depending on what you wish to do
--(1) Calulate and display from a select using time in, timeout only
--(2) Generate and store the data and then display if from a select statement.
--(3) Update and display at the same time (using the 'OUTPUT' command) but I suspect this is not what you seek!
--
-- I have provided both below (I have slightly changed the column names on output to help in distinguishing which code
-- produced each set).
-- Note: 540 is 9hrs in minutes, the code below converts the result to a varchar as per your table. I used the
-- Timein/TimeOut simply to avoid having to recast the 'spend' from nvarchar to a format datediff could use.
-- Answer is written back to the table as an update (it does not check to see if the calculation had alread
-- been done but this would be a simple modification.
-- (1) Calculate at query time (so does not make use of 'spend' not 'excessshort')
select
CalDate,
Timein,
Timeout ,
CONVERT(varchar(8),DATEADD(ss,DATEDIFF(ss,[Timein],[Timeout]),0),108) AS SpendTime_runtime,
CAST ( ABS( 540 - DATEDIFF (MINUTE, Timein, timeout ) ) / 60 as varchar ) + ':' +-- Hours ( + ':' separator )
CAST ( ABS( 540 - DATEDIFF (MINUTE, Timein, timeout ) ) % 60 as varchar ) as excesstime_runtime
from
#attendance
-- (2) This will calculate and store 'excessshort' & 'SpendTime' in the database so you can select it later
update
#attendance
set
excessshort =
CAST ( ABS( 540 - DATEDIFF (MINUTE, Timein, timeout ) ) / 60 as varchar ) + ':' +-- Hours ( + ':' separator )
CAST ( ABS( 540 - DATEDIFF (MINUTE, Timein, timeout ) ) % 60 as varchar )-- Minutes
,-- Only here so visible
Spend =
CONVERT(varchar(8),DATEADD(ss,DATEDIFF(ss,[Timein],[Timeout]),0),108)
from
#attendance
-- Display the result
select * from #attendance
-- (3) This will calculate and store 'excessshort' & 'SpendTime' in the database. It uses the OUTPUT which
-- displays the changed data so you can see it too (an update is a DELETE followed by an INSERT)
update
#attendance
set
excessshort =
CAST ( ABS( 540 - DATEDIFF (MINUTE, Timein, timeout ) ) / 60 as varchar ) + ':' +-- Hours ( + ':' separator )
CAST ( ABS( 540 - DATEDIFF (MINUTE, Timein, timeout ) ) % 60 as varchar )-- Minutes
,-- Only here so visible
Spend =
CONVERT(varchar(8),DATEADD(ss,DATEDIFF(ss,[Timein],[Timeout]),0),108)
output
inserted.CalDate,
inserted.Timein ,
inserted.Timeout,
inserted.Spend as spend_inserted,
inserted.excessshort as excessshort_inserted-- Shows the changed data
from
#attendance
drop table #attendance
June 2, 2013 at 11:09 pm
Thank You Sir For Helping me.
these query are very accurate for me this is what i want.
Thank You Again
immad
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply