Welcome :Guest
Congratulations!!!

Top 5 Contributors of the Month
ffttt
 Post New Web Links

## 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)
derived
```

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:

5MIN_LMP

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)

thanx

### 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

Hello,

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.

e.g.:

 Hours at home Hours working 3:00 8:00 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?

Thanks

### Minus 1 hour in all columns in MySql

Hello.

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.

Thanks

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

Joe

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

Thanks.