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 18Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.
”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.
Our community of experts have been thoroughly vetted for their expertise and industry experience.