asked on
SELECT
SUM(VF.TOTALPURCHASES) PURCHASEVALUE,
SUM(VF.PURCHASERETURNS) PURCHASERETURNS,
SUM(VF.TOTALPURCHASES) + SUM(VF.PURCHASERETURNS) AS NETPURCHASES,
VF.DIMENSION_VALUE,
VF.DIMENSION_DESCRIPTION,
VF.DIMENSION_TYPE,
VF.DAC,
VF.DAC2
FROM
(
SELECT
CONVERT(INT,SUM(TOTALPURCHASES)) AS TOTALPURCHASES,
CONVERT(INT,SUM(PURCHASERETURNS)) AS PURCHASERETURNS,
DIMENSION_VALUE,
DIMENSION_DESCRIPTION,
DIMENSION_TYPE,
DAC,
DAC2
FROM
(SELECT
vs.RECID,
vs.PURCHID,
vs.ITEMID,
vs.LINENUM,
vs.DEFAULTDIMENSION,
DA.NAME AS DIMENSION_TYPE,
DACT.DataAreaId AS DAC,
DAVT.DataAreaId AS DAC2,
DAVSI.DISPLAYVALUE DIMENSION_VALUE,
CASE
WHEN DA.NAME='Customer' THEN DACT.NAME
WHEN DA.NAME='Vendor' THEN DAVT.NAME
WHEN DA.NAME='BusinessUnit' THEN DABT.NAME
ELSE DFT.DESCRIPTION
END AS DIMENSION_DESCRIPTION,
vs.LINEAMOUNTMST AS TOTALPURCHASES,
0 AS PURCHASERETURNS,
vs.QTY
FROM
DBO.vendinvoicetrans Vs
LEFT JOIN DimensionAttributeValueSetItem DAVSI ON vs.DEFAULTDIMENSION=DAVSI.DIMENSIONATTRIBUTEVALUESET
LEFT JOIN DIMENSIONATTRIBUTEVALUE DAV ON DAVSI.DIMENSIONATTRIBUTEVALUE=DAV.RECID
LEFT JOIN DimensionAttribute DA ON DAV.DIMENSIONATTRIBUTE=DA.RECID
LEFT JOIN DimensionAttributeDirCategory DADC ON DADC.DIMENSIONATTRIBUTE=DA.RECID
LEFT JOIN DimensionFinancialTag DFT ON DFT.FINANCIALTAGCATEGORY=DADC.DIRCATEGORY AND DFT.RECID=DAV.ENTITYINSTANCE
LEFT JOIN DIMATTRIBUTECUSTTABLE DACT ON DACT.Value = DAVSI.DisplayValue AND DACT.RecId = DAV.EntityInstance AND DACT.DataAreaId = 'abc'
LEFT JOIN DIMATTRIBUTEVENDTABLE DAVT ON DAVT.Value = DAVSI.DisplayValue AND DAVT.RecId = DAV.EntityInstance AND DAVT.DataAreaId = 'abc'
LEFT JOIN DIMATTRIBUTEOMBUSINESSUNIT DABT ON DABT.Value = DAVSI.DisplayValue AND DABT.RecId = DAV.EntityInstance
WHERE
INVOICEDATE BETWEEN '2017-01-01' AND '2018-01-01'
AND vs.QTY>0
)V
--WHERE DIMENSION_VALUE Like '%NDIS%'
GROUP BY DIMENSION_VALUE,DIMENSION_DESCRIPTION,DIMENSION_TYPE
--ORDER BY DIMENSION_DESCRIPTION
UNION ALL
SELECT
CONVERT(INT,SUM(TOTALPURCHASES)) AS PURCHASEVALUE,
CONVERT(INT,SUM(PURCHASERETURNS)) AS PURCHASERETURNS,
--CONVERT(VARCHAR, CAST(SUM(TOTALPURCHASES) AS MONEY), 1) AS PURCHASEVALUE_C,
--CONVERT(INT,SUM(QTY)) AS SALESQTY,
DIMENSION_VALUE,
DIMENSION_DESCRIPTION,
DIMENSION_TYPE,
DAC,
DAC2
FROM
(SELECT
vs.RECID,
vs.PURCHID,
vs.ITEMID,
vs.LINENUM,
vs.DEFAULTDIMENSION,
DA.NAME AS DIMENSION_TYPE,
DACT.DataAreaId AS DAC,
DAVT.DataAreaId AS DAC2,
DAVSI.DISPLAYVALUE DIMENSION_VALUE,
CASE
WHEN DA.NAME='Customer' THEN DACT.NAME
WHEN DA.NAME='Vendor' THEN DAVT.NAME
WHEN DA.NAME='BusinessUnit' THEN DABT.NAME
ELSE DFT.DESCRIPTION
END AS DIMENSION_DESCRIPTION,
0 AS TOTALPURCHASES,
vs.LINEAMOUNTMST AS PURCHASERETURNS,
vs.QTY
FROM
VENDINVOICETRANS Vs
LEFT JOIN DimensionAttributeValueSetItem DAVSI ON vs.DEFAULTDIMENSION=DAVSI.DIMENSIONATTRIBUTEVALUESET
LEFT JOIN DIMENSIONATTRIBUTEVALUE DAV ON DAVSI.DIMENSIONATTRIBUTEVALUE=DAV.RECID
LEFT JOIN DimensionAttribute DA ON DAV.DIMENSIONATTRIBUTE=DA.RECID
LEFT JOIN DimensionAttributeDirCategory DADC ON DADC.DIMENSIONATTRIBUTE=DA.RECID
LEFT JOIN DimensionFinancialTag DFT ON DFT.FINANCIALTAGCATEGORY=DADC.DIRCATEGORY AND DFT.RECID=DAV.ENTITYINSTANCE
LEFT JOIN DIMATTRIBUTECUSTTABLE DACT ON DACT.Value = DAVSI.DisplayValue AND DACT.RecId = DAV.EntityInstance AND DACT.DataAreaId = 'EF'
LEFT JOIN DIMATTRIBUTEVENDTABLE DAVT ON DAVT.Value = DAVSI.DisplayValue AND DAVT.RecId = DAV.EntityInstance AND DAVT.DataAreaId = 'EF'
LEFT JOIN DIMATTRIBUTEOMBUSINESSUNIT DABT ON DABT.Value = DAVSI.DisplayValue AND DABT.RecId = DAV.EntityInstance
WHERE
INVOICEDATE BETWEEN '2017-01-01' AND '2019-01-01'
AND vs.QTY<0
)V
--WHERE DIMENSION_VALUE Like '%NDIS%'
GROUP BY DIMENSION_VALUE,DIMENSION_DESCRIPTION,DIMENSION_TYPE
)VF
GROUP BY VF.DIMENSION_VALUE,VF.DIMENSION_DESCRIPTION,VF.DIMENSION_TYPE, VF.DAC, VF.DAC2
ORDER BY VF.DIMENSION_DESCRIPTION
Msg 8120, Level 16, State 1, Line 18