[SQL] report doesn't update with advanced sql (query problem)

hi all,

i creating a report to know how long the application is down for last 24 hours. but the report seems doesn't update (it's only show the report on 24 hours from the report is created)...

query :

SELECT ROW_NUMBER () over (order by Caption) as "NO", Nodes.NodeID as "Company Code", Nodes.Caption as "Server Name", Nodes.Location as "Location", APM_Application.Name, StartTime.EventTime as "DownTime",

(SELECT TOP 1 EventTime FROM Events AS Endtime

WHERE EndTime.EventTime > StartTime.EventTime AND EndTime.EventType = 504

AND EndTime.NetObjectType = 'AA'

AND EndTime.NetworkNode = StartTime.NetworkNode ORDER BY EndTime.EventTime) AS "UpTime",

STR(DATEDIFF(Mi, StartTime.EventTime,

(SELECT TOP 1 EventTime

FROM Events AS Endtime

WHERE EndTime.EventTime > StartTime.EventTime AND EndTime.EventType = 504

AND EndTime.NetObjectType = 'AA'

AND EndTime.NetworkNode = StartTime.NetworkNode

ORDER BY EndTime.EventTime))/60) + ':' + ltrim(STR(DATEDIFF(Mi, StartTime.EventTime,

(SELECT TOP 1 EventTime

FROM Events AS Endtime

WHERE EndTime.EventTime > StartTime.EventTime AND EndTime.EventType = 504

AND EndTime.NetObjectType = 'AA'

AND EndTime.NetworkNode = StartTime.NetworkNode

ORDER BY EndTime.EventTime))%60)) AS Duration

FROM Events StartTime INNER JOIN APM_Application ON StartTime.NetObjectID = APM_Application.ID INNER JOIN Nodes on Nodes.NodeID = APM_Application.NodeID

WHERE (StartTime.EventType = 505) AND (StartTime.NetObjectType = 'AA') AND

(eventtime BETWEEN 40823.4166666667 AND 40824.4583333333 )

ORDER BY StartTime.EventTime

can you help me guys to resolve this?

i don't know how to fix it, and i don't master on sql query either.

Find more posts tagged with

Accepted answers

cdoyle

For past 24 hours, you can use:

BETWEEN DATEADD(day, -1, GetDate()) AND GETDATE()

If you're just starting with using Advanced SQL, an easy way to play around with the time durations would be to create a new test report. Add the filters you need to the "Filter Results" tab (For example, Hour of Day > 7) , then go to Report -> Show SQL and view the SQL generated in the SQL tab.

You would see something like this for your example:

( EventTime BETWEEN 40796 AND 40827 )

AND

(

(DatePart(Hour,DateTime) > 7) AND

(DatePart(Hour,DateTime) <= 20)

)

All comments

cdoyle

Hi Zazaz,

This is the part of the query you would need to change:

(eventtime BETWEEN 40823.4166666667 AND 40824.4583333333 )

Those static numbers mean the report is only looking at a very specific point in time.

Try something like one of these instead

Last 30 days

eventtime between dateadd(month, -1, getdate()) and getdate()

This Month

eventtime between (DATEADD(m, DATEDIFF(m, 0, getdate()) , 0)) AND getdate())

Last Month

eventtime between (DATEADD(m, DATEDIFF(m, 0, getdate()) -1 , 0)) AND getdate())

Yesterday

DateTime BETWEEN (DATEADD(dd, DATEDIFF(dd,0, getdate()) -1, 0)) AND getdate())

Last 7 days

eventtime BETWEEN (DATEADD(dd, DATEDIFF(dd,0, getdate()) -7, 0)) AND getdate())

Hope this helps

zazaz

Hi Cdoyle,

it's really2 help!! thank you very much...

anyway, how about past 24 hours, or some duration of time (ex : 07:00 AM until 09:00 PM) ?

cdoyle

For past 24 hours, you can use:

BETWEEN DATEADD(day, -1, GetDate()) AND GETDATE()

You would see something like this for your example:

( EventTime BETWEEN 40796 AND 40827 )

AND

(

(DatePart(Hour,DateTime) > 7) AND

(DatePart(Hour,DateTime) <= 20)

)

zazaz

thank you so much mr cdoyle..

i own you 2 life for this answer