exclude when not enough data - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

exclude when not enough data

Author

Topic

Rliss
Starting Member

31 Posts

Posted - 2006-08-03 : 12:36:09

This query selects the average(of 10 records) for each of the last 12 months:

SELECT TOP 12 [YEAR_MONTH]
,(AVG(HIGH_OCTET_COUNT)/AVG(TODAYS_BANDWIDTH))*100 AS PCT_OCCUPANCY
FROM [LinkOccupancy].[dbo].[LINKSET_YEAR_MONTH_TOP10]
WHERE LINKSET_NAME = 'LINKSET001' AND
YEAR_MONTH < (SELECT MAX(A.YEAR_MONTH)
FROM [LinkOccupancy].[dbo].[LINKSET_YEAR_MONTH_TOP10] AS A
WHERE A.LINKSET_NAME = LINKSET_NAME)
GROUP BY YEAR_MONTH
ORDER BY YEAR_MONTH DESC

Let's say I begin late in the first month and do not have 10 records for that month. In this case I would like to exclude that month, therefore only accepted months with 10 records. I'm thinking another subquery but it is not working.

Help...

RLiss

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2006-08-03 : 12:42:17

[code]SELECT TOP 12 [YEAR_MONTH],
(AVG(HIGH_OCTET_COUNT)/AVG(TODAYS_BANDWIDTH))*100 AS PCT_OCCUPANCY
FROM [LinkOccupancy].[dbo].[LINKSET_YEAR_MONTH_TOP10]
WHERE LINKSET_NAME = 'LINKSET001'
AND YEAR_MONTH <
(SELECT MAX(A.YEAR_MONTH)
FROM [LinkOccupancy].[dbo].[LINKSET_YEAR_MONTH_TOP10] AS A
WHERE A.LINKSET_NAME = LINKSET_NAME)
GROUP BY YEAR_MONTH
HAVING COUNT(*) > 9
ORDER BY YEAR_MONTH DESC[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources