Greetings All,
I am pulling some F5 stats using the SNMP module and I need to SUM some values but I just can't seem to get it right. The issue I have is that all the data comes in on slightly different time stamps so doing my summation through group fails. Below is my query. Any help would be greatly appreciated.

v/r
Chris



__________________________________________________ ______________

--Get UTC Time
declare @UTCtd AS datetime
set @UTCtd = getutcdate()

--Get Today's Date and Time
declare @TodayTD AS datetime
set @TodayTD = GETDATE()

--Get Time Zone Informaiton
declare @TimeDif as int
set @TimeDif = DATEDIFF(hh, getutcdate(), getdate())

CREATE Table #SumF5Connections (DataSource VARCHAR(9),Value INT,DateLastVal DATETIME,DataAgeMinutes INT)

insert into #SumF5Connections

select
'ESOC West' as DataSource,
sum(d.Value) as Value,
DATEADD (HH,@TimeDif,dbo.fnSQLDateTime (d.Time)) as DateLastVal,
DATEDIFF(mi, @TodayTD, (DATEADD (HH,@TimeDif,dbo.fnSQLDateTime (d.Time)))) as DataAgeMinutes

from
QDB1.dbo.DataHeader as h WITH (NOLOCK)
join QDB1.dbo.Data as d WITH (NOLOCK) on h.DataID = d.DataID
where
h.Status & 0x08000000 = 0
and
h.KPName like '%F5_SNMP - ltmVirtualServStatClientCurConns'
and
h.HiddenLegend like '%Test_VIP%'
and
h.HiddenLegend not like '%Test_VIP_80%'

group by d.time

union

select
'ESOC East' as DataSource,
sum(d.Value) as Value,
DATEADD (HH,@TimeDif,dbo.fnSQLDateTime (d.Time)) as DateLastVal,
DATEDIFF(mi, @TodayTD, (DATEADD (HH,@TimeDif,dbo.fnSQLDateTime (d.Time)))) as DataAgeMinutes

from
QDB2.dbo.DataHeader as h WITH (NOLOCK)
join QDB2.dbo.Data as d WITH (NOLOCK) on h.DataID = d.DataID
where
h.Status & 0x08000000 = 0
and
h.KPName like '%F5_SNMP - ltmVirtualServStatClientCurConns'
and
h.HiddenLegend like '%Test_VIP_443%'

group by d.time

order by DataAgeMinutes desc

select
Sum(Value) as Value,
DataAgeMinutes,
Replace(DateLastVal, '.000', ' ') as DateLastVal
from
#SumF5Connections

Group By DataAgeMinutes,Replace(DateLastVal, '.000', ' ')

order by DataAgeMinutes DESC

drop table #SumF5Connections