.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

Get Average from interval(Time) by hour

Posted By:      Posted Date: September 22, 2010    Points: 0   Category :Sql Server

You Got to love management,

I've been averaging for the last 12 records with this code:

SELECT AVG(LMP) AS Average FROM (SELECT TOP (12) LMP FROM dbo.LMP_5Min order by id desc)

I need to get the average for just the current hours data that comes in every 5 minutes.  So there will be 12 records by the end of the hour.  However I need to average each increment as it comes in.  So after 1, 2, 3...... increments come in each would have to be averaged.

My tables goes as follows:


ID                  int, not null

Day         &n

View Complete Post

More Related Resource Links

Trying to set Calendar Time Interval Using Calculated Column, but getting Error



So i was trying to use a calculated column that added 7 days to the start date of a calendar item. The calculated column worked fine because i checked with several tests. Then i tried using the Column as the End Time in the Time Interval Setting in the Calendar View. This broke Sharepoint. Not the normal graceful error im using to seeing from Sharepoint but the Server Error Page when a dump happens in a ASP page. I havent enabled the full error page because i assume its not like i can fix it, i dont have the source code.


Does anyone else experience this? If so is it a known problem and being worked on?




Date Time interval Problem

Hi I have a problem in which i have to check the time interval to the another which is already exist in Database table.For Exp.10:00 am  To 11:00 am i add an Event now if user insert the again this time interval  it's throw the error message.if user want's to edit this event For exp.10:15 am to 11:00 amhow do this.Thanks

MP3 Uploading with time interval selection

I am writing a music related web site. What I am trying to do is have the user upload a song (MP3, MP4, Wav). I then want to be able to slice the song to a max lengh of 30 seconds. The user would be able to select a starting point and I would cut the song down to 30 seconds from that point. Any ideas on how to get started on this? Thanks

T-SQL query, average of daily time periods over a date range



I'm building a report in Crystal Reports using a SQL command against a T-SQL 2005 telephony database.

I need to be able to run the query across a given datetime range, 6 months for example and bring back a 5 day display (Mon-Fri) with a group for every 15 minute interval in each day.

The group figures need to contain an average of the amount of calls presented for each 15 minute interval on any day across the whole datetime range, so for example the Monday 10:00 - 10:15 figure would be an average of all calls presented in every 10:00-10:15 range on each of the Mondays that fall within the datetime range.

I've got a query built now that gives total presented figures grouped by these intervals across one week but I can't figure out how to do this average function across a range.

Does anyone have any idea how I'd go about accomplishing this? I'm pretty new to SQL but keen to learn so any pointers on functions to research etc would be very much appreciated.

Thanks alot in advance, Andy.

Query for detail view across one week included below for table/fields etc...



count(DISTINCT Calls.SessionID) as Presented, min(Calls.startDateTime) as DateTime

INNER JOIN QueueDetail
ON Calls.sessionID =  Queues.sessionID
AND Calls.sessionSeqNum =  QueueDet

Get Average by hour but but not by top of hour


I got this code with the help of K H Tan:

declare @now datetime

select @now = dateadd(hh,0,getdate())

; with cte as

 (select LMP,

  tm = convert(datetime, left(interval, 19))

 from LMP_5Min


select avg(LMP)

from cte

where tm >= dateadd(hour, datediff(Hour, 0, @now), 0)

and tm < dateadd(hour, datediff(hour, 0, @now) + 1, 0)

My only problem is that it grabs the data at the top of the hour if it is 8:00 P.M. it starts at 8:00 and goes to the last 5 minute increment at 8:55. 

SSRS 2008 Time min change to hour


Hi there

I've got a problem in my report.


I have 2 colums "Expense" "Entity"


Expense has an issue 300 and entity min.

what i'd like is, that it change to 5 hours.

but it should issue min if expense is below an hour.


Any ideas?

One who can read, have a clear advantage!

day repeated in a given time Interval


Good mornning all,

can anybody tell me how can we know that how many times a day is repeated in given time duration.

Means  if startdate "12/01/2010"  and enddate "12/31/2010"  the how can i know that between this duration how many times Sunday(or any other day) repeated.

Thanks to all.

"Date&Time" column in list - set actual time and sharepoint's proposes wrong hour (eg. 17:00 instea


I created new column in list - Date and time (Date and time format: Date&Time, Default value: Today's Date)

The problem is, when I create new item in list, the default time is always set as next full hour, eg. now it's 13:15 or 13:31 ora 13:57 and in aspx form it's all the time 14:00. Obviously, I can change that date with 5 minut precision (13:05, 13:10, 13:15 etc.), but it's kind of annoying.

Excute stored procedure on fixed time interval



My SQL Server 2008 is on the Windows Server 2008.
I have a stored procedure which is transferred from SQL 2000 (on Windows Server 2003).  Now I would like to have that stored procedure executes every 15 minutes, how do I do that in the SQL 2008/Win 2008 environment?
Thanks for help.


How to calculate total hour and minutes within time range


I need to prepare a yearly management report to show the total overtime (OT) work hours and minutes of all staff within time range and month.


The time format in database is as follows:

Each record will contains the FROM date & time and TO date & time


The report layout is as follows:



I had no idea how to divide and calculate the total hours & minutes within the time range as each OT records will overlap several time range and date.

Please help. Thanks.






Round off time to the nearest minute

How would you round this up to the nearest minute? There isn't a built in function to do this so you have to use a little bit of maths to get there. There are 60 seconds in a minute. We already have 38 seconds on the clock. So we need to add on 60 - 38 = 22 more seconds.

Performance Tests: Precise Run Time Measurements with System.Diagnostics.Stopwatch

Everybody who does performance optimization stumbles sooner or later over the Stopwatch class in the System.Diagnostics namespace. And everybody has noticed that the measurements of the same function on the same computer can differ 25% -30% in run time. This article shows how single threaded test programs must be designed to get an accuracy of 0.1% - 0.2% out of the Stopwatch class. With this accuracy, algorithms can be tested and compared.

Date and Time Functions in SQLSERVER

Date and time functions allow you to manipulate columns and variables with DATETIME and SMALLDATETIME data types.

1 DATEPART Function
2 DATENAME Function
3 DAY, MONTH, and YEAR Functions
5 DATEADD Functions
6 DATEDIFF Function
7 More SQL Server Functions

Data Types - Date and Time in SqlServer

Date and time values can be stored with either the DATETIME or SMALLDATETIME data type. The difference between the two is that SMALLDATETIME supports a smaller range of dates and does not give the same level of precision when accounting for time. The DATETIME data type can hold values from January 1st of 1753 to December 31st of 9999. The time is stored to the 1 three hundredths of a second and each value takes up 8 bytes of storage. The SMALLDATETIME data type can hold values between January 1st 1900 and June 6th of 2079. The time is tracked to the minute and each value takes up 4 bytes of storage. The majority of business applications can live happily with SMALLDATETIME, however, if you are in an environment where each second matters or you need to make estimates to the distant future (or past) then you have to resort to DATETIME. If you fail to specify the time when inserting a value into a DATETIME or SMALLDATETIME column, a default of midnight is used. If you fail to specify the date portion the default of January 1, 1900 is used.

How to programmatically add controls to Windows forms at run time by using Visual C#

Create a Windows Forms Application
Start Visual Studio .NET or Visual Studio 2005 or a later version, and create a new Visual C# Windows Application project named WinControls. Form1 is added to the project by default.
Double-click Form1 to create and view the Form1_Load event procedure.
Add private instance variables to the Form1 class to work with common Windows controls. The Form1 class starts as follows:

.NET 4 Web Application Startup Time

I was chatting with Jonathan Hawkins and some of the folks on the ASP.NET team about performance and Jonathan mentioned the startup time for large ASP.NET applications is improved on .NET 4. There are some improvements in the CLR and in ASP.NET itself that helped. If you have a giant app, you should do some tests.

Built-in Functions - Date and Time Functions

Date and time functions allow you to manipulate columns and variables with DATETIME and SMALLDATETIME data types.
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend