| Author |
Topic |
|
Jabberwocky
Starting Member
4 Posts |
 Posted - 2006-07-03 : 11:52:09
|
| I would welcome ideas and some code to help me with this one !I am writing a c# application which incorporates some dynamic barcharts, including one to indicate server perfomance, where the server data is extracted from a database.In the application, I will iterate over an array of 30 'ServerID' integers, and want to pass them one by one as parameters to an SQL stored procedure.I need the procedure to:declare variables for serverid, currentmonth, elapsedminutes, serverdowntime, and serveruptime.identify the current month and assign the value to an integer variable (currentmonth).calculate the number of minutes elapsed from the start of the month until the current date/time, and assign the value to a variable (elapsedminutes)Query a 'ServerDown' table to return a 'DownDuration' value (recorded in minutes) where the server id = the server id passed from the application, and the 'DateDown' field matches the 'current month' variable, and assign the value to the 'serverdown' variable.Subtract the 'DownDuration' minutes from the 'elapsedminutes' variable and calculate the remainder as a percentage of the 'elapsedminutes' variable, then assign the value to the 'serveruptime' variable, which will need to be type 'float'.Return the serveruptime variable value to the Application.The application will then take the returned floating point decimal and build a bar to graphically illustrate the current uptime of the given server during the elapsed current month period.I have been doing the calculation in the Application, but would prefer to do it in the stored procedure as this will increase program efficiency, but stored procedures are not my strong point !!Thanks in advance, and sorry its a bit of a brain teaser !! |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2006-07-03 : 11:57:03
|
| Why do you want to hold all these values in variables?==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
Jabberwocky
Starting Member
4 Posts |
Posted - 2006-07-03 : 11:59:50
|
| So I can do the calculations, but if there is a better way, I am happy to listen |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2006-07-03 : 12:02:22
|
| Are you familiar with SQL at all ? SELECT statements, JOINS, expressions, WHERE clauses, aggregate functions, etc? That would be a great place to start. It sounds like all you need is a simple query. |
|
|
|
Jabberwocky
Starting Member
4 Posts |
Posted - 2006-07-03 : 12:05:55
|
| My big problem is syntax. I know exactly what I need to do but lack the precise syntax. I've had a go but get laods of syntax errors. I would be happy if anyone knows a really good syntax reference they could pass on, then I can work it out myself. I know basic select, insert, where, but there it ends. |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2006-07-03 : 12:12:15
|
| current date = getdate()start of month = convert(varchar(6),getdate(),112) + '01'minutes from start of monthdatediff(mi,convert(varchar(6),getdate(),112) + '01', getdate())downtimeselect DownDuration from ServerDown where serverID = @ServerID and mth = convert(varchar(6),getdate(),112) + '01'so the query becomesselect uppercentage = (a.elapsed - convert(float,b.downduration)) / a.elapsed * 100from(select elapsed = datediff(mi,convert(varchar(6),getdate(),112) + '01', getdate())) a ,(select DownDuration from ServerDown where serverID = @ServerID and DateDown = convert(varchar(6),getdate(),112) + '01') bBut you could get all the values in a single query==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2006-07-03 : 12:17:20
|
| Something like this should give you a resultset of all the percentages which you can deal with in the app rather than calling for each oneselect severID, uppercentage = (e.elapsed - convert(float,d.downduration)) / e.elapsed * 100from ServerDown d ,(select elapsed = datediff(mi,convert(varchar(6),getdate(),112) + '01', getdate())) ewhere DateDown = convert(varchar(6),getdate(),112) + '01') border by serverid==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
Jabberwocky
Starting Member
4 Posts |
Posted - 2006-07-04 : 04:11:10
|
| Thanks guys. Thats an enormous help. I will give it a whirl.Re my question on an sql syntax reference source, any ideas ?? |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2006-07-04 : 04:44:44
|
| Books on line, practise, searching this site and asking questions should work.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2006-07-04 : 04:45:15
|
quote:
Originally posted by Jabberwocky
Thanks guys. Thats an enormous help. I will give it a whirl.Re my question on an sql syntax reference source, any ideas ??
Best syntax reference, ofcourse, would be Books Online (SQL Server help) ! Other than that I think Rob Viera's Programming Microsoft SQL Server 2000 (Wrox) is the best book of all time.Harsh AthalyeIndia."Nothing is Impossible" |
|
|
|
em_arivu
Starting Member
1 Post |
Posted - 2006-07-22 : 23:02:54
|
| i am familiar with sql. ok but i need to study normalising in sql. |
|
|
|
chiragkhabaria
Master Smack Fu Yak Hacker
1907 Posts |
|
|
|
Calculations in stored procedures