AndreaNobili AndreaNobili - 23 days ago 5
SQL Question

How can I multiply an obtained value of this query for the output value of another query in SQL?

I am not so into SQL and I am working on this query on a Microsoft SQL Server

SELECT
tr.PolizzaID AS NumeroPolizza,
pfr.FondoID AS CodiceFondo,
'2016-09-30' AS dataRiferimentoPrezzo,
SUM(isnull(pre.impPre,0)+isnull(riv.impRiv,0)) AS quote,
SUM(isnull(pre.impPre,0)+isnull(riv.impRiv,0)) AS Controvalore
FROM
TR_PrestazioneTotale tr WITH(nolock)

............................................................
............................................................
DO SOME JOIN OPERATIONS
............................................................
............................................................

WHERE
tr.PolizzaID = 1234567890
GROUP BY
tr.PolizzaID ,
pfr.FondoID
HAVING SUM(isnull(pre.impPre,0)+isnull(riv.impRiv,0)) <>0


This query return always a single value.

As you can see one of the obtained field of the previous query is this one:

SUM(isnull(pre.impPre,0)+isnull(riv.impRiv,0)) AS Controvalore


that is a numeric value (something like 550058.8400).

My problem is that I have to multiply this SUM(isnull(pre.impPre,0)+isnull(riv.impRiv,0)) value for the output of this other query:

select Aliquota from TirAliquotaRamoI where DataElaborazione = '2016-09-30 00:00:00'


Also this query return always a single value.

So basically, the Controvalore output field of the first query have to be the result of SUM(isnull(pre.impPre,0)+isnull(riv.impRiv,0)) multiplied by the Aliquota output value of the second query.

How can I do it?

Tnx

Answer

Since both return a single result, you can probably.. maybe get away with a subquery in your SELECT clause. I'm guessing by that aliquota name that this might work for your situation, but it really depends on the data underneath. At any rate, this is a good place to start.

SELECT
    tr.PolizzaID                                   AS NumeroPolizza,
    pfr.FondoID                                    AS CodiceFondo,
    '2016-09-30'                                   AS dataRiferimentoPrezzo,
    SUM(isnull(pre.impPre,0)+isnull(riv.impRiv,0)) AS quote,
    SUM(isnull(pre.impPre,0)+isnull(riv.impRiv,0)) * (select Aliquota from TirAliquotaRamoI where DataElaborazione = '2016-09-30 00:00:00') AS Controvalore
FROM
    TR_PrestazioneTotale tr WITH(nolock)

    ............................................................
    ............................................................
    DO SOME JOIN OPERATIONS
    ............................................................
    ............................................................

WHERE
    tr.PolizzaID = 1234567890
GROUP BY
tr.PolizzaID ,
pfr.FondoID
HAVING SUM(isnull(pre.impPre,0)+isnull(riv.impRiv,0)) <>0