I receive the following result set from TableA (In Time)
7/9/2013 9:27:00.000 AM
7/9/2013 10:24:00.000 AM
7/9/2013 11:25:00.000 AM
7/9/2013 1:23:00.000 PM
7/10/2013 7:27:00.000 AM
Then we receive the following result from TableB (Out Time)
7/9/2013 9:30:00.000 AM
7/9/2013 10:29:00.000 AM
7/9/2013 1:37:00.000 PM
[NULL]
[NULL]
We may not always get Out Times in TableB so I want to merge these into one table to have the In Time and Out Time in separate columns in that one table. In this example with the red type those should be In Time and Out Time for mapped unique identifiers from each table and yet the purple color coded example would have an In Time of 11:25 AM and the Out Time would remain as NULL.
I am using this block of code but is not working the way I want it to because the 11:25am In Time is getting mapped to the 1:37pm Out Time.
and out_time = (select min (out_time)
FROM tableB WHERE
tableB.record# = tableA.record#
and tableB.loc_id = tableA.loc_id
GROUP BY tableB.record#, tableB.loc_id )
It seems I need to focus on the minimum datediff for each record line but can't figure that part out.
I have a report that calculates mean, min, max, stddev of somer exercises for a class (pushups, situps, trunk lifts, etc). I also have to calculate those for the 1-mile run time (ex: data -- 7:56, 6:35, 9:45 ( in minuteseconds). Obviously the standard Avg(), Min(), Max(), StdDev() functions won't work for time. So I put in some custom code to convert the time to seconds so I can use the standard functions on the seconds and also code to convert that answer back to minuteseconds. Max() works, but for example when I try to calculate the min(), it includes nulls from the dataset as zeros (not every student has to do the mile run). So the min is always 0. How can I exclude nulls from being calculated. The min() function excludes nulls so it returns the correct min() on integer data. What else can I do?
I need to provide a minimum value over a 12 hour time range of data. I'm struggling with performance issues due to the amount of data. Currently I log about 100 devices reporting once per minute into a table. Also about once per minute I need to pull the minimum value reported for each device in the last 12 hours. Currently I'm maintaining a separate table with entries for just the last 12 hours and just performing a Select Min(Temp) Where DeviceID=x, but it already holds about 700,000 records at any given time. The number of devices will increase substantially and this will no longer be viable.
Sample Table ID DeviceID Temp InsertDate 1 10 55 04-28-2015 8:00 AM 2 65 74 04-28-2015 8:00 AM 3 44 23 04-28-2015 8:00 AM 4 10 87 04-28-2015 8:01 AM 5 65 65 04-28-2015 8:01 AM
I want to create a View, which contains columns from two tables, and an additional column, which should calculate the difference between two dates.01/05/2005 7:30 AM - 01/05/2005 8:00 AMThe column should be a calculated column,Can I assign the value of a column to a user defined function?In the UDF, how to get the difference between those two dates to be: "00:30"Are there SQL Server functions to time and date that allows me to do so? I have dateadd, datediff, but I am unable to figure out the 2 problems above.thank you,
In my table i have two cols and datatype datetime, for example they has the values,--------StartDate --------------------------------------- StopDate ------______________________________________________01/05/2005 7:30 AM ------------------------ 03/05/2005 10:00 AM13/05/2005 2:30 PM ------------------------- 01/08/2005 8:00 PM_____________________________________________How do I find out the avg time difference between StopDate and StartDate? I tried AVG(StopDate - StartDate ), but it's giving me the following error message. "The average aggregate operation cannot take a smalldatetime data type as an argument."Thanks for any reply.
I have the following staement : AVG( CASE WHEN EventTypeName=''EventA'' THEN CAST(DateDiff(s,EntryTime,EventDateTime) as float) END ) as Duration1 but the problem here is that i need to find the difference between two EventTypes EventA and EventB NOT EntryTime and EventDateTime of EventA so how can i re-do the above query to get: EventA - EventB Not EntryTime - EventA NOTE: EventDateTiem is the time that the EventA occurred.
advance thanks to all...can anybody helpme soon........... i am using sql server 2005........i want to find out time difference between two dates in HH,MM,SS format eg i want the time difference of these two dates 02/Nov/06 9:14:21 AM 19/Apr/07 11:52:31 AM now i am using this calculation in my procedure is as follows...but i think this is wrong...if we will seperatly calculate like this wrong i am getting set @totaltravelHr=datediff(HH,@datediff1,@datediff2) set @totaltravelMI=datediff(MI,@datediff1,@datediff2) set @totaltravelSE=datediff(SS,@datediff1,@datediff2)
I have a table containing following Columns plus other columns: Start Date 04-01-2002(mm-dd-yyyy) varchar(10) Start Time column 09:30:30(hh:mm:ss) varchar(8) End Date 04-05-2002(mm-dd-yyyy) varchar(10) End Time column 09:45:30(hh:mm:ss) varchar(8)
1]We want to create a view for Report Users So that they could just select the other columns and difference in start Date/time to End Date /Time. 2]The Difference in time should be based on Business Day. i.e. Saturday & Sunday should be excluded. & 3]Each business day is defined as 8.00 am to 5 .00pm ( 1 Business day = 9 hrs) 4]The time is stored as 24 hour clock.
For e.g. Start Date Start TimeEnd Date End Time Desired Output 04/01/2002 10:00:0004/01/2002 3:30:00 0 Days 5:30 Hrs 04/01/2002 16:00:0004/02/2002 10:45:00 0 Days 3:45 Hrs 04/01/2002 09:00:0004/03/2002 10:45:00 2 Days 1:45 Hrs 04/05/2002 16:30:0004/08/2002 9:45:00 0 Days 2:15 Hrs****** ******In last case as 04/06/2002 & 04/07/2002 were Saturday & Sunday ,hence excluded.So the difference of time is just 2:15 hrs
Can you please give me a suggestion/pseudo code/logic on how I might do this? We cannot use cursors as we want to make a view.This is for SQL Server 2000
I have a start_datetime column and an end_datetime column that records when a process we run starts and stops. I am trying to write a query that can calculate the difference between the start time and the end time and display it as hh:mm:ss but cannot seem to come up with a solution. Any ideas?
Is there anyway i can write down a query to get the sum of the counter with 2 minutes difference. I may take time slot between 9 AM to 10 AM so i should get the sum of the counter with 2 minutes gap in a single query.
I am new to SQL Server and am trying to write a query that subtracts the time difference in the same column. I need to know the time difference between BatchSequence 2, LoadSequence 1 and BatchSequence 13, LoadSequence 3. Below is an example of the data. Thanks in advacne for the help!
BatchSequenceLoadSequenceScanLabelProcessDate 13 1Part Number11/1/2007 6:08:02 AM 13 2Scan Trace11/1/2007 6:08:03 AM 13 3Slot Position11/1/2007 6:08:04 AM 2 1Part Number11/1/2007 5:53:06 AM 2 2Scan Trace11/1/2007 5:53:07 AM 2 3Slot Position11/1/2007 5:53:08 AM
Hi,I have a table called Bookings which has two important columns;Booking_Start_Time and Booking_End_Time. These columns are both of typeDATETIME. Given any day how can I calculate how many hours are availablebetween the hours of 09.00 and 17.30 so a user can see at a glance how manyhours they have unbooked on a particular day (i.e. 8.5 hours less the timeof any bookings on that day), can this be done with a queryor do I have to work it out in my code?Thanks for your help
It has been a while since I have used SQL server and require to show a time difference between a start and end time, with only 1 date to determine these times
SQL select Arrival_date locationdate as LocDate, locationid as Loc_ID, locations.loc_name as Location_Name from current_locations left outer join locations on locations.loc_id = current_locations.location_id where current_locations.Attend_ID = '1234567'
Results Arrival Date LocDate Loc_IDloc_name 26/02/2013 19:2126/02/2013 19:271270Queue 26/02/2013 19:2126/02/2013 19:341278Dept 1 26/02/2013 19:2126/02/2013 21:10222Dept 2 26/02/2013 19:2127/02/2013 02:5631Left Department
What I want to acheive is the following where the Start location date is either the arrival date or the next location date and the endLocDate is the next finish date.
I want to group the records on the time difference
declare @tbl as table(id int,intid int,val int,dt datetime) insert into @tbl select 1,1,10,'03/31/2006 15:05:22' union all select 2,1,12,'03/31/2006 15:10:22' union all select 3,1,15,'03/31/2006 15:15:22' union all select 4,1,12,'03/31/2006 15:25:22' union all select 5,1,8,'03/31/2006 15:30:22' union all select 6,1,6,'03/31/2006 15:35:22' union all select 7,1,4,'03/31/2006 15:40:22' union all select 8,1,3,'03/31/2006 15:45:22' union all select 9,1,10,'03/31/2006 15:50:22'
declare @tbl1 as table(intid int,Tm int,val int) insert into @tbl1 select 1,5,10
I want a output such that when the val in @tbl goes below the val in @tbl1 for the Tm mentioned in @tbl1 then the time difference should be shown.For example record 1 it starts with 10 the records remain more than 10 till record number 5.From 5 the records remains lower than 10 till record number 9.So I need to show the the time difference from record number 5 till 9. But there is a catch.In @tbl1 there is column named Tm.The time difference sould be calculated only if the diff more than Tm value in @tbl1. For example if the value of Tm is changed to say 25 then the there is no need to show the time difference since the time difference value from record 5 to record 9 is less than 25. Hope I am clear.
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 have a data set like so:UTC_TIME Timestamp NodeID Message FlagLineStation11/19/2005 10:45:07 1132397107.91 1 3 5 1028103411/3/2005 21:05:35 1131051935.20 2 3 5 1009104311/25/2005 21:12:16 1132953136.59 3 3 5 10371049I added the UTC_TIME column in as aconversion of the unix timestamp inthe TIMESTAMP column.Keeping things simple and straightforward, I need to be able tocalculate the difference from one record to the next (ordered byTIMESTAMP or UTC_TIME) and output the result into another column in thetable.NODEID is the unique id.First, what is the function to do so if, say, I only wanted tocalculate the difference between 2 records as just a basic SELECTstatement. That way I can answer quick question based on any one or twoNODEID's.Second, how would I further that to continually calculate (as statedabove)?WOuld this be a stored procedure? A trigger? A cursor?I am learning as I go here. Any help is greatly appreciated.R.
I am using SQL 2000 and/or SQL 2005 to link to an Oracle Db usingOPENQUERY.The SQL 2000 box is SP4 on Win2000.I can run a query thru Q.A. for SQL 2000 or I can run an Oracle querythru SQL-Plus to see the data. But, if I run a date-query using thelinked server then SQL Server returns a TIME that is off by 4 hours.SQL Query:SELECT * FROM OPENQUERY( ORASERVER, 'SELECT MYDATE FROM MYTABLE')Oracle Query:'SELECT MYDATE FROM MYTABLE'Oracle results:04/16/2006 01:07:00SQL 2000 result:04/16/2006 05:07:00Has anyone seen this before? Is there a fix?I have searched the newsgroups and MS Kb but can not find an answer.Thanks,Marty
I am setting up a monitor to alert me if an SQL job has failed in the "last 20 minutes". This should run 24 hours a day, 7 days a week. My query looks something like this.
select * from TALMAIN.msdb.dbo.sysjobhistory where job_id = '7139D5D1-CD88-46E8-8324-5D5A0D8D3A27' and run_status <> 1 and DATEPART(YYYY,GETDATE()) = substring(convert(char(8),run_date),1,4)and DATEPART(MM,GETDATE()) = substring(convert(char(8),run_date),5,2) and DATEPART(DD,GETDATE()) = substring(convert(char(8),run_date),7,2)and DATEPART(HH,GETDATE()) = substring(convert(char(8),run_time),1,2)and (DATEPART(MI,GETDATE()) - substring(convert(char(8),run_time),3,2)) <= 20.
The run_date and run_time columns in msdb..sysjobhistory are stored as integers. Tried a couple of things, but I am unable to convert both of them to datetime data type. The last conditions in the above logic hold true for only "2 digit" hour and minute values.
What about time values like 00:05 AM and single digit time values like 1:00 AM and 9:05 AM, for example?. I pasted some sample run_date and run_time values from sysjobhistory below.
run_date run_time
2006122821510 -- 02:15:10 AM (how to get the minute count?) 2006122821510 -- 02:15:10 AM (same as above) 20061227233014 -- 23:30:14 PM (this is strt forward) 20061227233014 -- 23:30:14 PM (same as above) 200612273016 -- 00:30:16 AM (how to get minute count?) 200612273015 -- 00:30:15 AM (how to get minute count?)
Is there a simpler logic to achieve this? Hope I was clear, else let me know. Please advise. Thank you.
Could any one please tell me how to find time difference between two dates?
i have two fields in database as datetime data type.i need to get time difference between this two fields.how to do that? i use this one SELECT outdate, (datediff(mi, outtime, intime) / 60.0)AS TimeUtilized FROM breaktime but it giving me results as 1.00000000 1.250000 3.00000 i jus want it to be 1 1.25 3 How to do this? Thanks for any help.
I have seen in this forum that many people has posted like my query time taken problem. But I couldn't get clear solution for this.
Here is the my issue:- I have written couple of batch query to bring data from another sql server. I have used proper index and index hints for these query. I have used SET NOCOUNT ON in stored procedure.
I am having the time difference for one of the blow qeury in various running process that executed in QA as single query , stored procedure for whole batch query and schedule this stored procedure in job
sotored Proc = 31 second > job =28 second > seperate query = 12 second.
one of 15 batch query
declare @last smalldatetime set @last ='11/08/2002'
UPDATE ENTERPRISE_STATE SET RESP_BUYER_FST_NAME = i.first_name, RESP_BUYER_LST_NAME = i.last_name FROM oesd.dbo.individual i WITH (NOLOCK index(XPKindividual)) INNER JOIN Leap.dbo.fnIndBuyer(@last) fn ON fn.individual_num=i.individual_num WHERE ENTERPRISE_NUM = fn.enterprise_num
Anbody can able to give proper reason why I am getting this time difference.
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
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 have a Stored Procedure (SP) that creates the data required for areport that I show on a web page. The SP does all the work and justreturns back a results set that I dump in an ASP.NET DataGrid. The SPtakes a product area and a start and end date as parameters.Here are the basics of the SP.1.Create temp table to store report results, all columns are createdthat will be needed at this point.2.Select products and general product data into the temp table.3.Create a cursor that loops through all the products in the temptable, running a more complex query with each individual product.4.The results of that query are updated on the temp table based on thecurrent product of the cursor.5.A complex "totals" query is run and the results from that areinserted into the temp table as the last 3 rows.In all we are talking about 120 rows in the temp table with 8 columnsthat are mostly numbers.I originally wrote this report SP about a month ago and it worked fine,ran in about 10 - 20 seconds based on server traffic and amount ofdata in the temp table. For the example I'm running there are the120 products.Just yesterday the (SP started timing out and when I ran the SPmanually from Query Analyzer (QA) (exec SP_NAME ... ) with the sameparameters as it was getting in the code it took 6 minutes to complete.I was floored. I immediately copied the SQL out of the SP and pastedinto another QA window, changed the variables to be hard coded valuesand ran it. It completed in 10 seconds.I'm really confused now. I ran a Profiler on the 2 when I ran themagain. The SQL code in QA executed again in ~10 seconds with 65,000reads. When the SP finished some 6 minutes later it had completed witthe right results but it needed 150,000,000 reads to do its job.How can the exact same SQL code produce such different results (time,disk reads) based on whether its in a SP or just run from QA but stillgive me the exact same output. The reports both look correct and havethe same numbers of rows.I asked my Sys Admin if he had done anything to anything and he saidno.I've been reading about recompiles and temp table indexes and allkinds of other stuff that could possibly be affecting it but havegotten nowhere.Any ideas are appreciated.
I have two nvarchar fields with time data 12:34:34 and the second one 12:34 I want to calculate the difference in Hours. The first field is called (OTIM) the second field is called (ReportedTime) if the name matters. I tried substring to trim the OTIM, I am unable to make it work.
