September 17, 2015 at 12:03 pm
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.
September 17, 2015 at 2:48 pm
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
September 17, 2015 at 2:53 pm
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
September 17, 2015 at 3:07 pm
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
September 17, 2015 at 3:25 pm
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