Multiply with different variables in query.

[svjensen]

The title might not be exactly to the point, so let try to explain in detail my problem.

Let us say that I extract and sum data using the following query:

TRANSFORM Sum(tblTest.Value) AS SumOfValue
SELECT tblTest.ExpenseType
FROM tblTest
GROUP BY tblTest.ExpenseType
PIVOT tblTest.Year;

The data is all stored in price level 2010, and I now need to calculate the sum in price level 2011.
I would then have a small function which defines the multiplicator, and apply it like so:

TRANSFORM Sum(tblTest.Value)*GetPriceLevel() AS SumOfValue
SELECT tblTest.ExpenseType
FROM tblTest
GROUP BY tblTest.ExpenseType
PIVOT tblTest.Year;

And this brings us to my problem.
tblTest.ExpenseType can be one of two values (i.e. 'Salary' and 'Expenses'), and each of them has its own multiplicator in order to calculate the price level.
Currently I add the multiplicator in two different queries - and get two recordsets:

TRANSFORM Sum(tblTest.Value)*GetPriceLevelSalary() AS SumOfValue
SELECT tblTest.ExpenseType
FROM tblTest
WHERE tblTest.ExpenseType = 'Salary'
GROUP BY tblTest.ExpenseType
PIVOT tblTest.Year;

TRANSFORM Sum(tblTest.Value)*GetPriceLevelExpenses() AS SumOfValue
SELECT tblTest.ExpenseType
FROM tblTest
WHERE tblTest.ExpenseType = 'Expenses'
GROUP BY tblTest.ExpenseType
PIVOT tblTest.Year;

The question would then be: Can this be done in one query? Giving me just one recordset.

 

[namliam]

How about changing your function to accept the expensetype??

TRANSFORM Sum(tblTest.Value)*GetPriceLevelExpenses(tblTest.ExpenseType) AS SumOfValue
SELECT tblTest.ExpenseType
FROM tblTest
GROUP BY tblTest.ExpenseType
PIVOT tblTest.Year;

Then in your function return different values depending on what type it is and your left with one recordset.

 

[svjensen]

Interesting thought. I will give it a go.

 

[svjensen]

It works like a charm

One follow up though.
As far as I have been able to get it to work, I need to group by the field which I use to call my function (in the example 'tblTest.ExpenseType').
This results in one row for each ExpenseType, and I do not necessarily want that.

Let us say that I want to group by department, then I would use the following query:

TRANSFORM Sum(tblTest.Value)*GetPriceLevelExpenses(tblTest.ExpenseType) AS SumOfValue
SELECT tblTest.Department
FROM tblTest
GROUP BY tblTest.Department, tblTest.ExpenseType
PIVOT tblTest.Year;

If there are two different ExpenseTypes, then I get two rows.
Is there some way to have it only return one row for each department?

 

[namliam]

You should be able to simply remove the expensetype from the Group by clause...

 

[svjensen]

That was my first approach as well, but it does not work. Or I might be missing something

I get the following error message (translated from Danish, so it might not be completely accurate):

You have attempted to run a query, which does not include the expression "Sum(tbl1cDB.Value)*GetRisInedx(tblUdgiftsTyper.PNGruppe)" as a part of an aggregate function.


The actual query is:

TRANSFORM (Sum(tbl1cDB.Value)*GetPrisIndex(tblUdgiftsTyper.PNGruppe)) AS SumOfValue
SELECT tblUdgiftsTyper.Gruppe2
FROM tblOpslagsListeType INNER JOIN (tblOpslagsListeStatus INNER JOIN (tblUdgiftsTyper INNER JOIN (((tblBCListe INNER JOIN (tblHMGenstand INNER JOIN (tblHMGruppe INNER JOIN (tbl1cFaner INNER JOIN tbl1cDB ON tbl1cFaner.uid = tbl1cDB.FaneId) ON tblHMGruppe.uid = tbl1cFaner.HMGruppeId) ON tblHMGenstand.uid = tbl1cFaner.HMGenstandId) ON tblBCListe.uid = tbl1cFaner.BCId) INNER JOIN tblBCStatus ON tblBCListe.uid = tblBCStatus.BCId) INNER JOIN tblBCType ON tblBCListe.uid = tblBCType.BCId) ON tblUdgiftsTyper.uid = tbl1cDB.UdgiftstypeId) ON tblOpslagsListeStatus.uid = tblBCStatus.StatusId) ON tblOpslagsListeType.uid = tblBCType.TypeId
WHERE (((tblBCStatus.DatoSat)<=GetVersionDate()) AND ((tblBCStatus.DatoUdloeb)>GetVersionDate()) AND ((tblBCType.DatoSat)<=GetVersionDate()) AND ((tblBCType.DatoUdloeb)>GetVersionDate()) AND ((tbl1cFaner.DatoSat)<=GetVersionDate()) AND ((tbl1cFaner.DatoUdloeb)>GetVersionDate()) AND ((tbl1cDB.DatoSat)<=GetVersionDate()) AND ((tbl1cDB.DatoUdloeb)>GetVersionDate()) AND ((tblBCStatus.StatusId) In (1,2)) AND ((tblBCType.TypeId) In (5,6,7,8,9)))
GROUP BY tblUdgiftsTyper.Gruppe2
PIVOT tbl1cDB.Year;

 

[namliam]

Then your probably have to make a query first to do the GetPrisIndex stuff, then do the crostabb on that query instead of the tables directly.