I am trying to add a staggered running total and average to a query
returning quarterly CPI data. I need to add 4 quarterly data points
together to calculate a moving 12-month sum (YrCPI), and then to
complicate things, calculate a moving average of the 12-month figure
(AvgYrCPI).
Given the sample data:
CREATE TABLE [dbo].[QtrInflation] (
[Qtr] [smalldatetime] NOT NULL ,
[CPI] [decimal](8, 4) NOT NULL
) ON [PRIMARY]
GO
INSERT INTO QtrInflation (Qtr, CPI)
SELECT '1960-03-01', 0.7500 UNION
SELECT '1960-06-01', 1.4800 UNION
SELECT '1960-09-01', 1.4600 UNION
SELECT '1960-12-01', 0.7200 UNION
SELECT '1961-03-01', 0.7100 UNION
SELECT '1961-06-01', 0.7100 UNION
SELECT '1961-09-01',-0.7000 UNION
SELECT '1961-12-01', 0.0000 UNION
SELECT '1962-03-01', 0.0000 UNION
SELECT '1962-06-01', 0.0000 UNION
SELECT '1962-09-01', 0.0000 UNION
SELECT '1962-12-01', 0.0000 UNION
SELECT '1963-03-01', 0.0000 UNION
SELECT '1963-06-01', 0.0000 UNION
SELECT '1963-09-01', 0.7100 UNION
SELECT '1963-12-01', 0.0000 UNION
SELECT '1964-03-01', 0.7000 UNION
SELECT '1964-06-01', 0.7000 UNION
SELECT '1964-09-01', 1.3900 UNION
SELECT '1964-12-01', 0.6800 UNION
SELECT '1965-03-01', 0.6800 UNION
SELECT '1965-06-01', 1.3500 UNION
SELECT '1965-09-01', 0.6700 UNION
SELECT '1965-12-01', 1.3200
I am trying to return the following results:
Qtr CPI YrCPI AvgYrCPI
--- -- -- ---
1-Jun-60 1.48
1-Sep-60 1.46
1-Dec-60 0.72
1-Mar-61 0.71 4.37
1-Jun-61 0.71 3.60
1-Sep-61 -0.70 1.44
1-Dec-61 0.00 0.72 2.53
1-Mar-62 0.00 0.01 1.44
1-Jun-62 0.00 -0.70 0.37
1-Sep-62 0.00 0.00 0.01
1-Dec-62 0.00 0.00 -0.17
1-Mar-63 0.00 0.00 -0.18
1-Jun-63 0.00 0.00 0.00
1-Sep-63 0.71 0.71 0.18
1-Dec-63 0.00 0.71 0.36
1-Mar-64 0.70 1.41 0.71
1-Jun-64 0.70 2.11 1.24
1-Sep-64 1.39 2.79 1.76
1-Dec-64 0.68 3.47 2.45
1-Mar-65 0.68 3.45 2.96
1-Jun-65 1.35 4.10 3.45
1-Sep-65 0.67 3.38 3.60
1-Dec-65 1.32 4.02 3.74
Note, 4 data points are required to calculate a moving sum of CPI
(YrCPI) and 4 calculate YrCPI figures are required calculate the
annual average of YrCPI (AvgYrCPI), giving a staggered effect to the
first 7 results
This sad effort is about as far as I've got:
SELECT I.Qtr, I.CPI, SUM(S.CPI) AS YrCPI
FROM QtrInflation I
JOIN (
SELECT TOP 4 Qtr, CPI
FROM QtrInflation
) S
ON S.Qtr <= I.Qtr
GROUP BY I.Qtr, I.CPI
ORDER BY I.Qtr ASC
Can anyone suggest how do achieve this result without having to resort
to cursors?
Thanks,
StephenHi
This will do it (I think!) but there may be a neater way!
SELECT S.Qtr, S.CPI, D.YrCPI, E.AvgCPI
FROM QtrInflation S LEFT JOIN
( SELECT Q.Qtr, SUM(A.CPI) AS YrCPI
FROM QtrInflation Q LEFT JOIN ( SELECT Qtr, SUM(CPI) AS CPI
FROM QtrInflation
GROUP BY Qtr) A ON Q.Qtr >= A.Qtr AND DATEADD(YEAR,-1,Q.Qtr) < A.Qtr
GROUP BY Q.Qtr
HAVING COUNT(A.Qtr) = 4 ) D ON S.Qtr = D.Qtr
LEFT JOIN
( SELECT R.Qtr, SUM(B.CPI)/4 AS AvgCPI
FROM QtrInflation R LEFT JOIN ( SELECT Q.Qtr, SUM(A.CPI) AS CPI
FROM QtrInflation Q LEFT JOIN ( SELECT Qtr, SUM(CPI) AS CPI
FROM QtrInflation
GROUP BY Qtr) A ON Q.Qtr >= A.Qtr AND DATEADD(YEAR,-1,Q.Qtr) < A.Qtr
GROUP BY Q.Qtr
HAVING COUNT(A.Qtr) = 4 ) B ON R.Qtr >= B.Qtr AND DATEADD(YEAR,-1,R.Qtr)
< B.Qtr
GROUP BY R.Qtr
HAVING COUNT(B.Qtr) = 4 ) E ON S.Qtr = E.Qtr
ORDER BY S.Qtr
John
"Stephen Miller" <jsausten@.hotmail.com> wrote in message
news:cdb404de.0309210139.58ffad34@.posting.google.c om...
> Hi,
> I am trying to add a staggered running total and average to a query
> returning quarterly CPI data. I need to add 4 quarterly data points
> together to calculate a moving 12-month sum (YrCPI), and then to
> complicate things, calculate a moving average of the 12-month figure
> (AvgYrCPI).
> Given the sample data:
> CREATE TABLE [dbo].[QtrInflation] (
> [Qtr] [smalldatetime] NOT NULL ,
> [CPI] [decimal](8, 4) NOT NULL
> ) ON [PRIMARY]
> GO
> INSERT INTO QtrInflation (Qtr, CPI)
> SELECT '1960-03-01', 0.7500 UNION
> SELECT '1960-06-01', 1.4800 UNION
> SELECT '1960-09-01', 1.4600 UNION
> SELECT '1960-12-01', 0.7200 UNION
> SELECT '1961-03-01', 0.7100 UNION
> SELECT '1961-06-01', 0.7100 UNION
> SELECT '1961-09-01',-0.7000 UNION
> SELECT '1961-12-01', 0.0000 UNION
> SELECT '1962-03-01', 0.0000 UNION
> SELECT '1962-06-01', 0.0000 UNION
> SELECT '1962-09-01', 0.0000 UNION
> SELECT '1962-12-01', 0.0000 UNION
> SELECT '1963-03-01', 0.0000 UNION
> SELECT '1963-06-01', 0.0000 UNION
> SELECT '1963-09-01', 0.7100 UNION
> SELECT '1963-12-01', 0.0000 UNION
> SELECT '1964-03-01', 0.7000 UNION
> SELECT '1964-06-01', 0.7000 UNION
> SELECT '1964-09-01', 1.3900 UNION
> SELECT '1964-12-01', 0.6800 UNION
> SELECT '1965-03-01', 0.6800 UNION
> SELECT '1965-06-01', 1.3500 UNION
> SELECT '1965-09-01', 0.6700 UNION
> SELECT '1965-12-01', 1.3200
>
> I am trying to return the following results:
> Qtr CPI YrCPI AvgYrCPI
> --- -- -- ---
> 1-Jun-60 1.48
> 1-Sep-60 1.46
> 1-Dec-60 0.72
> 1-Mar-61 0.71 4.37
> 1-Jun-61 0.71 3.60
> 1-Sep-61 -0.70 1.44
> 1-Dec-61 0.00 0.72 2.53
> 1-Mar-62 0.00 0.01 1.44
> 1-Jun-62 0.00 -0.70 0.37
> 1-Sep-62 0.00 0.00 0.01
> 1-Dec-62 0.00 0.00 -0.17
> 1-Mar-63 0.00 0.00 -0.18
> 1-Jun-63 0.00 0.00 0.00
> 1-Sep-63 0.71 0.71 0.18
> 1-Dec-63 0.00 0.71 0.36
> 1-Mar-64 0.70 1.41 0.71
> 1-Jun-64 0.70 2.11 1.24
> 1-Sep-64 1.39 2.79 1.76
> 1-Dec-64 0.68 3.47 2.45
> 1-Mar-65 0.68 3.45 2.96
> 1-Jun-65 1.35 4.10 3.45
> 1-Sep-65 0.67 3.38 3.60
> 1-Dec-65 1.32 4.02 3.74
> Note, 4 data points are required to calculate a moving sum of CPI
> (YrCPI) and 4 calculate YrCPI figures are required calculate the
> annual average of YrCPI (AvgYrCPI), giving a staggered effect to the
> first 7 results
> This sad effort is about as far as I've got:
> SELECT I.Qtr, I.CPI, SUM(S.CPI) AS YrCPI
> FROM QtrInflation I
> JOIN (
> SELECT TOP 4 Qtr, CPI
> FROM QtrInflation
> ) S
> ON S.Qtr <= I.Qtr
> GROUP BY I.Qtr, I.CPI
> ORDER BY I.Qtr ASC
> Can anyone suggest how do achieve this result without having to resort
> to cursors?
> Thanks,
> Stephen|||Stephen,
Here is another approach that I think will work
for you:
-- alternate solution
create table Weights (
offset int,
weight decimal(3,2),
weightA decimal(3,2),
weightB decimal(3,2)
)
go
insert into Weights
select 6, 0, 0, 0.25 union all
select 5, 0, 0, 0.5 union all
select 4, 0, 0, 0.75 union all
select 3, 0, 1, 1.00 union all
select 2, 0, 1, 0.75 union all
select 1, 0, 1, 0.5 union all
select 0, 1, 1, 0.25
go
select
dateadd(month,3*Offset,Q1.Qtr) Qtr,
sum(Weight*Q1.CPI) CPI,
case when sum(WeightA) = 4 then sum(WeightA*Q1.CPI) else NULL end as YrCPI,
case when sum(WeightB) = 4 then sum(WeightB*Q1.CPI) else NULL end as MACPI
from QtrInflation Q1, Weights
group by dateadd(month,3*Offset,Q1.Qtr)
having sum(Weight) = 1
order by dateadd(month,3*Offset,Q1.Qtr)
-- Steve Kass
-- Drew University
-- Ref: 17F9A22A-8DDA-4812-A8CD-B68062BADFA1
Stephen Miller wrote:
> Hi,
> I am trying to add a staggered running total and average to a query
> returning quarterly CPI data. I need to add 4 quarterly data points
> together to calculate a moving 12-month sum (YrCPI), and then to
> complicate things, calculate a moving average of the 12-month figure
> (AvgYrCPI).
> Given the sample data:
> CREATE TABLE [dbo].[QtrInflation] (
> [Qtr] [smalldatetime] NOT NULL ,
> [CPI] [decimal](8, 4) NOT NULL
> ) ON [PRIMARY]
> GO
> INSERT INTO QtrInflation (Qtr, CPI)
> SELECT '1960-03-01', 0.7500 UNION
> SELECT '1960-06-01', 1.4800 UNION
> SELECT '1960-09-01', 1.4600 UNION
> SELECT '1960-12-01', 0.7200 UNION
> SELECT '1961-03-01', 0.7100 UNION
> SELECT '1961-06-01', 0.7100 UNION
> SELECT '1961-09-01',-0.7000 UNION
> SELECT '1961-12-01', 0.0000 UNION
> SELECT '1962-03-01', 0.0000 UNION
> SELECT '1962-06-01', 0.0000 UNION
> SELECT '1962-09-01', 0.0000 UNION
> SELECT '1962-12-01', 0.0000 UNION
> SELECT '1963-03-01', 0.0000 UNION
> SELECT '1963-06-01', 0.0000 UNION
> SELECT '1963-09-01', 0.7100 UNION
> SELECT '1963-12-01', 0.0000 UNION
> SELECT '1964-03-01', 0.7000 UNION
> SELECT '1964-06-01', 0.7000 UNION
> SELECT '1964-09-01', 1.3900 UNION
> SELECT '1964-12-01', 0.6800 UNION
> SELECT '1965-03-01', 0.6800 UNION
> SELECT '1965-06-01', 1.3500 UNION
> SELECT '1965-09-01', 0.6700 UNION
> SELECT '1965-12-01', 1.3200
>
> I am trying to return the following results:
> Qtr CPI YrCPI AvgYrCPI
> --- -- -- ---
> 1-Jun-60 1.48
> 1-Sep-60 1.46
> 1-Dec-60 0.72
> 1-Mar-61 0.71 4.37
> 1-Jun-61 0.71 3.60
> 1-Sep-61 -0.70 1.44
> 1-Dec-61 0.00 0.72 2.53
> 1-Mar-62 0.00 0.01 1.44
> 1-Jun-62 0.00 -0.70 0.37
> 1-Sep-62 0.00 0.00 0.01
> 1-Dec-62 0.00 0.00 -0.17
> 1-Mar-63 0.00 0.00 -0.18
> 1-Jun-63 0.00 0.00 0.00
> 1-Sep-63 0.71 0.71 0.18
> 1-Dec-63 0.00 0.71 0.36
> 1-Mar-64 0.70 1.41 0.71
> 1-Jun-64 0.70 2.11 1.24
> 1-Sep-64 1.39 2.79 1.76
> 1-Dec-64 0.68 3.47 2.45
> 1-Mar-65 0.68 3.45 2.96
> 1-Jun-65 1.35 4.10 3.45
> 1-Sep-65 0.67 3.38 3.60
> 1-Dec-65 1.32 4.02 3.74
> Note, 4 data points are required to calculate a moving sum of CPI
> (YrCPI) and 4 calculate YrCPI figures are required calculate the
> annual average of YrCPI (AvgYrCPI), giving a staggered effect to the
> first 7 results
> This sad effort is about as far as I've got:
> SELECT I.Qtr, I.CPI, SUM(S.CPI) AS YrCPI
> FROM QtrInflation I
> JOIN (
> SELECT TOP 4 Qtr, CPI
> FROM QtrInflation
> ) S
> ON S.Qtr <= I.Qtr
> GROUP BY I.Qtr, I.CPI
> ORDER BY I.Qtr ASC
> Can anyone suggest how do achieve this result without having to resort
> to cursors?
> Thanks,
> Stephen|||John & Steve
Thank you for two very interesting (and very different) responses. You
guys are gurus! Both return the results I'm looking for and now I'm
stuck picking which one's best ;)
Thanks again,
Stephen
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message news:<3f6d878f$0$10783$afc38c87@.news.easynet.co.uk>...
> Hi
> This will do it (I think!) but there may be a neater way!
> SELECT S.Qtr, S.CPI, D.YrCPI, E.AvgCPI
> FROM QtrInflation S LEFT JOIN
> ( SELECT Q.Qtr, SUM(A.CPI) AS YrCPI
> FROM QtrInflation Q LEFT JOIN ( SELECT Qtr, SUM(CPI) AS CPI
> FROM QtrInflation
> GROUP BY Qtr) A ON Q.Qtr >= A.Qtr AND DATEADD(YEAR,-1,Q.Qtr) < A.Qtr
> GROUP BY Q.Qtr
> HAVING COUNT(A.Qtr) = 4 ) D ON S.Qtr = D.Qtr
> LEFT JOIN
> ( SELECT R.Qtr, SUM(B.CPI)/4 AS AvgCPI
> FROM QtrInflation R LEFT JOIN ( SELECT Q.Qtr, SUM(A.CPI) AS CPI
> FROM QtrInflation Q LEFT JOIN ( SELECT Qtr, SUM(CPI) AS CPI
> FROM QtrInflation
> GROUP BY Qtr) A ON Q.Qtr >= A.Qtr AND DATEADD(YEAR,-1,Q.Qtr) < A.Qtr
> GROUP BY Q.Qtr
> HAVING COUNT(A.Qtr) = 4 ) B ON R.Qtr >= B.Qtr AND DATEADD(YEAR,-1,R.Qtr)
> < B.Qtr
> GROUP BY R.Qtr
> HAVING COUNT(B.Qtr) = 4 ) E ON S.Qtr = E.Qtr
> ORDER BY S.Qtr
> John
> "Stephen Miller" <jsausten@.hotmail.com> wrote in message
> news:cdb404de.0309210139.58ffad34@.posting.google.c om...
> > Hi,
> > I am trying to add a staggered running total and average to a query
> > returning quarterly CPI data. I need to add 4 quarterly data points
> > together to calculate a moving 12-month sum (YrCPI), and then to
> > complicate things, calculate a moving average of the 12-month figure
> > (AvgYrCPI).
> > Given the sample data:
> > CREATE TABLE [dbo].[QtrInflation] (
> > [Qtr] [smalldatetime] NOT NULL ,
> > [CPI] [decimal](8, 4) NOT NULL
> > ) ON [PRIMARY]
> > GO
> > INSERT INTO QtrInflation (Qtr, CPI)
> > SELECT '1960-03-01', 0.7500 UNION
> > SELECT '1960-06-01', 1.4800 UNION
> > SELECT '1960-09-01', 1.4600 UNION
> > SELECT '1960-12-01', 0.7200 UNION
> > SELECT '1961-03-01', 0.7100 UNION
> > SELECT '1961-06-01', 0.7100 UNION
> > SELECT '1961-09-01',-0.7000 UNION
> > SELECT '1961-12-01', 0.0000 UNION
> > SELECT '1962-03-01', 0.0000 UNION
> > SELECT '1962-06-01', 0.0000 UNION
> > SELECT '1962-09-01', 0.0000 UNION
> > SELECT '1962-12-01', 0.0000 UNION
> > SELECT '1963-03-01', 0.0000 UNION
> > SELECT '1963-06-01', 0.0000 UNION
> > SELECT '1963-09-01', 0.7100 UNION
> > SELECT '1963-12-01', 0.0000 UNION
> > SELECT '1964-03-01', 0.7000 UNION
> > SELECT '1964-06-01', 0.7000 UNION
> > SELECT '1964-09-01', 1.3900 UNION
> > SELECT '1964-12-01', 0.6800 UNION
> > SELECT '1965-03-01', 0.6800 UNION
> > SELECT '1965-06-01', 1.3500 UNION
> > SELECT '1965-09-01', 0.6700 UNION
> > SELECT '1965-12-01', 1.3200
> > I am trying to return the following results:
> > Qtr CPI YrCPI AvgYrCPI
> > --- -- -- ---
> > 1-Jun-60 1.48
> > 1-Sep-60 1.46
> > 1-Dec-60 0.72
> > 1-Mar-61 0.71 4.37
> > 1-Jun-61 0.71 3.60
> > 1-Sep-61 -0.70 1.44
> > 1-Dec-61 0.00 0.72 2.53
> > 1-Mar-62 0.00 0.01 1.44
> > 1-Jun-62 0.00 -0.70 0.37
> > 1-Sep-62 0.00 0.00 0.01
> > 1-Dec-62 0.00 0.00 -0.17
> > 1-Mar-63 0.00 0.00 -0.18
> > 1-Jun-63 0.00 0.00 0.00
> > 1-Sep-63 0.71 0.71 0.18
> > 1-Dec-63 0.00 0.71 0.36
> > 1-Mar-64 0.70 1.41 0.71
> > 1-Jun-64 0.70 2.11 1.24
> > 1-Sep-64 1.39 2.79 1.76
> > 1-Dec-64 0.68 3.47 2.45
> > 1-Mar-65 0.68 3.45 2.96
> > 1-Jun-65 1.35 4.10 3.45
> > 1-Sep-65 0.67 3.38 3.60
> > 1-Dec-65 1.32 4.02 3.74
> > Note, 4 data points are required to calculate a moving sum of CPI
> > (YrCPI) and 4 calculate YrCPI figures are required calculate the
> > annual average of YrCPI (AvgYrCPI), giving a staggered effect to the
> > first 7 results
> > This sad effort is about as far as I've got:
> > SELECT I.Qtr, I.CPI, SUM(S.CPI) AS YrCPI
> > FROM QtrInflation I
> > JOIN (
> > SELECT TOP 4 Qtr, CPI
> > FROM QtrInflation
> > ) S
> > ON S.Qtr <= I.Qtr
> > GROUP BY I.Qtr, I.CPI
> > ORDER BY I.Qtr ASC
> > Can anyone suggest how do achieve this result without having to resort
> > to cursors?
> > Thanks,
> > Stephen|||Hi Stephen
I would expect Steve's solution to work alot better than mine under
large loads!
John
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||>> I am trying to add a staggered running total and average to a query
returning quarterly CPI data. I need to add 4 quarterly data points
together to calculate a moving 12-month sum (YrCPI), and then to
complicate things, calculate a moving average of the 12-month figure
(AvgYrCPI). <<
I hope you mean to have a key on this table and some contraints
CREATE TABLE QtrInflation
(qtr SMALLDATETIME NOT NULL PRIMARY KEY
CHECK (MONTH(qtr) IN (03, 06, 09, 12)
AND (DAY(qtr) = 01)),
cpi DECIMAL(8,4) NOT NULL
CHECK(cpi >= 0.0000));
CREATE TABLE QtrReportRanges
(start_date SMALLDATETIME NOT NULL
CHECK (MONTH(qtr) IN (03, 06, 09, 12)
AND (DAY(qtr) = 01)),
end_date SMALLDATETIME NOT NULL
CHECK (MONTH(qtr) IN (03, 06, 09, 12)
AND (DAY(qtr) = 01)),
CHECK (start_date < end_date),
PRIMARY KEY (start_date < end_date));
INSERT INTO QtrReportRanges VALUES ('1960-03-01', '1960-12-01');
INSERT INTO QtrReportRanges VALUES ('1960-06-01', '1961-03-01');
etc,
now you can get the report easily.
SELECT R.start_date, R.end_date, SUM(cpi) AS yr_cpi, AVG(cpi) AS
avg_yr_cpi
FROM QtrInflation AS I, QtrReportRanges AS R
WHERE I.qtr BETWEEN R.start_date AND R.end_date
GROUP BY R.start_date, R.end_date;
No comments:
Post a Comment