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

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

Get Average by hour but but not by top of hour

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

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. 

View Complete Post

More Related Resource Links

Get Average from interval(Time) by hour


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

Break apart timespan/duration group by hour segments

I need to break apart a timespan/duration value. I need to calculate the availability of resources by hour of the day. I have a call for service at 0945 that last until 1030 unit = E52 I have a call for servce at 0930 that last until 1015 unit = E53 I have a call for service at 1030 that last unitl 1230 unit = E53 query to show the following % of availabilty for said unit by hour of day 0900 to 1000 available 75% of the time for E52 0900 to 1000 available 50% of the time for E53 1000 to 1100 available 50% of the time for E52 1000 to 1100 avaialble 25% of the time for E53 1100 to 1200 available 0% of time for E53 1200 to 1300 available 50% of the time for E53   all times are actual DTS with dates, hour, minutes, seconds and can transcend past midnight. So in essence I am trying to calculate the availablity of a fire engine in your neighborhood. How often is it our of service on a call by hour of day.   Thanks   Jim

Last 24 hour data


i have this table


1	0	2010-09-22 10:54:52.000	1
1	0	2010-09-22 10:53:44.000	1
1	0	2010-09-22 10:52:36.000	1
1	0	2010-09-22 10:51:28.000	1
1	0	2010-09-22 10:50:20.000	1
1	0	2010-09-22 10:49:12.000	1
1	0	2010-09-22 10:48:04.000	1
1	0	2010-09-22 10:46:42.000	1
1	0	2010-09-22 10:45:34.000	1
1	0	2010-09-22 10:43:18.000	1
1	0	2010-09-22 10:42:09.000	1
1	0	2010-09-22 10:40:56.000	1
1	0	2010-09-22 10:39:48.000	1
1	0	2010-09-22 10:38:40.000	1
1	0	2010-09-20 14:53:58.000	1
1	0	2010-09-20 14:52:50.000	1
1	0	2010-09-20 14:51:42.000	1
1	0	2010-09-20 14:50:34.000	1
1	0	2010-09-20 14:49:26.000	1
1	0	2010-09-20 14:48:18.000	1
1	0	2010-09-20 14:47:10.000	1
1	0	2010-09-20 14:45:43.000	1
1	0	2010-09-20 14:44:35.000	1
1	0	2010-09-20 14:42:19.000	1

how can i fetch the last 24 hour data? (it should be only one record)


Cluster service takes 1 hour to bring service and applications online


We had a 1 hour outage with our production SQL cluster recently and I have been trawling through the Event Logs to try and find an explanation.  I know that a network failure actually triggered the outage, during a switch upgrade no network was available and so the cluster shut down:

"This cluster node has no network connectivity. It cannot participate in the cluster until connectivity is restored. "

There are lots of enties telling me that the cluster service is stopping and one saying that DTC is terminating unexpectedly which is fine, but then I see a log enty:

"The system failed to flush data to the transaction log. Corruption may occur"

This is worrying - how can I be sure that my data is not corrupted?

I can see the SQL Server and SQL Server Agent services '"terminate unexpectedly" and shortly after that I can see the cluster service entering the running state and various attempts to bring clustered applications online.  These attempts give the following messsage:

"The Cluster service failed to bring clustered service or application 'SQL Server (INSTANCENAME)' completely online or offline. One or more resources may be in a failed state. This may impact the availability of the clustered service or application."

Does this mean that the cluster service does no

Reading xlsx when cells are in hour format



I have a page that does an upload of an xlsx file to a datatable and write into SQL.

That is working, my problem is when i upload the file and pass the values to the datatable some of them are showing in the wrong format and the column name dont appears.


Hours at home

Hours working



01-01-1900 1:35:00

01-01-1900 2:35:00

01-01-1900 3:35:00

The first 2 columns its how i want the info the third is how my problem looks like.

I've tested and the problem occurs when the same column has more than one value.

The format of the cells is h:mm if i change it to text it works fine but i dont want that because the time will be converted to semething like this 0,123456789.

I dont know if it matters but the xlsx file is kinda complex and has 10,2MB.

Any pointers? Thanks for any reply

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!

How to Convert datetime to hour


int s = 0;

        //Start time
        int start_year =2010;
        int start_month = 10;
        int start_day = 2;
        int start_hour = 0;
        int start_min = 0;

        //End time
        int end_year = 2010;
        int end_month = 10;
        int end_day = 3;
        int end_hour = 0;
        int end_min = 0;
        DateTime startTime = new DateTime(start_year,start_month,start_day, start_hour, start_min,s); // 05:40 Hour
        DateTime endTime = new DateTime(end_year, end_month, end_day, end_hour, end_min, s); // 17:45 Hour
        TimeSpan diff = endTime - startTime;
        int hourDiff = diff.Hours; // 12 Hours
        int minDiff = diff.Minutes; // 15 Mins
        double totDiffHours = (double)hourDiff + (double)minDiff /60; // 12.25 Hours
       // double hhh = (DateTime) diff / 24;

        Label1.Text = Convert.ToString(diff);

How to convert days into hours?


Minus 1 hour in all columns in MySql



I have a timestamp column which I require to change the time back by 1 hour for all records.

Please post the SQL for this.


"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.

Workflow that starts at certain hour - how to set trigger?


There is some list with DateTime column. Then I have "Expiration" column, which would be calculated and shows 'Yes', when it's in 30days period till today (eg. today is 17112010, when dateTime is 16102010, than Expiration shows 'No', when dateTime is 10112010 then Expiration shows 'Yes'). I can manange to create such column.

The problem is with Workflow. I'd like to create such one, that would starts everyday eg. at 03:00AM and check the column Expiration - if there is 'Yes', then it does something, it's not important. The thing is, how to make it run everyday? In MS Designer there are only three triggers: when edit, when create and manually.

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.






SRR V0015612 or DG0072. how to dectect if a user has changes is password more than once in a 24 hour


I'm having to harden a SQL Server 2005 SE SP4 enviroment, and now I must find a way to dectect or not allow a SQL Server user to change is password more than once in a 24 hour period. Is this a Windows server policy that can be checked, or is this a stored system proc, or will I have to right something to track this. If someone has already indentified this, send me some bread crumbs please.



Oscar Powell Database Administration BlueCross BlueShield of South Carolina

How to subtract 1 hour from getdate()?


Hi All,

Can any one tell me how to subtract 1 hour from Getdate()? I tried like this 

for example modify_date=10:55:55 am  and getdate=11:05:05 am 

DATEDIFF(hh,MODIFY_DATE,GETDATE()) is giving result as 1 but i want difference 1 hr when getdate is 11:55:55 am ???

Any help is appriciated......


For loop to find sum and average

I am trying to write a program that will use the for loop to find the sum, and average of 10 numbers a user enters. I was able to do something like this with out a for loop but I want something a bit more efficient . double num1; double num2; double num3; double num4; double num5; double num6; double num7; double num8; double num9; double num10; Console.Write("Please enter a number: "); num1 = Convert.ToDouble(Console.ReadLine()); Console.Write("Please enter a number: "); num2 = Convert.ToDouble(Console.ReadLine()); Console.Write("Please enter a number: "); num3 = Convert.ToDouble(Console.ReadLine()); Console.Write("Please enter a number: "); num4 = Convert.ToDouble(Console.ReadLine()); Console.Write("Please enter a number: "); num5 = Convert.ToDouble(Console.ReadLine()); Console.Write("Please enter a number: "); num6 = Convert.ToDouble(Console.ReadLine()); Console.Write("Please enter a number: "); num7 = Convert.ToDouble(Console.ReadLine()); Console.Write("Please enter a number: "); num8 = Convert.ToDouble(Console.ReadLine()); Console.Write("Please enter a number: "); num9 = Convert.ToDouble(Console.ReadLine()); Conso

average of difference between rows

Hello, I need to calculate the difference between two rows and than find the average of those values. For example I have: date Value Value I need (difference between rows) 22.03.2010 50 30 24.01.2010 20 10 10.09.2009 10 NULL ----- 20 (which is the average of this column) Any suggestions? Thanx in advance :)

SSRS 2008 Matrix - Cannot get average formula or growth formula to work

I have a report in Excel that I'm trying to replicate in SSRS 2008.  The report looks like this:   Priority 08/21/10 08/14/10 08/07/10 07/31/10 07/24/10 07/17/10 07/10/10 TOTAL Average Customer Name Code # of Lds # of Lds # of Lds # of Lds # of Lds # of Lds # of Lds  # of Lds   Check  #N/A            4            4            4            8            4            9           -            33          5       83%  A2             1          -            -            -            -            -           
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