Код:
SELECT SACARDCLASS.TREE SACARDCLASS_TREE,
SACARDCLASS.NAME SACARDCLASS_NAME,
SMCARD.ARTICLE SMCARD_ARTICLE,
SMCARD.NAME SMCARD_NAME,
SUM(V1.SALENOVAT) as "Реализация без налогов",
SUM(V1.PRIMECOSTNOVAT) as "Себестоимость без НДС",
SUM(V1.SALENOVAT) - SUM(V1.PRIMECOSTNOVAT) as "Доход",
ROUND(
((SUM(V1.SALENOVAT) - SUM(V1.PRIMECOSTNOVAT))*100/SUM_SALENOVAT),
3
) as "Доля в общем доходе, %",
ROUND(
((SUM(V1.SALENOVAT) - SUM(V1.PRIMECOSTNOVAT))*100/SUM(V1.SALENOVAT)),
3
) as "Маржа, %",
SUM_SALENOVAT as "Общий доход",
SUM(QUANTITY) as "Количество"
FROM (
SELECT ARTICLE,
SALELOCATIONFROM,
SALELOCATIONTO,
SALEDATE,
SALEID,
SALETYPE,
SALEOP,
SALEQ,
SALESUM,
SALENOVAT,
SALENOTAX,
SALESUMCUR,
QUANTITY,
PRIMECOST,
PRIMECOSTNOVAT,
INCOMEID,
INCOMETYPE,
INCOMEQ,
INCOMESUM,
INCOMENOVAT,
INCOMEVATRATE,
INCOMEDATE,
SUM(SALENOVAT) OVER () -
SUM(PRIMECOSTNOVAT) OVER () SUM_SALENOVAT
FROM FVMAPREP
WHERE SALETYPE IN('CS')
AND SALEDATE >= to_date('01.06.2008', 'DD.MM.YYYY')
AND SALEDATE <= to_date('06.06.2008', 'DD.MM.YYYY')
AND SALELOCATIONFROM IN(2)
AND ARTICLE IN(
SELECT SMCARD.ARTICLE
FROM SMCARD, SACARDCLASS
WHERE SMCARD.IDCLASS = SACARDCLASS.ID
AND SACARDCLASS.TREE LIKE '1.%'
)
AND FORCEDMAPPING = 0
AND SALELOCATIONFROM IS NOT NULL
AND SALEOP IN(1)
UNION ALL
SELECT ARTICLE,
SALELOCATIONFROM,
SALELOCATIONTO,
SALEDATE,
SALEID,
SALETYPE,
SALEOP,
SALEQ,
-SALESUM,
-SALENOVAT,
SALENOTAX,
SALESUMCUR,
-QUANTITY,
-PRIMECOST,
-PRIMECOSTNOVAT,
INCOMEID,
INCOMETYPE,
INCOMEQ,
INCOMESUM,
INCOMENOVAT,
INCOMEVATRATE,
INCOMEDATE,
- SUM(SALENOVAT) OVER () -
SUM(PRIMECOSTNOVAT) OVER () SUM_SALENOVAT
FROM FVMAPREP
WHERE SALETYPE IN('CR')
AND SALEDATE >= to_date('01.06.2008', 'DD.MM.YYYY')
AND SALEDATE <= to_date('06.06.2008', 'DD.MM.YYYY')
AND SALELOCATIONTO IN(2)
AND ARTICLE IN(
SELECT SMCARD.ARTICLE
FROM SMCARD, SACARDCLASS
WHERE SMCARD.IDCLASS = SACARDCLASS.ID
AND SACARDCLASS.TREE LIKE '1.%'
)
AND FORCEDMAPPING = 0
AND SALELOCATIONTO IS NOT NULL
AND SALEOP IN(3)
) V1,
SACARDCLASS,
SMCARD
WHERE SACARDCLASS.ID = SMCARD.IDCLASS
AND SMCARD.ARTICLE = V1.ARTICLE
GROUP BY SACARDCLASS.TREE,
SACARDCLASS.NAME,
SMCARD.ARTICLE,
SMCARD.NAME,
SUM_SALENOVAT