| Author |
 Topic  |
|
|
Release44
Starting Member
Canada
3 Posts |
 Posted - 03/30/2012 : 14:26:49
|
I got a database with Column-Item structure. There’s one column named "Time_Stamp" and lot of other column that are tags values. I wish to build a year reports of one column. For this example, I'll have the column "Time_Stamp" and the column "FLOW".
I'm doing a big table with 365 days, 1 column for each month (January to December) and 1 row for each day of a month (1 to 31). Normally I do a query for each day like this:
January 1 :
Select MAX([FLOW])-MIN([FLOW]) FROM [Indusoft].[dbo].[TREND001] WHERE [Time_Stamp] BETWEEN (SELECT DateAdd(mm, 0, DateAdd(dd, 0, DateAdd(yy, (SELECT DATEPART(year,GETDATE())) - 2000, '2000-01-01 00:00:00')))) AND (SELECT DateAdd(mm, 0, DateAdd(dd, 0, DateAdd(HH, 23, DateAdd(MI, 59, DateAdd(SS, 59, DateAdd(yy, (SELECT DATEPART(year,GETDATE())) - 2000, '2000-01-01 00:00:00')))))))
January 2:
Select MAX([FLOW])-MIN([FLOW]) FROM [Indusoft].[dbo].[TREND001] WHERE [Time_Stamp] BETWEEN (SELECT DateAdd(mm, 0, DateAdd(dd, 1, DateAdd(yy, (SELECT DATEPART(year,GETDATE())) - 2000, '2000-01-01 00:00:00')))) AND (SELECT DateAdd(mm, 0, DateAdd(dd, 1, DateAdd(HH, 23, DateAdd(MI, 59, DateAdd(SS, 59, DateAdd(yy, (SELECT DATEPART(year,GETDATE())) - 2000, '2000-01-01 00:00:00')))))))
I do this query 365 times and I change the month and the day manually:
December 31:
Select MAX([FLOW])-MIN([FLOW]) FROM [Indusoft].[dbo].[TREND001] WHERE [Time_Stamp] BETWEEN (SELECT DateAdd(mm, 11, DateAdd(dd, 30, DateAdd(yy, (SELECT DATEPART(year,GETDATE())) - 2000, '2000-01-01 00:00:00')))) AND (SELECT DateAdd(mm, 11, DateAdd(dd, 30, DateAdd(HH, 23, DateAdd(MI, 59, DateAdd(SS, 59, DateAdd(yy, (SELECT DATEPART(year,GETDATE())) - 2000, '2000-01-01 00:00:00')))))))
Since this is very long and very painful I'm now trying to optimize the process and I've achieve for 1 month a good query. For this I've create table for every month (January to December) and I've done 12 times the query below:
Month of January:
DELETE FROM [master].[dbo].[Month1]
DECLARE @someInt as int;
SET @someInt = 0
WHILE @someInt < 31 BEGIN
insert into Month1 ("January")
Select MAX([FLOW])-MIN([FLOW]) FROM [Indusoft].[dbo].[TREND001] WHERE [Time_Stamp] BETWEEN (SELECT DateAdd(mm, 0, DateAdd(dd, @someInt, DateAdd(yy, (SELECT DATEPART(year,GETDATE())) - 2000, '2000-01-01 00:00:00')))) AND (SELECT DateAdd(mm, 0, DateAdd(dd, @someInt, DateAdd(HH, 23, DateAdd(MI, 59, DateAdd(SS, 59, DateAdd(yy, (SELECT DATEPART(year,GETDATE())) - 2000, '2000-01-01 00:00:00')))))))
SET @someInt=@someInt + 1
END
I wish to do this whole year reports into 1 query.
All Month of the year:
DECLARE @day as int;
DECLARE @month as int;
SET @day = 0
SET @month = 0
WHILE @month < 12 BEGIN
SET @day = 0
WHILE @day < 31 BEGIN
Select MAX([FLOW])-MIN([FLOW]) FROM [Indusoft].[dbo].[TREND001] WHERE [Time_Stamp] BETWEEN (SELECT DateAdd(mm, @month, DateAdd(dd, @day, DateAdd(yy, (SELECT DATEPART(year,GETDATE())) - 2000, '2000-01-01 00:00:00')))) AND (SELECT DateAdd(mm, @month, DateAdd(dd, @day, DateAdd(HH, 23, DateAdd(MI, 59, DateAdd(SS, 59, DateAdd(yy, (SELECT DATEPART(year,GETDATE())) - 2000, '2000-01-01 00:00:00')))))))
SET @day=@day + 1
END
SET @month = @month +1
END
The result is good but all 365 days are in 1 column. SQL write with every select in every Column. Do any experienced SQL programmer can give me hint or help to achieve this year’s reports query? |
|
|
DonAtWork
Flowing Fount of Yak Knowledge
2111 Posts |
|
|
Release44
Starting Member
Canada
3 Posts |
Posted - 04/01/2012 : 08:19:57
|
Database I Have
This database is filled by another program. I got 10 columns, the first one is called "Time_Stamp" and it contains the date and time of every logged data. All other columns are logged data at a periodical time rate. This is into a water treatment plant. In this plant there are instruments that measure real time data such has distributed flow, distributed pressure, chlorine, ph and other stuff. Snap shot of every real time date are saved into this database every 30 seconds since years. I got 1 row for each periodical save. The database looks like this:
Time_Stamp / Distributed_Flow / Distributed_Pressure / Chlorine / (1 column per logged instruments)
2011-01-01 00:00:00 / 245.32 / 89.23 / 0.321 / ...
2011-01-01 00:00:30 / 244.42 / 90.30 / 0.330 / ...
2011-01-01 00:01:00 / 246.00 / 89.21 / 0.311 / ...
2011-01-01 00:01:30 / 250.12 / 89.99 / 0.344 / ...
2011-01-01 00:02:00 / 245.33 / 90.89 / 0.350 / ...
...
2011-12-31 23:59:30 / 311.22 / 90.22 / 0.387 / ...
The first row begins in 2008 and every 30 seconds another row is added with snap shot of every real time instrument in their respective column.
What I want to do:
We want year reports of the distributed flow. This report will be generated automatically by an SQL Procedure launched with Windows Scheduler every last day of every year.
The reports must contain an average of every day of the years, this mean an average between the beginning and the end of every day. For now on, I do 365 queries, one for each day and I send the result into Excel:
Select AVG(Distributed_Flow) FROM [DATABASE] WHERE [Time_Stamp] BETWEEN (SELECT DateAdd(mm, 0, DateAdd(dd, 0, DateAdd(yy, (SELECT DATEPART(year,GETDATE())) - 2000, '2000-01-01 00:00:00')))) AND (SELECT DateAdd(mm, 0, DateAdd(dd, 0, DateAdd(HH, 23, DateAdd(MI, 59, DateAdd(SS, 59, DateAdd(yy, (SELECT DATEPART(year,GETDATE())) - 2000, '2000-01-01 00:00:00')))))))
Those queries are equal to:
Select AVG(Distributed_Flow) FROM [DATABASE] WHERE [Time_Stamp] BETWEEN 2011-01-01 00:00:00 AND 2011-01-01 23:59:00
I change the number of DateAdd(mm, 0) and DateAdd(dd, 0) from 0 to 12 and 0 to 31 to get result of each day of each month :
Select AVG(Distributed_Flow) FROM [DATABASE] WHERE [Time_Stamp] BETWEEN 2011-01-01 00:00:00 AND 2011-01-01 23:59:00
Select AVG(Distributed_Flow) FROM [DATABASE] WHERE [Time_Stamp] BETWEEN 2011-01-02 00:00:00 AND 2011-01-02 23:59:00
Select AVG(Distributed_Flow) FROM [DATABASE] WHERE [Time_Stamp] BETWEEN 2011-01-03 00:00:00 AND 2011-01-03 23:59:00
...
Select AVG(Distributed_Flow) FROM [DATABASE] WHERE [Time_Stamp] BETWEEN 2011-31-31 00:00:00 AND 2011-31-31 23:59:00
Since this report is generating automatically, I can’t get a simple query, because it has to be the current years.
The final report that engineer wants must look like this:
31 rows and 12 columns. Each column is a month and each row is a day.
Day / January / February / March / April ... December
1 / Avg(January 1 Data) / Avg(February 1 Data) / Avg(March 1 Data) / Avg(April 1 Data) ... Avg(December 1 Data)
2 / Avg(January 2 Data) / Avg(February 2 Data) / Avg(March 2 Data) / Avg(April 2 Data) ... Avg(December 2 Data)
3 / Avg(January 3 Data) / Avg(February 3 Data) / Avg(March 3 Data) / Avg(April 3 Data) ... Avg(December 3 Data)
4 / Avg(January 4 Data) / Avg(February 4 Data) / Avg(March 4 Data) / Avg(April 4 Data) ... Avg(December 4 Data)
5 / Avg(January 5 Data) / Avg(February 5 Data) / Avg(March 5 Data) / Avg(April 5 Data) ... Avg(December 5 Data)
...
30 / Avg(January 30 Data) / N/A / Avg(March 30 Data) / Avg(April 30 Data) ... Avg(December 30 Data)
31 / Avg(January 31 Data) / N/A / Avg(March 31 Data) / N/A ... Avg(December 31 Data)
I wish to optimize my 365 queries into less. I'm new to SQL, and maybe I took a totally bad way to achieve my result. If I can send you a picture of the report that can maybe help to understand my explanation. |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47040 Posts |
Posted - 04/01/2012 : 10:48:06
|
DECLARE @Year int
SET @Year=2011--example value
SELECT DATEADD(DAY,DATEDIFF(DAY,0,[Time_Stamp]),0),AVG(Distributed_Flow) AS AvgDistributedFlow
FROM [DATABASE]
WHERE [Time_Stamp] >=DATEADD(yy,@Year-1900,0) AND [Time_Stamp] < DATEADD(yy,@Year-1899,0)
GROUP BY DATEADD(DAY,DATEDIFF(DAY,0,[Time_Stamp]),0)
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
Release44
Starting Member
Canada
3 Posts |
Posted - 04/02/2012 : 09:03:55
|
Wow that's very nice !! It will create a column that's contains data from the first day of the year and the last.
I'll ask the question if it's better to do :
[Time_Stamp] >= AND [Time_Stamp] <
rather than doing BETWEEN [Time_Stamp] AND [Time_Stamp]...
Thank you! |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47040 Posts |
Posted - 04/02/2012 : 15:52:20
|
its better. as it will make sure all records for period are getting included even if there are records that go created after 23:59:59 of period but before next day midnight
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
robvolk
Most Valuable Yak
USA
15558 Posts |
Posted - 04/02/2012 : 17:08:27
|
quote:
Originally posted by DonAtWork
Wow. First time i have ever seen a Negative 2nd Normal Form database.
I may steal that from you, once I finish laughing. It could take a while. |
|
|
| |
Topic |
|
|
|
Years Table
