Click here to Skip to main content
15,036,823 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
Hi All,

I have one table and multiple columns my table data like this
tblEmployeeTimeCard

RecID |EmployeeID|clientID |FromTime              |ToTime                 |AutoDetect 
1     |  1       |1        |2013-04-05 08:10:00AM |2013 -04-05 12:00:00PM |1
2     |  1       |1        |2013-04-05 13:00:00PM |2013-04-05 19:10:00PM  |1
3     |  1       |1        |2013-04-05 20:00:00PM |2013-04-05 23:45:00 pM |1


the above rows are single Employee work stats report. my question is
1)how to get the total(SUM) working hours in particular day.
2)Employee works above 6hrs in particular Day then Every 6hr detect the 30 mints form total hours
(i.e- Subtract the detect mints form (sum)total working hrs when autoDetect= 1)

how to write the query for get the total working hours with in the single row.

please help me..

Thanks and Regards
Posted
Updated 20-May-13 10:19am
v2

1 solution

Hi,

check the following Code
SQL
-- Question 1 Answer
SELECT E.EmployeeID,E.Day, (E.TotalTime/60) 'TotalHours', (E.TotalTime%60) 'Minites'
FROM (SELECT EmployeeID,CONVERT(VARCHAR(10),FromTime,102) 'Day',SUM(DATEDIFF(mi,FromTime,ToTime)) 'TotalTime'
      FROM tblEmployeeTimeCard
      GROUP BY EmployeeID,CONVERT(VARCHAR(10),FromTime,102))E

-- Question 1&2 Answer
SELECT E.EmployeeID,E.Day, (E.TotalTime/60) 'TotalHours', (E.TotalTime%60) 'Minites',
CASE WHEN (E.TotalTime/60)>=6 THEN E.TotalTime - (30* ROUND((E.TotalTime/60*6),1))
     ELSE (E.TotalTime/60) END 'TotalWorkHours'
FROM (SELECT EmployeeID,CONVERT(VARCHAR(10),FromTime,102) 'Day',SUM(DATEDIFF(mi,FromTime,ToTime)) 'TotalTime'
      FROM tblEmployeeTimeCard
      GROUP BY EmployeeID,CONVERT(VARCHAR(10),FromTime,102))E


For ur Question 2, I am not getting. give some more details about that. if possible send some sample data. if access over lap like (From date is 2013 -04-05 11:55:00PM to 2013-04-05 19:10:00PM)
then we can't able to fine the time difference. So check the code for Question No 1 and give the feedback.

Regards,
GVPrabu
   
v2
Comments
Maciej Los 20-May-13 16:35pm
   
It should works!
+5
Santhosh23 21-May-13 3:00am
   
hi prabu..
thanks for replay
your query is working fine. thank you so much...
Santhosh23 21-May-13 3:06am
   
my second questions is how to detect the hours from TotalHours. i.e
employee working 14hours in single day. so my question is..
after every 6 hours subtract the 30 mints from TotalHours. employee worked on 14hrs in the Day. so Subtract the 60 mints in total hours. how to write the query for subtract detect hours and get the reaming hours..?
please help me prabhu..
thanks
gvprabu 21-May-13 3:22am
   
ok fine, I ll check and get back to u soon :-)
gvprabu 21-May-13 3:36am
   
hi,
Check my solution, I updated. I can't able to check this bcos in my machine I don't have SQL Server. :-) :-) :-)
Santhosh23 21-May-13 5:05am
   
hi prabhu..
just am modify single line in your query now its working fine..

CASE WHEN (E.TotalTime/60)>=6 THEN E.TotalTime - (30* ROUND((E.TotalTime/60*6),1))
replace with

CASE WHEN (E.TotalTime/60)>=6 THEN E.TotalTime - (30* ROUND(((E.TotalTime/60)/6),1))

than q so much.. prabu..
i have any doubt in sql server query. i will contact with u..

thanks and regards
gvprabu 21-May-13 5:47am
   
yes correct, I missed bracket "()", try this also
CASE WHEN (E.TotalTime/60)>=6 THEN E.TotalTime - (30* ROUND((E.TotalTime/(60*6)),1))
Both also same divide by two numbers or multiple by that number and divide one time.
Ex 12 divide by 2 then divide by 3 = 12/2=6 6/3= Ans :2 same as 12/(2*3) = 12/6 = 2
:-) :-) :-)
Santhosh23 21-May-13 6:55am
   
good logic..
nice..
Santhosh23 24-May-13 5:04am
   
hi prabu..
i want i more query using Case statement using case
above query inner join with anther table then i didn't get any rows. then how to update one table row value. if else after inner join display any rows no need to update query.

how it possible please help me..
thanks and regards


SELECT E.EmployeeID,E.Day, (E.TotalTime/60) 'TotalHours', (E.TotalTime%60) 'Minites',
CASE WHEN (E.TotalTime/60)>=6 THEN E.TotalTime - (30* ROUND((E.TotalTime/60*6),1))
ELSE (E.TotalTime/60) END 'TotalWorkHours'
gvprabu 24-May-13 5:49am
   
If you ll use INNER JOIN, ur Tables have proper data. If table 2 didn't have any records for the join condition then u can't get the records.
gvprabu 24-May-13 5:50am
   
if u ll use more JOIN also not a problem, If ur joining table have data.
Santhosh23 24-May-13 6:54am
   
hi prabhu how to get the days between two dates
my query like this
select ((DATEDIFF(d,2013-01-25,2013-03-25))) but output shows -2, i given yyyy-mm-dd format
what is error. how to get total days
Santhosh23 24-May-13 7:32am
   
ok prabu i solved my problem. am not mention single code. this is error in my query
gvprabu 24-May-13 7:34am
   
Just now I saw, ok good
gvprabu 24-May-13 7:37am
   
Hi,
Check the following link... in Code Project
http://www.codeproject.com/Articles/566542/Date-and-Time-Data-Types-and-Functions-SQL-Server
Like this u ll get so many articles...
Santhosh23 28-May-13 7:54am
   
Hi Prabu..
i have small doubt in sql query. am repeat the while loop in my query. same query executes 10 times. each and every time get some value. how to show total some value(all of some values) or how to sum all some values and display as anther column
please give me any ideas.

thanks and regards
gvprabu 28-May-13 8:46am
   
Hi,

Use Variables, If another column means.... I am not getting your Question.
Santhosh23 28-May-13 9:33am
   
ya i tried declare variables. but not get total some value. another column means. display total some value with in same table.? its possible.?
gvprabu 28-May-13 9:55am
   
shall u use like SELECT @VariableName=ISNULL(@VariableName,0)+SUM(ColumnName) FROM YourQuery

SELECT Column_List, @VariableName
FROM YourQuery
Santhosh23 11-Jun-13 5:35am
   
Hi Prabu.. How r u..?
I have Employee Time sheet table in this table Form-time and to-time and Admin-Approve-on three columns are there. you are given good solution for me. same like that
1) Admin approve 10 records at a time based on employee request different dates. how to get the employee working dates when admin approve on date.
2) suppose am get 5 the 5 different days then how to get the time record for each and every day..?
3) how to get the working hours. above 6 hrs work detect the 30 mints.

please prabu give me any suggestions.?
thanks and regards
gvprabu 11-Jun-13 5:45am
   
For
1) Admin Approve Details, U have any indicator in same table, then we can use that.
2) I am not clear about your question.
3) I am not clear about your question.(This also same like that right)
Santhosh23 11-Jun-13 6:35am
   
ya default value is NULL. when admin approve employee request that time am inserted.
employee send request for admin approval (2013-6-5 & 2013-6-7) but admin approved date is 2013-6-9.
when ever am select the admin approved date ex - 2013-6-9 then shows the working dates. this is question 1

and
2) display the the total working hours day wise. (2013-6-5 working 7hours) (2013-6-5 working 5 hours)
this 2 query example

3)now detect the 30 mints every 6 hours day wise.
gvprabu 11-Jun-13 6:43am
   
So Qn 2,3...
Date Work Hours Accrual work Hours
2013-06-10 8 7.5
Right
Santhosh23 11-Jun-13 6:45am
   
yup excalty.. prabu...
Santhosh23 11-Jun-13 6:46am
   
did you get the question 1.. idea..?
gvprabu 11-Jun-13 6:51am
   
not yet.... :-)
Santhosh23 11-Jun-13 6:54am
   
am passing the admin approve date then display the employee working days or request days.
gvprabu 11-Jun-13 7:07am
   
so in that same query u need this all out put or this is different query
gvprabu 11-Jun-13 7:07am
   
if its different query means u ll use that condition in case Statement
Santhosh23 11-Jun-13 7:12am
   
yup prabu.. i tried with sub query's. but i got the error like
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
gvprabu 11-Jun-13 7:15am
   
yes , Subquery it will work for one values while u ll use <,>,<=,>=,
So just try with 'IN' Operator.
Send ur Query ... what u tried.?
Santhosh23 11-Jun-13 7:20am
   
Declare @Days datetime
Begin


if((select Distinct DATEADD(dd, 0, DATEDIFF(dd, 0,SubmittedOn)) as WorkingDate
--SubmittedOn
from dbo.tblEmployeeTimeCards where
EmployeeID=71 and ClientID=20
and convert(Datetime,convert(char,AdminApporveOn,20),20) between
convert(Datetime,convert(char,'6-7-2013 7:00:00',20),20) AND convert(Datetime,convert(char,'6-21-2013 6:59:00',20),20)) is Not null)
Begin


END


END

just am tried in normal way.. please check my query
Santhosh23 11-Jun-13 7:22am
   
if(@Mode=5) -- Get Total hours In Particular day with employee and ClientID (Client)
BEGIN
DECLARE @FromDate4 DateTime
DECLARE @RegHrs Int
SET @FromDate1 = @From
WHILE @FromDate1 <= @To
BEGIN
IF((select IsDeduct30minforevery6hours from dbo.tblEmployeeWagesFromClient where EmployeeID=@EmployeeID AND EmployerID=@ClientID)=0)
Begin



SELECT DISTINCT EmployeeName,E.EmployeeID,E.Wage, ClientID, (TotalTime/60) 'TotalHours', (TotalTime%60) 'Minites',((TotalTime/60)*60+(TotalTime%60) ) 'TotalMints',
((TotalTime/60)*60+(TotalTime%60) )'Remaing Mintus',
(Convert(varchar,((E.TotalTime - (30* ROUND(((E.TotalTime/60)/6),2)))/60)))+':'+(convert(varchar,((E.TotalTime - (30* ROUND(((E.TotalTime/60)/6),2)))%60))) 'Display',
(((E.TotalTime/60)*60+(E.TotalTime%60))*((E.Wage)/60)) 'PayPeriod',
CASE WHEN (TotalTime='' or Totaltime=0) THEN
(0)
ELSE (0) END 'Detect Mintus'

FROM (
SELECT DISTINCT Emp.EMPLOYEE_FIRSTNAME+' '+Emp.EMPLOYEE_LASTNAME 'EmployeeName',
CONVERT(decimal,EWC.ForHourRate) 'Wage', ETC.EmployeeID,ClientID,SUM(DATEDIFF(mi,FromTime,ToTime)) 'TotalTime'
FROM tblEmployeeTimeCards ETC INNER jOIN
EMPLOYEE_DETAILS Emp on Emp.EMPLOYEE_ID= ETC.EmployeeID inner join
tblEmployeeWagesFromClient EWC on EWC.EmployeeID=ETC.EmployeeID and EWC.EmployerID=ETC.ClientID

where ETC.EmployeeID=@EmployeeID and ETC.ClientID=@ClientID and
convert(Datetime,convert(char(10),ETC.AdminApporveOn,103),103) between convert(Datetime,convert(char(10),@From,103),103) AND convert(Datetime,convert(char(10),@To,103),103)
and
CONVERT(datetime, CONVERT(VARCHAR(10),etC.AdminApporveOn, 112)) = DATEADD(dd, 0, DATEDIFF(dd, 0,@FromDate1))
and ETC.IsAdminApporve=1 and ETC.AdminApporveOn is not null

GROUP BY ETC.EmployeeID,ETC.clientID,Emp.EMPLOYEE_FIRSTNAME,Emp.EMPLOYEE_LASTNAME,EWC.ForHourRate )E
WHERE E.EmployeeID=@EmployeeID and ClientID=@ClientID
SET @FromDate1 =DATEADD(d,1,@FromDate1)

END
ELSE
BEGIN


SELECT DISTINCT EmployeeName, E.EmployeeID,ClientID,Wage, (TotalTime/60) 'TotalHours', (TotalTime%60) 'Minites',((TotalTime/60)*60+(TotalTime%60) ) 'TotalMints',

CASE WHEN (E.TotalTime/60)>=6 THEN E.TotalTime - (30* ROUND(((E.TotalTime/60)/6),1))
ELSE ((TotalTime/60)*60+(TotalTime%60) ) END 'Remaing Mintus',
CASE WHEN (E.TotalTime/60)>=6 THEN (30* ROUND(((E.TotalTime/60)/6),1))
ELSE 0 END 'Detect Mintus',

CASE WHEN (E.TotalTime/60)>=6 THEN (Convert(varchar,((E.TotalTime - (30* ROUND(((E.TotalTime/60)/6),2)))/60)))+':'+(convert(varchar,((E.TotalTime - (30* ROUND(((E.TotalTime/60)/6),2)))%60)))
ELSE (Convert(varchar,((E.TotalTime - (30* ROUND(((E.TotalTime/60)/6),2)))/60)))+':'+(convert(varchar,((E.TotalTime - (30* ROUND(((E.TotalTime/60)/6),2)))%60))) END 'Display',
CASE WHEN ((E.Wage != 0 Or E.Wage !='') and (E.TotalTime/60)>=6 )
THEN (E.TotalTime - (30* ROUND(((E.TotalTime/60)/6),1)))*((Wage)/60)
ELSE (((E.TotalTime/60)*60+(E.TotalTime%60))*((Wage)/60) ) END 'PayPeriod'
FROM (
SELECT DISTINCT Emp.EMPLOYEE_FIRSTNAME+' '+Emp.EMPLOYEE_LASTNAME 'EmployeeName',
CONVERT(decimal,EWC.ForHourRate) 'Wage', ETC.EmployeeID,ClientID,SUM(DATEDIFF(mi,FromTime,ToTime)) 'TotalTime'
FROM tblEmployeeTimeCards ETC inner join
EMPLOYEE_DETAILS Emp on Emp.EMPLOYEE_ID= ETC.EmployeeID inner join
tblEmployeeWagesFromClient EWC on EWC.EmployeeID=ETC.EmployeeID and EWC.EmployerID=ETC.ClientID

where ETC.EmployeeID=@EmployeeID and ETC.ClientID=@ClientID and
CONVERT(datetime, CONVERT(VARCHAR(10),etC.AdminApporveOn, 112)) = DATEADD(dd, 0, DATEDIFF(dd, 0,@FromDate1))
AND
convert(Datetime,convert(char(10),ETC.AdminApporveOn,103),103) between convert(Datetime,convert(char(10),@From,103),103) AND convert(Datetime,convert(char(10),@To,103),103)
and IsAdminApporve=1 and AdminApporveOn is not null
GROUP BY ETC.EmployeeID,ETC.clientID,Emp.EMPLOYEE_FIRS
gvprabu 11-Jun-13 7:30am
   
Hi frieend,
I am not able to understand, because all the scrips in single line.
Pls make some alignment and send to me
Santhosh23 11-Jun-13 7:24am
   
its my original query..
in mode 5 am get the total working hours admin approved date.
gvprabu 11-Jun-13 7:27am
   
U need to check... if that query have any values then do some process else some Process right. For this u can use EXISTS as like below.
Declare @Days datetime
Begin
if EXISTS (select Distinct DATEADD(dd, 0, DATEDIFF(dd, 0,SubmittedOn)) as WorkingDate --SubmittedOn
from dbo.tblEmployeeTimeCards
where EmployeeID=71 and ClientID=20
and convert(Datetime,convert(char,AdminApporveOn,20),20) between convert(Datetime,convert(char,'6-7-2013 7:00:00',20),20) AND convert(Datetime,convert(char,'6-21-2013 6:59:00',20),20)
)
BEGIN
-- Your SQL Code
END

END
Santhosh23 11-Jun-13 7:38am
   
i get the 4 days in sub query how to write query for get the total hours per day wise.
how to repeat the loop.
gvprabu 11-Jun-13 7:59am
   
I am not getting u. Sub Query means
date Hours
2013-06-10 6
2013-06-10 2 ...
Like this right
Santhosh23 11-Jun-13 8:54am
   
if condition query get 4days. how to use 4days(if condition days). how to repeat the loop and get the total hours in particular day wise (above 4 days)
Santhosh23 11-Jun-13 9:13am
   
my queries are..
1) First get the Employee Working days between admin approve on days.
2) Second get the total hours for based on above days.
3) detect the hours more than 6 hours.

its possible for single query above requirements.?
gvprabu 11-Jun-13 10:00am
   
1) Is not a problem right
2) Make GROUP BY Empid and Day then find the Day wise Total work hours.
3) use CASE Statement and do the 6 hrs calculation
gvprabu 11-Jun-13 10:01am
   
sorry yar.. I don't have SQL server in my machine. So I can't able to check any query with sample data... If possible send the Table script with data
Santhosh23 11-Jun-13 11:19am
   
give me your email id i will send the table script data
gvprabu 11-Jun-13 11:21am
   
venkateshprabu.g@gmail.com
Santhosh23 12-Jun-13 2:03am
   
check your mail and See above Mode 5 stored procedure
Santhosh23 2-Jul-13 7:46am
   
hi prabu.. how r u.?

how to get the top 10 records from two tables. order by last-modification desc .

its possible..?
please help me prabhu..
thanks and regards
gvprabu 2-Jul-13 10:06am
   
Last Modification Desc means, give some sample. Its datetime or number value.?

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900