All.. I am new to SQL writing and need help with this custom sql that was already written in report writer. Request - To report on sites for the following 30 days where we filter out hours 11pm to 6am mon - friday and all weekends (sat/sun) - so we are only reporting on business hours. Sql is below SELECT Nodes.NodeID, Nodes.CustomerID as [Site Number], Nodes.CustomerName AS [Site Name], AVG(FMC_AvailabilityByDays.Availability) AS [Average Availability], ISNULL(FMC_OutagesLast30Days.Outages, 0) AS '# Outages', ISNULL(FMC_OutageDurationLast30Days.TotalOutageDuration, 0) AS 'Total Outage Duration'FROM Nodes INNER JOIN FMC_AvailabilityByDays ON Nodes.NodeID = FMC_AvailabilityByDays.NodeID LEFT OUTER JOIN FMC_OutageDurationLast30Days ON Nodes.NodeID = FMC_OutageDurationLast30Days.nodeid LEFT OUTER JOIN FMC_OutagesLast30Days ON Nodes.NodeID = FMC_OutagesLast30Days.NodeIDWHERE (FMC_AvailabilityByDays.datetime BETWEEN DATEADD(day, - 30, DateDiff(dd, 0, getdate())) AND DATEADD(day, - 1, DateDiff(dd, 0, getdate()))) AND ((Nodes.LocationType like '%Clinic%') OR (Nodes.LocationType = 'Dialysis Location')) AND (Nodes.DeviceType = 'Router') AND (Nodes.Application IS NULL) AND (Nodes.UnManaged = 0) GROUP BY Nodes.CustomerName, Nodes.CustomerID, Nodes.NodeID, FMC_OutageDurationLast30Days.TotalOutageDuration, FMC_OutagesLast30Days.Outages ORDER BY [Average Availability]

Community
- Command Central
- MVP Program
- Monthly Mission
- Blogs
- Groups
- Events
- Media Vault
Products
- Observability
- Network Management
- Application Management
- IT Security
- IT Service Management
- System Management
- Database Management
Content Exchange
- SolarWinds Platform
- Server & Application Monitor
- Database Performance Analyzer
- Server Configuration Monitor
- Network Performance Monitor
- Network Configuration Manager
- SQL Sentry
- Web Help Desk
Free Tools & Trials
Store

Report SQL help

All.. I am new to SQL writing and need help with this custom sql that was already written in report writer.

Request - To report on sites for the following 30 days where we filter out hours 11pm to 6am mon - friday and all weekends (sat/sun) - so we are only reporting on business hours.

Sql is below

SELECT	Nodes.NodeID, Nodes.CustomerID as [Site Number], Nodes.CustomerName AS [Site Name], AVG(FMC_AvailabilityByDays.Availability)
	AS [Average Availability], ISNULL(FMC_OutagesLast30Days.Outages, 0) AS '# Outages',
	ISNULL(FMC_OutageDurationLast30Days.TotalOutageDuration, 0) AS 'Total Outage Duration'
FROM	Nodes INNER JOIN
	FMC_AvailabilityByDays ON Nodes.NodeID = FMC_AvailabilityByDays.NodeID LEFT OUTER JOIN
	FMC_OutageDurationLast30Days ON Nodes.NodeID = FMC_OutageDurationLast30Days.nodeid LEFT OUTER JOIN
	FMC_OutagesLast30Days ON Nodes.NodeID = FMC_OutagesLast30Days.NodeID
WHERE	(FMC_AvailabilityByDays.datetime BETWEEN DATEADD(day, - 30, DateDiff(dd, 0, getdate())) AND DATEADD(day, - 1, DateDiff(dd, 0, getdate()))) AND
	((Nodes.LocationType like '%Clinic%') OR (Nodes.LocationType = 'Dialysis Location')) AND (Nodes.DeviceType = 'Router') AND (Nodes.Application IS NULL) AND
	(Nodes.UnManaged = 0)

GROUP BY Nodes.CustomerName, Nodes.CustomerID, Nodes.NodeID, FMC_OutageDurationLast30Days.TotalOutageDuration, FMC_OutagesLast30Days.Outages

ORDER BY [Average Availability]

Find more posts tagged with

Accepted answers

All comments

njoylif

look in content share section for reports and find one with business hours;

you should then be able to add that section of where clause to your report.

njoylif

ah, this is a little more tricky being the last 30 days...

add something like:

AND

(

LTRIM(MONTH(FMC_AvailabilityByDays.datetime)) = (LTRIM(MONTH(getdate())) -1)

AND

DATEPART(dw, FMC_AvailabilityByDays.datetime) between 2 and 6 --- gives monday through friday

AND

( --- gives btn 6a and 11p

t.DateTime BETWEEN

CAST(Convert(Char(10), FMC_AvailabilityByDays.datetime, 101) as smalldatetime) + ' 06:00'

AND

CAST(Convert(Char(10), FMC_AvailabilityByDays.datetime, 101) as smalldatetime) + ' 23:00'

)