This is my StoredProcedure that I want to convert into query and RDP but not understanding where to start.
SELECT
SUM(VF.TOTALSALES) SALESVALUE,
SUM(VF.SALESRETURNS) SALESRETURNS,
SUM(VF.TOTALSALES) + SUM(VF.SALESRETURNS) NETSALES,
VF.DIMENSION_VALUE,
VF.DIMENSION_DESCRIPTION,
VF.DIMENSION_TYPE
FROM
(
SELECT
CONVERT(INT,SUM(TOTALSALES)) AS TOTALSALES,
CONVERT(INT,SUM(SALESRETURNS)) AS SALESRETURNS,
DIMENSION_VALUE,
DIMENSION_DESCRIPTION,
DIMENSION_TYPE
FROM
(SELECT
CS.RECID,
CS.SALESID,
CS.ITEMID,
CS.LINENUM,
CS.DEFAULTDIMENSION,
DA.NAME AS DIMENSION_TYPE,
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,
CS.LINEAMOUNTMST AS TOTALSALES,
0 AS SALESRETURNS,
CS.QTY
FROM
DBO.CUSTINVOICETRANS CS
LEFT JOIN DimensionAttributeValueSetItem DAVSI ON CS.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 @FROM_DATE AND @TO_DATE
AND CS.QTY>0
)V
WHERE DIMENSION_TYPE='SalesMans'
GROUP BY DIMENSION_VALUE,DIMENSION_DESCRIPTION,DIMENSION_TYPE
--ORDER BY DIMENSION_DESCRIPTION
UNION ALL
SELECT
CONVERT(INT,SUM(TOTALSALES)) AS SALESVALUE,
CONVERT(INT,SUM(SALESRETURNS)) AS SALESRETURNS,
--CONVERT(VARCHAR, CAST(SUM(TOTALSALES) AS MONEY), 1) AS SALESVALUE_C,
--CONVERT(INT,SUM(QTY)) AS SALESQTY,
DIMENSION_VALUE,
DIMENSION_DESCRIPTION,
DIMENSION_TYPE
FROM
(SELECT
CS.RECID,
CS.SALESID,
CS.ITEMID,
CS.LINENUM,
CS.DEFAULTDIMENSION,
DA.NAME AS DIMENSION_TYPE,
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 TOTALSALES,
CS.LINEAMOUNTMST AS SALESRETURNS,
CS.QTY
FROM
CUSTINVOICETRANS CS
LEFT JOIN DimensionAttributeValueSetItem DAVSI ON CS.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 @FROM_DATE AND @TO_DATE
AND CS.QTY<0
)V
WHERE DIMENSION_TYPE='SalesMans'
GROUP BY DIMENSION_VALUE,DIMENSION_DESCRIPTION,DIMENSION_TYPE
)VF
GROUP BY VF.DIMENSION_VALUE,VF.DIMENSION_DESCRIPTION,VF.DIMENSION_TYPE
ORDER BY VF.DIMENSION_DESCRIPTION
