Advice on SQL statement please.

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:
> 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 t
o
> 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......
>
>
It'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?|||Tracy McKibben wrote:
> Russell Verdun wrote:
> It'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?
>
Sorry, those CASE statements are missing ENDs...|||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.|||>> 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.