Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Release44
Starting Member
3 Posts |
 Posted - 2012-03-30 : 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 ENDI 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 ENDThe 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
Master Smack Fu Yak Hacker
2167 Posts |
Posted - 2012-03-30 : 14:50:05
|
| Wow. First time i have ever seen a Negative 2nd Normal Form database. Perhaps if you told us what data you had, and what you wanted to do with it, someone could give you a MUCH better suggestion than what you have now...How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspxFor ultra basic questions, follow these links.http://www.sql-tutorial.net/ http://www.firstsql.com/tutor.htm http://www.w3schools.com/sql/default.asp |
 |
|
|
Release44
Starting Member
3 Posts |
Posted - 2012-04-01 : 08:19:57
|
| Database I HaveThis 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:00I 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:00Select AVG(Distributed_Flow) FROM [DATABASE] WHERE [Time_Stamp] BETWEEN 2011-01-02 00:00:00 AND 2011-01-02 23:59:00Select 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:00Since 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
52326 Posts |
Posted - 2012-04-01 : 10:48:06
|
| [code]DECLARE @Year intSET @Year=2011--example value[code]SELECT DATEADD(DAY,DATEDIFF(DAY,0,[Time_Stamp]),0),AVG(Distributed_Flow) AS AvgDistributedFlowFROM [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)[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
Release44
Starting Member
3 Posts |
Posted - 2012-04-02 : 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
52326 Posts |
Posted - 2012-04-02 : 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 MVPhttp://visakhm.blogspot.com/ |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2012-04-02 : 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. |
|
|
|
|
|
|
|
|
Years Table