April 18, 2011 at 3:38 pm
I have a data consisting of level readings every few minutes for 100’s of reservoirs. I am looking for the Min and Max reading for 23 specific reservoirs , each day for 5 years.
In a day, there could be multiple values of Min and Max (ex: Max on 2009-01-01 07:14:52, 2009-01-01 09:12:13, 2009-01-01 22:08:16 etc).
What I am looking to do is grab values in a 24 hour period for one reservoir: Min, Max, the date and reservoir name, put it in a table, then move to the next day and put the results in the table and so on. Once I have processed through five years, process for the next reservoir; Min, Max, the date and reservoir name so on until the end of the list of tanks.
The following works for one daily value.
Select MAX(Value) as MaxValue, Min (Value) as MINValue, CONVERT(VARCHAR(8), datetime, 1) as Date_ , p.Tagname
From
(Select Value, DateTime
from Data
where
DateTime between '2009-07-01 00:00:00' and '2009-07-01 23:59:59' --need to loop though each 24 hour period
And TagName = '1TANK' -- this is the name of the reservoir
and wwRetrievalMode = 'cyclic'
and wwResolution = 6000)p
group by CONVERT(VARCHAR(8), [datetime], 1),p.tagname)
Result:
MaxMinDate_Tagname
72627/1/20091Tank
My question is – What are the mechanisms to iterate through as I need? I have read about cursors and while statement and think that may be the way to go but am uncertain of how to write it.
April 18, 2011 at 4:17 pm
If you post your complete working code, (along with table definitions and sample data, as per the first link in my signature), we'll show you a method that will blow the cursor to kingdom-come ... where it belongs! (99% of cursors are no longer necessary)
Questions: what exactly do you mean by the previous 5 years? from the current date, or from the start of the year?
Do you have a table of the reservoir names?
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
April 18, 2011 at 5:32 pm
Wayne,
Thank you very much for the info on how to post.
Unfortunately I cannot get the table definitions of the SCADA (supervisory control and data acquisition) data so I cannot provide working code. I thought posting a generic example would help explain what I am trying to do and it seems I missed the mark. I will re post the question properlly.
April 18, 2011 at 6:30 pm
kmaker (4/18/2011)
Wayne,Thank you very much for the info on how to post.
Unfortunately I cannot get the table definitions of the SCADA (supervisory control and data acquisition) data so I cannot provide working code. I thought posting a generic example would help explain what I am trying to do and it seems I missed the mark. I will re post the question properlly.
I can build a pot wad of test data on the fly. How many rows are in the table? 3 minutes per sample for 100 sites for 5 years only adds up to a little over 87 million rows.
--Jeff Moden
April 18, 2011 at 8:25 pm
kmaker (4/18/2011)
Wayne,Thank you very much for the info on how to post.
Unfortunately I cannot get the table definitions of the SCADA (supervisory control and data acquisition) data so I cannot provide working code. I thought posting a generic example would help explain what I am trying to do and it seems I missed the mark. I will re post the question properlly.
Okay.....
can you answer the questions I asked?
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
April 19, 2011 at 10:24 am
Wayne,
To answer your questions:
what exactly do you mean by the previous 5 years? from the current date, or from the start of the year? - the previous five years from today
Do you have a table of the reservoir names? - yes I have a temp table I created of Reservoir names
Kimberly
April 19, 2011 at 1:18 pm
Just noticed that you posted in the SQL 7/2000 forum - can you verify what version of SQL you are working with? (I was looking at a solution that would be using 2005...)
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
April 19, 2011 at 1:24 pm
Wayne,
The databases are on SQL server 2000. I am using SSMS 2005 to build my queries.
Kimberly
April 19, 2011 at 1:38 pm
Okay. Do you have a "Tally" table? The "Numbers" or "Tally" Table: What it is and how it replaces a loop.[/url]
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
April 19, 2011 at 2:56 pm
Kimberly,
This should work. It does use a Tally table; see my previous post for how to build one, and how they can be used to get rid of loops.
-- This is only needed when running manually; when in a proc it is safe to remove.
IF object_id('tempdb..#CalendarTable') IS NOT NULL DROP TABLE #CalendarTable;
IF object_id('tempdb..#Reservoirs') IS NOT NULL DROP TABLE #Reservoirs;
-- First you need to build a calendar table for the previous 5 years.
-- Change the "-(N-1)" to "-N" from the date calculation, and change the
-- "<=" to "<" in the where clause if you do NOT want today's date.
SELECT MyDate = DATEADD(DAY, -(N-1), DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)),
N
INTO #CalendarTable
FROM dbo.TALLY
WHERE N <= DATEDIFF(DAY, DATEADD(YEAR, -5, GETDATE()), GETDATE())+1
ORDER BY N desc;
-- data table, with some data:
IF OBJECT_ID('tempdb..#Data') IS NOT NULL DROP TABLE #Data;
CREATE TABLE #Data ([DateTime] DATETIME,
VALUE INT,
Tagname VARCHAR(50),
wwRetrievalMode VARCHAR(10),
wwResolution SMALLINT);
INSERT INTO #Data
SELECT '20091011', 60, '1TANK', 'cyclic', 6000 UNION ALL
SELECT '20091011', 30, '1TANK', 'cyclic', 6000 UNION ALL
SELECT '20091011', 45, '1TANK', 'cyclic', 6000 UNION ALL
SELECT '20091012', 60, '2TANK', 'cyclic', 6000 UNION ALL
SELECT '20091012', 30, '2TANK', 'cyclic', 6000 UNION ALL
SELECT '20091012', 55, '1TANK', 'cyclic', 6000 UNION ALL
SELECT '20091012', 45, '1TANK', 'cyclic', 6000 UNION ALL
SELECT '20090701', 72, '1TANK', 'cyclic', 6000 UNION ALL
SELECT '20090701', 69, '1TANK', 'cyclic', 6000 ;
-- here is your temp table of reservoir names.
CREATE TABLE #Reservoirs (name VARCHAR(50) PRIMARY KEY CLUSTERED);
INSERT INTO #Reservoirs (name)
SELECT DISTINCT Tagname FROM #Data;
SELECT MaxValue = MAX(Value),
MinValue = MIN(Value),
Date_ = CONVERT(VARCHAR(10), [DateTime], 101),
TagName
FROM (SELECT Data.TagName,
[DateTime] = DATEADD(DAY, DATEDIFF(DAY, 0, Data.[DateTime]), 0), -- strip the time from the date
Data.Value
,ct1.MyDate
FROM #Data Data
JOIN #Reservoirs p
ON p.name = Data.TagName
JOIN #CalendarTable ct1
ON Data.[DateTime] = ct1.MyDate
WHERE wwRetrievalMode = 'cyclic'
AND wwResolution = 6000) p
GROUP BY p.TagName, CONVERT(VARCHAR(10), [DateTime], 101)
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
April 19, 2011 at 3:02 pm
Wow! I hadn't finished digesting tally tables and look what you did. I'll finish tally tables, look at the scripting you provided and see if it works for my problem.
Thanks Wayne
April 19, 2011 at 3:18 pm
NP. After you have tested everything out, please respond back with how things are going. (BTW, I only tested this on a SQL 2008 box, but I believe that it should work fine on 2000.)
Also, as my signature states: if you don't understand, please ask. We're happy to teach (actually, we prefer that... :-))
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply