Breaking Down Total Hours Worked Into Day And Evening Hours
Sep 21, 2006
I have data coming from a telephony system that keeps track of when an
employee makes a phone call to conduct a survey and which project number
is being billed for the time the employee spends on that phone call in a
MS SQL Server 2000 database (which I don't own).
The data is being returned to me in a view (see DDL for w_HR_Call_Log
below). I link to this view in MS access through ODBC to create a
linked table. I have my own view in Access that converts the integer
numbers for start and end date to Date/Time and inserts some other
information i need.
This data is eventually going to be compared with data from some
electronic timesheets for purposes of comparing entered hours vs hours
actually spent on the telephone, and the people that will be viewing the
data need the total time on the telephone as wall as that total broken
down by day/evening and weekend. Getting weekend durations is easy
enough (see SQL for qryTelephonyData below), but I was wondering if
anyone knew of efficient set-based methods for doing a day/evening
breakdown of some duration given a start date and end date (with the
day/evening boundary being 17:59:59)? My impression is that to do this
correctly (i.e., handle employees working in different time zones,
adjusting for DST, and figuring out what the boundary is for switching
from evening back to day) will require procedural code (probably in
Visual Basic or VBA).
However, if there are set-based algorithms that can accomplish it in
SQL, I'd like to explore those, as well. Can anyone give any pointers?
Thanks.
--
DDL for view in MS SQL 2000 database:
CREATE VIEW dbo.w_HR_Call_Log
AS
SELECT TOP 100 PERCENT dbo.TRCUsers.WinsID, dbo.users.username AS
Initials, dbo.billing.startdate, dbo.billing.startdate +
dbo.billing.duration AS EndDate,
dbo.billing.duration, dbo.projects.name AS
PrjName, dbo.w_GetCallTrackProject6ID(dbo.projects.descript ion) AS ProjID6,
dbo.w_GetCallTrackProject10ID(dbo.projects.descrip tion) AS ProjID10,
dbo.billing.interactionid
FROM dbo.projects INNER JOIN
dbo.projectsphone INNER JOIN
dbo.users INNER JOIN
dbo.TRCUsers ON dbo.users.userid =
dbo.TRCUsers.UserID INNER JOIN
dbo.billing ON dbo.users.userid =
dbo.billing.userid ON dbo.projectsphone.projectid =
dbo.billing.projectid ON
dbo.projects.projectid = dbo.projectsphone.projectid
WHERE (dbo.billing.userid 0)
ORDER BY dbo.billing.startdate
I don't have acess to the tables, but the fields in the view come
through as the following data types:
WinsID - varchar(10)
Initials - varchar(30)
startdate - long integer (seconds since 1970-01-01 00:00:00)
enddate - long integer (seconds since 1970-01-01 00:00:00)
duration - long integer (enddate - startdate)
ProjID10 - varchar(15)
interactionid - varchar(255) (the identifier for this phone call)
MS Access SQL statement for qryTelephonyData (based on the view,
w_HR_Call_Log):
SELECT dbo_w_HR_Call_Log.WinsID, dbo_w_HR_Call_Log.ProjID10,
FORMAT(CDATE(DATEADD('s',startdate-(5*60*60),'01-01-1970
00:00:00')),"yyyy-mm-dd") AS HoursDate,
CDATE(DATEADD('s',startdate-(5*60*60),'01-01-1970 00:00:00')) AS
StartDT,
CDATE(DATEADD('s',enddate-(5*60*60),'01-01-1970 00:00:00')) AS EndDT,
DatePart('w',[StartDT]) AS StartDTDayOfWeek, Duration,
IIf(StartDTDayOfWeek=1 Or StartDTDayOfWeek=7,Duration,0) AS
WeekendSeconds,
FROM dbo_w_HR_Call_Log
WHERE WinsID<>'0'
View 3 Replies
ADVERTISEMENT
Apr 22, 2015
I want to display Days Hours Mins Format.
I am Having two columns Like below,
Col1 (in days) col2 (In Hours : Mins)
3days 4:5
In this first have to add Col1 and Col2 (Here one day is equals to 9 hours ) so the addition is 31.5
From this 31.5 I should display 3 Days 4 Hours 30 Mins because 31.5 contains 3 (9 hours) days 4 Hours and .5 is equals to 30 mins.
View 6 Replies
View Related
Aug 8, 2006
Hi,
I'm new to Reporting Services, and I have to make a sort of timesheet-report out of a sharepoint list.
The sharepoint list works great, employees give in their start, stop and lunch times, and the project they are working for.
Now, on my report, everything looks great, except, I can not get the grand total of the worked hours.
I think I need some function in my totalHours field, but I don't know how to write this.
So I am looking for someone to help me write this, and I am willing to pay for it.
Can anyone help me out?
Thanks in advance, Natascha
View 5 Replies
View Related
May 13, 2008
I have a float variable that holds a decimal number of hours.
So 1.5 equals 1 hour 30 minutes.
I need to change this to the format 1:30
Any idea how to do this?
View 10 Replies
View Related
Aug 13, 2004
How can I change the output into the total hours between 2 smalldatetime data? TIF
DECLARE @iDAY AS SmallDatetime
SET @iDAY = '2004-08-12 10:05:00'
PRINT @iDAY
PRINT GETDATE()
PRINT GETDATE()-@iDAY
+++++++++++++++
Aug 12 2004 10:05AM
Aug 13 2004 10:05AM
Jan 2 1900 12:00AM <--- I want to get the total hours (i.e. 24)
View 4 Replies
View Related
Jul 5, 2014
need to calculate the total hours mintues in sql server and asp.net
View 11 Replies
View Related
Dec 4, 2007
Dear All
I want to get the total no of records created in a hour.
If i pass a date then i want to get output like this
9-10 10-11 12-13 13-14
2 6 4 8
View 2 Replies
View Related
Nov 15, 2013
I have to calculate the total working hours between days, there hours must get automatically round off to nearest value example:
Date :12-05-2013 time : 4:15 will change to 4.00 and if Date :13-05-2013 time: 4:25 then needs to 4.30 and sum the above total hours and results Total : 8.30 hrs.
View 3 Replies
View Related
Mar 2, 2014
Given the two datetimes below, what's the best way to obtain the total duration in hours, minutes and seconds (HH:mm:ss)?
Start Time: 2014-03-02 20:55:00.000
End Time: 2014-03-03 07:00:00.000
Duration = 10:05:00
View 6 Replies
View Related
Oct 23, 2015
I have a table called PJLabDet.Total_Hrs which is the sum of hours of 7 days.
I would like to get a running total for this field which would reset when my group (called PJLabDat.pjt_entity) changes.
View 2 Replies
View Related
Jul 21, 2015
Creating a burn down chart using a running total of cumulative hours with the following formula:
CumulativeHoursLeft:=CALCULATE (
SUM('Projects'[Budget hours]) - SUM ( 'hours'[Hours] ),
FILTER (
ALL ( 'hours'[Date] ),
'hours'[Date] <= MAX ('hours'[Date])
)
)
Works great except that in a Line Chart using [Date] as the Axis and CumulativeHoursLeft as the value, I get these spikes on days for which the employee reported no hours. I do know what exactly the measure is doing in this instance and I do not get this in a table, those dates simply do not appear. I have tried both Categories and Continuous for the Line Chart. I have also tried filtering where [Date] is not blank.how to get rid of the spikes?
View 5 Replies
View Related
Jun 28, 2001
We have a few Servers that have space issues that need full DB Backups every day. As a result, we have to carefully watch how many DB Backup files we keep on the Server. On the Maintenance Plan, if I set the 'Delete files older than:' to '1' 'Day', will this delete anything over 24 hours old? What I have noticed, is that it tends to delete on Tuesday anything that was created on Sunday, but not Monday. Therefore, I am assuming that instead of '1 Day' being 24 hours, it is anything less than 47 hours and 59 minutes...
Is my assumption correct or do I need to look at something else. Thanks!
Billy
View 1 Replies
View Related
Oct 31, 2005
SELECT sysjobs.name, sysjobservers.last_run_outcome, CONVERT(varchar(8), sysjobservers.last_run_date, 112) AS LastRunDate, CONVERT(varchar(8),
sysjobservers.last_run_time, 8) AS LastRunTime, GETDATE() AS CurrentDateTime
FROM sysjobs LEFT OUTER JOIN
sysjobservers ON sysjobs.job_id = sysjobservers.job_id
WHERE (sysjobservers.last_run_outcome = 0) AND (CONVERT(varchar(8), sysjobservers.last_run_date, 112) >= DATEADD(day, - 1, GETDATE()))
I am wanting a similar solution, I need to convert an "int" to a time format, when I use this where ">= DATEADD(minute, - 720, GETDATE())" for the last_run_time I get this error:
Syntax error converting datetime from character string.
View 3 Replies
View Related
Jul 1, 2004
I ran the following:
ALTER TABLE Recipients ADD Obscene BIT NOT NULL DEFAULT 0
On a table with 80 million records. It's been running for 8 hours and counting now. This is ridiculous. No one else is using this server.
Server configuration:
SQL Server 2000 Enterprise Edition with SP3a
2 GB RAM
3.0 GHz P4 with hyperthreading
SCSI RAID
Any ideas why this is taking so long? Can I find out what it's doing? Is there anything I can do to make it go faster?
View 13 Replies
View Related
May 15, 2008
My database is current 135GB. I have been running the following:
DBCC SHRINKFILE([database_data],2)
DBCC SHRINKDATABASE ( [Database],20,TRUNCATEONLY)
It's been running for 28 hours. Should it be taking this long? If I stop the shrink, will it need to rollback?
Thanks!
View 2 Replies
View Related
Feb 29, 2012
I have a job that runs between the hours of 10 PM and 9 AM. It launches a controller stored procedure that will call other stored procedure until the entire process is done.I would like the controlling stored procedure to only call the steps between the hours of 10PM and 9AM also.. So at 8:59 AM it will start the next step, but at 9:00 AM it will exit.
View 6 Replies
View Related
Mar 17, 2004
I use SQL 2K with an Access 2000 project as front end. I have four date fields in a table. The first one should contain a date (eg 20th of February), the second one a starting hour (eg 8:00h), the third one the finishing hour (eg 10:00h) and the fourth should calculate the difference between the second and third column.
So I created four datetime fields. Storing a date in the first column is no problem, but storing only hours doesn't seem to do what I want. When I enter 8:00 from within the Access, it is stored 1/1/1900 8:00 in SQL Server. How can I make sure that the hours are saved with the date of the second column, so either as 8:00 without further information or as eg 20-2-2004 8:00?
View 14 Replies
View Related
Apr 21, 2015
I am querying a database that has hours worked listed by pay rate. I want to return sum of hours per pay rates I have been given but hours are stored in multiple columns. So If I run the below query for one worker it returns.
HoursBill_rate_1 Hours_2Bill_rate_2
34.2610.26 0.000.00
5.7410.26 5.689.67
I want to report to the business:
40hrs 10.26
5.68 9.67
The query I have written is below:
Select Sum(Hours_1) As Hours,Bill_rate_1,Sum(Hours_2) as Hours_2,Bill_rate_2--,Hours_3,Bill_rate_3
From Valid_Timesheets
Where (Department = '938' and tax_period = 3 and tax_year = 2015 AND PERSONNEL_REF = '991A001198') and
((Bill_Rate_1 = £10.26 or
Bill_Rate_1 = £9.67 or
Bill_Rate_1 = £8.27 or
[Code] ....
View 4 Replies
View Related
Jul 23, 2005
Hi,I have the following query:SELECT p.employee_code, p.employee_name,CONVERT(VARCHAR(12),t.tran_date,101) AS TranDate,CONVERT(VARCHAR(12),t.post_date,101) AS PostDate, SUM(tobill_hrs) ASTotalHoursFROM tat_time t, hbm_persnl pWHERE t.tk_empl_uno = p.empl_uno AND t.tran_date BETWEEN '2005-03-01'AND '2005-03-31' AND p.employee_code IN ('0340')GROUP BY p.employee_code, p.employee_name, t.tran_date, t.post_dateORDER BY p.employee_codeI would like to sum the tobill_hrs by week (Monday-Sunday) instead ofby day. For instance the user will put in a date range (a from dateand to date).Is this possible to do?Thanks!
View 9 Replies
View Related
Apr 18, 2006
I am creating a report that has call duration and if i run it over amulti-day span the duration is limited to 24 hours and starts over atzero. is there a way to not limit hours?I am usign the following in my select statement:convert(varchar(12), dateadd(s,SUM(ISNULL(Calls.OutgoingCallDuration,0)), '19900101'), 108) AS[Outgoing Call Duration]
View 3 Replies
View Related
Dec 14, 2007
Is there a function in SQL Server 2005 that can add GMT deviation hours to the existing date. I have a data field with the GMT_Deviation_Hours listed in numeric format e.g. -1, 12 etc. I have another date field to whom i wish to deviate the datetime according to the GMT_Deviation hours.
Thanks
View 2 Replies
View Related
Sep 28, 2007
Hi, i have this problem :
I have to compare 1 hour between two days , example:
I have this datetime value (BitacoraProcesoUsuario.FechaInicioProceso) in 108 format : 23:35:22
and I make this query:
Select TOP 100 * from BitacoraProcesoUsuario
INNER JOIN Turno
ON convert(varchar,BitacoraProcesoUsuario.FechaInicioProceso,108) > = '24:00:00'
AND convert(varchar,BitacoraProcesoUsuario.FechaInicioProceso,108) <= '06:29:00'
and returns nothing........ this because 23:35:22 is gtreater than 06:29:00' value, my question is how can I compare this hours in this format.
Regards
View 14 Replies
View Related
Feb 20, 2008
Would you help me ?
In the example below, how can I do to have count = 9 ?
It means that I have 9 periods of 30 minuts from 09:00 to 13:30.
Thank you.
declare initial_hour as varchar(10)
declare final_hour as varchar(10)
declare per30min as varchar(10)
declare count as int
set initial_hour = '09:00'
set final_hour = '13:30'
set per30min = '0:30'
set count = (final_hour - initial_hour) / per30min ------>>> count = 9
View 8 Replies
View Related
Feb 18, 2008
I would like to know the best method or the simplest method to do the following. Take a datetime value and subtract a given number of hours. I am taking a UTC time and subtracting a given number or hours in a query. I seem to be getting mixed results.
Thanks
View 2 Replies
View Related
May 16, 2006
Hi,
I need to make a select getDate() subtracting 4 hours...
How can I make it ???
Thanks
View 1 Replies
View Related
Feb 1, 2007
What is the best data type to use when tracking hours, for example;
2.30 < this is stored in DB
(two hours and thirty minutes)
5.15 < this is stored in DB
(5 hours and fifteen minutes)
View 15 Replies
View Related
Mar 26, 2008
MS SQLJust to confirm, I need to store the number of hours a user has spent doing something, am I correct using the smalldatetime field for the MS SQL database?I will eventually need to be able to calculate totals using SQL Reporting Services so it's important that the hours add up correctly (to 60 minutes, not 100).ASP.NETI'm trying to find the correct sort of formatting to use? Short time stores the time as 9:50AM, which isn't what i'm after.I need to store the hours as in 1 hour, or 01:00:00 (1 hour, 0 minutes, 0 seconds) or just 01:00 for 1 hour. I do need minutes but seconds are not required.I can't find the right formatting for this, would Long time be more suited?
View 18 Replies
View Related
Sep 7, 2005
its possible make a procedure or something inside the sql server to run every 5 hour?? to make a update of a table
View 2 Replies
View Related
Jul 7, 2000
I have a SQL 7.0 backend for a new DB of mine. This is the first time I
have developed a SQL backend from scratch, so the problem may be my design.
The problem may lie with my Access frontend (I have tried both 97 and 2k),
but I am inclined to think not considering the behavior.
On only one table (the master table no less), I cannot add records during
business hours. I can add records to other tables and edit records on this
table though. This troublesome table has a field indexed as unique that is
not the PK (don't know if that has any bearing, but it is the only table in
this DB that has that property).
The adding records problem appears to be network traffic related because
sometimes records can be added at lunchtime and definitely after hours. I currently only have 3 connected users. I had these problems with only one connected user, so the DB itself is not being strained by traffic. I often have more traffic on the DB after hours to avoid the problems. I have worked on the connection and network traffic angle for awhile, when I stumbled upon the bizarre part. If I add a record via a stored procedure and run this SP on the SQL query analyzer on the server (no network is involved) it is still slow to add the record (30 minutes or more). Needless to say this would equate to an failure on the front end. The same query will run in 0-3 seconds after hours!
Does anyone have any clue how this could happen? I am stumped.
I do have a budget for a consultant if I need one, but I need to find
someone who has a chance to fix this problem. If you don't know the answer
but can refer someone in the DFW area, I am in your debt.
Debbie
View 4 Replies
View Related
May 10, 2006
I am trying to calculate business hours that an order that is open. The rules are 8am-5pm. For instance the first row, the clock would stop at 17:00, and pick up again at 8am and add on to total business hours. If the order was created after business hours, and the endstamp was before business hours, this would be 0. If created after 5pm friday, and the endstamp was before 8am monday, this would be 0. In the second set of timestamps I have here the order was recieved at 14:39 but took until the following day at 14:49 to be ordered. I imagine I have to use datepart and datediff for this, but other than that I am not sure on how to do it. Any help would really be appreciated!
StartStampEndstamp
6/27/2005 14:356/27/2005 17:41:11
6/27/2005 14:396/28/2005 14:49
6/27/2005 18:486/27/2005 18:54
6/27/2005 11:416/27/2005 11:45
View 7 Replies
View Related
Jul 4, 2004
hello this is my store procedure and i want to set the hour of the first date to be 00:00:00 and the hour of the second date to be 23:59:59 but i don't know how to do this,so please HELP me
CREATE PROCEDURE dbo.Proba
@date1 smalldatetime ,
@date2 smalldatetime,
@s float output
AS
Select @s=sum(Pay) from RKO where Dates between @date1 AND @date2
GO
View 1 Replies
View Related
Jun 23, 2004
Hi,
I'd like a function that returns the number of hours in a specific month (or the number of days which I could then multiply by 24). The function would have to consider leap years for February.
Any ideas?
Thanks,
Skip.
View 6 Replies
View Related
Jul 7, 2004
I previously posted about a problem where I added a non-NULL DEFAULT 0 bit column to a table with 80 million records. It was taking a LONG time and we needed that database up fast. It ended up taking a total of 17 hours.
Now my coworker added the same non-NULL DEFAULT 0 bit column to another table on another important server. But this table has more like 400 million rows. It's been running for 100+ hours and is still going. We were hoping it would scale linearly (5*80 million records would hopefully take 5*17 hours) but that isn't happening. I have no idea how much longer it will take. I really need this to be done. I'm tempted to cancel but that will incur a potentially massive rollback, right? Any guestimate on how large that would be?
Any ideas?
View 9 Replies
View Related