error group by

[Former Member]

hello everyone,

i've a syntax problem in this query, i can't be able to find the error. i want to calculate subtotal with function group by ROLL UP, but THE FUNCTION GROUP BY doesn't work. Can anybody help me? thank you

SELECT T2.CardName, T1.CardName, T0.U_DtInMan, T0.U_DtRiMan, T0.U_ImpMan, 13 - month(T0.[U_DtInMan]) AS Mesi_manutenzione, (T0.[U_ImpMan] * (13 - month(T0.[U_DtInMan])) /12) AS importo_annuale_manutenzione FROM OINV T0 INNER JOIN OCRD T1 ON T0.CardCode = T1.CardCode INNER JOIN OCRD T2 ON T0.FatherCard = T2.CardCode WHERE T0.U_ImpMan <> 0 and T0.U_DtRiMan >=[%0] and T0.U_DtRiMan <=[%1] GROUP BY T2.CardName WITH ROLLUP

[former_member583013 · Accepted solution]

I would like to get some idea about what the calculation is for and what you are trying to do. I might probably be able to help you after understand your requirement.

Suda

[Former Member · Accepted solution]

ok. .thank you SUDA for your availability… I have consolidation business partner that he has associate some customers. I invoice to the consolidation partner and interests to calculate the maintenance quota to me that this must pay for all the customers.

this drift from the sum of the totals calculates with the following formula: ((T0. [U_ImpMan] * (13 - month (T0. [U_DtInMan])) /12))

where U_ImpMan is a field customer

where DtInMan is a field customer and indicates the beginning date maintenance

the idea is POSSESSION a report therefore:

consolidationbp client total month total all

Bp1 rudy 100

Bp1 lorenzo 50 150

[Former Member]

it is possible to import with dtw oltra the terms of payment of business the partner also the number of installments and its percentages. thanks

[Former Member]

i dont't find a solution

[former_member583013]

Hi,

It seems you are missing the quote around '[%0]' and '[%1]'

CHANGE YOUR SQL WITH QUOTE LIKE

SELECT T2.CardName, T1.CardName, T0.U_DtInMan, T0.U_DtRiMan, T0.U_ImpMan, 13 - month(T0.[U_DtInMan]) AS Mesi_manutenzione, (T0.[U_ImpMan] * (13 - month(T0.[U_DtInMan])) /12) AS importo_annuale_manutenzione FROM OINV T0 INNER JOIN OCRD T1 ON T0.CardCode = T1.CardCode INNER JOIN OCRD T2 ON T0.FatherCard = T2.CardCode WHERE T0.U_ImpMan <> 0 and T0.U_DtRiMan >='[%0]' and T0.U_DtRiMan <='[%1]' GROUP BY T2.CardName WITH ROLLUP

SUDA

[Former Member]

hi Suda,

thank you very much for your availability, i changed my query in this mode:

it seems to work but the subtotal doesn't calculate for groups T2.CardName, but it continues to calculate them in increasing way. what do you advise me to do? thank you

SELECT T2.CardName, T1.CardName, T0.U_DtInMan, T0.U_DtRiMan, T0.U_ImpMan, 13 - month(T0.[U_DtInMan]) as mesi_fatturazione, (T0.[U_ImpMan] * (13 - month(T0.[U_DtInMan])) /12), Count(T2.cardname), Count(T1.CardName), Count(T0.U_DtInMan), Count(T0.U_DtRiMan), SUM((T0.[U_ImpMan] * (13 - month(T0.[U_DtInMan])) /12)) AS TOTALE_MANUTENZIONI FROM OINV T0 INNER JOIN OCRD T1 ON T0.CardCode = T1.CardCode INNER JOIN OCRD T2 ON T0.FatherCard = T2.CardCode WHERE T0.U_ImpMan <> 0 and T0.U_DtRiMan >=[%0] and T0.U_DtRiMan <=[%1] GROUP BY T2.CardName, T1.CardName, T0.U_DtInMan, T0.U_DtRiMan, T0.U_ImpMan, (T0.[U_ImpMan] * (13 - month(T0.[U_DtInMan])) /12) WITH ROLLUP