Transact SQL :: How To Calculate Estimated Completion Time Of A Job
Nov 4, 2015
How to calculate estimated completion time of a job and what is the variance/difference in time based on previous job history. Looking for tsql query which can accomplish this.For example)...Daily a job is taking 10 mins to complete. However, today due to some reason, the job is running over an hour and still running. It could be a blocking issue or some performance issue on the server due to which the job is still running.
In such cases, using a tsql query or a stored proc which monitor these jobs every 3 mins (Configurable value), so every 3 mins , query has to check, if they are any jobs which are taking more time than its usual completion time/avg completion time in that case shoot an email using dbmail functionality i.e. sp_Senddbmail .. From there, DBA can dig further using waits or sql trace etc...
Sometimes when I do "alter database ABCD set partner failover" I get the following message: Nonqualified transactions are being rolled back. Estimated rollback completion: 100%.
In 99 percent of the cases after such message the first attempt to use an open connection would also raise an error such as "Exception: A transport-level error has occurred when sending the request to the server. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.)"
After the first error all subsequent queries would run perfectly.
I am running an update query.It is taking long time. To find the estimated completion time i checked sys.dm_exec_request or sys.dm_exec_session or sp_who2 but there is no clue. It is showing as zero.
I need a formula to calculate the time (let's say in minutes) between two dates/times. The problem is that I have to exclude the time between 06 PM and 06 AM and also exclude the time in the weekend (Saturday and Sunday). I will use this in a couple of reports made in Reporting Services. If anyone have an algoritm that could be modified for this and is willing to share this I would be very grateful. Many thanks! /Per Lissel
Hi i am trying to make a trigger that calculates a new due time from a start time. I have a hour target like 24 hours, but the dates have an starttime and stoptime or the days could even be closed. I need it to calculate the new due datetime using the 24 hours as effective working time.
How do you build a query to calculate the time of employement or any space of time for each person in a table? I'm thinking I would want to use a "Current Date" and "Select Datediff" calculations of some sort and reference the hiredate against the current date but I have been unable to find much on this type of query.. Please help..
Can anyone help with the following Transact SQL question? Thanks. Ineed a store procedure to return the the result recordset which will beexecute from a web page. The database has tables, A and B. For each Arecord, there are many related B records. In the B table there is atimestamp field which tracks the change of A record. For example, A1has B like the followings:ID TimeStamp Chg Code Descption== ========= ======= ========A1 1138375875 E null //end of the eventA1 1138025002 S resumeA1 1137092615 S don't careA1 1137092570 S stopA1 1137092256 I null //start of theeventI need to generate all records in table A and total elapse time foreach record, but B with Chg Code 'S' that has "don't cacre" to bededucted from the total time, so that the result will be like this:ID Name TotalTime(seconds)== ==== =======A1 xyz 351187
Need calculating the count of events on each day. I have an event log table as shown below.
DECLARE @EventLog TABLE ( event_id int, status_id varchar(20) , dttm datetime ) INSERT INTO @EventLog SELECT '100','Inplace','01/05/2015 05:00' UNION ALL SELECT '100','Removed','03/05/2015 10:00' UNION ALL SELECT '100','Inplace','05/05/2015 10:41' UNION ALL
[Code] ....
I need an output to look like on each day how many events were in place.
E.g.: for the month on May , Event_id 100 was inplace on 1st and removed on 3rd , so on 1st may 2 event_ids (100, 103) hence 2 .
event_ids - 103 / 104 and inplace the whole month and not removed. so its calculated the count as 2 til towards teh end of the month.
So I need to count of all each event_id in place for each day in may.
How do I author a SQL Query which meets the following requirement: Display a ratio of (instances where status is a success)/(instances where status is a success or instance is a failure). The below syntax is incorrect. However, I hope to beter explain what I am trying to accomplish using the incorrect SQL query below.
SELECT SUM(Case When Status = "SUCCESS")/SUM( Case When Status = "FALLBACK") FROM ruleResults;
Ok, I know that there is a very smart programmer out there that can resovle my issue.
I am trying to calculate time worked by 15 minute intervals.
Example: Emp 1 started work at 13:00:00 and worked 183 minutes Emp 2 started work at 17:15:00 and worked 150 minutes Emp 3 started work at 08:30:00 and worked 17 minutes
CREATE TABLE #Attendance( [ID] [int] IDENTITY(1,1) NOT NULL, [StudentID] [int] NOT NULL, [ClassID] [int] NOT NULL, [DateAdded] [datetime] default getdate() NOT NULL ) ON [PRIMARY]
insert into #Attendance(StudentID,ClassID,DateAdded) values(1,1,'2014-10-07 10:38:02.900')
[Code] ....
DateAdded column in first table is nothing but in and out time.
Now I want to prepare a query where I want to consider MIN DateAdded and max DateAdded and calculate the duration of student present in the class.
Validations i need to consider are:
If class is starting at 10am then student can come at 9:50am, i.e. Dateadded column should consider as student present in that class if value is less that 10 minutes of StartTime from #ClassAttendance table. Class End time i want to calculate depending upon ClassMinutes from #ClassAttendance
Also DateAdded column should be 10 minutes plus compared to calculated endtime. If its more than that consider lower DateAdded time.
And by using this thingIi want to calculate total number of minutes student present in the class and number of minutes absent.
If there is only one DateAdded for class then consider as a absent student.
i am using this expression to get the time difference between two times.
{%Z.elapsed.time(@AK.VD.depart.date,@AK.VD.depart.time,@AK.VD.depart.date,@DV.VD.arrival.time,"hh.hh")*60} as [LOS (min)]
When Arrival time and depart time both are on same day above expression working to get the diference .
But if arrival date 2013-09-20 00:00:00.000 and arrival time 0800 and depart date 2013-09-21 00:00:00.000 and depart time 0050 when i calculate the time difference(using above expression) between these two i am getting -429.60 which is wrong. i have to get around 990.
I am using SQL Server 2000. I need to query my database for all thecontracts that came in during a certain time frame (user is promptedfor reportingperiodid).Table - PeriodsFields - Reporting Period id intReporting Period desc varchar(30)Reporting Period Begin Date datetimeReporting Period End Date datetimeIf the user selects a 3 then the begin date is Jan. 1, 2004 and theend date is June 30, 2004.Now I need to calculate did any money come in for each week in thattime frame. I need to create a weekly list of all the weeks in thattime frame. Each time frame begins on a Monday. So my list wouldlook like1/5/20041/12/20041/19/20041/26/2004All the way to the end of that time period.How do I create this weekly list from a given time period using T-SQL?I would appreciate any and all help on this.Thanks,Tony
For the second record, ItemsStatus=1 which means the item return to stock, at the time the running stock value calculation should be the previous row Running Stok value (=9 ) +(ItemQuantity*ItemUnitWeight)When the ItemStatus=2, that means the item is definitely out and will be never back to current stock. Is there a way to get that calculation field ?
I have a number of databases with large tables. I need to update them from time to time. I want to get the recordcount of the table and calculate based on that the amount of time it would take to update the table. Any idea who I can do this? I'm using coldfusion 8 with sql to do this. Any advice would be appreciate!
I am trying to create a "Loan Ledger Card" in SSRS which does the calculation up to report extracting time. I was able to create it on Excel (Click to download the Excel file) as we can calculate amounts for each installment based on Row and Column name. You can test it by putting any amount (Here it should be below 62000 as it is the total balance of loan) in "Amount Paid" yellow cell and see the effect.
I have made a selection in SQL (attached query and result in screenshot). It calculates the total paid amount by now on the first row, and all due amounts in other rows. Basically, It should first deduct the interest from first row, then principle, same process for second row and so on until it covers the whole paid amount.
SELECT '0' As RN ,memrepay.lnr ,'Repayment' AS Type ,SYSDATETIME() AS NOW ,SUM(memrepay.mprinc) As PrincPaid ,SUM(memrepay.mint) As IntPaid
[Code] .....
How can I do the similar calculation of Excel file in "SQL query" or "SSRS custom code"?
table A-NewParts PartNumber Description LaunchDate a123 product a 4/1/2009
[code].....
I want to get end result as sales for each new product for a rolling period from Launch date as year1 sales,year2 sales...and so on which I got through case statements.I am stuck on how to get the total Sales for all products (including new products) in the same rolling period based on the different launch dates for each new product.
PartNumber Description LaunchDate Yr1 Sales Total Yr1 Sales Yr2 Sales Total Yr2 Sales a123
I am on SQL Server 2008 R2. I have a table that contains a field called [Location]. In that field is a UNC path to the physical file on the repository. Is there a way in SQL Server that I can say give me the select sum([Location] UNC file) where criteria? I saw some posts about xp_filesize or xp_GetFileDetails, but I do not see them in master. I am unable to add anything and wondering if there is any native functionality that would allow me to accomplish this!?
I have a table which has name,Speciality,start date and end date. So each person may have 1/more rows .They will have more if they change their specialities. For example if you look at the data below.
AdjusterNameSpecialtyDatestartDateEnd Test Inside Property2009-08-29 2010-07-31 Test Management2010-08-012012-07-31
If we see at the data above Test has 2 rows because he changed his specialty in the middle.My requirement is to calculate the total number of employees in each month for last 2 years in each speciality. For example if we look at the example above, Test was in Inside property from 2009 Aug to 2010 Aug but if i use just the date start and take the month for each adjuster it gives me the number of adjusters started in that year and month but what i want is Test should be counted in all the months for Inside property until 2010 07 month. Which means i want to have the total number of adjusters present by each speciality for each month of last 2 years .
Basically I want to calculate the time spent by S_Users on a particular S_ACTV_CODE:
- S_ACTV_CODE_PREV means the previous active records.
- S_START_TIME is the time of S_DATETIME when a S_ACTV_CODE starts
- S_END_TIME is the time before a S_ACTV_CODE changes to another S_ACTV_CODE
- For the first record, S_ACTV_CODE is null, so there is no S_ACTV_CODE_PREV, so S_ACTV_CODE_PREV is NULL
- For the second record S_ACTV_CODE has some value, but S_ACTV_CODE_PREV is NULL for first record. So second record S_ACTV_CODE_PREV is also NULL
- For the last record (means S_ACTV_IND = 1), the user is currently working on it and S_ACTV_CODE is not changed. So S_END_TIME is a open time and we want to keep it as NULL
I needed to create a stored procedure to lock a user who makes 3 incorect entries of his password. I did it successfully. Now the problem what i have is that i want to lock the user only if he makes the 3 incorrect entries within 30 minutes.
I created a field named "FirstEntryTime" of type datetime that saves the date of the first incorrect entry. I tried to make an if statement:
if (@timesOfEntry <=2 AND DATEDIFF(MINUTE, firstEntryTime,GETDATE()) <= 30) Begin Update myTable set ... End
Iam trying to calculate the number of working days between two dates. Iam getting the uouput as only 1 02 r working days??
select building_number as SchoolID,building_name as Campus, count( distinct( CASE WHEN(( DATEPART(dw, CurDate) + @@DATEFIRST)%7 NOT IN (0,1)) tHEN 1 ELSE 0 END)) as NumberofDaysServed from Sales sl join Buildings b on sl.Building_Num =b.Building_number join students2 s on s.Student_Number= sl.Student_Num join Sale_Items SI on si.UID = sl.UID where CONVERT(CHAR(10),CurDate,120) between '2015-05-01' and '2015-05-07' and VoidReview <> 'v' and SI.INum = '1' group by building_number,building_name order by building_number,Building_Name;
I hope to update a DateTime column value with a Time input parameter. Poor attempt below but it looks like the @ApptTime param is coming in as 10:45:00.0000000 and I might have an existing @SendOnDate as: 2015-10-05 07:00:00.000...I hope to end up with 2015-10-05 10:45:00.000
ALTER PROCEDURE [dbo].[SendEditUPDATE] @QuePoolID int=null ,@ApptTime time(7) ,@SendOnDate datetime
