How to query Sites with Last 10 Days above Temperature

Question

0.00/5 (No votes)

See more:

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:

VB

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

Marvin@CDM

Add a Solution

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Christian Graus · Accepted Answer · 2012-12-17T13:43:00

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.

Posted 17-Dec-12 13:43pm

Christian Graus

Comments

CHill60 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 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 = x.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
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 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 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

Rohit Shrivastava · Accepted Answer · 2012-12-18T11:33:00

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.

SQL

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.

Posted 18-Dec-12 11:33am

Rohit Shrivastava

Comments

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

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 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

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

Marvin@CDM · Accepted Answer · 2012-12-19T10:12:00

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:

SQL

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:

SQL

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