# A quick interesting query

• Hi All

below is the sample table structure and problem statement. Request help to form this query:

create table months (jan float,

feb float,

mar float,

apr float,

may float,

jun float,

jul float,

aug float,

sep float,

oct float,

nov float,

dec float)

insert into months values(1.22,4.55,6.13,0,0,0,0,0,0,0,0,0)

insert into months values(7.22,8.55,0,0,0,0,0,0,0,0,0,0)

insert into months values(9.22,0,0,0,0,0,0,0,0,0,0,0)

select * from months

Problem:Whenever a month has 0 value, it should get the average of all previous months. So here in first row,

my query should return this for apr: (1.22+4.55+6.13)/3

this for may: (1.22+4.55+6.13+Avg we got for Apr)/4 and so on till dec...

Same logic will go on for all rows.

Awaiting responses...

Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.

• Something like this???

`IF OBJECT_ID('tempdb..#months') IS NOT NULL`

`DROP TABLE #months;`

`create table #months (`

`jan float,`

`feb float,`

`mar float,`

`apr float,`

`may float,`

`jun float,`

`jul float,`

`aug float,`

`sep float,`

`oct float,`

`nov float,`

`dec float);`

`insert into #months values(1.22,4.55,6.13,0,0,0,0,0,0,0,0,0);`

`insert into #months values(7.22,8.55,0,0,0,0,0,0,0,0,0,0);`

`insert into #months values(9.22,0,0,0,0,0,0,0,0,0,0,0);`

`SELECT`

`m.jan,`

`ISNULL(NULLIF(m.feb, 0), m.jan) AS feb,`

`ISNULL(NULLIF(m.mar, 0), (m.jan + m.feb) / 2) AS mar,`

`ISNULL(NULLIF(m.apr, 0), (m.jan + m.feb + m.mar) / 3) AS apr,`

`ISNULL(NULLIF(m.may, 0), (m.jan + m.feb + m.mar + m.apr) / 4) AS may,`

`ISNULL(NULLIF(m.jun, 0), (m.jan + m.feb + m.mar + m.apr + m.may) / 5) AS jun,`

`ISNULL(NULLIF(m.jul, 0), (m.jan + m.feb + m.mar + m.apr + m.may + m.jun) / 6) AS jul,`

`ISNULL(NULLIF(m.aug, 0), (m.jan + m.feb + m.mar + m.apr + m.may + m.jun + m.jul) / 7) AS aug,`

`ISNULL(NULLIF(m.sep, 0), (m.jan + m.feb + m.mar + m.apr + m.may + m.jun + m.jul + m.aug) / 8) AS sep,`

`ISNULL(NULLIF(m.oct, 0), (m.jan + m.feb + m.mar + m.apr + m.may + m.jun + m.jul + m.aug + m.sep) / 9) AS oct,`

`ISNULL(NULLIF(m.nov, 0), (m.jan + m.feb + m.mar + m.apr + m.may + m.jun + m.jul + m.aug + m.sep + m.oct) / 10) AS nov,`

`ISNULL(NULLIF(m.dec, 0), (m.jan + m.feb + m.mar + m.apr + m.may + m.jun + m.jul + m.aug + m.sep + m.oct + m.nov) / 11) AS [dec]`

`FROM`

`#months m`

• Jason A. Long (9/17/2015)

Something like this???

`IF OBJECT_ID('tempdb..#months') IS NOT NULL`

`DROP TABLE #months;`

`create table #months (`

`jan float,`

`feb float,`

`mar float,`

`apr float,`

`may float,`

`jun float,`

`jul float,`

`aug float,`

`sep float,`

`oct float,`

`nov float,`

`dec float);`

`insert into #months values(1.22,4.55,6.13,0,0,0,0,0,0,0,0,0);`

`insert into #months values(7.22,8.55,0,0,0,0,0,0,0,0,0,0);`

`insert into #months values(9.22,0,0,0,0,0,0,0,0,0,0,0);`

`SELECT`

`m.jan,`

`ISNULL(NULLIF(m.feb, 0), m.jan) AS feb,`

`ISNULL(NULLIF(m.mar, 0), (m.jan + m.feb) / 2) AS mar,`

`ISNULL(NULLIF(m.apr, 0), (m.jan + m.feb + m.mar) / 3) AS apr,`

`ISNULL(NULLIF(m.may, 0), (m.jan + m.feb + m.mar + m.apr) / 4) AS may,`

`ISNULL(NULLIF(m.jun, 0), (m.jan + m.feb + m.mar + m.apr + m.may) / 5) AS jun,`

`ISNULL(NULLIF(m.jul, 0), (m.jan + m.feb + m.mar + m.apr + m.may + m.jun) / 6) AS jul,`

`ISNULL(NULLIF(m.aug, 0), (m.jan + m.feb + m.mar + m.apr + m.may + m.jun + m.jul) / 7) AS aug,`

`ISNULL(NULLIF(m.sep, 0), (m.jan + m.feb + m.mar + m.apr + m.may + m.jun + m.jul + m.aug) / 8) AS sep,`

`ISNULL(NULLIF(m.oct, 0), (m.jan + m.feb + m.mar + m.apr + m.may + m.jun + m.jul + m.aug + m.sep) / 9) AS oct,`

`ISNULL(NULLIF(m.nov, 0), (m.jan + m.feb + m.mar + m.apr + m.may + m.jun + m.jul + m.aug + m.sep + m.oct) / 10) AS nov,`

`ISNULL(NULLIF(m.dec, 0), (m.jan + m.feb + m.mar + m.apr + m.may + m.jun + m.jul + m.aug + m.sep + m.oct + m.nov) / 11) AS [dec]`

`FROM`

`#months m`

I don't think that this will add in the calculated average into subsequent columns.

Compare your results to those in my subsequent post:

`jan feb mar apr may jun jul aug sep oct nov dec`

`---- ---- ----- ----- ------ ----- ----- ----- ------- ----- ----- -----`

`1.22 4.55 6.13 3.966 2.975 2.38 1.983 1.7 1.4875 1.322 1.19 1.081`

`7.22 8.55 7.885 5.256 3.9425 3.154 2.628 2.252 1.97125 1.752 1.577 1.433`

`9.22 9.22 4.61 3.073 2.305 1.844 1.536 1.317 1.1525 1.024 0.922 0.838`

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

• In order to get and use the calculated average in subsequent columns, I decided to go with the "quirky update" method.

Edit: I hope that someone can come up with a better, set-based solution - I just couldn't think of one.

This is a method that has a few rules to it's usage - read and understand the referenced article in the code, and again here[/url].

`IF OBJECT_ID('tempdb.dbo.#months') IS NOT NULL DROP TABLE #months;`

`CREATE table #months`

` (jan float,`

` feb float,`

` mar float,`

` apr float,`

` may float,`

` jun float,`

` jul float,`

` aug float,`

` sep float,`

` oct float,`

` nov float,`

` dec float);`

`insert into #months values(1.22,4.55,6.13,0,0,0,0,0,0,0,0,0);`

`insert into #months values(7.22,8.55,0,0,0,0,0,0,0,0,0,0);`

`insert into #months values(9.22,0,0,0,0,0,0,0,0,0,0,0);`

`-- build a working temp table`

`IF OBJECT_ID('tempdb.dbo.#temp') IS NOT NULL DROP TABLE #temp;`

`CREATE TABLE #temp`

`(`

` RowID INTEGER,`

` MonthNbr FLOAT,`

` MonthValue FLOAT,`

` PRIMARY KEY CLUSTERED (RowID, MonthNbr) -- clustered index needed in the order to process the rows`

`);`

`-- populate the temp table.`

`-- use "CROSS APPLY (VALUES..." to unpivot the columns to rows`

`INSERT INTO #temp`

` (RowID, MonthNbr, MonthValue)`

`SELECT m.RowID, ca.*`

`FROM (SELECT RowID = ROW_NUMBER() OVER (ORDER BY (SELECT NULL)), *`

` FROM #months) m`

`CROSS APPLY (VALUES (1, jan), (2, feb), (3, mar), (4, apr), (5, may), (6, jun),`

` (7, jul), (8, aug), (9, sep), (10, oct), (11, nov), (12, [dec])) ca(MonthNbr, MonthValue)`

`;`

`-- working variables`

`DECLARE @value FLOAT = 0, -- total accumulator`

` @RowID INTEGER = 1, -- anchor column`

` @value2 FLOAT, -- value for the current row`

` @SequenceNbr INTEGER = 0; -- safety check (ensure updates in proper order)`

`-- this method is called a "Quirky Update"`

`-- full instructions can be read at: http://www.sqlservercentral.com/articles/T-SQL/68467/`

`-- there are several rules that need to be followed - ensure that you do so`

`-- cte to create the sequence number safety check`

`WITH SafeUpdate AS`

`(`

`SELECT RowID, MonthNbr, MonthValue,`

` -- get the order that the rows should be updated in.`

` SequenceNbr = ROW_NUMBER() OVER (ORDER BY RowID, MonthNbr)`

`FROM #temp`

`)`

`UPDATE a`

`SET -- reset total accumulator`

` @value = CASE WHEN RowID = @RowID THEN @value ELSE 0 END,`

` -- if current month value = 0, then get the value / (number of months - 1)`

` -- (running value thru prior row / number of months thru prior row... aka average of previous rows)`

` -- otherwise, get the current value`

` @value2 = CASE WHEN MonthValue = 0 THEN @value / (MonthNbr - 1) ELSE a.MonthValue END,`

` -- add to the accumulator`

` @value = @value + @value2,`

` -- update the MonthValue column with this calculation.`

` MonthValue = @value2,`

` -- anchor column`

` @RowID = RowID,`

` -- safety check to ensure that the rows are updated in the proper order`

` @SequenceNbr = CASE WHEN SequenceNbr = @SequenceNbr + 1 THEN @SequenceNbr + 1 ELSE 1/0 END`

`FROM SafeUpdate a`

`-- now pivot the columns to rows and return the results`

`SELECT`

` jan = MAX(CASE WHEN MonthNbr = 1 THEN MonthValue ELSE NULL END),`

` feb = MAX(CASE WHEN MonthNbr = 2 THEN MonthValue ELSE NULL END),`

` mar = MAX(CASE WHEN MonthNbr = 3 THEN MonthValue ELSE NULL END),`

` apr = MAX(CASE WHEN MonthNbr = 4 THEN MonthValue ELSE NULL END),`

` may = MAX(CASE WHEN MonthNbr = 5 THEN MonthValue ELSE NULL END),`

` jun = MAX(CASE WHEN MonthNbr = 6 THEN MonthValue ELSE NULL END),`

` jul = MAX(CASE WHEN MonthNbr = 7 THEN MonthValue ELSE NULL END),`

` aug = MAX(CASE WHEN MonthNbr = 8 THEN MonthValue ELSE NULL END),`

` sep = MAX(CASE WHEN MonthNbr = 9 THEN MonthValue ELSE NULL END),`

` oct = MAX(CASE WHEN MonthNbr = 10 THEN MonthValue ELSE NULL END),`

` nov = MAX(CASE WHEN MonthNbr = 11 THEN MonthValue ELSE NULL END),`

` [dec] = MAX(CASE WHEN MonthNbr = 12 THEN MonthValue ELSE NULL END)`

`FROM #temp`

`GROUP BY RowID;`

results:

`jan feb mar apr may jun jul aug sep oct nov dec`

`---- ---- ----- ----- ----- ----- ----- ----- ----- ----- ----- -----`

`1.22 4.55 6.13 3.966 3.966 3.966 3.966 3.966 3.966 3.966 3.966 3.966`

`7.22 8.55 7.885 7.885 7.885 7.885 7.885 7.885 7.885 7.885 7.885 7.885`

`9.22 9.22 9.22 9.22 9.22 9.22 9.22 9.22 9.22 9.22 9.22 9.22`

The 3.966 in the first row is actually 3.96666666666667... I just truncated them to make them easier to see here.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

• WayneS (9/17/2015)

I don't think that this will add in the calculated average into subsequent columns.

Good catch! here's another attempt using CTE...

`IF OBJECT_ID('tempdb..#months') IS NOT NULL`

`DROP TABLE #months;`

`create table #months (`

`jan float,`

`feb float,`

`mar float,`

`apr float,`

`may float,`

`jun float,`

`jul float,`

`aug float,`

`sep float,`

`oct float,`

`nov float,`

`dec float);`

`insert into #months values(1.22,4.55,6.13,0,0,0,0,0,0,0,0,0);`

`insert into #months values(7.22,8.55,0,0,0,0,0,0,0,0,0,0);`

`insert into #months values(9.22,0,0,0,0,0,0,0,0,0,0,0);`

`WITH feb AS (`

`SELECT`

`m.jan,`

`ISNULL(NULLIF(m.feb, 0), m.jan) AS feb,`

`m.mar,m.apr,m.may,m.jun,m.jul,m.aug,m.sep,m.oct,m.nov,m.dec`

`FROM #months m`

`), mar AS (`

`SELECT`

`m.jan,m.feb,`

`ISNULL(NULLIF(m.mar, 0), (m.jan + m.feb) / 2) AS mar,`

`m.apr,m.may,m.jun,m.jul,m.aug,m.sep,m.oct,m.nov,m.dec`

`FROM feb m`

`), apr AS (`

`SELECT`

`m.jan,m.feb,m.mar,`

`ISNULL(NULLIF(m.apr, 0), (m.jan + m.feb + m.mar) / 3) AS apr,`

`m.may,m.jun,m.jul,m.aug,m.sep,m.oct,m.nov,m.dec`

`FROM mar m`

`), may AS (`

`SELECT`

`m.jan,m.feb,m.mar,m.apr,`

`ISNULL(NULLIF(m.may, 0), (m.jan + m.feb + m.mar + m.apr) / 4) AS may,`

`m.jun,m.jul,m.aug,m.sep,m.oct,m.nov,m.dec`

`FROM apr m`

`), jun AS (`

`SELECT`

`m.jan,m.feb,m.mar,m.apr,m.may,`

`ISNULL(NULLIF(m.jun, 0), (m.jan + m.feb + m.mar + m.apr + m.may) / 5) AS jun,`

`m.jul,m.aug,m.sep,m.oct,m.nov,m.dec`

`FROM may m`

`), jul AS (`

`SELECT`

`m.jan,m.feb,m.mar,m.apr,m.may,m.jun,`

`ISNULL(NULLIF(m.jul, 0), (m.jan + m.feb + m.mar + m.apr + m.may + m.jun) / 6) AS jul,`

`m.aug,m.sep,m.oct,m.nov,m.dec`

`FROM jun m`

`), aug AS (`

`SELECT`

`m.jan,m.feb,m.mar,m.apr,m.may,m.jun,m.jul,`

`ISNULL(NULLIF(m.aug, 0), (m.jan + m.feb + m.mar + m.apr + m.may + m.jun + m.jul) / 7) AS aug,`

`m.sep,m.oct,m.nov,m.dec`

`FROM`

`jul m`

`), sep AS (`

`SELECT`

`m.jan,m.feb,m.mar,m.apr,m.may,m.jun,m.jul,m.aug,`

`ISNULL(NULLIF(m.sep, 0), (m.jan + m.feb + m.mar + m.apr + m.may + m.jun + m.jul + m.aug) / 8) AS sep,`

`m.oct,m.nov,m.dec`

`FROM aug m`

`), oct AS (`

`SELECT`

`m.jan,m.feb,m.mar,m.apr,m.may,m.jun,m.jul,m.aug,m.sep,`

`ISNULL(NULLIF(m.oct, 0), (m.jan + m.feb + m.mar + m.apr + m.may + m.jun + m.jul + m.aug + m.sep) / 9) AS oct,`

`m.nov,m.dec`

`FROM sep m`

`), nov AS (`

`SELECT`

`m.jan,m.feb,m.mar,m.apr,m.may,m.jun,m.jul,m.aug,m.sep,m.oct,`

`ISNULL(NULLIF(m.nov, 0), (m.jan + m.feb + m.mar + m.apr + m.may + m.jun + m.jul + m.aug + m.sep + m.oct) / 10) AS nov,`

`m.dec`

`FROM oct m`

`)`

`SELECT`

`m.jan,m.feb,m.mar,m.apr,m.may,m.jun,m.jul,m.aug,m.sep,m.oct,m.nov,`

`ISNULL(NULLIF(m.dec, 0), (m.jan + m.feb + m.mar + m.apr + m.may + m.jun + m.jul + m.aug + m.sep + m.oct + m.nov) / 11) AS [dec]`

`FROM nov m`

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply