Advice on SQL statement please

SQL Apprentice Question
have a query that generates the dataset below, based on the year being
filtered I get the sum of an amount Group By the type. What I would like to
do is use the exact qry using a differnet date, to generate a third column
called Prior12Mnths. How would I use my qry to accomplish this task.
I appreciate the help.

Here's my qry:


Select GroupType, Sum(SumRevAmt) as Last12Mnths
from MyQRY
Where Period = '200006'
Group by GroupType


Type Last12Mnths_200006 Prior12Mnths_199906


Airlines 1234.50 ??????
Concessions 73854.00 ??????


Celko Answers
>> I have a query that generates the dataset below, based on the year being filtered I get the sum of an amount Group By the type. What I would like to do is use the exact qry using a differnet date, to generate a third column


called Prior12Mnths. <<

Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is also helpful if the data elements have good
names.


CREATE TABLE Revenues -- guess at meaningful name
(grp_type INTEGER NOT NULL
REFERENCES GroupTypes(grp_type),
rev_amt DECIMAL(12,2) NOT NULL,
rev_date DATETIME NOT NULL PRIMARY KEY);


In the vague pseudo-code you posted, only some kind of vague date can
be a key


The best trick for this kind of summary is to build a reporting range
table


CREATE TABLE ReportRanges
(range_name CHAR() NOT NULL,
start_date DATETIME NOT NULL,
end_date DATETIME NOT NULL,
CHECK (start_date < end_date),
PRIMARY KEY (range_name, start_date));


INSERT INTO ReportRanges
VALUES ('2006-06: Prior12' , '2005-06-01', '2006-06-31' );
INSERT INTO ReportRanges
VALUES ('2006-06: ytd' , '2006-01-01', '2006-06-31' );


SELECT grp_type,
SUM (CASE WHEN R.range_name = '2006-06: ytd'
THEN rev_amt ELSE 0.00 END) AS ytd,
SUM (CASE WHEN R.range_name = '2006-06: Prior12'
THEN rev_amt ELSE 0.00 END) AS Prior12,
etc.
FROM Revenues
GROUP BY grp_type;


Adjust the table as needed.