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



ADVERTISEMENT

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    Soil          Primary             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/L         1
5                 Acetone                 90.70     ug/L         2
6                 Benzene                 31.40    ug/L         2
7                 Trichloroethene       19.20     ug/L         2
8                 Xylenes                    2.00     ug/L         2
9                 Acetone                140.30     ug/L        3
10               Benzene                 21.50     ug/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.70     ug/Kg     4
17               Acetone                  211.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
1                    Acetone             120.80        ug/L                 1
5                    Acetone               90.70        ug/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 would greatly 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

Can Anyone Give Me A Layman's Explanation Of The Difference Between CURRENT_TIMESTAMP And GETDATE() (if There Is A Difference)?

Question is in the subject.
 
Thanks in advance
-Jamie

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 users from different Time Zones.
 
I have a 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 I would like to display it as 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







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