If null use previous value that is not null and take average
in [Programming SQL]
|
Prev: MLM - Binary Payout Script
Next: Byte[] and File.
From: Sheldon on 2 Feb 2010 11:54 I have a join that produces results similar to this: SomeDate .444 SomeDate NULL SomeDate NULL SomeDate NULL SomeDate NULL SomeDate .676 SomeDate NULL SomeDate NULL I need to find the average of results like this: SomeDate .444 SomeDate .444 SomeDate .444 SomeDate .444 SomeDate .444 SomeDate .676 SomeDate .676 SomeDate .676 Any ideas on how to do this? -- Sheldon
From: Mike H on 2 Feb 2010 12:04 On Feb 2, 10:54 am, Sheldon <Shel...(a)discussions.microsoft.com> wrote: > I have a join that produces results similar to this: > > SomeDate .444 > SomeDate NULL > SomeDate NULL > SomeDate NULL > SomeDate NULL > SomeDate .676 > SomeDate NULL > SomeDate NULL > > I need to find the average of results like this: > > SomeDate .444 > SomeDate .444 > SomeDate .444 > SomeDate .444 > SomeDate .444 > SomeDate .676 > SomeDate .676 > SomeDate .676 > > Any ideas on how to do this? > > -- > Sheldon Way more information needed, as you have not provided any clue to how you might decide which somedate gets .676 and which somedate gets . 444. At this point, the best answer on how to do it is to get data in the right format.
From: Sheldon on 2 Feb 2010 12:55 Mike - Thanks for replying. If row value is null, use the previous value and then take the average of all of that (the dates are in order oldest to latest). In other words, if the value on 1/2/2010 is .444 and value on 1/3/2010 is NULL then make the value on 1/3/2010 .444. If the value on 1/4/2010 is NULL then make the value on 1/4/2010 .444. If the value on 1/5/2010 is NULL then make the value on 1/5/2010 .444. If the value on 1/6/2010 is .676, use .676. If the value on 1/7/2010 is NULL then make the value on 1/7/2010 .676. If the value on 1/8/2010 is NULL then make the value on 1/8/2010 .676. Take the average of the column (which no longer contains nulls). -- Sheldon "Mike H" wrote: > On Feb 2, 10:54 am, Sheldon <Shel...(a)discussions.microsoft.com> wrote: > > I have a join that produces results similar to this: > > > > SomeDate .444 > > SomeDate NULL > > SomeDate NULL > > SomeDate NULL > > SomeDate NULL > > SomeDate .676 > > SomeDate NULL > > SomeDate NULL > > > > I need to find the average of results like this: > > > > SomeDate .444 > > SomeDate .444 > > SomeDate .444 > > SomeDate .444 > > SomeDate .444 > > SomeDate .676 > > SomeDate .676 > > SomeDate .676 > > > > Any ideas on how to do this? > > > > -- > > Sheldon > > Way more information needed, as you have not provided any clue to how > you might decide which somedate gets .676 and which somedate gets . > 444. At this point, the best answer on how to do it is to get data in > the right format. > . >
From: Tom Cooper on 2 Feb 2010 13:48 The following won't be efficient if you have lots of values, but Declare @TestTable Table (SomeDate datetime, TheValue decimal(10,5)); Insert @TestTable(SomeDate, TheValue) Select '20100120', .444 Union All Select '20100121', NULL Union All Select '20100122', NULL Union All Select '20100123', NULL Union All Select '20100124', NULL Union All Select '20100125', .676 Union All Select '20100126', NULL Union All Select '20100127', NULL; With NewValues(NewValue) As (Select Coalesce(t.TheValue, (Select t1.TheValue From @TestTable t1 Where t1.SomeDate = (Select Max(t2.SomeDate) From @TestTable t2 Where t2.SomeDate < t.SomeDate And t2.TheValue Is Not Null))) From @TestTable t) Select Avg(NewValue) From NewValues; Tom "Sheldon" <Sheldon(a)discussions.microsoft.com> wrote in message news:45EBC325-126F-43C6-9071-133C586B1433(a)microsoft.com... > Mike - > > Thanks for replying. > > If row value is null, use the previous value and then take the average of > all of that > (the dates are in order oldest to latest). > > In other words, if the value on 1/2/2010 is .444 and value on 1/3/2010 is > NULL then make the value on 1/3/2010 .444. If the value on 1/4/2010 is > NULL > then make the value on 1/4/2010 .444. If the value on 1/5/2010 is NULL > then > make the value on 1/5/2010 .444. > > If the value on 1/6/2010 is .676, use .676. If the value on 1/7/2010 is > NULL then make the value on 1/7/2010 .676. If the value on 1/8/2010 is > NULL > then make the value on 1/8/2010 .676. > > Take the average of the column (which no longer contains nulls). > > -- > Sheldon > > > "Mike H" wrote: > >> On Feb 2, 10:54 am, Sheldon <Shel...(a)discussions.microsoft.com> wrote: >> > I have a join that produces results similar to this: >> > >> > SomeDate .444 >> > SomeDate NULL >> > SomeDate NULL >> > SomeDate NULL >> > SomeDate NULL >> > SomeDate .676 >> > SomeDate NULL >> > SomeDate NULL >> > >> > I need to find the average of results like this: >> > >> > SomeDate .444 >> > SomeDate .444 >> > SomeDate .444 >> > SomeDate .444 >> > SomeDate .444 >> > SomeDate .676 >> > SomeDate .676 >> > SomeDate .676 >> > >> > Any ideas on how to do this? >> > >> > -- >> > Sheldon >> >> Way more information needed, as you have not provided any clue to how >> you might decide which somedate gets .676 and which somedate gets . >> 444. At this point, the best answer on how to do it is to get data in >> the right format. >> . >>
From: Plamen Ratchev on 2 Feb 2010 13:54
Here is one example: CREATE TABLE Foo ( somedate DATETIME NOT NULL PRIMARY KEY, somevalue DECIMAL(10, 4)); INSERT INTO Foo VALUES ('20090101', 0.444); INSERT INTO Foo VALUES ('20090104', NULL); INSERT INTO Foo VALUES ('20090105', NULL); INSERT INTO Foo VALUES ('20090108', NULL); INSERT INTO Foo VALUES ('20090111', NULL); INSERT INTO Foo VALUES ('20090113', 0.676); INSERT INTO Foo VALUES ('20090115', NULL); INSERT INTO Foo VALUES ('20090118', NULL); SELECT A.somedate, COALESCE(A.somevalue, B.somevalue) AS somevalue FROM Foo AS A JOIN (SELECT somedate, somevalue FROM Foo WHERE somevalue IS NOT NULL) AS B ON A.somedate >= B.somedate AND NOT EXISTS(SELECT * FROM Foo AS C WHERE C.somedate > B.somedate AND C.somedate <= A.somedate AND C.somevalue IS NOT NULL); /* somedate somevalue ----------------------- -------------- 2009-01-01 00:00:00.000 0.4440 2009-01-04 00:00:00.000 0.4440 2009-01-05 00:00:00.000 0.4440 2009-01-08 00:00:00.000 0.4440 2009-01-11 00:00:00.000 0.4440 2009-01-13 00:00:00.000 0.6760 2009-01-15 00:00:00.000 0.6760 2009-01-18 00:00:00.000 0.6760 */ SELECT AVG(COALESCE(A.somevalue, B.somevalue)) AS avgvalue FROM Foo AS A JOIN (SELECT somedate, somevalue FROM Foo WHERE somevalue IS NOT NULL) AS B ON A.somedate >= B.somedate AND NOT EXISTS(SELECT * FROM Foo AS C WHERE C.somedate > B.somedate AND C.somedate <= A.somedate AND C.somevalue IS NOT NULL); /* avgvalue --------------------------------------- 0.531000 */ -- Plamen Ratchev http://www.SQLStudio.com |