Click here to Skip to main content
Rate this: bad
good
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
good
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.
  Permalink  
Comments
CHill60 at 17-Dec-12 19:54pm
   
Really good site for working with SQL dates ...http://www.sqlservercentral.com/blogs/lynnpettis/2009/03/25/some-common-date-routines/
Marvin@CDM at 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 at 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 at 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 at 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 at 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 at 18-Dec-12 18:50pm
   
Oh. It needs to also filter in the subquery on the site. where (select count(*) from xxx where site = x.site temp > 60 and id >= DATEADD(D , -10, DATEDIFF(D,0, GETDATE())) = 10 ) , assuming the parent table is named x.
Marvin@CDM at 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
WHERE (
SELECT COUNT(*)
FROM myTable as b
WHERE b.site = a.site AND a.temp > 60 and a.id >= 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 at 18-Dec-12 20:05pm
   
select id, site from myTable a
where
(
SELECT COUNT(*)
FROM myTable
WHERE site = a.site AND temp > 60 and id >= DATEADD(D , -10, DATEDIFF(D,0, GETDATE())) = 10
)
 
I think
Marvin@CDM at 18-Dec-12 20:36pm
   
"An expression of non-boolean type specified in a context where a condition is expected"
 
Christian Graus at 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 at 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 at 18-Dec-12 22:48pm
   
Excellent - glad we got there
Rate this: bad
good
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.
 
SELECT
   Site,
   Avg(Temp)
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 B.id >= DATEADD(D , -10, DATEDIFF(D,0, GETDATE())))
GROUP BY Site
 
Please fix any syntax error is there, i have not compiled it.
  Permalink  
Comments
Marvin@CDM at 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 at 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 at 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 at 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 at 18-Dec-12 18:14pm
   
I guess you have temp as INT, please change AVG to Avg(CAST (Temp AS float))
Marvin@CDM at 18-Dec-12 18:20pm
   
temp is originally set to float.
Rohit Shrivastava at 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 at 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 at 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 at 18-Dec-12 18:41pm
   
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
Rohit Shrivastava at 18-Dec-12 21:16pm
   
please remove temp from group by, thats it.
Marvin@CDM at 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 at 18-Dec-12 22:53pm
   
add Having Count(1) > 5 or 10 whatever days in the end of query
Rohit Shrivastava at 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 at 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 at 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
 
SELECT
Site,
Avg(Temp)
FROM TableName A
WHERE id >= DATEADD(D , -5, DATEDIFF(D,0, GETDATE()))
GROUP BY Site
Having Min (temp) >= 60 and Count(Distinct Convert(VARCHAR(10),ID , 101))> 5
Rate this: bad
good
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
WHERE (
  SELECT COUNT(*)
  FROM myTable as b
  WHERE b.site= a.site AND b.temp > 50 AND b.id >= DATEADD(D , -10, DATEDIFF(D,0, GETDATE()))) > 9 
  AND a.id >= DATEADD(D , -10, DATEDIFF(D,0, GETDATE()))
GROUP BY site
ORDER BY site
 
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 B.id >= DATEADD(D , -10, DATEDIFF(D,0, GETDATE())))
GROUP BY site
Having Count(Distinct Convert(VARCHAR(10),id , 101))> 9
order by site
  Permalink  
v2

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



Advertise | Privacy | Mobile
Web01 | 2.8.141022.2 | Last Updated 19 Dec 2012
Copyright © CodeProject, 1999-2014
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