Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    MS SQL Server






SuperbHosting.net & Arvixe.com have generously sponsored dedicated servers and web hosting to ensure a reliable and scalable dedicated hosting solution for BigResource.com.







How To Get Average Value Of Time Difference?


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


View Complete Forum Thread with Replies
Sponsored Links:

Related Messages:
User-Defined Functions To Calculate Average And Relative Percent Difference(RPD):How To Designate The Variables Of Average/RPD
Hi all,


This is my first time to do T-SQL User-Defined Functions programming. I just learned some basic DDL and DML statements. But I do not know how to designate the variables of the User-Defined Function.

I have 3 dbo tables in SQL Server Management Studio Express (SSMSE):

dbo.Projects:
ProjectID ProjectName LabName
1 Blueriver ALSI
2 Greentree GPL
3 Redrock STL-NJ
NULL NULL NULL

dbo.Samples:
SampleID SampleName Matrix SampleType Chemical ProjectID
1 Blueriver01 Water Primary VOCs 1
2 Blueriver02 Water Duplicate VOCs 1
3 Blueriverr03 Water QA VOCs 1
4 Greentree11 SoilPrimary VOCs 2
5 Greentree12 Soil Duplicate VOCs 2
6 Greentree13 Soil QA VOCs 2
NULL NULL NULL NULL NULL NULL

dbo.LabTests:
AnalyteID AnalyteName Result Unit SampleID
1 Acetone 120.80 ug/L 1
2 Benzene 25.60 ug/L 1
3 Trichloroethene 13.00 ug/L 1
4 Xylenes 0.00 ug/L1
5 Acetone 90.70 ug/L 2
6 Benzene 31.40 ug/L 2
7Trichloroethene 19.20 ug/L 2
8 Xylenes 2.00 ug/L 2
9 Acetone 140.30 ug/L 3
10 Benzene 21.50ug/L 3
11 Trichloroethene 22.20 ug/L 3
12 Xylenes 0.00 ug/L 3
13 Acetone 222.10 ug/Kg 4
14 Benzene 10.30 ug/Kg 4
15 Trichloroethene 30.20 ug/Kg 4
16 Xylenes 50.70ug/Kg 4
17 Acetone211.90 ug/Kg 5
18 Benzene 16.40 ug/Kg 5
19 Trichloroethene 34.70 ug/Kg 5
20 Xylenes 60.00 ug/Kg 5
21 Acetone 220.30 ug/Kg 6
22 Benzene 13.20 ug/Kg 6
23 Trichloroethene 32.00 ug/Kg 6
24 Xylenes 55.50 ug/Kg 6
NULL NULL NULL NULL NULL

The Average of chemical/analyte in 2 samples is defined as:
Average = Abs(the result of primary sample + the result of duplicate sample)/2.
Average = Abs(the result of primary sample + the result of QA sample)/2.
Average = Abs(the result of duplicate sample + the result of QA sample)/2.

The RPD of chemical/analyte in 2 samples is defined as:
RPD = Abs(the result of primary sample - the result of duplicate sample)/
Average
RPD = Abs(the result of primary sample - the result of QA sample)/
Average
RPD = Abs(the result of Duplicate sample - the result of QA sample)/
Average

I want to calculate the average and RPD of each chemical/analyte for the following 3 pairs of the related samples:
(i) Between the primary and duplucate samples
(ii) Between the primary and QA samples
(iii) Between the duplucate and QA samples.

In the Table "dbo.Projects", ProjectID is the primary key.
In the Table "dbo.Samples", SampleID is the primary key and ProjectID is the foreign key.
In the Table "dbo.LabTests", AnalyteID is the primary key and SampleID is the foreign key.

I do not know how to designate the variable of each result of analyte in a sample and use it to calucalte the Average and RPD for each pair (i.e. for (i), (ii), or (iii)).
For example: I do not know how to designate the following 2 results:
dbo.LabTests:
AnalyteID AnalyteName Result Unit SampleID
1Acetone 120.80ug/L 1
5 Acetone 90.70ug/L 2

Please help and advise me how to designate the 2 results in T-SQL User Defined Functions to calculate the Average and RPD.

Thanks in advance,
Scott Chang

View Replies !   View Related
Average Turnaround Time - Datediff() Help
Hi everyone, I need some help with creating a report that calculates the average turnaround time in days that it takes for units to return from trips destined to a location.

The database that I am working with lists a trip each time a unit is dispatched to a destination, and then another trip is created for the units return. In the example below I am trying to calculate the number of days that it takes for a unit to return to Vancouver by calculating the difference between the departure date from Vancouver and the arrival date back into Vancouver. I then need to calculate the average number of days that it takes for a unit to return from a trip. See sample data below.


UNIT -- TRIP -- START LOCATION --START DATE--FIN LOCATION--FIN DATE
================================================== =======
U12 ----001 --- VANCOUVER -------FEB 10 ------ ONTARIO ----- FEB 15
U10 ----002 --- VANCOUVER -------FEB 13 ------ ONTARIO ----- FEB 18
U12 ----003 --- ONTARIO ----------MARCH 13 --- VANCOUVER -- MARCH 18
U10 ----004 --- ONTARIO ----------MARCH 1 ---- VANCOUVER ---MARCH 6


Unit U12 took 36 days to return back to Vancouver
Unit U10 took 21 days to return back to Vancouver

Therefore based on the two trips it takes an average of aproximately 28.5 days for a unit to return from trips destined to Ontario.

View Replies !   View Related
Getting The Average Results Based On Time.
Hello~,
The table has columns like this.
________________________________
time smalldatetime
value1 int
value2 int
----------------------------------------------------------

for example,
....
'2006-11-16 12:00:00',100,200
'2006-11-16 13:00:00',110,210
'2006-11-16 14:00:00',120,220
....

The record is inserted at every hour.

I want get daily,monthly,yearly average and display the result ordered by time.

View Replies !   View Related
Average Processing Time With Your Cubes
I am running into a barrier and need to understand the average length of time that a fully optimized data cube should take to process.

We are currently running an average of 15 to 20 minutes per cube, with average of 2000 aggregations, 25% performance increase, and approximately 2 million rows, with around 40 dimensions and 30 measures.

I personally think this is a pretty good time to process. However, I am being challenged to reduce this time frame. In theroy I can't possibly see it getting below where we currently are. SO I am reaching out to the group of guru's...

What is your average length of time to process your Data Cubes? Please respond to me at ken.kolk@medcor.com I wouldgreatly appreciate it and need the averages from the field.


View Replies !   View Related
Determine Average Time Between Events
I am trying to figure out how to calculate the average time between phone calls for a user. The initial requirement is to calcualte this on all calls for an entire month but I would guess that would lead to other periods as well, such as daily, weekly, etc. One hurdle is what to do when going from one day to the next. I could possibly just week out any times between calls that are greater than a certain amount of time to address that.
Any way, here is a small sample of what I'll be dealing with. Any ideas on how to approach this or get it to work would be greatly appreciated.




Code Block
CREATE TABLE #avetime (origdate datetime, duration_seconds int, duration_minutes decimal(9,2), phoneuser varchar(20), calltype varchar(15))
INSERT into #avetime VALUES ('Jan 7 2008 9:19AM', 21, 0.4, 'Coleman', 'Long Distance')
INSERT into #avetime VALUES ('Jan 7 2008 9:19AM', 48, 0.8, 'Coleman', 'Long Distance')
INSERT into #avetime VALUES ('Jan 7 2008 9:33AM', 81, 1.4, 'Coleman', 'Long Distance')
INSERT into #avetime VALUES ('Jan 7 2008 9:35AM', 87, 1.5, 'Coleman', 'Internal')
INSERT into #avetime VALUES ('Jan 7 2008 9:37AM', 27, 0.5, 'Coleman', 'Internal')
INSERT into #avetime VALUES ('Jan 7 2008 9:42AM', 13, 0.2, 'Coleman', 'Long Distance')
INSERT into #avetime VALUES ('Jan 7 2008 9:43AM', 84, 1.6, 'Coleman', 'Long Distance')
INSERT into #avetime VALUES ('Jan 7 2008 12:00PM', 914, 15.2, 'Coleman', 'Long Distance')
INSERT into #avetime VALUES ('Jan 7 2008 2:24PM', 3, 0.1, 'Coleman', 'Internal')
INSERT into #avetime VALUES ('Jan 8 2008 10:13AM', 21, 0.4, 'Coleman', 'Long Distance')
INSERT into #avetime VALUES ('Jan 8 2008 10:33AM', 482, 8.0, 'Coleman', 'Long Distance')
INSERT into #avetime VALUES ('Jan 8 2008 11:49AM', 56, 0.9, 'Coleman', 'Long Distance')






View Replies !   View Related
Time Difference
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 Replies !   View Related
Avg Time Difference
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 Replies !   View Related
Time Difference Between Two Dates
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 Replies !   View Related
Need Help In Working Out A Time Difference ..
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 Replies !   View Related
Calculating Time Difference
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 Replies !   View Related
Date/Time Difference
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 Replies !   View Related
Finding Time Difference
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 Replies !   View Related
Time Difference In Sql Server
hi to all,
very urgent query

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

regards
ammu

View Replies !   View Related
Time Difference In SQL Server
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 Replies !   View Related
Calculating 'time Difference' Between Two Records....
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 Replies !   View Related
Linked Server Time Difference
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 Replies !   View Related
Grouping The Records On The Time Difference
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 Replies !   View Related
Calculating Time Difference In Msdb..sysjobhistory
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 Replies !   View Related
Latches - Average Waits, Average Duration?
I have been monitoring the average number of latch waits and the average duration of each latch wait on my primary SQL box. On average, I see around 30 latch waits per second with an average duration of under 1 second.

What type of average are you seeing on your production machines? What is a normal average?

I have tried researching for suggested values on these two counters, but I haven't turned up any information.

Thanks,

MV

View Replies !   View Related
How To Get Time Time Difference In Sql?
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 Replies !   View Related
Stored Procedure Vs SQL Huge Difference In Execution Time
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 Replies !   View Related
Time Difference Sotored Proc &> Job &> Seperate Query
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 Replies !   View Related
Returning Time Difference As Days/ Hours/ Minutes
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 Replies !   View Related
Working Out The Time Difference Betwen Two Dates Then Adding Them Up


Hi,
I am trying to write a report that will work out the time difference between two dates and then add up these values to give me a grand total. I want to group these times together for an individual. Basically I want to produce a summary of hours worked for an individual. I can work out the time difference, but when I add them up instead of an nice total of X hour Y mins., I get a huge number and no matter how I try to format this number it does not change.

View Replies !   View Related
Need An Average By Year Of An Average By Month
I have a temp_max column and a temp_min column with data for every day for 60 years. I want the average temp for jan of yr1 through yr60, averaged...
I.E. the avg temp for Jan of yr1 is 20 and the avg temp for Jan of yr2 is 30, then the overall average is 25.
The complexity lies within calculating a daily average by month, THEN a yearly average by month, in one statement.
?confused?

Here's the original query.
accept platformId CHAR format a6 prompt 'Enter Platform Id (capital letters in ''): '

SELECT name, country_cd from weather_station where platformId=&&platformId;

SELECT to_char(datetime,'MM') as MO, max(temp_max) as max_T, round(avg((temp_max+temp_min)/2),2) as avg_T, min(temp_min) as min_temTp, count(unique(to_char(datetime, 'yyyy'))) as TOTAL_YEARS
FROM daily
WHERE platformId=&&platformId and platformId = platformId and platformId = platformId and datetime=datetime and datetime=datetime
GROUP BY to_char(datetime,'MM')
ORDER BY to_char(datetime,'MM');

with a result of:

NAME_________________CO
-------------------- --
OFFUTT AFB___________US

MO______MAX_T _____AVG_T__MIN_TEMTP_TOTAL_YEARS
-- ---------- ---------- ---------- -----------
01_________21______-5.31________-30__________60
02_________26______-2.19______-28.3__________61
03_______31.1_______3.61______-26.1__________60
04_______35.6______11.07______-12.2__________60
05_______37.2_______17.2_______-3.3__________60
06_______41.1______22.44__________5__________60
07_______43.3______24.92________7.2__________60
08_______40.6______23.71________5.6__________60
09_________40______18.84_______-2.2__________59
10_______34.4_______12.5_______-8.9__________59
11_________29_______4.13______-23.9__________60
12_________21______-2.52______-28.3__________60

View Replies !   View Related
How To Convert UTC Time (retrieved From SQL) To Local Time In Reporting Services Based On Time Zone


Hi all,

I have created a report in SSRS 2005 which is being viewed by usersfrom different Time Zones.

I havea dataset which has a field of type datetime (UTC). Now I would like to display this Date according to the User Time Zone.

For example if the date is August 07, 2007 10:00 AM UTC,

then Iwould liketodisplay itas August 07, 2007 03:30 PM IST if the user Time Zone is IST.


Similarly for other Time Zones it should display the time accordingly.

Is this possible in SSRS 2005?

Any pointers will be usefull...

Thanks in advance
sudheer racha.

View Replies !   View Related
Average
Hello,I have two tables:[Posts] > PostId, ...[Ratings] > RatingId, PostId, RatingI want to select all posts and add a new column named AverageRating.This new column is the average of all ratings associated to that Post.If a post was not rated then its AverageRating would be NULL.How can I do this?Thanks,Miguel

View Replies !   View Related
Average Help
if I have a bunch of times, and want an average of them, how do I do that?

Thanks.

For more details, see this thread:

http://www.dbforums.com/showthread.php?t=1215633

View Replies !   View Related
Getting Average?
Using MS SQL 2005 server how can I get the average cost price of item 'shirt' from this table please?

code item description cost retail
---- ---- ----------- ---- ------
191 shirt shirt - white 2.30 9.99
170 pants pants - beige 6.34 15.00
196 shirt shirt - red 2.64 9.99
199 shirt shirt - blue 2.61 9.99

View Replies !   View Related
Average?
I am getting the number of transactions for two different months based on dates entered from the user. I need to display the number of transactions along with the average sale. I have figured out how to get the dates and sum the transactions but I can't seem to add in the average....

(@prevMonthStart datetime, @prevMonthEnd datetime, @thisMonthStart datetime, @thisMonthEnd datetime)

as

select sum(case when orders.orderdate between @prevMonthStart and @prevMonthEnd then 1 else 0 end ) as PrevMonthCount,

sum(case when orders.orderdate between @thisMonthStart and @thisMonthEnd then 1 else 0 end ) as ThisMonthCount

(how do I add in the average for both months? The column is orders.ordertotal)

View Replies !   View Related
Average
Hi All,

I was hopingsomeone could help me with pluging moving average into my report.


This is the template I am using for moving average:
//
/*Returns the average value of a member over a specified time interval.*/
CREATE MEMBER CURRENTCUBE.[MEASURES].[Moving Average]
AS Avg
(
[<<Target Dimension>>].[<<Target Hierarchy>>].CurrentMember.Lag(<<Periods to Lag>>) :
[<<Target Dimension>>].[<<Target Hierarchy>>].CurrentMember,
[Measures].[<<Target Measure>>]
)
// This calculation returns the average value of a member over the specified time interval.
FORMAT_STRING = "Standard";


This is what I have before I started to add the moving average. The report works but only displays the cummulative complexity rank.

WITH

MEMBER [Measures].[Date Key] AS

[Date].[Date].CurrentMember.UniqueName

SELECT

{

[Measures].[Date Key],

[Measures].[ComplexityRank]

} ON COLUMNS,

(

[Work Item].[System_State].[System_State],

(StrToMember(@StartDateParam):StrToMember(@EndDateParam))

)

ON ROWS

FROM [Team System]

WHERE

(

STRTOMEMBER("[Team Project].[Team Project].["+@Project+"]"),

STRTOSET(@IterationParam),

STRTOSET(@AreaParam),

STRTOSET(@WorkItemTypeParam)

)



What I want to do is also add the moving average for this over the parameter of Iterations. (IterationParam). This is what I have so far with the moving average in my report: I know I must have a lot of errors but I can't get past this first error. I highlighted the line with error. The error said: Query(7, 15) Parser: The syntax for '.' is incorrect. (msmgdsrv)


WITH

MEMBER

[Measures].[Date Key] AS

[Date].[Date].CurrentMember.UniqueName,

[Measures].[Moving Average] as

AVG({[Date].[Date].currentmember.lag(@IterationParam):

[Date].[Date].currentmember},[Measures].[ComplexityRank])



SELECT

{

{measures.[moving average],measures.[Internet Total Product Cost]} on 0,

[Date].[Date]. Members

[Measures].[Date Key],

[Measures].[ComplexityRank]

} ON COLUMNS,

(

[Work Item].[System_State].[System_State],

(StrToMember(@StartDateParam):StrToMember(@EndDateParam))

)

ON ROWS

FROM [Team System]

WHERE

(

STRTOMEMBER("[Team Project].[Team Project].["+@Project+"]"),

STRTOSET(@IterationParam),

STRTOSET(@AreaParam),

STRTOSET(@WorkItemTypeParam)




Does anyone haveany suggestions?

Thanks,
Nici

View Replies !   View Related
How To Get Average Of Average.


Hi !

I am having this problem. I have a grouping someting like this.

Group1
Resolved Resolution Time Average Resolution Time
30 2904.46 96.82

54 1442.0326.70

00.00 0.00
00.000.00





------------------------------
Avg : 30.88

Now to calculate the average inside the grouping I have used this


iif(Sum(Fields!ResolutionTime.Value) = 0 ,"0.00",(Sum(Fields!ResolutionTime.Value)/(iif(Count(Fields!ResolvedDate.Value)=0,1,Count(Fields!ResolvedDate.Value)))))

I am at a loss how to calculate the group footer (Result = 30.88 ) which is equal to (96.82 + 26.70 + 0 + 0 ) / 4 . This is the result from the existing Crystal report. I am trying to convert this report to reporting services. Now I cannot nest aggregate functions and also the row number can be dynamic I am confused how to resolve it.

Any ideas will be greatly appreciated.

Thanks in advance.

--Abbi.


View Replies !   View Related
Getting An Average From A Table
I have two tables, table a holds all the votes by users of each element in table b. I was wondering, how do I get the average of all those votes in table a that relate to that each instance of a element in table b. For example table b has two elements created by a certain user that has been voted 5 times each by users with scores like 2, 5, 4, 2 , 2 for both of them. I just need to get the average of those numbers that pertain to those elements in table b. Thanks for any help.  

View Replies !   View Related
How To Get Weekly Average?
I have a view call view_jobComponent and it contain the following fields: date_complete, job_tag_no, part_model, component_id, component_description, component_qty 
If I want to get the average component_qty in a certain time frame (weekly, monthly, qtrly, bi-annually, annually .etc) in a certain time period.
For example, I want to get the weekly average of component_qty for the year of 2005, how should my sql query like?

View Replies !   View Related
Hourly Average
Apologies for the simplicity of the question, but it reflects mycapabilities! I have the following sample fields coming from differenttables:LocationTimeDate (timestamp)DataI need to return the average of Data per Location per HOUR.Thanks.

View Replies !   View Related
How To Do An Average Of A Count
Hi AllWe have an order processing database which includes the standard OrderHeader/Order Lines tables. I'm trying to write a query to get the averagenumber of lines per order over a given period, and I'm stuck :-(I can get the number of lines per order with:SELECT COUNT(*) FROM OrderDetailsINNER JOIN Order Header ONOrderHeader.OrderNo = OrderDetails.OrderNoWHERE OrderHeader.OrderDate ..... {various criteria} ...GROUP BY OrderDetails.OrderNumberBut how do I then get an average of this for the period?TIAMike Bannon

View Replies !   View Related
Average Of Middle 90%
Hello all!This might be a newbie question, and there might be something Im just not thinking of right now, but I have a set of values that I need to get the average of but only from the middle 90%. Example:11 <-From here1234456 <- To here.7I thought I could solve it by subqueries and do the following:Select (((Select sum top 5 order asc) + (Select sum top 5 order desc)) - sum total)/rows*0.9 which would give me what I want, but I realised that when aggregating I cant order the subqueries.This is for an application (that will run the query on a sql-server) that only takes one query (although subqueries should be fine), and thats why I have a problem, I cant build any views or things like that.I guess my question is very simple: How can I get a sum of the bottom 5 percent without sorting descending?

View Replies !   View Related
MDX Average Query
Hi

I have been batteling to get the below right. I am fairly new to MDX but managing to keep my head above water.

I am trying to get the average of the Due vs Delivery. What makes this more interesting is that not all the items have been delivered.
Here is some sample information from the underlying fact table.

Department Size Due Date Delivery Date Due vs Delivery Count
01-Mens 0028 13/04/2006 13/04/2006 0 1
01-Mens 0028 13/04/2006 13/04/2006 0 1
01-Mens 0028 13/04/2006 NULL NULL 1
01-Mens 0028 13/04/2006 NULL NULL 1
01-Mens 0028 13/04/2006 NULL NULL 1
01-Mens 0028 18/04/2006 NULL NULL 1
01-Mens 0028 13/04/2006 12/04/2006 -1 1
01-Mens 0028 13/04/2006 12/04/2006 -1 1
01-Mens 0028 13/04/2006 12/04/2006 -1 1
01-Mens 0028 13/04/2006 12/04/2006 -1 1

I am trying to get to an answer where MDX sums the Due vs Delivery and Devides it by the lines that actualy contains some value and not null. The answer should come out to be -4/6 = -0.67

Any help would be much appreciated

View Replies !   View Related
Rolling Average
I am trying to figure out how to within a query create a count from a two deep gouping and then from that count create a 6 month rolling average...

View Replies !   View Related
Moving Average
Can any one be able to help me to write a querry on Moving Average

Example

Product Volume
Fish

View Replies !   View Related
Moving Average
Can any one be able to help me to write a querry on Moving Average

Example

Product Volume Moving Average Working
Fish 300 300 300 /1
Fish 100 200 (300 + 100) /2
Fish 500 300 (300 + 100 + 500) /3
Fish 100 200 (300 + 100 + 500 + 200 ) /4

Thanks

View Replies !   View Related
Compute Average
Hello,

I have columns in a table and each column may have a value of 0, 1 or NULL.
I see that I am going to have trouble with columns that are NULL. I do not want to use a column if it is NULL as my number of total columns to divide the sum of the totals by. For example:

Col1 Col2 Col3 Col4
0 1 NULL 1

I would want to add Col1, Col2, and COl4 (for a total of 2) together and then divide by 3 (the number of columns that are not NULL). How could I do something like this? Thanks for your help. I have been lost for a solution.

View Replies !   View Related
Average With Condition
Problem

fldyear fldascending flddecending
2007 20 23
2006 21 14
2007 41 12
2007 12 5


how do i find the average of fld ascending and descending where year=2007

Your help will be appreciated

View Replies !   View Related
Finding Average
Hi, I have two tables, Weekly and AverageEngTime. Weekly table has 14 columns, "Mon_Day,Mon_Night,Tue_day,....Sun_Night". AverageEngTime table has 15 columns, "Shifts,Mon_Day,Mon_Night,Tue_day,....Sun_Night". I have inserted "Average" as a value for column "Shifts" in AverageEngTime table. Now how do I find the average for each column in Weekly table and insert into AverageEngTime table in the respective columns, and in the row where Shifts = "Average".

I used the following codes to try but i receive errors at where statement.Please correct me. The expected output as below.

Shifts | Mon_day | Mon_Night | Tue_Day.....
Average | 5.2 | 10.2 | 15.2........

The codes that i tried..

declare @Weekday tinyint, @hour int
select @Weekday = datepart(dw,getdate()),@hour= datepart(hh,getdate())

if (@Weekday= 1 and (@hour>= 7 AND @hour<19))
begin
Insert Into AverageEngTime(Sat_Night) where Shifts = 'Average'
SELECT AVG(Sat_Night)
FROM Weekly
end

if ((@Weekday= 1 and @hour >= 19) OR (@Weekday = 2 and @hour < 7))
begin
Insert Into AverageEngTime(Sun_Day) where Shifts = 'Average'
SELECT AVG(Sun_Day)
FROM Weekly
end

View Replies !   View Related
Average By Columns
Here's my sample formula:

SELECT dbo.TechphonesCalibQA.ID, dbo.TechphonesCalibQA.EmpID, dbo.TechphonesCalibQA.EmpName, dbo.TechphonesCalibQA.[Level],
CASE dbo.TechphonesCalibQA.G_Branded WHEN dbo.TechphonesCalibClient.G_Branded THEN 1 ELSE 0 END AS G_Branded
FROM dbo.TechPhonesCalibClient INNER JOIN
dbo.TechphonesCalibQA ON dbo.TechPhonesCalibClient.SesID = dbo.TechphonesCalibQA.SesID

I would like to get the average of column G_branded (Result from the above formula) Any idea?

Thanks

View Replies !   View Related
How Can I Get The Average Datediff?
I made a code to in SQL reporting services for the reports of our company regarding the new applicants. Now I need to get the average datedifference in each phase from all applicants who entered Initial/Data Collection. Example, "Initial/Data collection"PHASE will have an average of 60 minutes to be finished, averaged from all 200 applicants, and "Screening"PHASE 240 minutes to be finished, averaged from 100 applicants.

All applicants must go through stage 1 before he can go to stage 2 and so on.

The date diff will be obtained from the "createdon" column subtracted to the last "modifiedon" column in each phase.

I need to insert that code to the existing code that I have if it is possible(which my boss prefer); or either if i can manipulate it in SQL reporting services in visual studio 2005.

Here is a sample output that I have.Each phase has a different color.


Here is the code that i used
SELECT subject, regardingobjectidname, createdon, modifiedon, stat, phase, initial,
(CASEphaseWHEN 'Initial/Data Collection' THEN 1
WHEN 'Screening' THEN 2
WHEN 'Assesment and Selection' THEN 3
WHEN 'Placement' THEN 4
END) AS Phasesort
FROM(
SELECT subject, regardingobjectidname, createdon, modifiedon, stat, initial,
(CASEstatWHEN 'Application Recieved' THEN 'Initial/Data Collection'
WHEN 'For Screening' THEN 'Screening'
WHEN 'Shortlisted' THEN 'Screening'
WHEN 'For Assesment' THEN 'Assesment and Selection'
WHEN 'For Assesment' THEN 'Assesment and Selection'
WHEN 'Passed Initial Interview' THEN 'Assesment and Selection'
WHEN 'Passed Profiles Assesment' THEN 'Assesment and Selection'
WHEN 'Passed Technical Exam' THEN 'Assesment and Selection'
WHEN 'For Placement' THEN 'Placement'
END) AS phase
FROM(
SELECT subject, regardingobjectidname, createdon, modifiedon, initial,
(CASEinitialWHEN '1 stage' THEN 'Application Recieved'
WHEN '2 stage' THEN 'Application Recieved'
WHEN '3 stage' THEN 'For Screening'
WHEN '4 stage' THEN 'Shortlisted'
WHEN '5 stage' THEN 'For Assesment'
WHEN '6 stage' THEN 'Passed Initial Interview'
WHEN '7 stage' THEN 'Passed Profiles Assesment'
WHEN '8 stage' THEN 'Passed Technical Exam'
WHEN '9 stage' THEN 'For Placement'
WHEN '10 stage' THEN 'For Placement'
END) AS stat
FROM(
SELECT subject, regardingobjectidname, createdon, modifiedon,
(CASEsubjectWHEN N'application received' THEN '1 stage'
WHEN 'process application' THEN '2 stage'
WHEN 'screen application' THEN '3 stage'
WHEN 'Phone interview' THEN '4 stage'
WHEN N'initial interview' THEN '5 stage'
WHEN 'profiles assessment' THEN '6 stage'
WHEN 'technical exam and interview' THEN '7 stage'
WHEN 'background and reference check' THEN '8 stage'
WHEN 'Job Offer' THEN '9 stage'
WHEN 'Contract Signing' THEN '10 stage'
END) AS initial
FROM(SELECT subject, regardingobjectidname, createdon, modifiedon
FROMFilteredTask
WHERE (subject IN ('application received', 'process application',
'screen application', 'initial interview', 'profiles assessment',
'technical exam and interview', 'background and reference check', 'job offer',
'contract signing'))
UNION ALL
SELECT subject, regardingobjectidname, createdon, modifiedon
FROMFilteredPhoneCall
WHEREsubject = 'phone interview'
) AS Orion
) AS Initials
) AS Phases
) AS Stats
ORDER BY regardingobjectidname, initial

I appreciate your help.

Thanks a lot.

__________________________________________________
Your future is made by the things you are presently doing.

Andrew

View Replies !   View Related
Moving Average
Can anyone tell me how to calculate a moving average for the below table?
--------------------
MONS /vol /counter(the date)
jan/8 / 2006-01-01
feb/9 / etc.
........
oct/5 / etc.
nov/6 /
dec/7 /
--------------------- I am using the below code, but it is having no effect......
SELECT i1.MONS, i1.COUNTER,
( SELECT AVG( i2.VOL )
FROM #RES1 AS i2
WHERE i2.MONS = i1.MONS And
DATEDIFF( month , i2.COUNTER , i1.COUNTER ) Between 0 And 12
) AS MovingAverageOver12Months
FROM #RES1 AS i1
ORDER BY i1.COUNTER

thanks in advance, dirwin

View Replies !   View Related
Distinct Value Or Average
I don't even know if this is possible but here is a picture of some sample results.

Description Amount Account Location Begin Balance


Test 1234 $100 0001 Las Vegas $5000
Test 2345 $50 0001 Las Vegas $5000
Test 3456$80 0001 Phoenix$2000

What this is, is transactions and their amounts and account/location they went to. The last column is the beginning balance for the account/location combination. I only need the beginning balance once per account/location or averaged. So I need either this (beginning balance only shows once).....

Description Amount Account Location Begin Balance


Test 1234 $100 0001 Las Vegas $5000
Test 2345 $50 0001 Las Vegas $0
Test 3456$80 0001 Phoenix$2000


or I need this (beginning balance is averaged)......

Description Amount Account Location Begin Balance


Test 1234 $100 0001 Las Vegas $2500
Test 2345 $50 0001 Las Vegas $2500
Test 3456$80 0001 Phoenix$2000

That way I can do a sum on the beginning balance results in my report for the account/location combination, and do a sum on just account or just location while still having my transactions.

Is this possible?

Thanks,
Adam

View Replies !   View Related

Copyright 2005-08 www.BigResource.com, All rights reserved