Search code examples
sqlms-accesscrosstab

Add additional expression column to cross-tab - Is this possible?

I have a cross-tab that aggregates values by month and year as averages. The only component left is calculating the spread between the two agg. function columns. The user is able to pick the two nodes in which they want to see the spreads for. The user can also pick the year.

Here is the code:

PARAMETERS [Node 1] Long, [Node 2] Long, [Year] Long;
TRANSFORM Format(Avg([Monthly values].[total_lmp_on]),"Fixed") AS AVG_ON_LMP
SELECT [Monthly values].Month, [Monthly values].Year
FROM [Monthly values]
WHERE ((([Monthly values].pnode_id)=[Node 1] Or ([Monthly values].pnode_id)=[Node 2])AND [Monthly values].Year = [Year])
GROUP BY [Monthly values].Month, [Monthly values].Year
PIVOT [Monthly values].pnode_id;

The user is prompted Node 1, Node 2, and year. Lets say:

  1. Node 1: 12345
  2. Node 2: 6789
  3. Year: 2017

The following will show:

Month--------------Year-----------12345--------------6789

Jan-----------------2017------------10-----------------20

Feb-----------------2017------------15-----------------15

March----------------2017------------5-----------------0

April-----------------2017------------20-----------------10

The problem

How can I add a column that will give me the spread between the two chosen nodes? Which would look like:

Month--------------Year-----------12345--------------6789-------------Spread

Jan-----------------2017------------10-----------------20----------------(-10)

Feb-----------------2017------------15-----------------15-----------------(0)

March----------------2017------------5-----------------0------------------(5)

April-----------------2017------------20-----------------10---------------(10)

I am fairly certain this isn't possible, but would like to exhaust all resources. Or if there are any other options out there.

Solution

  • Consider conditional aggregation using AVG(IIF(...)) to find the difference of corresponding node averages. Do note: Spread column will appear to the right of pivoted columns.

    PARAMETERS [Node 1] Long, [Node 2] Long, [Year] Long;
TRANSFORM Format(AVG(m.[total_lmp_on]), "Fixed") AS AVG_ON_LMP
SELECT m.Month, m.Year,
       AVG(IIF(m.pnode_id = [Node1], m.[total_lmp_on], NULL)) -  
       AVG(IIF(m.pnode_id = [Node2], m.[total_lmp_on], NULL)) AS Spread
FROM [Monthly values] m
WHERE (m.pnode_id) IN ([Node 1], [Node 2]) AND (m.Year = [Year])
GROUP BY m.Month, m.Year
PIVOT m.pnode_id;