Calculating Duration Of Activities Per Hour

Jul 25, 2014

My data is in the following form:

CREATE TABLE Temp (ORG_NAME VARCHAR(20), START_TIME datetime, END_TIME datetime)

INSERT INTO Temp VALUES('Org Name', '2014-06-20 14:25:00.000', '2014-06-20 15:25:00.000') - AND many more like these with different START_TIME and END_TIME.

The Task:

- I need to calculate the duration of the activities PER HOUR.

i.e. in the example above, if I want to see the productivity for 2PM (i.e. activity duration from 2-3PM), I should only get 35 mins (as the activity started at 14:25). In the same way, if I see the productivity for 3PM (i.e activity duration from 3-4 PM), I should only see 25 minutes (as activity ended at 15:25).

There would technically be many activities with overlapping times - for example, there might be 5 activities starting at 14:30 and ending at 15:10. In this case, if I were to see the productivity for 2PM, I'd see 150 minutes (as each activity starts at 14:30, so 30 min per activity = 150 min). In the same way, if I saw the productivity for 3PM for those 5 activities, I'd see 50 minutes.

View 1 Replies


ADVERTISEMENT

Calculating Duration

Jan 18, 2007

I am trying to get a query that will allow me to report the time taken to complete a certain training module.

The database itself does not have a duration field so I am tring to get the duration by using MIN and MAX. I can get the timing for when the module was opened and the time for the last mouse click on it, from this I need to be able to calculate the time taken to complete.

Query I am using to get the basic info comes from 3 tables so I have only attached the relevent output. Query used is as follow:

SELECT *
FROM PPS_SCOS, PPS_TRANSCRIPTS, PPS_TRANSCRIPT_DETAILS, PPS_PRINCIPALS
WHERE PPS_SCOS.SCO_ID = PPS_TRANSCRIPTS.SCO_ID
AND PPS_TRANSCRIPTS.TRANSCRIPT_ID = PPS_TRANSCRIPT_DETAILS.TRANSCRIPT_ID
AND PPS_TRANSCRIPTS.PRINCIPAL_ID = PPS_PRINCIPALS.PRINCIPAL_ID
AND PPS_SCOS.NAME LIKE 'MTM-106 The Dangers of Smoking'
AND PPS_PRINCIPALS.NAME LIKE 'Nigel Cordiner'
AND PPS_TRANSCRIPTS.TICKET NOT LIKE 'l-%'
ORDER BY PPS_TRANSCRIPT_DETAILS.DATE_CREATED

Output:

pps_scospps_scospps_transcript_detailspps_principalspps_principals
SCO_IDNAME DATE_CREATED PRINCIPAL_ID NAME
136850MTM-106 The Dangers of Smoking08:17:2516287Nigel Cordiner
136850MTM-106 The Dangers of Smoking08:17:2516287Nigel Cordiner
136850MTM-106 The Dangers of Smoking08:17:4016287Nigel Cordiner
136850MTM-106 The Dangers of Smoking08:18:2516287Nigel Cordiner
136850MTM-106 The Dangers of Smoking08:18:5716287Nigel Cordiner
136850MTM-106 The Dangers of Smoking08:19:1416287Nigel Cordiner
136850MTM-106 The Dangers of Smoking08:19:4716287Nigel Cordiner
136850MTM-106 The Dangers of Smoking08:20:2116287Nigel Cordiner
136850MTM-106 The Dangers of Smoking08:20:4416287Nigel Cordiner
136850MTM-106 The Dangers of Smoking08:21:2616287Nigel Cordiner
136850MTM-106 The Dangers of Smoking08:22:1316287Nigel Cordiner
136850MTM-106 The Dangers of Smoking08:24:5516287Nigel Cordiner
136850MTM-106 The Dangers of Smoking08:25:1216287Nigel Cordiner
136850MTM-106 The Dangers of Smoking08:25:2916287Nigel Cordiner
136850MTM-106 The Dangers of Smoking08:26:4916287Nigel Cordiner
136850MTM-106 The Dangers of Smoking08:27:0216287Nigel Cordiner
136850MTM-106 The Dangers of Smoking08:27:2916287Nigel Cordiner
136850MTM-106 The Dangers of Smoking08:27:4316287Nigel Cordiner



Have added the column heading and the tables the output comes from.

Relatively new to SQL so any help would be greatly received.

View 4 Replies View Related

Help With Calculating Duration Time

Oct 14, 2005

I have a table called Tickets which contains ticket information for a machine. Each machine can have more than one ticket number opened at the same time. The ticket number contains start date/time and end date/time of the ticket. Thereefore the table looks something like this:

Ticket_No (int)
Machine_No (int)
Description (char)
Start_Time (datetime)
End_Time (datetime)

I want to be able to calculate total duration time(in hours) that EACH MACHINE had a ticket open...but here is the tricky part. The total duration time that a machine had ticket open has to encompas any tickets that may fall in the same time period. For example:
If Machine A has a ticket open at 8:30 and the ticket is closed at 10:00. Meanwhile, Machine A had another separate ticket open at 9:30 which was closed at 10:30. In this case, the total duration time for this machine would be from 8:30 to 10:30 for a total of 2 hrs duration time.

Can anyone help me get started in tackling this problem or provide any examples?

View 4 Replies View Related

Calculating Duration Time

Dec 13, 2007

Hi guys, I am having difficulty calculating the time duration between receiving process to shipping process.
I have a table that consists of: Order#, Processes, Time_In, Time_Out.
Order# can be 1, 2, 3, 4, 5.
While at the same time Order# 1 can go through more than one process, i.e.: Receiving, VisualTest, MechanicalTest, ..., Shipping.
Every Order# does not necessarily goes through all processes, but surely they will go through receiving process and shipping process.
For each process we will have recorded time when the order# comes in and when it finishes with each process.
I need to calculate the length of time from Time_In from Receiving to Time_Out in Shipping.

I.E.:

Order# | Process | Time_In | Time_out
1 | Receiving | 2007-12-1 10:00:00.000 | 2007-12-1 10:10:00.000
1 | Incoming Q.A. | 2007-12-1 10:40:00.000 | 2007-12-1 11:42:00.000
1 | Visual Check | 2007-12-2 08:10:00.000 | 2007-12-2 11:00:00.000
1 | Shipping | 2007-12-2 11:20:00.000 | 2007-12-2 11:52:00.000
2 | xxxxx | xxxxx | xxxxx
2 | xxxxx | xxxxx | xxxxx
2 | xxxxx | xxxxx | xxxxx

Please help.
Thanks in advance.

View 2 Replies View Related

I Need Help In Calculating Time Duration

Dec 13, 2007

Hi guys, I am having difficulty calculating the time duration between receiving process to shipping process.
I have a table that consists of: Order#, Processes, Time_In, Time_Out.
Order# can be 1, 2, 3, 4, 5.
While at the same time Order# 1 can go through more than one process, i.e.: Receiving, VisualTest, MechanicalTest, ..., Shipping.
Every Order# does not necessarily goes through all processes, but surely they will go through receiving process and shipping process.
For each process we will have recorded time when the order# comes in and when it finishes with each process.
I need to calculate the length of time from Time_In from Receiving to Time_Out in Shipping.

I.E.:

Order# | Process | Time_In | Time_out
1 | Receiving | 2007-12-1 10:00:00.000 | 2007-12-1 10:10:00.000
1 | Incoming Q.A. | 2007-12-1 10:40:00.000 | 2007-12-1 11:42:00.000
1 | Visual Check | 2007-12-2 08:10:00.000 | 2007-12-2 11:00:00.000
1 | Shipping | 2007-12-2 11:20:00.000 | 2007-12-2 11:52:00.000
2 | xxxxx | xxxxx | xxxxx
2 | xxxxx | xxxxx | xxxxx
2 | xxxxx | xxxxx | xxxxx

Please help.
Thanks in advance.

View 6 Replies View Related

Calculating Salary Per Hour?

Jan 26, 2008

is there a way to create a SELECT clause which counts the accumulate hours from tw columns in same row (entering hour and leaving hour) and then calculating the total price according to a parameter?

Shimi

View 2 Replies View Related

Calculating The Time Duration For Each Event

Jun 6, 2008

Hi all,

Thanks for setting up such a great site and forum.

Here is my problem:

I have a table like the following in SQL Server 2005:


order | taskid | main_person | temp_person | start_assign_date
1 | 3 | John | John | 2008-01-01 10:20:22
2 | 3 | John | Joe | 2008-02-05 15:20:22
3 | 3 | John | John | 2008-02-07 20:25:20
4 | 6 | Joe | Joe | 2008-01-01 10:20:22
5 | 6 | Joe | Mike | 2008-02-01 10:20:22
6 | 10 | Doug | Doug | 2008-01-01 10:20:22
7 | 7 | Russ | Russ | 2008-02-01 11:20:22
8 | 7 | Russ | Mike | 2008-02-08 12:20:22
9 | 7 | Russ | John | 2008-02-10 20:05:12


It was made to record who was in charge of a specific task at a specific time. Each task has its own main responsible person and some substitutes for that person as Temporary Persons (who did the task while main person was away). The Main Person's name is in the temp_person column when he is doing the task by himself.

I'd like to generate a report that shows:
- in a specific time period
- which persons were in charge of a specific task and
- for how long

Something like this:

From 2008-##-## to 2008-##-##
Task 3 - John - 15 days
Task 3 - Joe - 5 days
Task 6 - Joe - 18 days
Task 6 - Mike - 2 days
Task 10 - Doug - 20 days


I have some ideas to do that when there are both start and end dates for every record but I couldn't find a way to use the next assignment start date for each task, as the end date for its previous record (in that task group) to calculate the duration for that record.

I can group the tasks and users and put them in the chronological order but I can't indicate the next start date as the end date for the previous record (in specific task group) to use the date difference functions.

Any hint or comment would be appreciated.

Thanks
Sami

View 4 Replies View Related

Transact SQL :: Calculating Duration In Days For Generic From And To

Oct 7, 2015

I need the SQL to calculate the duration between a day and time, no specific date. 

For example:
Sun 00:00 > Mon 08:00 = 5.67 days

So there is no date, it's just the general duration  (in days), from a specific day and time to a specific day and time.

View 3 Replies View Related

Transact SQL :: Get Total Request Duration From Multiple Task Duration?

Jun 4, 2015

I have the following SQL query

SELECT
[Req_ID]
,[Service_Name]
,[Req_Started_Date]
,[Task_Name]
,[Task_Status]
,[Performer_Full_Name]

[code]....

Which works fine, but what I need to calculate the total duration of a request based on the duration of the tasks completed in the request based on Req_ID. I would like to use the CASE statement I have to determine the SLA_Mins for each task and add them together to get total request SLA_Mins.

Below is the create table schema and data

GO
/****** Object: Table [dbo].[MidrangeOtherSourceControl] Script Date: 06/03/2015 18:13:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[MidrangeOtherSourceControl](
[Req_ID] [float] NULL,
[Service_Name] [nvarchar](255) NULL,

[code]....

View 9 Replies View Related

Counting Popularity, Hour By Hour

Nov 14, 2001

I have a table that is recording hits to a website. Everytime someone views a page, the datetime of the hit is recorded in a field called hit_date_time. I would like to be able to come up with a query that will show how many hits occured on a given day or given days, broken down by hour.

The resulting table for two days would look something like:
Time Hits
1/1/01 12:00 1
1/1/01 1:00 23
1/1/01 2:00 54
1/2/01 1:00 15
1/2/01 2:00 14

I can't seem to figure out how to write the query so that I can take into consideration the date and hour of the event so that I can count it.

Thanks,

Eron

View 2 Replies View Related

Dba Activities

Sep 20, 2006

can any one tell me what are the sql server dba activities?
i'm a junnior dba in hyderabad, my organization is product based company.

i need the general activities, so that i can become master in atleast some aspects.

generally what is the expectations of big organizations from DBA's? especially i'm plannning for H1.

View 5 Replies View Related

Can Not See Current Activities

Mar 31, 1999

Today when I opened current activity window on my SQL6.5 server, I could not able to see any activities listed. What will be the problem?

But sp_who works fine.

Srini

View 1 Replies View Related

Auditing Sa Activities

Jun 24, 1999

Someone had changed the SA password on one of my servers. I need to find out who did this. Can you tell me if there is any historical information kept on any of the system tables that can tell me who (what machine name) and when (date and time)this was done?
Does anyone have a 3rd party or inhouse developed task/procedure to report this kind of security issues?

View 1 Replies View Related

How To View Activities Of Companies

Jun 16, 2008

Hi, I've a table named Attività there I can have many type "IDAttivitaTipo" field:T TelefonataV VisitaP PreventivoC Chiuso CREATE TABLE [dbo].[Attivita]( [IDAttivita] [int] IDENTITY(1,1) NOT NULL, [IDAttivitaStato] [varchar](1) COLLATE Latin1_General_CI_AS NULL, [IDAttivitaTipo] [varchar](2) COLLATE Latin1_General_CI_AS NULL, [IDAnagrafica] [int] NULL, [Data] [datetime] NULL CONSTRAINT [DF_Attivita_Data] DEFAULT (getdate()), [Descrizione] [varchar](max) COLLATE Latin1_General_CI_AS NULL, CONSTRAINT [PK_Attivita] PRIMARY KEY CLUSTERED ( [IDAttivita] ASC)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]) ON [PRIMARY] with "IDAttivitaTipo":T TelefonataV VisitaP PreventivoC Chiuso with "IDAttivitaStato" E Eseguita  (held)D Da Eseguire (not held)I would like to see the state of each IDAnagrafica (company) creating a query with X rows (X companies ... X IDAzienda) and 4 fields 1/0 (true/false ....bit) that tell me if there is or there isn't an activity (IDAttivita) in T V P C type for each IDAzienda (company) .... with IDAttivitaStato E (held)T -> 1/0V -> 1/0P -> 1/0C -> 1/0I've created a SP with a parameter "IDAttivitaTipo" that allow me to view all campanies (IDAnagrafica) stopped in the "IDAttivitaTipo"if I use 'V' parameter in the SPI want to see all the companies stopped in V (IDAttivitaStato E -> Held) ...that don't have activities in P and C state .... I'm not interested if there are activities in T stateif I use 'P' parameter in the SP
I want to see all the companies stopped in P (IDAttivitaStato P ->
Held) ...that don't have activities  C state .... I'm not interested if there are activities in T or V state
if I use 'C' parameter in the SP
I want to see all the companies stopped in C (IDAttivitaStato C ->
Held) ...that don't have activities  after C .... I'm not interested if there are activities in T or V or P state
I've tried with these query but the result insn't right ... I can't to see some activieswhere is it my error??IF(@IDAttivitaTipo= 'V') BEGIN SELECT
(case when exists (SELECT 1 FROM Attivita WHERE (Attivita.IDAnagrafica
= Anagrafica.IDAnagrafica) AND (IDAttivitaTipo='T') AND
(IDAttivitaStato='E') AND (Privato = 0)) then 1 else 0 end) as
Telefonata, (case when exists (SELECT 1 FROM Attivita WHERE
(Attivita.IDAnagrafica = Anagrafica.IDAnagrafica) AND
(IDAttivitaTipo='V') AND (IDAttivitaStato='E') AND (Privato = 0)) then
1 else 0 end) as Visita, (case when exists (SELECT 1 FROM Attivita
WHERE (Attivita.IDAnagrafica = Anagrafica.IDAnagrafica) AND
(IDAttivitaTipo='P') AND (IDAttivitaStato='E') AND (Privato = 0)) then
1 else 0 end) as Preventivo, (case when exists (SELECT 1 FROM
Attivita WHERE (Attivita.IDAnagrafica = Anagrafica.IDAnagrafica) AND
(IDAttivitaTipo='C') AND (IDAttivitaStato='E') AND (Privato = 0)) then
1 else 0 end) as Chiuso FROM Anagrafica WHERE ('V' in (SELECT IDAttivitaTipo FROM Attivita WHERE Attivita.IDAnagrafica = Anagrafica.IDAnagrafica)) AND ('P' not in (SELECT IDAttivitaTipo FROM Attivita WHERE Attivita.IDAnagrafica = Anagrafica.IDAnagrafica)) AND ('C' not in (SELECT IDAttivitaTipo FROM Attivita WHERE Attivita.IDAnagrafica = Anagrafica.IDAnagrafica)) ENDIF(@IDAttivitaTipo = 'P') BEGIN SELECT
(case when exists (SELECT 1 FROM Attivita WHERE (Attivita.IDAnagrafica
= Anagrafica.IDAnagrafica) AND (IDAttivitaTipo='T') AND
(IDAttivitaStato='E') AND (Privato = 0)) then 1 else 0 end) as
Telefonata, (case when exists (SELECT 1 FROM Attivita WHERE
(Attivita.IDAnagrafica = Anagrafica.IDAnagrafica) AND
(IDAttivitaTipo='V') AND (IDAttivitaStato='E') AND (Privato = 0)) then
1 else 0 end) as Visita, (case when exists (SELECT 1 FROM Attivita
WHERE (Attivita.IDAnagrafica = Anagrafica.IDAnagrafica) AND
(IDAttivitaTipo='P') AND (IDAttivitaStato='E') AND (Privato = 0)) then
1 else 0 end) as Preventivo, (case when exists (SELECT 1 FROM
Attivita WHERE (Attivita.IDAnagrafica = Anagrafica.IDAnagrafica) AND
(IDAttivitaTipo='C') AND (IDAttivitaStato='E') AND (Privato = 0)) then
1 else 0 end) as Chiuso FROM Anagrafica WHERE ('P' in (SELECT IDAttivitaTipo FROM Attivita WHERE Attivita.IDAnagrafica = Anagrafica.IDAnagrafica)) AND ('C' not in (SELECT IDAttivitaTipo FROM Attivita WHERE Attivita.IDAnagrafica = Anagrafica.IDAnagrafica)) AND END

View 1 Replies View Related

Current Activities - No Items

Jun 4, 2001

We are having blocking issues on our server. Recently, I noticed that we no longer have any thing under Current Activity. When I click the plus sign beside it, it shows no items - that is, no process info, locks/process ID, or the locks/object. Does anybody know why and how I can reset it?? Thanks for your help.

View 3 Replies View Related

Auditing Database Activities

Aug 5, 1999

We are finding ourselves editing data within a sql database using tools such as MS Query, Access or VB. Is there anyway to log these edits? Auditing is set up within the application to log changes made by the users but not by third pary applications. ANy thoughts?

Thanks,
David

View 2 Replies View Related

Tracking User Activities

Oct 14, 2005

Sybase and DB2 both have the capability of tracking user activities ata number of levels: invalid access attempts to databases, table, etc.;creation/deletion/modification of database objects/users/groups,grants/revokes.For MS SQLServer, the only setting that I've seen in the documentationis access attempts (none, fail only, etc.)The monitor program has the capability of tracking the events that Iwant to be monitored, but it seems as though these settings persistonly while the monitor program is running.I'd like these settings to persist permanently and the event records tobe sent to the system log.I can't seem to find the right term to get this information out of theMS Books On LIne.Help!

View 5 Replies View Related

Scheduling Maintenance Activities

Jun 26, 2006

Am I able to do this in SS Express -like a backup or something or do I need to purhcase a SQL Server Agent (I believe I saw a 3rd party company always advertising this in SQL Server Central.com). It's cheap -- just curious if I really need it.

View 1 Replies View Related

How To Monitor All Sql Query Activities On Sql Server 7.0?

Nov 4, 2004

Hi Gurus,
I have a database server running on a sql server 7.0. Some external app is accessing this db server, reading and updating some tables. For some reason, some updating actions didn't work. I just want to track those updating actions, and see if there're some logs reporting failure reason. Is there some tool in sql server 7.0 that could help me do that? Thank you.

View 2 Replies View Related

T-SQL (SS2K8) :: CDC Activities On Receiving Machine?

Aug 19, 2014

Is there a way for me to set up CDC so that all the processing (SQL Agent, etc) happens on the machine receiving the data? I'd like to move as much of the processing as possible to the destination.

View 3 Replies View Related

Auditing User And Security Related Activities In SQLServer

Jul 23, 2005

On the other database types, there is an audit capability in that yourecord such items asfailed login attemptsattemtped access to tables user is not authroized tochanges to databse schemachanges to permissionschanges to logins (add, delete, lock, unlock, passwrod reset)All I can find in the SQLServer documentation is the reference totracking failed logins when you set up a database, plus the Profiler'sactivities.Yes, I'm taking voer my first SQLServer database and have been asked tomake sure that this database is closely monitored for inappripriateactivity.Questions:1) Does SQLServer have this capability? (Sybase has this, which iswhere I'm coming from)2) Does SQLServer do this automatically or do I have to set up theevents to be tracked as happens with Sybase?3) What commands are there for setting up these events to be tracked?Thanks in advance!

View 2 Replies View Related

Customize Database Admin Activities By Creating New User Group?

Aug 17, 2012

The requirement is to customize database admin activities by creating new user group.

Need to create a group of user / dbauser1 which will have restriction in seeing the data but they should be able to alter database - add / remove the data file , increase or decrease the data file space when required.

This requirement came we wanted to create a new dba group they should not be able to any user data / any table but increase / decrease / add / modify space etc.

View 1 Replies View Related

Transact SQL :: Server Trigger To Monitor Member Of Sysadmin Activities

Jul 22, 2015

I have to make server trigger to monitor the actitites if sysadmin members. i need to get the login name,hostname and query which they are running.

View 3 Replies View Related

Analysis :: Creating User Who Can Only Do User Management Activities?

Nov 25, 2015

I would like to limit the role of an user In Visual Studio only to assign roles to other users for the cubes. Other than that the user should not be able to create / delete the exisiting cubes or dimensions.

View 2 Replies View Related

Duration In Sql Profiler

Jan 20, 2005

If monitoring for duration with sql profiler, what does the number represent ie 2733906 is it milliseconds, thousandths, looked in BOL no clear definition

View 2 Replies View Related

SQL Profiler - Duration

Aug 1, 2002

Can someone please tell me what unit of measurement the Duration column is in when running SQL Profiler? I assume milleseconds, but am not sure.

I'm trying to filter on this and want to be sure I'm not missing anything.

Thanks!

View 1 Replies View Related

How To Run CAST On Duration

May 13, 2015

I found this nifty code on stackoverflow that works well but I'm trying to send the results to a text file and the column lengths are huge. I used CAST for the first line and it worked great but I can't seem to make it work with duration. Here's the original code:

SELECT
j.name,
h.run_status,
durationHHMMSS = STUFF(STUFF(REPLACE(STR(h.run_duration,7,0),
' ','0'),4,0,':'),7,0,':'),

[code]....

how to run a CAST on DURATION?

View 5 Replies View Related

Need Help With Job-duration Query

Jan 24, 2008

I have a query that displays number of hours duration in a jobs by months matrix. Here is a simplified version of my tables.

CREATE TABLE jobhead (
jobnum varchar (14) NULL,
jobcompletiondate datetime NULL
)

CREATE TABLE labordtl (
jobnum varchar (14) NULL ,
clockindate datetime NULL ,
clockintime decimal(6, 2) NULL ,
)
Here's an abbreviated query.

SELECT h.JobNum,
(CASE WHEN MONTH(h.JobCompletionDate) = 1 THEN datediff(day,MIN(l.ClockInDate),MAX(l.clockInDate)) ELSE 0 END) AS JAN,
(CASE WHEN MONTH(h.JobCompletionDate) = 2 THEN datediff(day,MIN(l.ClockInDate),MAX(l.clockInDate)) ELSE 0 END) AS FEB,
...
FROM JobHead h
INNER JOIN LaborDtl l ON h.JobNum=l.JobNum
WHERE JobCompletionDate>='20070101' AND JobCompletionDate <'20080101'
AND l.ClockInTime<>0
GROUP BYh.JobNum
,h.JobCompletionDate

The query shows, for each job, the month in which the job completed, and the number of hours it took to complete. I€™m calculating the number of days€™ duration by doing a datediff between the oldest and newest clockindates. I need to ignore adjustment transactions in the labordtl table €“ these rows are easily identified as they have clockintime values of 0. So far, so good. Now here€™s my problem.

There are some jobs which have only one €œreal€? labor transaction €“ this could happen if the job only took one day to complete. Other labor transactions may exist for that job, but let's say they are adjustments which we can ignore -- the date they were entered should not extend the duration of the job. In this situation, my datediff between the oldest valid transaction and the newest, returns 0. I don€™t have to count hours between clockintime and clockouttime. The rule is simply that if there is only one "real" labor transaction, I need to count this as a 1 day job.

I thought a nested CASE statement or expression might be the way to go but I didn't make any real progress.

Any ideas to solve this problem would be appreciated.

View 5 Replies View Related

Querying For Job Duration

Apr 13, 2007

Hi!

Does anyone know how to query for the job duration of all jobs in a server?

Thanks!

View 6 Replies View Related

How Do I Get Overall Package Duration?

May 20, 2006

I'm developing a web app that displays the running packages and the total elapsed time. I'm calling GetRunningPackages() method and using the ExecutionDuration property of the returned package. The duration seems to be only for the currently executing container and not the entire package. Is there a way to get the duration of the entire package? Thanks.

View 2 Replies View Related

How To Store Duration In Database?

Nov 27, 2007

 Hi all, I want to find working duration between two datetimes in c#.i'm using following code...    DateTime starttime = Convert.ToDateTime(Session["StartTime"]);    DateTime endtime = DateTime.Now;    TimeSpan duration = endtime - starttime;             DateTime period = new DateTime(duration.Ticks);      i want to store this duration in database through stored procedure, i've give datetime datatype to duration but it is giving error in conversion of TimeSpan to DateTime..Please help... Thanks

View 1 Replies View Related

Run Duration On SQL Job Doubled After Conversion From 6.5 To 7.0

Nov 29, 1999

SQL 6.5 - run duration 6-7 hours
SQL 7.0 - run duration 12-13 hours
175+ columns with total record size=570
4.2M records with tablesize 2.5G

It's a simple 'select into...' with some embedded logic from a work table with all char fields into the actual table converting char fields into various data types (int, datetime, real, etc.) Here is a sample of the code:

SELECT
LoanNum=CASE
WHEN ISNUMERIC(ACCT#)=1 THEN CONVERT(int,ACCT#)
ELSE NULL
END,
PaidToDt=CASE
WHEN PAIDDT = '0001-01-01' THEN NULL
WHEN ISDATE(PAIDDT)=1 AND SUBSTRING(PAIDDT,1,2) = '19' THEN CONVERT(smalldatetime,PAIDDT)
WHEN ISDATE(PAIDDT)=1 AND SUBSTRING(PAIDDT,1,2) = '20' AND SUBSTRING(PAIDDT,3,2) < '79' THEN CONVERT(smalldatetime,PAIDDT)
ELSE NULL
END,
.
.
.
INTO db.owner.tablename
FROM db.owner.wrktablename (NOLOCK)

View 1 Replies View Related

Duration Too Long (opinions)?

Sep 15, 2004

Hearing complaints from users about speed on db server (I have almost no control on design) it just has to work. Ran profiler looking for all sql statements over 4000 millsec and in one hour returned over 715 tsql statements. Over 300 of these were over 10000 milliseconds. THis is on an 8 way Dell with 8 gig of RAM. Looking for opinions, how bad does this look compared to other servers you are taking care of? Cache hit ratio is at 99 % and system queue length still under 1, but this does not look good.

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved