June 25, 2010 at 6:52 am
All,
This is my first post, so please bear with me.
What I'm trying to do is calculate precipitation values over a 1 hour, 3 hour, and 6 hour period. I have a table that shows accumulation values every 15 minutes. My thoughts were to sum the data values (T_DATA) for the first 4 rows (this would equal the past hour), sum the first 12 rows (this would equal the past three hours), and sum the first 24 rows (this would equal the past 6 hours).
I can get this to work by setting up a view for each time span and then running a query on each view, but I have 8 stations to do, with 3 time spans each (24 views plus 24 queries). I would imagine that there is a more efficient way of doing this.
My end goal is to get the final product in a table that looks like this:
CREATE TABLE [dbo].[RiverRd_Precip_Totals] (
[Total_1hr]float,
[Total_3hr]float,
[Total_6hr]float
)
GO
I tried to follow Jeff Moden’s Forum Etiquette[/url] as closely as possible…please forgive me if I’ve missed anything. The following is the structure/data that I currently have:
/****** Drop Table If It Exists ******/
if object_id('DataWise.dbo.RiverRd_Precip_15') is not null
drop table DataWise.dbo.RiverRd_Precip_15
GO
/****** Create Table ******/
CREATE TABLE [DataWise].[dbo].[RiverRd_Precip_15] (
[T_TIME] datetime,
[T_DATA] float
)
GO
/****** Insert Data Into Table ******/
INSERT INTO DataWise.dbo.RiverRd_Precip_15
(T_TIME, T_DATA)
SELECT 'Jun 24 2010 3:00PM','.6' UNION ALL
SELECT 'Jun 24 2010 2:45PM','1.5' UNION ALL
SELECT 'Jun 24 2010 2:30PM','1.6' UNION ALL
SELECT 'Jun 24 2010 2:15PM','1.4' UNION ALL
SELECT 'Jun 24 2010 2:00PM','1.7' UNION ALL
SELECT 'Jun 24 2010 1:45PM','1.3' UNION ALL
SELECT 'Jun 24 2010 1:30PM','1.8' UNION ALL
SELECT 'Jun 24 2010 1:15PM','1.2' UNION ALL
SELECT 'Jun 24 2010 1:00PM','1.9' UNION ALL
SELECT 'Jun 24 2010 12:45PM','1.1' UNION ALL
SELECT 'Jun 24 2010 12:30PM','2.3' UNION ALL
SELECT 'Jun 24 2010 12:15PM','2.2' UNION ALL
SELECT 'Jun 24 2010 12:00PM','.1' UNION ALL
SELECT 'Jun 24 2010 11:45AM','.8' UNION ALL
SELECT 'Jun 24 2010 11:30AM','1.7' UNION ALL
SELECT 'Jun 24 2010 11:15AM','1.6' UNION ALL
SELECT 'Jun 24 2010 11:00AM','1.8' UNION ALL
SELECT 'Jun 24 2010 10:45AM','1.5' UNION ALL
SELECT 'Jun 24 2010 10:30AM','1.9' UNION ALL
SELECT 'Jun 24 2010 10:15AM','1.4' UNION ALL
SELECT 'Jun 24 2010 10:00AM','2.1' UNION ALL
SELECT 'Jun 24 2010 9:45AM','1.3' UNION ALL
SELECT 'Jun 24 2010 9:30AM','2.4' UNION ALL
SELECT 'Jun 24 2010 9:15AM','1.2'
GO
There is no special formatting on the datetime field.
I've tried running the query:
SELECT TOP 4 SUM(T_DATA) As Total
FROM [DataWise].[dbo].[RiverRd_Precip_15]
But it just gives me the total of all the rows (this, I’m sure, is due to my lack of expertise when it comes to T-SQL).
My intentions are to apply what I learn here and compile an SSIS package to run on a regular schedule.
Any help would be greatly appreciated.
Thank you.
June 25, 2010 at 7:07 am
Is it always the last hour, the last 3 hours, the last 6 hours which you want to retrieve?
As a single row?
For each site?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
June 25, 2010 at 7:21 am
/****** Drop Table If It Exists ******/
--if object_id('#RiverRd_Precip_15') is not null
drop table #RiverRd_Precip_15
/****** Create Table ******/
CREATE TABLE #RiverRd_Precip_15 (
[T_TIME] datetime,
[T_DATA] float
)
/****** Insert Data Into Table ******/
INSERT INTO #RiverRd_Precip_15
(T_TIME, T_DATA)
SELECT 'Jun 24 2010 3:00PM','.6' UNION ALL
SELECT 'Jun 24 2010 2:45PM','1.5' UNION ALL
SELECT 'Jun 24 2010 2:30PM','1.6' UNION ALL
SELECT 'Jun 24 2010 2:15PM','1.4' UNION ALL
SELECT 'Jun 24 2010 2:00PM','1.7' UNION ALL
SELECT 'Jun 24 2010 1:45PM','1.3' UNION ALL
SELECT 'Jun 24 2010 1:30PM','1.8' UNION ALL
SELECT 'Jun 24 2010 1:15PM','1.2' UNION ALL
SELECT 'Jun 24 2010 1:00PM','1.9' UNION ALL
SELECT 'Jun 24 2010 12:45PM','1.1' UNION ALL
SELECT 'Jun 24 2010 12:30PM','2.3' UNION ALL
SELECT 'Jun 24 2010 12:15PM','2.2' UNION ALL
SELECT 'Jun 24 2010 12:00PM','.1' UNION ALL
SELECT 'Jun 24 2010 11:45AM','.8' UNION ALL
SELECT 'Jun 24 2010 11:30AM','1.7' UNION ALL
SELECT 'Jun 24 2010 11:15AM','1.6' UNION ALL
SELECT 'Jun 24 2010 11:00AM','1.8' UNION ALL
SELECT 'Jun 24 2010 10:45AM','1.5' UNION ALL
SELECT 'Jun 24 2010 10:30AM','1.9' UNION ALL
SELECT 'Jun 24 2010 10:15AM','1.4' UNION ALL
SELECT 'Jun 24 2010 10:00AM','2.1' UNION ALL
SELECT 'Jun 24 2010 9:45AM','1.3' UNION ALL
SELECT 'Jun 24 2010 9:30AM','2.4' UNION ALL
SELECT 'Jun 24 2010 9:15AM','1.2'
-- get these datetimes up-to-date
UPDATE #RiverRd_Precip_15 SET T_TIME = DATEADD(hh, -1, DATEADD(dd, 1, T_TIME))
;WITH DataWithExtras AS (
SELECT Recency = ROW_NUMBER() OVER(ORDER BY T_TIME DESC),
*, Age_minutes = DATEDIFF(mi, T_TIME, GETDATE())
FROM #RiverRd_Precip_15
WHERE DATEDIFF(hh, T_TIME, GETDATE()) <= 6
)
SELECT
[1 hour] = SUM(CASE WHEN Age_minutes <= 60 THEN T_DATA ELSE 0 END),
[3 hour] = SUM(CASE WHEN Age_minutes <= 180 THEN T_DATA ELSE 0 END)
FROM DataWithExtras
Or this, but it's less fun;
SELECT
[1 hour] = SUM(CASE WHEN DATEDIFF(mi, T_TIME, GETDATE()) <= 60 THEN T_DATA ELSE 0 END),
[3 hour] = SUM(CASE WHEN DATEDIFF(mi, T_TIME, GETDATE()) <= 180 THEN T_DATA ELSE 0 END)
FROM #RiverRd_Precip_15
WHERE DATEDIFF(hh, T_TIME, GETDATE()) <= 6
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
June 25, 2010 at 7:30 am
wilsot (6/25/2010)
I've tried running the query:
SELECT TOP 4 SUM(T_DATA) As Total
FROM [DataWise].[dbo].[RiverRd_Precip_15]
That query is doing exactly what you're asking it to
SELECT SUM(a.t_data)
FROM (SELECT TOP 4 *
FROM DataWise.dbo.riverrd_precip_15) AS a
June 25, 2010 at 3:46 pm
Hey guys...thanks for the quick response!
I'll try this out and see what I come up with.
June 30, 2010 at 10:27 am
Well...I got it up and running.
I ended up importing all 8 precip tables (with 15 minute intervals), and then running the following script to update the values to a common table:
UPDATE DataWise.dbo.CurrentData
SET Total_1hr =(SELECT SUM(t_data)
FROM (SELECT TOP 4 *
FROM DataWise.dbo.RiverRd_Precip) AS a)
Where T_SENSOR=103
GO
UPDATE DataWise.dbo.CurrentData
SET Total_3hr =(SELECT SUM(t_data)
FROM (SELECT TOP 12 *
FROM DataWise.dbo.RiverRd_Precip) AS a)
Where T_SENSOR=103
GO
UPDATE DataWise.dbo.CurrentData
SET Total_6hr =(SELECT SUM(t_data)
FROM (SELECT TOP 24 *
FROM DataWise.dbo.RiverRd_Precip) AS a)
Where T_SENSOR=103
GO
UPDATE DataWise.dbo.CurrentData
SET Total_24hr =(SELECT SUM(t_data)
FROM (SELECT TOP 96 *
FROM DataWise.dbo.RiverRd_Precip) AS a)
Where T_SENSOR=103
GO
(this is just the code for one station)
This gives me 1 hr, 3 hr, 6 hr, and 24 hr values. It's also included in my SSIS package and is running smoothly.
Thanks for all your help.
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy