Organize Data by two weeks. - SQL Server Forums

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

Organize Data by two weeks.

Author

Topic

whiterabbot
Starting Member

18 Posts

Posted - 2008-11-17 : 21:49:39

I have a bunch of data that has the timestamp and average wind speeds. However, due to bad sensors the wind speed averages have been changed to Null. What I want to do is when the wind speed average is null, I want to find the mean wind speed for a week before and for a week after when the wind speed average is null.

This is what I have so far

sqlQuery(dbconnection,"drop view test")

sqlQuery(dbconnection,"create view test as select * from ScaledData_505")

flagging wind speed to null when there is sensor failure

sqlQuery(dbconnection,"UPDATE test
SET CH1Avg = CASE WHEN CH1QUAL IS NULL THEN CH1Avg ELSE NULL END,
CH2Avg = CASE WHEN CH2QUAL IS NULL THEN CH2Avg ELSE NULL END,
CH3Avg = CASE WHEN CH3QUAL IS NULL THEN CH3Avg ELSE NULL END,
CH4Avg = CASE WHEN CH4QUAL IS NULL THEN CH4Avg ELSE NULL END,
CH5Avg = CASE WHEN CH5QUAL IS NULL THEN CH5Avg ELSE NULL END,
CH7Avg = CASE WHEN CH7QUAL IS NULL THEN CH7Avg ELSE NULL END,
CH8Avg = CASE WHEN CH8QUAL IS NULL THEN CH8Avg ELSE NULL END,
CH9Avg = CASE WHEN CH9QUAL IS NULL THEN CH9Avg ELSE NULL END,
CH10Avg = CASE WHEN CH10QUAL IS NULL THEN CH10Avg ELSE NULL END")

sqlQuery(dbconnection,"drop view null_data1")

sqlQuery(dbconnection,"create view null_data1 as select TimeStamp FROM test where CH1Avg IS NULL")

sqlQuery(dbconnection,"drop view week_day1")

sqlQuery(dbconnection,"create view week_day1 as select (datepart(dw,min(TimeStamp))-2) as day FROM ScaledData_505")

a<-sqlQuery(dbconnection,"select DATEADD(wk,DateDiff(wk,4,null_data1.Timestamp),4) as week, Avg(ScaledData_505.CH1Avg) as ws from null_data1,ScaledData_505 where ScaledData_505.CH1Avg IS NOT NULL group by DATEADD(wk,DateDiff(wk,4,null_data1.Timestamp),4)")

This last part is where I have problems. Does anyone know of a better way to do this?

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-17 : 23:11:04

use DATEADD(wk,DateDiff(wk,0,null_data1.Timestamp),-1) for last week and DATEADD(wk,DateDiff(wk,0,null_data1.Timestamp),1) for next week. take union all for combining both queries

whiterabbot
Starting Member

18 Posts

Posted - 2008-11-18 : 13:30:33

The problem I am having is not with the DateAdd but with the Avg(ScaledData_505. The dates and the average wind speed are two different tables. The command works fine if I am using the same table, the TimeStamp and Wind Speed Averages in the same table, but as soon as I try it the two different tables, the wind speed average in table ScaledDate_505 and the TimeStamp in null_data1 then it no longer works. I am not sure why. I forgot to mention this earlier.

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-18 : 13:33:17

quote:
Originally posted by whiterabbot

The problem I am having is not with the DateAdd but with the Avg(ScaledData_505. The dates and the average wind speed are two different tables. The command works fine if I am using the same table, the TimeStamp and Wind Speed Averages in the same table, but as soon as I try it the two different tables, the wind speed average in table ScaledDate_505 and the TimeStamp in null_data1 then it no longer works. I am not sure why. I forgot to mention this earlier.

Thanks

how do you link two tables? is relation 1 to 1?

whiterabbot
Starting Member

18 Posts

Posted - 2008-11-18 : 13:47:48

I think that is the problem. In previous queries I did not need to link the tables because the relationship was 1 to 1 here it is not. I am not sure how to get around this problem. I was trying to think of a statement that would do the function if null_data1.TimeStamp = ScaledData_505.Timestamp then find average wind speed for the next week.

whiterabbot
Starting Member

18 Posts

Posted - 2008-11-19 : 17:35:30

This is my new code,
select DateAdd(wk,DateDiff(wk,ScaledData_801.TimeStamp-7,ScaledData_801.TimeStamp+7),ScaledData_801.TimeStamp-7) as date, Sum(ScaledData_801.CH1Avg) as ws from ScaledData_801, null_data2 where null_data2.TimeStamp = ScaledData_801.TimeStamp and ScaledData_801.CH2Avg is not null group by DateAdd(wk,DateDiff(wk,ScaledData_801.TimeStamp-7,ScaledData_801.TimeStamp+7),ScaledData_801.TimeStamp-7) order by DateAdd(wk,DateDiff(wk,ScaledData_801.TimeStamp-7,ScaledData_801.TimeStamp+7),ScaledData_801.TimeStamp-7)
The dates are right but the sum is only summing the value as that date. Is there a way to have ScaledData_801.CH1Avg summed betwee the two weeks?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-20 : 00:53:40

didnt understand your logic..whats the signifance of 7? you're adding and subtracting 7. can you explain that?

whiterabbot
Starting Member

18 Posts

Posted - 2008-11-21 : 14:43:36

The dates from my null_data2 table are when the sensor is down. What I want to do is when for the timestamp when the sensor is down from that point I want to find the average windspeeds for a week before and a week after. The code DATEADD(wk,DateDiff(wk,0,null_data1.Timestamp),-1) for last week and DATEADD(wk,DateDiff(wk,0,null_data1.Timestamp),1) for the next week does not work the way I want it to. I think the reason why is because for example, the sensor was down 8/30/2005 00:10 am and 8/30/2005 1:10 am and 8/31/2005 12:10 am and 9/14/2005 4:00 pm. There are a lot of days and even minutes when the sensors are down. At each on of these timestamps I want to find the averages for the week before and after. I hope that makes sense.

Thanks

whiterabbot
Starting Member

18 Posts

Posted - 2008-11-21 : 21:03:06

I now know what is happening but I am not sure how to get around the problem.
My code is
select DATEADD(dd,DateDiff(dd,0,null_data2.TimeStamp),7), Count(ScaledData_801.CH1Avg) from ScaledData_801, null_data2 where ScaledData_801.TimeStamp = null_data2.TimeStamp group by DATEADD(dd,DateDiff(dd,0,null_data2.TimeStamp),7) order by DATEADD(dd,DateDiff(dd,0,null_data2.TimeStamp),7)
The problem is that the null_data2.TimeStamps are defined when ScaledData_801.CH1Qual IS NOT NULL, I think what is happening is when I do the join and set ScaledData_801.TimeStamp = null_data2.TimeStamp this is also only counting the CH1Avg for when this is true but I need to incorporate all the CH1Avg for that whole week not the averages when ScaledData_801.TimeStamp = null_data2.TimeStamp within that week.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources