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