Time Difference In Sql Server

May 21, 2007

hi to all,
very urgent query

how to minus the two times(hh:mm:ss)in sql server

regards
ammu

View 2 Replies


ADVERTISEMENT

Time Difference In SQL Server

Dec 6, 2007

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

View 20 Replies View Related

Linked Server Time Difference

May 31, 2006

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

View 1 Replies View Related

SQL Server 2008 :: Time Difference With Dates In Same Column

Mar 17, 2015

How do I find the time difference when the dates are in one column? I need to find hours and minutes between each row.

CREATE TABLE #Time ([TimeStamp] DATETIME, TimeDiff INT)
INSERT INTO #Time (TimeStamp)
VALUES ('2014-09-02 07:51:02.810'), ('2014-09-02 07:48:09.567'), ('2014-09-02 08:37:09.647')
, ('2014-09-02 16:16:42.593'), ('2014-09-02 08:06:13.387'),('2014-09-02 14:32:00.113')
DROP TABLE #Time

View 6 Replies View Related

SQL Server 2008 :: Get Time Difference Of Char Datatype Column Value

May 29, 2015

How can I get time difference of the following record :

STARTTIME ENDTIME
3:30 PM 4:30PM
7:30 PM 8:30PM

I have tried it by below query,

SELECT CONVERT(TIME,STARTTIME,108) - CONVERT(TIME,ENDTIME,108) FROM BATCH_MASTER

but it gives following error message

[color=red]Operand data type time is invalid for subtract operator.[/color]

View 6 Replies View Related

SQL Server 2008 :: How To Find Time Difference Between Two Rows Of Record

Nov 6, 2015

I have the table with the similar set of records which mentioned below, find the time difference between two rows of record. By Using the MsgOut column i have to find time taken b/w PS & PV and some record doesnt have PV .

LogID LocIDClientCert MsgType MsgOutMessageTimeStamp System
1151334934NOT SPECIFIEDQ_T12PS 2015-10-01 00:00:40.980AHR
1151335243NOT SPECIFIEDD_T12PV 2015-10-01 00:00:53.800AHR
1151342944NOT SPECIFIEDQ_T12PS 2015-10-01 00:05:40.957AHR
1151343281NOT SPECIFIEDD_T12PV 2015-10-01 00:05:53.670AHR
1151350046NOT SPECIFIEDQ_T12PS 2015-10-01 00:10:40.970AHR
1152760563759NOT SPECIFIEDQ_T12PS 2015-10-01 15:28:29.617AHR
1152760739690NOT SPECIFIEDQ_T12PS 2015-10-01 15:28:33.633AHR

View 6 Replies View Related

SQL Server 2008 :: Calculated Time Difference Column In Create Table

Jul 14, 2015

How to include a time difference column using 2 other date columns during creation of a table ?

The requirement is to create a table and include the following columns:

1.Downtime start date & time
2.Downtime end date & time
3.Downtime Duration in hh:mm (calculated date difference between column 1 and 2)

View 3 Replies View Related

Time Difference

Aug 19, 2005

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,

View 10 Replies View Related

Avg Time Difference

Aug 21, 2005

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.

View 1 Replies View Related

Need Help In Working Out A Time Difference ..

Oct 23, 2007

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.
 
thanks
robby

View 8 Replies View Related

Time Difference Between Two Dates

Apr 10, 2008

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)
 
 

View 9 Replies View Related

Date/Time Difference

Apr 23, 2002

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

View 1 Replies View Related

Finding Time Difference

Jan 15, 2003

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?

View 2 Replies View Related

How To Get Sum Of Counter With Time Difference

Dec 9, 2013

Table

DateField--------------CNT-1 CNT-2 CNT-3
2013-12-09 09:00:00.000 8 1 2
2013-12-09 09:01:00.000 3 7 1
2013-12-09 09:02:00.000 1 2 3
2013-12-09 09:03:00.000 2 5 2
2013-12-09 09:04:00.000 1 1 7
2013-12-09 09:05:00.000 2 3 2

My requirement is to get the output in the following format if i take the time with 2 minutes difference

DateField ------------------ Sum-CNT-1 Sum-CNT-2 Sum-CNT-3
09:00:00.000 - 09:02:00.000 12 10 6
09:02:00.000 - 09:04:00.000 3 6 9

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.

View 1 Replies View Related

Calculating Time Difference

Jul 20, 2005

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

View 2 Replies View Related

How To Get Average Value Of Time Difference?

Apr 2, 2008

My table has two datetime columns (TheatreArivalDate and TheatreDepartDate). Can I get an average value of the time differences of them? Thanks

View 1 Replies View Related

Calculating Time Difference Between Two Different Dates

Dec 9, 2011

I have data in which i need to calculate employees working hours for a day...

name time in_out
manisha 2011-01-01 9:30:00.000am 1
manisha 2011-01-01 10:30:00.000 0
manisha 2011-01-01 10:45:00.000 1
manisha 2011-01-02 1:00:00.000am 0

How can i calculate time in that two dates as 1 is for entry an 0 is for exit..

View 2 Replies View Related

Compare Time Difference Action

Jan 13, 2012

Is it possible to send a sql statement to update column 1 where the current time is 10 mins older than the time in col 2.

Column 1 is holding a true statement that we need to update to false if over 10 mins old...

View 8 Replies View Related

Time Difference Between Sequence Dates?

Feb 27, 2013

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.

StartLocDateEndLocDateTime DifferenceDept
26/02/2013 19:2126/02/2013 19:2700/01/1900 00:06Queue
26/02/2013 19:2726/02/2013 19:3400/01/1900 00:07Dept 1
26/02/2013 19:3427/02/2013 02:5600/01/1900 07:22Dept 2

View 4 Replies View Related

Grouping The Records On The Time Difference

May 14, 2008

Hi Guys,

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.

Please help me out.

Regards

View 3 Replies View Related

Minimum Time Difference Between Tables

Sep 3, 2013

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.

View 2 Replies View Related

Calculate The Difference Time Between Two Times?

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

Calculating 'time Difference' Between Two Records....

Dec 21, 2005

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.

View 4 Replies View Related

Calculating Time Difference In Msdb..sysjobhistory

Dec 28, 2006

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.

DATEPART(HH,GETDATE()) = substring(convert(char(8),run_time),1,2)and (DATEPART(MI,GETDATE()) - substring(convert(char(8),run_time),3,2)).

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.

View 5 Replies View Related

Transact SQL :: Getting Time Difference In Hours And Minutes?

Jul 10, 2015

Currently my script is using the below mentioned query to find the time difference.

DATEDIFF(HH,DATEADD(SS,hcreacion,fcreacion) ,DATEADD(SS,hcerrar,fcreacion))

If there is 1 hr 30 minutes time difference, I am getting 2 hours as output. But we need 1.30 as output. is there any way to achieve this?

View 14 Replies View Related

Transact SQL :: Total Difference Time In Last Column?

May 23, 2015

I have the below query

DECLARE @GivenDate DATE='2015-05-13'
create table #table (LedgerID int,AttDate Date, checkedtime time,checkedtype varchar(1))
insert into #table (LedgerID,AttDate,checkedtime,checkedtype) values (1232,'2015-05-13','09:01:48.0000000','I')
insert into #table (LedgerID,AttDate,checkedtime,checkedtype) values (1232,'2015-05-13','13:05:52.0000000','O')
insert into #table (LedgerID,AttDate,checkedtime,checkedtype) values (1232,'2015-05-13','14:10:25.0000000','I')

[code]....

the result is like below

i need 'TotalDiffernceTime' column as new column (OUT1-IN1)+(OUT2-IN2).

i am using SQLServer 2008 R2

View 8 Replies View Related

Transact SQL :: Getting Time Difference Between Same Column In Different Rows

Jun 21, 2015

I have a table data like below

id         type      timestamp
1001    start1    10:34:23:545
1001    start2    10:34:24:545
1001    end2     10:34:24:845
1001    end1     10:34:25:545
1002    start1    10:34:25:645
1002    start2    10:34:25:745
1002    end2     10:34:25:945
1002    end1     10:34:25:965

I need the result as follows

id              millisecond diff start1end1                 millisecond diff start2end2
1001    end1 timestamp-start1 timestamp    end2 timestamp-start2 timestamp
1002    end1 timestamp-start1 timestamp    end2 timestamp-start2 timestamp

SQL Server 2008 R2

View 5 Replies View Related

How To Get Time Time Difference In Sql?

Nov 30, 2007

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.
 
 

View 5 Replies View Related

Time Difference Sotored Proc &> Job &> Seperate Query

Dec 4, 2002

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.

Thabnks,
Ravi

View 3 Replies View Related

Returning Time Difference As Days/ Hours/ Minutes

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

SQL 2012 :: Calculate Time Difference For Consecutive Rows?

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

Stored Procedure Vs SQL Huge Difference In Execution Time

Jul 23, 2005

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.

View 5 Replies View Related

Nvarchar Fields With Time Data - Calculate Difference In Hours

Sep 19, 2014

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.

View 3 Replies View Related







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