BUEN DIA
estimados
Cita
tengo esta consulta para un crystal report y es para visualizar uan tabla las ventas vs presupuesto de cada grupo de artículos pero me arroja un error, agradezco si me pueden ayudar con este tema
gracias
SELECT TT0."ItmsGrpNam" "Grupo", SUM(TT0."Quantity") "Cantidad", SUM(TT0."TotalBruto") "Venta", SUM(TT0."GrssProfSC") "Ganancia",
CASE WHEN SUM(TT0."TotalBruto") = 0 THEN 0 ELSE (SUM(TT0."GrssProfSC")/SUM(TT0."TotalBruto"))*100 END "%Ganancia", TT1."PRESUPUESTO",
CASE WHEN IFNULL(TT1."PRESUPUESTO",0) = 0 THEN 0 ELSE (SUM(TT0."TotalBruto")/TT1."PRESUPUESTO")*100 END "%Cumplimiento"
FROM (
SELECT T2."ItmsGrpCod", T3."ItmsGrpNam", T1."unitMsr", T1."Quantity", T1."TotalSumSy" - (T1."TotalSumSy" * (T0."DiscSumSy" / (T0."DocTotalSy" + T0."WTSumSC" - T0."VatSumSy" - T0."RoundDifSy" - T0."TotalExpSC" + T0."DpmAmntSC" + T0."DiscSumSy"))) as "TotalBruto", T1."GrssProfSC"
FROM {?Schema@}.OINV T0 inner join {?Schema@}.INV1 T1 ON T0."DocEntry" = T1."DocEntry"
INNER JOIN {?Schema@}.OITM T2 ON T1."ItemCode" = T2."ItemCode"
INNER JOIN {?Schema@}.OITB T3 ON T2."ItmsGrpCod" = T3."ItmsGrpCod"
WHERE YEAR(T0."DocDate") = YEAR({?2Fecha}) AND T0."CANCELED" = 'N'
UNION ALL
SELECT T2."ItmsGrpCod", T3."ItmsGrpNam", T1."unitMsr", -T1."Quantity", -(T1."TotalSumSy" - (T1."TotalSumSy" * (T0."DiscSumSy" / (T0."DocTotalSy" + T0."WTSumSC" - T0."VatSumSy" - T0."RoundDifSy" - T0."TotalExpSC" + T0."DpmAmntSC" + T0."DiscSumSy")))), -T1."GrssProfSC"
FROM {?Schema@}.ORIN T0 inner join {?Schema@}.RIN1 T1 ON T0."DocEntry" = T1."DocEntry"
INNER JOIN {?Schema@}.OITM T2 ON T1."ItemCode" = T2."ItemCode"
INNER JOIN {?Schema@}.OITB T3 ON T2."ItmsGrpCod" = T3."ItmsGrpCod"
WHERE YEAR(T0."DocDate") = YEAR({?2Fecha}) AND T0."CANCELED" = 'N'
) TT0
LEFT JOIN
(
SELECT T3."AccntntCod",
T1."Line_ID" + 1 "MES",
SUM(T1."CredSTotal") "PRESUPUESTO"
FROM {?Schema@}.OBGT T0
INNER JOIN {?Schema@}.BGT1 T1 ON T0."AbsId" = T1."BudgId"
INNER JOIN {?Schema@}.OBGS T2 ON T0."Instance" = T2."AbsId"
INNER JOIN {?Schema@}.OACT T3 ON T0."AcctCode" = T3."AcctCode"
WHERE YEAR(T2."FinancYear") >=YEAR({?2Fecha})
AND T2."U_ID" = -2
GROUP BY T3."AccntntCod",
T1."Line_ID"
) TT1 ON TT0."ItmsGrpCod" = TT1."AccntntCod" AND TT1."MES" = MONTH({?2Fecha})
GROUP BY TT0."ItmsGrpCod", TT0."ItmsGrpNam", TT1."PRESUPUESTO"
ORDER BY TT0."ItmsGrpNam";
