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...
View 7 Replies
ADVERTISEMENT
Apr 30, 2007
Hi all,
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.
What am I missing?
Avi
View 4 Replies
View Related
May 16, 2015
I have a table like below
i need to calculate total working time against 'AttID' (where I - intime, O - OutTime)
ex :
AttID TotalTime
1 08:02:00
2 07:45:00
View 7 Replies
View Related
Aug 6, 2014
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.
View 3 Replies
View Related
Dec 18, 2007
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
View 3 Replies
View Related
Aug 3, 2015
How can I calculate a DateTime column by merging values from a Date column and only the time part of a DateTime column?
View 5 Replies
View Related
Apr 21, 2006
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.
Any ideas ?
View 7 Replies
View Related
Jan 27, 2008
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..
Thanks..
View 12 Replies
View Related
Apr 5, 2006
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
View 5 Replies
View Related
Jun 17, 2015
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 to calculate the missing dates to ...
View 7 Replies
View Related
Oct 28, 2015
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;
View 2 Replies
View Related
Dec 19, 2007
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
I need to show the following results:
time employee #of_min_worked
----------------------------------------------
08:30:00 3 15
08:45:00 3 2
09:00:00
08:30:00
08:45:00
09:00:00
09:15:00
09:30:00
09:45:00
10:00:00
10:15:00
10:30:00
10:45:00
11:00:00
11:15:00
11:30:00
11:45:00
12:00:00
12:15:00
12:30:00
12:45:00
13:00:00 1 15
13:15:00 1 15
13:30:00 1 15
13:45:00 1 15
14:00:00 1 15
14:15:00 1 15
14:30:00 1 15
14:45:00 1 15
15:00:00 1 15
15:15:00 1 15
15:30:00 1 15
15:45:00 1 15
16:00:00 1 3
16:15:00
16:30:00
16:45:00
17:00:00
17:15:00 2 15
17:30:00 2 15
17:45:00 2 15
18:00:00 2 15
18:15:00 2 15
18:30:00 2 15
18:45:00 2 15
19:00:00 2 15
19:15:00 2 15
19:30:00 2 15
19:45:00
20:00:00
20:15:00
20:30:00
20:45:00
21:00:00
21:15:00
21:30:00
21:45:00
22:00:00
22:15:00
22:30:00
22:45:00
23:00:00
23:15:00
23:30:00
23:45:00
View 1 Replies
View Related
Oct 8, 2014
I am having below schema:
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.
View 7 Replies
View Related
Sep 3, 2015
I have 2 tables as defined below. I want to calculate PS1time and Ps2 time.
Table 1
O_IDP_TYPEP_startdateP_enddate
ABCP8/24/2015 13:148/24/2015 13:41
ABCP8/24/2015 14:038/24/2015 15:31
ABCP8/25/2015 12:098/25/2015 13:25
XYZP8/28/2015 13:108/28/2015 21:44
Table 2
O_IDS_TYPES_startdateS_enddate
ABCS28/24/2015 13:148/24/2015 19:22
ABCS28/24/2015 19:228/30/2015 21:34
XYZS28/27/2015 22:228/28/2015 13:10
XYZS28/28/2015 13:108/28/2015 15:34
XYZS18/28/2015 15:348/28/2015 22:44
OUTPUT
O_IDSP_TYPEPS1_starttimePS1_starttime
ABCPS18/24/2015 13:148/24/2015 19:22
XYZPS18/28/2015 15:348/28/2015 21:44
XYZPS28/28/2015 13:108/28/2015 15:34
For Each O_Id How much time spent for Ps1 and PS2. I tried but not able to reach expecting results as mentioned.
View 3 Replies
View Related
Oct 9, 2013
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.
View 1 Replies
View Related
Apr 29, 2015
Now a sample table is included and an expected result list. I also added a piece of the sql to obtain the results.
The remaining question is how to calculate the total break time in minutes. The first two columns of the results are already in the code.
Sample of table clock
employee check_in check_out
----------- ---------------- ----------------
1 08:00:00.0000000 11:00:00.0000000
2 08:30:00.0000000 12:14:00.0000000
2 12:25:00.0000000 16:00:00.0000000
1 11:30:00.0000000 14:00:00.0000000
sample of table breakt
startt endt
---------------- ----------------
09:45:00.0000000 10:00:00.0000000
12:00:00.0000000 12:30:00.0000000
The result I am searching for:
| Employee | Timemin| Breakmin|
+----------+--------+---------+
| 1 | 180 | 15 |
| 2 | 224 | 19 |
| 2 | 215 | 5 |
| 1 | 150 | 30 |
+----------+--------+---------+
DROP TABLE breakt;
CREATE TABLE breakt(
startt TIME
, endt TIME
);
INSERT INTO breakt(startt,endt) VALUES ('09:45:00','10:00:00');
[Code] .....
View 3 Replies
View Related
Jul 20, 2005
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
View 3 Replies
View Related
Oct 8, 2015
I have the following store procedure :
SELECT APHIST.ReturnDate AS ATDATE
,API_HIST.[ActionPlanItemID]
,API_HIST.[ActionPlanID]
,PIT.[ProductItemID]
,PIT.ProductItemCode
,PIT.Name,
[code]....
What I am trying to get is a RunningStock level column which is able to display stock level as describe below :
If ItemStatus value is 0, that means that the item has been taken out from stock.
So based on that the first row running Stock level is calclulated as
(ProductQuantity * ItemUnitWeight)-ItemQuantity=9...
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 ?
View 6 Replies
View Related
Sep 16, 2015
I have table named #table and three columns. SinGroup(it does not matter, can be anything), Column1, and Column2.
CREATE TABLE #table(
sinGroup NVARCHAR(10)
,column1 INT
,column2 int
);
And i have some data:
INSERT INTO #table(sinGroup,column1,column2) VALUES
('y1',100,0),
('y2',0,60),
('y3',40,20),
('z1',150,0),
('z2',0,50),
('z3',0,50)
I want to know how to get this result set (c1 - c2 and group by Y and Z and the result shown under the column 1):
SinGroup C1 C2
y1 100 0
y2 0 60
y3 40 20
Y 60 0
z1 150 0
z2 0 50
z3 0 50
Z 50 0
And also result like this (c1-c2 and grop by each row,the result show under the column 1):
SinGroup C1 C2
y1 100 0
y1 100 0
y2 0 60
y2 -60 0
y3 40 20
y3 20 0
z1 150 0
z1 150 0
z2 0 50
z2 -50 0
z3 0 50
z3 -50 0
View 5 Replies
View Related
Apr 9, 2015
My data is looks like this,
Date---------------------------------------A
2015-03-01 13:38:07.343----------------1
2015-03-01 14:04:04.460----------------1
2015-03-02 19:33:55.117----------------3
2015-03-02 19:33:55.117----------------4
2015-03-02 19:39:26.580----------------1
I want data looks like this
Date-------------------------------------------A
Day 1------------------------------------------2
Day 2------------------------------------------8
View 7 Replies
View Related
Apr 10, 2008
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!
Thanks
Shuvi
View 2 Replies
View Related
Nov 4, 2015
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"?
View 2 Replies
View Related
Aug 19, 2015
I have 2 tables as follows:
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
[code].....
View 3 Replies
View Related
Nov 16, 2015
I need to build a query to calculate the size of all tables in a database ...
View 5 Replies
View Related
Mar 27, 2014
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!?
View 4 Replies
View Related
Jun 17, 2015
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 .
View 2 Replies
View Related
Jul 14, 2015
I have a data set as -
ID Set Date DB Date
100 Null 07/01/15
100 07/05/15 07/02/15
100 07/10/15 07/08/15
I want to able to get 2 unique dates
1. 07/02/15 - As I want the DB date for ID - 100 when set date changed from a null value to non null value.
2. 07/08/15 - As I want the DB date for ID - 100 when a non null set date changes.
The table has such records for lot of different ID's.
View 29 Replies
View Related
Oct 30, 2015
Col1 Col2 Col3 Col4
54763.00 21 0 0
--Row1
59574.00 23 0 0
--Row2
64085.00 20 0 0
--Row3
0.0 0.00 0 0 0
--Row4
I'm trying to calculate Average of Col1 of above table based on below scenario:
CASE WHEN all the columns in the above table are “0” (as highlighted) THEN I want AVERAGE of Col1 as (Row1+Row2+Row3)/3
ELSE if at least one of the column of highlighted row has value other than “0”, THEN I want the AVERAGE of Col1 to be (Row1+Row2+Row3+Row4)/4
View 7 Replies
View Related
Jun 4, 2015
I have a table "PrintArea" with below table structure.
The Details column have " " separated data.
CREATE TABLE [dbo].[PrintArea](
[ID] [int] IDENTITY(1,1) NOT NULL,
[AccessDate] [datetime] NULL,
[IPAddress] [varchar](16) NULL,
[SerialNum] [varchar](256) NULL,
[Description] [varchar](256) NULL,
[Details] [varchar](2048) NULL,
)
This is how the data lookis like.
INSERT [dbo].[PrintArea] ([ID], [AccessDate], [IPAddress], [SerialNum], [Description], [Details]) VALUES (104280, CAST(0x0000A28A00A88310 AS DateTime), N'70.89.39.161', N'InSign-f529c824-85a6-4a64-8c59-e9f786d718f3-YM2AS31367,BFEBFBFF000306A9', N'Printing', N'Headboard_Insert_4
1
1
7938
5579
[Code] ...
I want to calculate totalAreaPrinted based on below formula from Details column.
View 2 Replies
View Related
Jul 2, 2015
I have a table like this.
CREATE TABLE Table1
([S_ID] varchar(7), [S_ACTV_CODE] varchar(4), [S_USER] varchar(5), [S_DATETIME] varchar(19), [S_ACT_IND] int)
;
INSERT INTO Table1
([S_ID], [S_ACTV_CODE], [S_USER], [S_DATETIME], [S_ACT_IND])
VALUES
('AAA-111', NULL, 'USER1', '2015-06-15 00:21:06', 0),
('AAA-111', '2', 'USER1', '2015-06-15 00:21:07', 0),
[code]....
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
So the result should be as below:
S_ID S_ACTV_CODE_PREV S_ACTV_CODE_CURR S_USER S_START_TIME
S_END_TIME TIME_SPENT (in Sec)
AAA-111 NULL NULL USER1 2015-06-15 00:21:06
2015-06-15 00:21:07 1
AAA-111 NULL 2 USER1 2015-06-15 00:21:07
2015-06-17 03:20:33 183566
AAA-111 2 4 USER2 2015-06-17 03:20:33
[code]....
View 9 Replies
View Related
May 9, 2015
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
View 6 Replies
View Related
Jun 7, 2015
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;
View 8 Replies
View Related
Oct 3, 2015
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
[code]...
View 14 Replies
View Related
