Summary table with logic?

[DugzDMan]

I have a report with 2 tables, one for monthly data and one for the quarterly average. The quarterly average then is used to come up with a yearly average. What I would like to do is have one last table, call it a forecast table, that finds the highest of these 3 figures and addds 10% to it.

Does anyone know how this could be done?

Thanks!

 

[Sridharan]

Dugz,

Highest of what?????? You mean the values of the three tables. Possible to provide the some data and what you are looking at??? You know it always help peers to give a good solution.

Sri

 

[DugzDMan]

Yes, the highest of the three figures. Here's some of the data:

Monthly Data
           AA00
06/2003	17.14
05/2003	20.97
04/2003	21.29
03/2003	29.58

Quarterly Average
                AA00	
2003 / 2	59.40	19.80	
2003 / 1	63.82	21.27	
2002 / 4	53.83	17.94	
2002 / 3	48.85	16.28	
Yearly Average       18.83

Now, I would like to have another table with the highest of either the latest months usage (17.14), latest quarter average (19.80), or yearly average (18.83). in this case, it would be the usage for second quarter, 19.80. In some cases, it might be the yearly average or this months usage. I just need to be able to figure out the highest of the three and display them.

Thanks!

 

[PAULSGS]

This is the sort of SQL that will work out the value you want

SELECT MAX(AVERAGE) FROM
(
SELECT AVERAGE FROM MONTH WHERE MONTH = (SELECT MAX(MONTH) FROM MONTH)
UNION
SELECT MAX(QUARTERTRAVERAGE) FROM YEAR WHERE QUARTER = (SELECT MAX(QUARTER) FROM YEAR)
UNION
SELECT AVG(QUARTERTRAVERAGE) FROM YEAR
)

 

[DugzDMan]

Here is the only query I'm using right now:
SELECT USAGE_TYPE, DASD_Type, USAGE_MONTH, LPAR, USAGE
FROM SPECIAL_TABLES.CABS_CPU_DASD_USAGE_DATA

Right now, the monthly data table is based on the sql above. Then, the averages (quarterly and yearly) are based on the monthly data table.

Basically, I don't have multiple tables or views containing monthly, quarterly, yearly data ... maybe I could just create a view on the DB and use that. Hhhmmmm.

Thanks for the help, now I'm thinking about some other ways to do this.

Thanks!

 

[Sridharan]

Instead of doing this calculation at the Report Level, have a calendar table which I suppose will be there. Using that insert a another DP with the required query to find the Max() like Paul suggested and use it in the report.

Sri

 

[DugzDMan]

I went ahead and added a quarter column to the usage table. Now, I can grab everything with the union query you suggested, like this:

SELECT A.USAGE_TYPE, A.DASD_TYPE, LPAR, MAX(A.USAGE) * 1.1
FROM 
/*-- Gets Monthly Usage --*/
(SELECT USAGE_TYPE, DASD_Type, LPAR, USAGE
FROM SPECIAL_TABLES.CABS_CPU_DASD_USAGE_DATA
WHERE USAGE_MONTH IN 
	(SELECT MAX(USAGE_MONTH) 
	FROM SPECIAL_TABLES.CABS_CPU_DASD_USAGE_DATA)
UNION
/*-- Gets Quarterly Usage --*/
SELECT USAGE_TYPE, DASD_Type, LPAR, SUM(USAGE)/COUNT(USAGE) AS USAGE
FROM SPECIAL_TABLES.CABS_CPU_DASD_USAGE_DATA
WHERE USAGE_QUARTER IN 
	(SELECT DISTINCT USAGE_QUARTER 
	FROM SPECIAL_TABLES.CABS_CPU_DASD_USAGE_DATA
	WHERE USAGE_MONTH IN 
		(SELECT MAX(USAGE_MONTH) 
		FROM SPECIAL_TABLES.CABS_CPU_DASD_USAGE_DATA))
GROUP BY 1, 2, 3
UNION
/* -- Gets Yearly Usage -- */
SELECT USAGE_TYPE, DASD_Type, LPAR, SUM(USAGE)/COUNT(USAGE) AS USAGE
FROM SPECIAL_TABLES.CABS_CPU_DASD_USAGE_DATA
GROUP BY 1, 2, 3) A
GROUP BY 1, 2, 3

It might not be the prettiest query, but it gets the job done ;-)

Thanks for the help!!