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. 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                                   ??????
etc........

Russell Verdun wrote:
Show quoteHide quoteIt's not clear from your example what this "other date" would be, so
I'll use a different example.  Say I have a table containing
transactions, and each transaction consists of an account, a transaction
date, and an amount:

SELECT
    account,
    SUM(CASE WHEN DATEDIFF(m, transdate, GETDATE()) <= 12 THEN amount
ELSE 0) AS Last12Months,
    SUM(CASE WHEN DATEDIFF(m, transdate, GETDATE()) BETWEEN 13 AND 24
THEN amount ELSE 0) AS Prev12Months
FROM table
GROUP BY account

Is that enough to get you started?

Hi Russell,
I believe you could do something like this:

Select GroupType, Sum(CASE Period=200006 THEN SumRevAmt ELSE 0 END) as
Last12Mnths, Sum(CASE Period=199906 THEN SumRevAmt ELSE 0 END) as
Prior12Mnths
from MyQRY
Where Period = '200006' or Period = '199906'
Group by GroupType

I Dont know if thats the best way, but that is what first comes to
mind.

Paul T.

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.

SQLXML 4.0 "timestamp" updategram question
Identity keys in a One-to-Zero-or-Many relationship
Insert in every ID
SQL Newbie
Iteration in a stored procedure