Returning Hours A Call Was Open
Apr 24, 2008
What I am trying to do is look at a 30 day history of calls that were closed and find (in hours) how long the work orders were open. My major problem is working with dates, the results I'm getting appear to be inaccurate. My Code is as follows:
Code:
SELECT C.Priority, MIN(CONVERT([INT], DATEPART(hh, T1.CalcDate), 120)) AS Minimum, MAX(CONVERT([INT], DATEPART(hh, T1.CalcDate), 120))
AS Maximum
FROM HEAT.dbo.CallLog C, HEAT.dbo.Profile P,
(SELECT CallID, CONVERT(datetime, ClosedDate + ' ' + ClosedTime, 120) - CONVERT(datetime, RecvdDate + ' ' + RecvdTime, 120)
AS CalcDate
FROM HEAT.dbo.CallLog C1
WHERE (CONVERT(datetime, CONVERT([INT], GETDATE(), 120) - 30, 120) >= ClosedDate)) T1
WHERE C.CustID = P.CustID AND C.CallID = T1.CallID AND (C.CallStatus = 'Closed') AND (P.CustType <> 'services') AND (P.PrimarySupportGroupID = 'CTS') AND
(C.Priority IN ('1', 'E1', 'E2')) AND (CONVERT([INT], T1.CalcDate, 1) >= 0)
GROUP BY C.Priority
ORDER BY C.Priority
If anyone could give me any suggestions or help me out I would greatly appreciate it. The results are used to draw a bar chart of the information.
Thank you
Court
View 2 Replies
ADVERTISEMENT
Jan 7, 2004
Hello -
I need to call a SQL Server stored procedure, which takes over five hours to run, from an asp.NET web page. This procedure then calls a DTS package which is what takes 5+ hours to run. I need the user to be able to click on the 'run' button and have the page kick off the stored procedure (or the DTS Package if that will work instead) and display a message saying the load has begun and to check a 'status' link.
Right now I get a page timeout because it's waiting for results.
Any help would be greatly appreciated!
Thanks,
cat72
View 1 Replies
View Related
Sep 16, 2005
I have written a function that returns the number of Days, Hours and minutes from a given number of minutes. On testinf the results are close but not quite there. Can anyone see where I have gone wrong or is there an easier way of doing this? Code is as follows:
CREATE FUNCTION dbo.GetTimeBetweenLong
(@StartTime DateTime, @EndTime DateTime, @CurrentDate DateTime)
RETURNS VarChar(50) AS
BEGIN
DECLARE @TotalTime Numeric
DECLARE @Minutes Numeric
DECLARE @Hours Numeric
DECLARE @Days Numeric
DECLARE @MinutesInDays Numeric
IF @EndTime IS NULL
BEGIN
SET @Days = DATEDIFF(Day, @StartTime, @CurrentDate)
SET @Hours = DATEDIFF(Hour, @StartTime, @CurrentDate) - (@Days * 24)
SET @Minutes = DATEDIFF(Minute, @StartTime, @CurrentDate) - ((@Days * 24)*60) - (@Hours * 60)
END
ELSE
BEGIN
SET @Days = DATEDIFF(Day, @StartTime, @EndTime)
SET @Hours = DATEDIFF(Hour, @StartTime, @EndTime) - (@Days * 24)
SET @Minutes = DATEDIFF(Minute, @StartTime, @EndTime) - ((@Days * 24)*60) - (@Hours * 60)
END
IF(@Days <0)
BEGIN
SET @Days = @Days - @Days - @Days
END
IF (@Hours < 0)
BEGIN
SET @Hours = @Hours - @Hours - @Hours
END
IF (@Minutes <0)
BEGIN
SET @Minutes = @Minutes - @Minutes - @Minutes
END
RETURN CONVERT(nVarChar(10),@Days) + ' Days, ' + CONVERT(nVarChar(5), @Hours) + ' Hours, ' + CONVERT(nVarCHar(5), @Minutes) + ' Mins'
END
View 1 Replies
View Related
Jun 6, 2007
I have a stored proc which should be returning a datatable. When I execute it manually it returns all requested results. However, when I call it via code (C#) it is returning a null table which leads me to believe the problem is in my code. I'm not getting any errors during runtime. Any help at all would be a BIG help!
private void PopulateControls() { DataTable table = CartAccess.getCart(); }
public static DataTable getCart() { DbCommand comm = GenericDataAccess.CreateCommand(); comm.CommandText = "sp_cartGetCart";
DbParameter param = comm.CreateParameter(); param.ParameterName = "@CartID"; param.Value = cartID; param.DbType = DbType.String; param.Size = 36; comm.Parameters.Add(param);
DataTable table = (GenericDataAccess.ExecuteSelectCommand(comm)); return table; }
public static DataTable ExecuteSelectCommand(DbCommand command) { // The DataTable to be returned DataTable table; // Execute the command making sure the connection gets closed in the end try { // Open the data connection command.Connection.Open(); // Execute the command and save the results in a DataTable DbDataReader reader = command.ExecuteReader(); table = new DataTable(); table.Load(reader); // Close the reader reader.Close(); } catch (Exception ex) { Utilities.SendErrorLogEmail(ex); throw ex; } finally { // Close the connection command.Connection.Close(); } return table; }
View 1 Replies
View Related
Oct 10, 2007
Does anyone know why this statement would fail? I have created my own assembly which is on the server and when i run the mdx query call myassembly.mystoredproc() it returns data, but now when I use call myassembly.mystoredproc() it returns an error.
using an mdx query this works fine
call AsmTest.Asm.Analysis.TestClass.NameMe()
When using openquery i get an error
select * from openquery(NV, 'call AsmTest.Asm.Analysis.TestClass.NameMe()' )
OLE DB provider "MSOLAP.3" for linked server "NV" returned message "Prepare is not safe during execution of the NameMe stored procedure.".
Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing the query "call AsmTest.Asm.Analysis.TestClass.NameMe()" for execution against OLE DB provider "MSOLAP.3" for linked server "NV".
linked server definition
USE master
GO
/* Add new linked server */
EXEC sp_addlinkedserver
@server='NV', -- local SQL name given to the linked server
@srvproduct='', -- not used
@provider='MSOLAP.3', -- OLE DB provider (the .2 means the SQL2K version)
@datasrc='nvsifwfp', -- analysis server name (machine name)
@catalog='ARTSDW' -- default catalog/database
View 1 Replies
View Related
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
Sep 21, 2006
I have data coming from a telephony system that keeps track of when anemployee makes a phone call to conduct a survey and which project numberis being billed for the time the employee spends on that phone call in aMS 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_Logbelow). I link to this view in MS access through ODBC to create alinked table. I have my own view in Access that converts the integernumbers for start and end date to Date/Time and inserts some otherinformation i need.This data is eventually going to be compared with data from someelectronic timesheets for purposes of comparing entered hours vs hoursactually spent on the telephone, and the people that will be viewing thedata need the total time on the telephone as wall as that total brokendown by day/evening and weekend. Getting weekend durations is easyenough (see SQL for qryTelephonyData below), but I was wondering ifanyone knew of efficient set-based methods for doing a day/eveningbreakdown of some duration given a start date and end date (with theday/evening boundary being 17:59:59)? My impression is that to do thiscorrectly (i.e., handle employees working in different time zones,adjusting for DST, and figuring out what the boundary is for switchingfrom evening back to day) will require procedural code (probably inVisual Basic or VBA).However, if there are set-based algorithms that can accomplish it inSQL, 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_LogASSELECT TOP 100 PERCENT dbo.TRCUsers.WinsID, dbo.users.username ASInitials, dbo.billing.startdate, dbo.billing.startdate +dbo.billing.duration AS EndDate,dbo.billing.duration, dbo.projects.name ASPrjName, dbo.w_GetCallTrackProject6ID(dbo.projects.descript ion) AS ProjID6,dbo.w_GetCallTrackProject10ID(dbo.projects.descrip tion) AS ProjID10,dbo.billing.interactionidFROM dbo.projects INNER JOINdbo.projectsphone INNER JOINdbo.users INNER JOINdbo.TRCUsers ON dbo.users.userid =dbo.TRCUsers.UserID INNER JOINdbo.billing ON dbo.users.userid =dbo.billing.userid ON dbo.projectsphone.projectid =dbo.billing.projectid ONdbo.projects.projectid = dbo.projectsphone.projectidWHERE (dbo.billing.userid 0)ORDER BY dbo.billing.startdateI don't have acess to the tables, but the fields in the view comethrough 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-197000:00:00')),"yyyy-mm-dd") AS HoursDate,CDATE(DATEADD('s',startdate-(5*60*60),'01-01-1970 00:00:00')) ASStartDT,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) ASWeekendSeconds,FROM dbo_w_HR_Call_LogWHERE WinsID<>'0'
View 3 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
Mar 19, 2008
Hi,
I am producing a php report using SQL queries to show the SLA status of our calls. Each call has response, fix & completion targets. If any of these targets are breached, the whole SLA status is set as 'Breach'.
The results table should look like the one below:
CallRef.
Description
Severity
ProblemRef
Logged
Date
Call
Status
SLA Status
C0001
Approval for PO€™s not received
2
DGE0014
05-01-06 14:48
Resolved
Breach
C0002
PO€™s not published
2
DGE0014
06-01-06 10:21
Resolved
OK
C0003
Approval for PO€™s not received from Siebel.
2
n/a
05-01-06 14:48
Investigating
OK
Whereas I can pick the results for the first 6 columns from my Select query, the 'SLA Status' column requires the following calculation:
if (due_date < completed_date)
{ sla_status = 'OK';
}
else sla_status = 'Breach';
The Select statement in my query is looking like this...
Select Distinct CallRef, Description, Severity, ProblemRef, Logdate, Status, Due_date, Completed_date;
The problem is that my query is returning multiple entries for each stage of the call (see below), whereas I just want one entry for each call, with SLA status 'Breach' if any of the stages for the call were out of SLA.
CallRef.
Description
Severity
ProblemRef
Logged
Date
Call
Status
SLA Status
C0001
Approval for PO€™s not received
2
DGE0014
05-01-06 14:48
Resolved
Breach
C0001
Approval for PO€™s not received
2
DGE0014
05-01-06 14:48
Resolved
OK
C0001
Approval for PO€™s not received
2
DGE0014
05-01-06 14:48
Resolved
Breach
Any help will be much much appreciated, this issue has been bothering me for some time now!!!
View 7 Replies
View Related
Sep 7, 2007
I get the below error only when my IDE open. It connects well when it is found closed.
[SqlException (0x80131904): Cannot open user default database. Login failed.Login failed for user 'JPASPNET'.]
I could solve this by giving the logged in windows user to impersonate under IIS window > WEBSITE > ASP.NET tab > EDIT CONFIG > APPLICATION tab
But I wish someone could give me the proper solution.
I almost tried all from giving ASPNET user as a administrator to configuring the same in Express management tool.
Environment: XP pro, VWD and SQL Express
View 3 Replies
View Related
Jun 7, 2006
Hi there,
I got an approach like that:
1) Read something from DB - check the value, if true stop if false go on2) Read the second Value (another SQL Statement) - check the value etc.
Now I could open the connection at 1) and if I have to go to 2) I leave the connection open and use the same connection at 2). Is it ok to do that?
The other scenario would be opening a connection at 1), immediately close it after I read the value and open a new connection at 2).
Thanks for the input!
View 4 Replies
View Related
Nov 3, 2014
Post installation of SQL Server 2014 Express edition, I am able to connect to the Database Instance.
But while opening a new query window in SSMS or opening a table getting the error:
Package 'RadLangSvc.Package, RadLangSvc, Version 12.0.0.0, Culture=Neutral, Public Token=89845dcd8080cc91' failed to load
Object reference not set to an instance of an object. (mscorlib)..Have already tried installing the componentsDACProjectSystemSetup_enu.msi, TSql LanguageService_enu.msi, DACFramework_enu.msi from path VS 2010 WCU DAC.
View 5 Replies
View Related
Sep 25, 2007
I am opening a simple command against a view which joins 2 tables, so that I can return a column which is defined as a tinyint in one of the tables. The SELECT looks like this:
SELECT TreatmentStatus FROM vwReferralWithAdmissionDischarge WHERE ClientNumber = 138238 AND CaseNumber = 1 AND ProviderNumber = 89
The TreatmentStatus column is a tinyint. When I execute that above SQL SELECT statement in SQL Server Management Studio (I am using SQL Server 2005) I get a value of 2. But when I execute the same SQL SELECT statement as a part of a SqlDataReader and SqlCommand, I get a return data type of integer and a value of 1.
Why?
View 5 Replies
View Related
Jan 10, 2007
I hvae a stored procedure that has this at the end of it:
BEGIN
EXEC @ActionID = ActionInsert '', @PackageID, @AnotherID, 0, ''
END
SET NOCOUNT OFF
SELECT Something
FROM Something
Joins…..
Where Something = Something
now, ActionInsert returns a Value, and has a SELECT @ActionID at the end of the stored procedure.
What's happening, if that 2nd line that I pasted gets called, 2 result sets are being returned. How can I modify this SToredProcedure to stop returning the result set from ActionINsert?
View 2 Replies
View Related
Jun 26, 2006
Hi,
I am using SSRS Microsoft SQL Server Reporting Services Designers
Version 9.00.1399.00. I want to open linked report in new window.
I tried whats mentioned in http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=240172&SiteID=1 but i get an error on Window.Open method.
How do I solve the problem?
Thanks
View 1 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