multiplying time value 1

[jshanoo]

Hi All,
I need some output like
lemme explain

an employee min work hours are 8 hrs.
like that 40 employee.
i want the total work hours by multiplying both the fields.

Can any one please help me.

Regards
John PHilip




*** Even the Best, did the Bad and Made the Best ***

John Philip

 

[dbomrrsm]

Can you post a sample of the date and explain if you need to update a field for each employee or do you want the total hours worked by all employees

 

[jshanoo]

Hi,
following is my sql query '08:00:00 is static, 42 will vary means a filed will come there.

---------------------
Select convert(varchar,A.currdatetime,101) as CallDate, datename(dw,A.currdatetime) as weekday,
Fulliro as FullTime, PartIro as PartTime,(Fulliro+partiro) as TotalIro,(cast('08:00:00' as datetime) * 42) as a1,
incnt as Landed,anscalls as Sucess,abancalls as Abandoned,failcalls as Failure
from agents A,vwacdgrp B
where A.currdatetime=B.acddt and
(A.currdatetime between '04/01/2004' and '04/04/2004')
order by A.currdatetime


Best Regards
John Philip

*** Even the Best, did the Bad and Made the Best ***

John Philip

 

[dbomrrsm]

Still not sure if I fully understand you but if the '08:00:00' is constant and is the number of hours a person works (and it doesnt need to be calculated from anywhere else instead of
(cast('08:00:00' as datetime) * 42) as a1 Do
8*42 as a1

From what you say though the 42 comes from elsewhere - if it is the total number of employees then you can do

8* SELECT COUNT(EMPLOYEEid) FROM EMPLOYEE_TABLE

If the employee table or wherever you get it from could contain duplicate employee id's do

8* SELECT COUNT(DISTINCT (EMPLOYEEid))
FROM EMPLOYEE_TABLE

hope this is of some help

DBomrrsm

 

[dbomrrsm]

when you do

select cast('08:00:00' as datetime)

you Get

1900-01-01 08:00:00.000

Not what you want - I think !!!!

DBomrrsm

 

[jshanoo]

Hi all,
Sorry all well i think i was not clear with my post.
Let me explain the situation.

there are employess full time & part time
Full time work for 8 hrs and part timers work for 4:45 mts

at any given point if i want to get the total man hours for the given day .
i have calculate like teh following

08:00:00 * no. of employees
04:45:00 * no. of employees
total of this will give man hours of the day.

this is we do in excel.

I want to put in sql and see this..

Please give me ur valid advice

regards
John Philip





*** Even the Best, did the Bad and Made the Best ***

John Philip

 

[SQLSister]

First if you are calculating from an amount of time, 8 hours) vice from a specific date and time (8:00 am today), Do not use a datetime field.

Determine which interval of time is the minimum you need to show (hours and minutes or hours minutes seconds)
create a numeric field (int or big int will probably be best unless you want to use decimals, but few people really think in decimal time but if you are going to divide the number use a decimal datatype for better results). When you input the data have the GUI convert hours and minutes to minutes and send that figure to the the database rather than 8:25 as 8 hours and 25 minutes.

Then when you want to do math on this figure, you simply sum it or avg it or whatever like any other numeric field.

To display it write a function to convert the minutes mathematically to hours and minutes. Use the function in your queries.

 

[jshanoo]

HI SQL sis,

I got ur point that is very a good idea

i worked around some sql query and it came out like this.
but i can see one hour is always less in the total

sample records are given below
--------------------------------------
Select currdatetime,fulliro,partiro,
cast(datediff(n, cast('0:00:01' as datetime),(convert(datetime,convert(datetime, (convert(float,convert(datetime,fulltime,108),108))*fulliro,108),108))) /60 as numeric(8)),
datediff(n, cast('00:00:01' as datetime),(convert(datetime,convert(datetime, (convert(float,convert(datetime,parttime,108),108))*partiro,108),108))) / 60 as parthrs
from agents
-----------------------------
CurrDateTime FullIro PartIro FullTime Parttime
2004-04-01 00:00:00.000 62 25 08:00:00 04:45:00
2004-04-02 00:00:00.000 59 27 08:00:00 04:45:00
2004-04-03 00:00:00.000 62 26 08:00:00 04:45:00
2004-04-04 00:00:00.000 63 26 08:00:00 04:45:00


------------------------------
Best Regards
John Philip


*** Even the Best, did the Bad and Made the Best ***

John Philip

 

[ESquared]

Where are you getting the numbers for how many part-time and full-time employees there are? And do they always work exactly 8/4.75 hours or is there some listing of start times and end times that lists actual hours worked?

 

[jshanoo]

Hi Esqaure,
Sorry for the late reply.
Well the working hrs are fixed for employees.
so it doesnt change. it is static value for us.


*** Even the Best, did the Bad and Made the Best ***

John Philip