ASP.NET Chart With MSSQL DataSource problem

Hi I have a table which holds SNMP polling data.  The table looks like this:

SNMP_History_ID, Deice_SNMP_Monitor_ID, Result, Result_Date_Time

Basically this is polling the interfaces on 2 cisco routers and storing the data in and out.  The data in the result is converted before its written into the table into Mb.  

To find out Mbps I need to take the second result – the first result and the time between the 2 in seconds and divide the result by the time gap.

I can do this ok for one set of data like below code.  


SELECT top 360
      c2.Result_Date_Time as IN_End_DateTime,
      cast(case when cast(c2.Result as numeric(9,4)) - cast(c1.Result as numeric(9,4)) > 0 then
             case when datediff(SECOND,c1.Result_Date_Time,c2.Result_Date_Time) > 0 then
             (cast(c2.Result as numeric(9,4)) - cast(c1.Result as numeric(9,4))) / datediff(SECOND,c1.Result_Date_Time,c2.Result_Date_Time)
             else
             0
            End
      else
      0
      End as numeric(9,2) )as IN_PerSecond


FROM  
(
  SELECT  top 360 SNMP_History_ID,
         Result,
         Result_Date_Time,
         rn=ROW_NUMBER()OVER(ORDER BY SNMP_History_ID)
  FROM   SNMP_History
  WHere Deice_SNMP_Monitor_ID = 347 and Result_Date_Time > dateadd(MINUTE,-70,getdate())
  Order By  SNMP_History_ID DESC
)
c1
INNER JOIN
(
  SELECT  top 360 SNMP_History_ID,
         Result,
         Result_Date_Time,
         rn=ROW_NUMBER()OVER(ORDER BY SNMP_History_ID)
  FROM   SNMP_History
  WHere Deice_SNMP_Monitor_ID = 347 and Result_Date_Time > dateadd(MINUTE,-70,getdate())
  Order By  SNMP_History_ID DESC
)
c2
ON  c2.RN=c1.RN + 1
Order By c2.Result_Date_Time Desc



The problem is I want another couple of columns for another device so I can plot them on a graph, but because the row numbers are different in total I cant display the latest data.  I will be adding more devices over time but they’ll never be added at the same time and thus the number of rows will always alter.

Can anyone give me any help or advice on how to achieve this?

Thanks

Si Brasso
LVL 1
brasso_42Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

John_VidmarCommented:
I would use a stored-procedure that returns a result-set:
capture your 360 records into a temporary-table
use that temporary-table to denormalize other data
end with a select-statement, which returns the result-set back to the caller
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.