Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Calculating a Moving Average Rate Topic Display Mode Topic Options
Author
 Message
 Posted Friday, February 08, 2013 12:35 PM
 SSC-Enthusiastic Group: General Forum Members Last Login: Yesterday @ 2:33 PM Points: 103, Visits: 277
 Hi,I have a need to calculate a moving average. Ultimately, I need to convert the Select statement into an Update command. But lets start with the SELECT statement.I have two tables, TestDate and TestNode. The TestDate table is a date lookup table. It contains an integer field (TPIntDate) which represent the day and can be used for numeric calculations, such as subtracting 20 days. Here is the code to create two tables and populate them with data.TestDate Table first.`/****** Object: Table [dbo].[TestDate] Script Date: 02/08/2013 13:58:52 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE TABLE [dbo].[TestDate]( [TimePoint] [datetime] NULL, [TPYear] [int] NULL, [TPMth] [int] NULL, [TPDay] [int] NULL, [TPHour] [int] NULL, [TPIntDate] [int] NULL) ON [PRIMARY]GOInsert into TestDate(Timepoint,TPYear,TPMth, TPDay, TPHour, TPIntDate)select '01/01/2012 05:00:00',2012,1,1,5,1unionselect '01/02/2012 05:00:00',2012,1,2,5,2unionselect '01/03/2012 05:00:00',2012,1,3,5,3unionselect '01/04/2012 05:00:00',2012,1,4,5,4unionselect '01/05/2012 05:00:00',2012,1,5,5,5unionselect '01/06/2012 05:00:00',2012,1,6,5,6unionselect '01/07/2012 05:00:00',2012,1,7,5,7unionselect '01/08/2012 05:00:00',2012,1,8,5,8unionselect '01/09/2012 05:00:00',2012,1,9,5,9unionselect '01/10/2012 05:00:00',2012,1,10,5,10unionselect '01/11/2012 05:00:00',2012,1,11,5,11unionselect '01/12/2012 05:00:00',2012,1,12,5,12unionselect '01/13/2012 05:00:00',2012,1,13,5,13unionselect '01/14/2012 05:00:00',2012,1,14,5,14unionselect '01/15/2012 05:00:00',2012,1,15,5,15unionselect '01/16/2012 05:00:00',2012,1,16,5,16unionselect '01/17/2012 05:00:00',2012,1,17,5,17unionselect '01/18/2012 05:00:00',2012,1,18,5,18unionselect '01/19/2012 05:00:00',2012,1,19,5,19unionselect '01/20/2012 05:00:00',2012,1,20,5,20unionselect '01/21/2012 05:00:00',2012,1,21,5,21unionselect '01/22/2012 05:00:00',2012,1,22,5,22unionselect '01/23/2012 05:00:00',2012,1,23,5,23unionselect '01/24/2012 05:00:00',2012,1,24,5,24unionselect '01/25/2012 05:00:00',2012,1,25,5,25unionselect '01/26/2012 05:00:00',2012,1,26,5,26unionselect '01/27/2012 05:00:00',2012,1,27,5,27unionselect '01/28/2012 05:00:00',2012,1,28,5,28unionselect '01/29/2012 05:00:00',2012,1,29,5,29unionselect '01/30/2012 05:00:00',2012,1,30,5,30unionselect '01/31/2012 05:00:00',2012,1,31,5,31unionselect '02/01/2012 05:00:00',2012,2,1,5,32unionselect '02/02/2012 05:00:00',2012,2,2,5,33unionselect '02/03/2012 05:00:00',2012,2,3,5,34unionselect '02/04/2012 05:00:00',2012,2,4,5,35unionselect '02/05/2012 05:00:00',2012,2,5,5,36unionselect '02/06/2012 05:00:00',2012,2,6,5,37unionselect '02/07/2012 05:00:00',2012,2,7,5,38unionselect '02/08/2012 05:00:00',2012,2,8,5,39unionselect '02/09/2012 05:00:00',2012,2,9,5,40unionselect '02/10/2012 05:00:00',2012,2,10,5,41unionselect '02/11/2012 05:00:00',2012,2,11,5,42unionselect '02/12/2012 05:00:00',2012,2,12,5,43`TestNode table next.`/****** Object: Table [dbo].[TestNode] Script Date: 02/08/2013 14:08:19 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOSET ANSI_PADDING ONGOCREATE TABLE [dbo].[TestNode]( [TimePoint] [datetime] NULL, [Sink] [varchar](50) NULL, [Source] [varchar](50) NULL, [Delta] [decimal](8, 2) NULL) ON [PRIMARY]GOSET ANSI_PADDING OFFGOInsert into TestNode(Timepoint,Sink, Source, Delta)select '01/01/2012 05:00:00','PEACH BOTTOM','OVEC',1unionselect '01/02/2012 05:00:00','PEACH BOTTOM','OVEC',1.5unionselect '01/03/2012 05:00:00','PEACH BOTTOM','OVEC',1.7unionselect '01/04/2012 05:00:00','PEACH BOTTOM','OVEC',9unionselect '01/05/2012 05:00:00','PEACH BOTTOM','OVEC',6unionselect '01/06/2012 05:00:00','PEACH BOTTOM','OVEC',-8unionselect '01/07/2012 05:00:00','PEACH BOTTOM','OVEC',5unionselect '01/08/2012 05:00:00','PEACH BOTTOM','OVEC',4unionselect '01/09/2012 05:00:00','PEACH BOTTOM','OVEC',5.1unionselect '01/10/2012 05:00:00','PEACH BOTTOM','OVEC',7unionselect '01/11/2012 05:00:00','PEACH BOTTOM','OVEC',3unionselect '01/12/2012 05:00:00','PEACH BOTTOM','OVEC',7unionselect '01/13/2012 05:00:00','PEACH BOTTOM','OVEC',35unionselect '01/14/2012 05:00:00','PEACH BOTTOM','OVEC',-9unionselect '01/15/2012 05:00:00','PEACH BOTTOM','OVEC',3unionselect '01/16/2012 05:00:00','PEACH BOTTOM','OVEC',47unionselect '01/17/2012 05:00:00','PEACH BOTTOM','OVEC',1.6unionselect '01/18/2012 05:00:00','PEACH BOTTOM','OVEC',7.4unionselect '01/19/2012 05:00:00','PEACH BOTTOM','OVEC',5unionselect '01/20/2012 05:00:00','PEACH BOTTOM','OVEC',6.3unionselect '01/21/2012 05:00:00','PEACH BOTTOM','OVEC',7unionselect '01/22/2012 05:00:00','PEACH BOTTOM','OVEC',8unionselect '01/23/2012 05:00:00','PEACH BOTTOM','OVEC',6.5unionselect '01/24/2012 05:00:00','PEACH BOTTOM','OVEC',-7unionselect '01/25/2012 05:00:00','PEACH BOTTOM','OVEC',7unionselect '01/26/2012 05:00:00','PEACH BOTTOM','OVEC',6.2unionselect '01/27/2012 05:00:00','PEACH BOTTOM','OVEC',7unionselect '01/28/2012 05:00:00','PEACH BOTTOM','OVEC',1unionselect '01/29/2012 05:00:00','PEACH BOTTOM','OVEC',-8unionselect '01/30/2012 05:00:00','PEACH BOTTOM','OVEC',-3unionselect '01/31/2012 05:00:00','PEACH BOTTOM','OVEC',7unionselect '02/01/2012 05:00:00','PEACH BOTTOM','OVEC',10unionselect '02/02/2012 05:00:00','PEACH BOTTOM','OVEC',9unionselect '02/03/2012 05:00:00','PEACH BOTTOM','OVEC',6unionselect '02/04/2012 05:00:00','PEACH BOTTOM','OVEC',-9unionselect '02/05/2012 05:00:00','PEACH BOTTOM','OVEC',-74unionselect '02/06/2012 05:00:00','PEACH BOTTOM','OVEC',6unionselect '02/07/2012 05:00:00','PEACH BOTTOM','OVEC',3unionselect '02/08/2012 05:00:00','PEACH BOTTOM','OVEC',34unionselect '02/09/2012 05:00:00','PEACH BOTTOM','OVEC',-54unionselect '02/10/2012 05:00:00','PEACH BOTTOM','OVEC',-41unionselect '02/11/2012 05:00:00','PEACH BOTTOM','OVEC',-35unionselect '02/12/2012 05:00:00','PEACH BOTTOM','OVEC',100Unionselect '01/01/2012 05:00:00','PEACH BOTTOM','ELMARA',1unionselect '01/02/2012 05:00:00','PEACH BOTTOM','ELMARA',1.5unionselect '01/03/2012 05:00:00','PEACH BOTTOM','ELMARA',1.7unionselect '01/04/2012 05:00:00','PEACH BOTTOM','ELMARA',9unionselect '01/05/2012 05:00:00','PEACH BOTTOM','ELMARA',6unionselect '01/06/2012 05:00:00','PEACH BOTTOM','ELMARA',-8unionselect '01/07/2012 05:00:00','PEACH BOTTOM','ELMARA',5unionselect '01/08/2012 05:00:00','PEACH BOTTOM','ELMARA',4unionselect '01/09/2012 05:00:00','PEACH BOTTOM','ELMARA',5.1unionselect '01/10/2012 05:00:00','PEACH BOTTOM','ELMARA',7unionselect '01/11/2012 05:00:00','PEACH BOTTOM','ELMARA',3unionselect '01/12/2012 05:00:00','PEACH BOTTOM','ELMARA',7unionselect '01/13/2012 05:00:00','PEACH BOTTOM','ELMARA',35unionselect '01/14/2012 05:00:00','PEACH BOTTOM','ELMARA',-9unionselect '01/15/2012 05:00:00','PEACH BOTTOM','ELMARA',3unionselect '01/16/2012 05:00:00','PEACH BOTTOM','ELMARA',47unionselect '01/17/2012 05:00:00','PEACH BOTTOM','ELMARA',1.6unionselect '01/18/2012 05:00:00','PEACH BOTTOM','ELMARA',7.4unionselect '01/19/2012 05:00:00','PEACH BOTTOM','ELMARA',5unionselect '01/20/2012 05:00:00','PEACH BOTTOM','ELMARA',6.3unionselect '01/21/2012 05:00:00','PEACH BOTTOM','ELMARA',7unionselect '01/22/2012 05:00:00','PEACH BOTTOM','ELMARA',8unionselect '01/23/2012 05:00:00','PEACH BOTTOM','ELMARA',6.5unionselect '01/24/2012 05:00:00','PEACH BOTTOM','ELMARA',-7unionselect '01/25/2012 05:00:00','PEACH BOTTOM','ELMARA',7unionselect '01/26/2012 05:00:00','PEACH BOTTOM','ELMARA',6.2unionselect '01/27/2012 05:00:00','PEACH BOTTOM','ELMARA',7unionselect '01/28/2012 05:00:00','PEACH BOTTOM','ELMARA',1unionselect '01/29/2012 05:00:00','PEACH BOTTOM','ELMARA',-8unionselect '01/30/2012 05:00:00','PEACH BOTTOM','ELMARA',-3unionselect '01/31/2012 05:00:00','PEACH BOTTOM','ELMARA',7unionselect '02/01/2012 05:00:00','PEACH BOTTOM','ELMARA',10unionselect '02/02/2012 05:00:00','PEACH BOTTOM','ELMARA',9unionselect '02/03/2012 05:00:00','PEACH BOTTOM','ELMARA',6unionselect '02/04/2012 05:00:00','PEACH BOTTOM','ELMARA',-9unionselect '02/05/2012 05:00:00','PEACH BOTTOM','ELMARA',-74unionselect '02/06/2012 05:00:00','PEACH BOTTOM','ELMARA',6unionselect '02/07/2012 05:00:00','PEACH BOTTOM','ELMARA',3unionselect '02/08/2012 05:00:00','PEACH BOTTOM','ELMARA',34unionselect '02/09/2012 05:00:00','PEACH BOTTOM','ELMARA',-54unionselect '02/10/2012 05:00:00','PEACH BOTTOM','ELMARA',-41unionselect '02/11/2012 05:00:00','PEACH BOTTOM','ELMARA',-35unionselect '02/12/2012 05:00:00','PEACH BOTTOM','ELMARA',100`There is a record for every combination of Source, Sink, day and hour. Every source/sink combination has a specific Date and time (Time is in hours, 0 - 23). The supplied data only contains two Source/Sink pairs for only one hour of the day across 43 days. Here is what I have tried but this code receives an error.`Select N.Source, N.Sink, N.TimePoint, D.TPHour, AVG(Delta)as MA20 From dbo.TestNode Ninner join dbo.TestDate DOn N.Timepoint = D.TimePoint inner Join Dbo.TestNode N2on N.Source = N2.Source and N.Sink = N2.Source and D.TPHour = D2.TPHour and D2.TPIntDate <= D.TPIntDate and D2.TPIntDate >= D.TPIntDate - 20inner join dbo.TestDate D2On N2.TimePoint = D2.TimePointGroup by N.Source, N.Sink, N.TimePoint, D.TPHour`Could some please assist me in writing a select statement that will return a moving average (20 day) for every Souce, Sink, Date and Time record?Thank you in advance,pat
Post #1417876
 Posted Sunday, February 10, 2013 6:20 PM
 SSCrazy Group: General Forum Members Last Login: Today @ 12:22 AM Points: 2,370, Visits: 3,250
 If your TestNode data contains contiguous dates (meaning no missing dates) for both source/sinks (as provided), there is no need to use the TestDate table.`SELECT a.Sink, a.[Source], a.TimePoint, [20DayAvg]=AVG(a.Delta)FROM TestNode aINNER JOIN TestNode b ON a.Sink = b.Sink AND a.[Source] = b.[Source] AND b.Timepoint BETWEEN a.TimePoint - 19 AND a.TimePointGROUP BY a.Sink, a.[Source], a.TimePointHAVING COUNT(*) = 20ORDER BY a.[Sink], a.[Source], a.TimePoint`This assumes you want records with a 20 day moving average starting on 21 Jan.If your actual records are missing data for some days, how would you calculate the moving average? Would you want to go back the 20 days where there are records? No loops! No CURSORs! No RBAR! Hoo-uh!INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?Need to UNPIVOT? Why not CROSS APPLY VALUES instead?Since random numbers are too important to be left to chance, let's generate some!Are you too recursively challenged?Splitting strings based on patterns can be fast!
Post #1418163
 Posted Sunday, February 10, 2013 7:40 PM
 SSC-Enthusiastic Group: General Forum Members Last Login: Yesterday @ 2:33 PM Points: 103, Visits: 277
 Dwain,Thanks. I was presenting the most simplistic example I would need to process. I was hoping once this basic query was working I would be able to increase its complexity on my own. This example does use contiguous data. However, all the queries that will be built from this query will not use contiguous data. So I would like to keep the date table. The date table contains numbers representing the DOW, Year, Month and a day counter. I created this lookup table so that all the date math that is required can be performed on Integers rather than DateTime fields. This was advice I had received here on SQLServerCentral.The only records that may be missing data would be those at the beginning of the dataset (First 19 days. We haven’t discussed how we will deal with those points). A data point will always be defined as the Day and hour of a Source/Sink combination. Values exists for every Source/Sink combination by hour for every day. I realize the date table complicates this simplistic example but I think I need to keep it. Any suggestions?Thanks,pat
Post #1418187
 Posted Sunday, February 10, 2013 7:56 PM
 SSCrazy Group: General Forum Members Last Login: Today @ 12:22 AM Points: 2,370, Visits: 3,250
 Try removing some of the TestNode data for ELMARA and then see if the modified query gives you what you want.`--union--select '01/27/2012 05:00:00','PEACH BOTTOM','ELMARA',7--select '02/06/2012 05:00:00','PEACH BOTTOM','ELMARA',6--union--select '02/10/2012 05:00:00','PEACH BOTTOM','ELMARA',-41--unionSELECT a.Sink, a.[Source], a.TimePoint, [20DayAvg]=AVG(a.Delta)FROM TestNode aINNER JOIN TestDate b ON -- a.Sink = b.Sink AND a.[Source] = b.[Source] AND b.Timepoint BETWEEN a.TimePoint - 19 AND a.TimePointGROUP BY a.Sink, a.[Source], a.TimePointHAVING COUNT(*) = 20ORDER BY a.[Sink], a.[Source], a.TimePoint`Note that I substituted TestDate for TestNode as derived table b.If it does not give you what you want (you'll find that 27 Jan, 06&10 Feb records are missing for ELMARA) then you'll need to be a little more explicit with respect to your expected output results. No loops! No CURSORs! No RBAR! Hoo-uh!INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?Need to UNPIVOT? Why not CROSS APPLY VALUES instead?Since random numbers are too important to be left to chance, let's generate some!Are you too recursively challenged?Splitting strings based on patterns can be fast!
Post #1418189
 Posted Monday, February 11, 2013 1:04 AM
 SSCertifiable Group: General Forum Members Last Login: Today @ 2:26 AM Points: 5,705, Visits: 11,136
 Gabriel Priester wrote an article discussing the calculation of moving average. Various alternatives are covered in the discussion. “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor 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 ModenExploring Recursive CTEs by Example Dwain Camps
Post #1418240
 Posted Monday, February 11, 2013 1:33 AM
 SSCrazy Group: General Forum Members Last Login: Today @ 12:22 AM Points: 2,370, Visits: 3,250
 ChrisM@Work (2/11/2013)Gabriel Priester wrote an article discussing the calculation of moving average. Various alternatives are covered in the discussion.A QU for moving averages??? Who'da thunk it! No loops! No CURSORs! No RBAR! Hoo-uh!INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?Need to UNPIVOT? Why not CROSS APPLY VALUES instead?Since random numbers are too important to be left to chance, let's generate some!Are you too recursively challenged?Splitting strings based on patterns can be fast!
Post #1418250
 Posted Monday, February 11, 2013 1:47 AM
 SSCertifiable Group: General Forum Members Last Login: Today @ 2:26 AM Points: 5,705, Visits: 11,136
 dwain.c (2/11/2013)ChrisM@Work (2/11/2013)Gabriel Priester wrote an article discussing the calculation of moving average. Various alternatives are covered in the discussion.A QU for moving averages??? Who'da thunk it!It's almost as sneaky as some of the stuff you've been messing with recently “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor 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 ModenExploring Recursive CTEs by Example Dwain Camps
Post #1418254
 Posted Tuesday, February 12, 2013 4:29 AM
 SSC-Enthusiastic Group: General Forum Members Last Login: Yesterday @ 2:33 PM Points: 103, Visits: 277
 ChrisM and DwainC,Thank you for the reference. I had actually seen that before. I used it as best I could. But since it didn't use the lookup table, I was not able to fully implement it. What is a QU?I am still stuck.pat
Post #1418867
 Posted Tuesday, February 12, 2013 5:02 AM
 SSCrazy Group: General Forum Members Last Login: Today @ 12:22 AM Points: 2,370, Visits: 3,250
 mpdillon (2/12/2013)ChrisM and DwainC,Thank you for the reference. I had actually seen that before. I used it as best I could. But since it didn't use the lookup table, I was not able to fully implement it. What is a QU?I am still stuck.patQU=Quirky Update No loops! No CURSORs! No RBAR! Hoo-uh!INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?Need to UNPIVOT? Why not CROSS APPLY VALUES instead?Since random numbers are too important to be left to chance, let's generate some!Are you too recursively challenged?Splitting strings based on patterns can be fast!
Post #1418885
 Posted Tuesday, February 12, 2013 5:55 AM
 SSCertifiable Group: General Forum Members Last Login: Today @ 2:26 AM Points: 5,705, Visits: 11,136
 mpdillon (2/12/2013)...since it didn't use the lookup table, I was not able to fully implement it....Which lookup table, Pat? The article (and the discussion) should provide you with all the information you need to calculate SMA's. What do you need help with? “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor 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 ModenExploring Recursive CTEs by Example Dwain Camps
Post #1418907

 Permissions