Comments by Marvin@CDM (Top 13 by date)

Unfortunately it is picking any sites where at least one day is above 60. For example 208249 was hovering around 40, but had one day where it was up to 124 (correct data.) No problem on adding the average once i get the rest of the query working. Thanks so far =)

SELECT
rtrim(ltrim(site)),
avg(temp) as avgShTemp
FROM myTable A
WHERE temp > 60 and id >= DATEADD(D , -5, DATEDIFF(D,0, GETDATE()))
AND Not Exists (SELECT rtrim(ltrim(site)) FROM myTable B WHERE B.temp <= 60 and rtrim(ltrim(A.site)) = rtrim(ltrim(B.site)) and B.id >= DATEADD(D , -5, DATEDIFF(D,0, GETDATE())))
GROUP BY rtrim(ltrim(site)), temp
order by rtrim(ltrim(site))

I cut the filter down to -5 (from -10) to allow wider returns while testing. This seems to be when the duplicate site appears.

USE [myTable]
GO
/****** Object: Table [dbo].[data] Script Date: 12/18/2012 15:38:08 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[data](
[id] [datetime] NOT NULL CONSTRAINT [DF_data_id] DEFAULT (getdate()),
[site] [nvarchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[temp] [float] NULL
CONSTRAINT [PK_data] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

Here is sample data for 210450:
id site temp
======================
12/6/2012 4:06:48 AM 210450 60
12/7/2012 4:06:52 AM 210450 61
12/8/2012 4:05:27 AM 210450 61
12/9/2012 4:05:07 AM 210450 61
12/10/2012 4:05:37 AM 210450 61
12/11/2012 4:05:49 AM 210450 60
12/12/2012 4:05:44 AM 210450 61
12/13/2012 4:05:40 AM 210450 62
12/14/2012 4:05:54 AM 210450 61
12/15/2012 4:05:26 AM 210450 61
12/16/2012 4:05:09 AM 210450 61
12/17/2012 4:05:33 AM 210450 61
12/18/2012 4:05:47 AM 210450 61

no problem on the multiples, i'm grateful for your help =)

site is originally nvarchar. So i replaced all instances of 'site' with 'rtrim(ltrim(site))', is this what you mean? The results were the same.

temp is originally set to float.

That gives me something like:
recCnt site temp
====================
1 208249 124
1 208694 77
1 226035 63
1 227561 61
1 227561 61
1 227561 61
1 264622 75
1 264622 78
1 264622 80
1 264622 75
1 264622 76
1 291286 150
1 291286 99
1 300019 67
1 307983 134

Which is what i was referring to above about the count always = 1.

Thanks Rohit. I tried your suggestion. I get something like this:
site avgTemp
=====================
210278 67
210450 61
210450 62
300019 67

I don't understand the 2nd instance of 210450, and it's actual average is 61. Also 300019 only has a single record so far.

Thank you Christian for your assistance.

I had come up with a similar query to get a similar return. The return gives me the sites that have any days above the threshold temperature. But somehow i need to filter it down to sites that have ALL 10 days above the threshold temp (not just a few.)

select id, site, temp
from myTable
where temp > 60 and id >= DATEADD(D , -10, DATEDIFF(D,0, GETDATE()))
order by site, id
++++returns something like this++++
id site temp
===================================
2012-12-17 09:01:59.547 208249 124
2012-12-12 07:07:02.833 208694 77
2012-12-14 04:15:59.920 226035 63
2012-12-12 05:16:34.633 227561 61
2012-12-14 05:17:09.827 227561 61
2012-12-17 05:18:05.720 227561 61
2012-12-10 06:07:33.097 264622 75
2012-12-12 06:06:49.820 264622 78
2012-12-13 06:06:56.960 264622 80
2012-12-17 06:08:19.733 264622 75
2012-12-18 06:08:09.270 264622 76
2012-12-17 04:07:41.580 291286 150
2012-12-18 04:07:32.110 291286 99
2012-12-14 15:00:11.030 300019 67
2012-12-10 06:05:52.910 307983 134
Distinct is not really necessary in this case since there will be only one record per day.

And IF the site has all 10 days above threshold, then i need to see each of the 10 day's records for that site.

I tried putting this query into a subquery and doing a count, but if i said HAVING Count(site) > 1 i would get nothing. Seems each record had a count of 1. I couldn't figure out how to have it count up all the same sites in the 1st return.

Any ideas?