Click here to Skip to main content
12,552,255 members (55,982 online)
Rate this:
Please Sign up or sign in to vote.
See more: SQL
I have a table (on MS SQL 2005) that stores daily reports from sites including their temperature. There are several hundred sites reporting daily.
Something like this:
id                      Site Temp
12/16/2012 3:06:20 AM   9876  52
12/16/2012 4:02:28 AM   1234  66
12/17/2012 7:08:24 AM   2222  53
12/17/2012 1:00:14 PM   1234  65
12/17/2012 1:10:36 PM   9876  51

I need to find a way to find all those sites that have had the past 10 days above a certain temperature (lets say 60 degrees.) And then i need to find the average temp of each of the resulting sites over those 10 days.

With this problem i have reached the wall of my self taught sql knowledge. Any assistance would be greatly appreciated.
Posted 17-Dec-12 12:22pm
Rate this: bad
Please Sign up or sign in to vote.

Solution 1

select distinct site from mytable where temp > 60 and id >= DATEADD(D , -10, DATEDIFF(D,0, GETDATE()))

This gets the start of the day 10 days ago, and uses distinct so that if a site had more than one result, only one is returned.
CHill60 17-Dec-12 19:54pm
Really good site for working with SQL dates ...
Marvin@CDM 18-Dec-12 18:01pm
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?
Christian Graus 18-Dec-12 18:04pm
What about where (select count(*) from xxx where temp > 60 and id >= DATEADD(D , -10, DATEDIFF(D,0, GETDATE())) = 10 ) ?
Marvin@CDM 18-Dec-12 18:17pm
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.
Christian Graus 18-Dec-12 18:18pm
And is that wrong ? Won't my suggestion check the last 10 days and pick only those where all 10 were above 60 ? The temp has to be wrong, where is that coming from ? If you want an avg temp, then add that to your query
Marvin@CDM 18-Dec-12 18:47pm
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 =)
Christian Graus 18-Dec-12 18:50pm
Oh. It needs to also filter in the subquery on the site. where (select count(*) from xxx where site = temp > 60 and id >= DATEADD(D , -10, DATEDIFF(D,0, GETDATE())) = 10 ) , assuming the parent table is named x.
Marvin@CDM 18-Dec-12 20:03pm
Sorry i'm being dense headed. I'm not getting how you mean to filter the subquery with COUNT. Can you correct me?

SELECT COUNT(*), id, site, temp
FROM myTable as a
FROM myTable as b
WHERE = AND a.temp > 60 and >= DATEADD(D , -10, DATEDIFF(D,0, GETDATE()))

Obviously its missing GROUP BY's also. But i'm not understanding a SELECT inside a WHERE...
Christian Graus 18-Dec-12 20:05pm
select id, site from myTable a
FROM myTable
WHERE site = AND temp > 60 and id >= DATEADD(D , -10, DATEDIFF(D,0, GETDATE())) = 10

I think
Marvin@CDM 18-Dec-12 20:36pm
"An expression of non-boolean type specified in a context where a condition is expected"

Christian Graus 18-Dec-12 20:43pm
I bet you just need brackets. select count(*) = 10 is a boolean expression, the brackets must be off. Check they are balanced, I can't run this SQL, obviously.
Marvin@CDM 18-Dec-12 22:41pm
Yep you got it. Your note about the boolean suddenly clarified it for me. Now to work out the avg...

Thank you so much Christian!
Christian Graus 18-Dec-12 22:48pm
Excellent - glad we got there
Rate this: bad
Please Sign up or sign in to vote.

Solution 2

For this you need find out all the site which are above 60 and negate all site which anytime had less than or equal to 60. Then take avg of temp with group by clause.

FROM TableName A
WHERE temp > 60 and id >= DATEADD(D , -10, DATEDIFF(D,0, GETDATE()))
AND Not Exists (SELECT Site FROM TableName B WHERE B.temp <= 60 and a.Site = b.Site and >= DATEADD(D , -10, DATEDIFF(D,0, GETDATE())))

Please fix any syntax error is there, i have not compiled it.
Marvin@CDM 18-Dec-12 18:06pm
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.
Rohit Shrivastava 18-Dec-12 18:08pm
GROUP BY Site is for outer sql just make sure you have in the end without parenthesis. by the time I will test at my end too
Rohit Shrivastava 18-Dec-12 18:12pm
I tested my query for sample data and it is working fine and giving me single result for each site, could you please post the sample data so that I can test against that.
Marvin@CDM 18-Dec-12 18:23pm
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
Rohit Shrivastava 18-Dec-12 18:14pm
I guess you have temp as INT, please change AVG to Avg(CAST (Temp AS float))
Marvin@CDM 18-Dec-12 18:20pm
temp is originally set to float.
Rohit Shrivastava 18-Dec-12 18:17pm
Make sure site is int, if its varchar/char then put LTRIM AND RTRIM while Group by... I am sorry for multiple comments.
Marvin@CDM 18-Dec-12 18:21pm
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.
Rohit Shrivastava 18-Dec-12 18:29pm
I am getting right result with the sample data, I guess you have just changed <=60 to < 60. post the query and create table script.
Marvin@CDM 18-Dec-12 18:41pm
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( = rtrim(ltrim( and >= 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]
/****** Object: Table [dbo].[data] Script Date: 12/18/2012 15:38:08 ******/
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
[id] ASC

Rohit Shrivastava 18-Dec-12 21:16pm
please remove temp from group by, thats it.
Marvin@CDM 18-Dec-12 22:50pm
Yes that works much better =) The only issue with this approach is it still returns site with less than 10 days reporting. There is one site in the table with only one record, that one should not be returned.
Rohit Shrivastava 18-Dec-12 22:53pm
add Having Count(1) > 5 or 10 whatever days in the end of query
Rohit Shrivastava 19-Dec-12 14:21pm
would be more appropriate if you use Count Distinct on date part of id like below

Having Count(Distinct Convert(VARCHAR(10),ID , 101))> 5 or 10
Marvin@CDM 19-Dec-12 16:01pm
Yes that now works! Thank you for your help Rohit =)
I now have 2 slightly different approaches that give me similar returns. This has been an excellent learning opportunity!
Rohit Shrivastava 19-Dec-12 16:14pm
We can also optimize the query by removing the not exists clause by adding one more having clause Min(temp) >= 60

FROM TableName A
Having Min (temp) >= 60 and Count(Distinct Convert(VARCHAR(10),ID , 101))> 5
Rate this: bad
Please Sign up or sign in to vote.

Solution 3

Thank you Christian and Rohit for helping me through this and the learning experience! You are both Scholars and Gentlemen.

Here are the two queries that yield similar returns.

From Christian:
SELECT site, avg(temp) AS avgTemp
FROM  myTable as a
  FROM myTable as b
  WHERE AND b.temp > 50 AND >= DATEADD(D , -10, DATEDIFF(D,0, GETDATE()))) > 9 

From Rohit:
SELECT site, avg(temp) as avgTemp
FROM myTable a
WHERE temp> 55 and id >= DATEADD(D , -10, DATEDIFF(D,0, GETDATE()))
    AND Not Exists (SELECT site FROM myTable B WHERE B.temp <= 55 and a.SID = b.SID and >= DATEADD(D , -10, DATEDIFF(D,0, GETDATE())))
Having Count(Distinct Convert(VARCHAR(10),id , 101))> 9
order by site

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month

Advertise | Privacy | Mobile
Web01 | 2.8.161021.1 | Last Updated 19 Dec 2012
Copyright © CodeProject, 1999-2016
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100