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
5  5290 
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" <js******@hotmail.com> wrote in message
news:cd**************************@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" <jb************@hotmail.com> wrote in message news:<3f***********************@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" <js******@hotmail.com> wrote in message
news:cd**************************@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;
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: nickdu |
last post by:
I'm trying to isolate "applications" into their own application domain within
a single process. I've quoted applications because it's a logical
representation of an application. Basically it...
|
by: Alex |
last post by:
Acc 97
Hi, I am in need of some help here....
I have a query which is based upon time, where i need to plot the
hours
a job has been working.. therefore, oN & off in a union query.
...
|
by: CSDunn |
last post by:
Hello,
I have 14 fields on a report that hold integer values. The field names
use the following naming convention: T1Number, T2Number ....T14Number.
I need to get a 'sub total' of all fields as...
|
by: Peter Bailey |
last post by:
I have a query that creates a graph of bookings from the course start date
looking back 20 weeks based on a running sum.
I also have a query that counts the number of bookings before that 20 week...
|
by: Gandalf186 |
last post by:
I need to create a query that produces running totals for every group within my table for example i wish to see: -
Group A
1
5
9
15
Group B
|
by: mochatrpl |
last post by:
I am looking for a way to make a query / report display the running
average for total dollars.
I have already set up a query to provide totals dollars per day from
which a report graphly shows...
|
by: Mark |
last post by:
Hi, I'm new to python and looking for a better idiom to use for the
manner I have been organising my python scripts. I've googled all over
the place about this but found absolutely nothing.
I'm...
|
by: Richard Hollenbeck |
last post by:
I hope this isn't too confusing.
The following query runs pretty fast by itself, but when I want to use it in
a report (pasted below the query), it takes at least fifteen seconds to run!
Then I...
|
by: Bill Cunningham |
last post by:
I have a row of values like such, placed in a text file by fprintf.
10.50
10.25
10.00
10.75
11.00
What I want to do to the above colum is add a new column right beside it
which is a total...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
| |
