Sales Representative wise Performance Report

[former_member188586]

Hi This Query Brings For each sales Representative  by date range, you will see Quote value, number of quotes, Sales value, number of sales, AR value, number of invoices and number of Credits.

/*SELECT FROM [dbo].[oinv] T11*/

DECLARE @myStartDate AS datetime

/* WHERE */

SET @myStartDate = /* T11.DocDate */ '[%0]'

/*SELECT FROM [dbo].[oinv] T12*/

DECLARE @myEndDate AS datetime

/* WHERE */

SET @myEndDate = /* T12.DocDate */ '[%1]'

/*Testing Data

DECLARE @myStartDate DateTime

DECLARE @myEndDate DateTime

SET @myStartDate = '20120101'

SET @myEndDate = '20121231'

*/

DECLARE @myTable TABLE

(

SP NVARCHAR(30)

, DocNum INT

, DocDate DATETIME

, LineTotal MONEY

, DocType NVARCHAR(20)

);

INSERT INTO @myTable

SELECT

T2.SlpName SP

, T0.DocNum

, T0.DocDate

, SUM(T1.LineTotal) LineTotal

, 'Quote' DocType

FROM OQUT T0

INNER JOIN QUT1 T1 ON T0.DocEntry = T1.DocEntry

INNER JOIN OSLP T2 ON T0.SlpCode = T2.SlpCode

WHERE T0.DocDate BETWEEN @myStartDate AND @myEndDate

AND T0.CANCELED = 'N'

GROUP BY

T2.SlpName

, T0.DocNum

, T0.DocDate

UNION ALL

SELECT

T2.SlpName SP

, T0.DocNum

, T0.DocDate

, SUM(T1.LineTotal) LineTotal

, 'SO' DocType

FROM ORDR T0

INNER JOIN RDR1 T1 ON T0.DocEntry = T1.DocEntry

INNER JOIN OSLP T2 ON T0.SlpCode = T2.SlpCode

WHERE T0.DocDate BETWEEN @myStartDate AND @myEndDate

AND T0.CANCELED = 'N'

GROUP BY

T2.SlpName

, T0.DocNum

, T0.DocDate

UNION ALL

SELECT

T2.SlpName SP

, T0.DocNum

, T0.DocDate

, SUM(T1.LineTotal) LineTotal

, 'ARInvoice' DocType

FROM OINV T0

INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry

INNER JOIN OSLP T2 ON T0.SlpCode = T2.SlpCode

WHERE T0.DocDate BETWEEN @myStartDate AND @myEndDate

GROUP BY

T2.SlpName

, T0.DocNum

, T0.DocDate

UNION ALL

SELECT

T2.SlpName SP

, T0.DocNum

, T0.DocDate

, SUM(T1.LineTotal) LineTotal

, 'ARCreditMemo' DocType

FROM ORIN T0

INNER JOIN RIN1 T1 ON T0.DocEntry = T1.DocEntry

INNER JOIN OSLP T2 ON T0.SlpCode = T2.SlpCode

WHERE T0.DocDate BETWEEN @myStartDate AND @myEndDate

GROUP BY

T2.SlpName

, T0.DocNum

, T0.DocDate

SELECT

T0.SlpName

, '$' + ISNULL(CONVERT(VARCHAR,CAST(

T1.LineTotal AS MONEY),1),'0') Quotes
, T1.DocCount QuotationCount
, '$' + ISNULL(CONVERT(VARCHAR,CAST(T3.LineTotal AS MONEY),1),'0') SO
, T3.DocCount [SO Count]
, '$' + ISNULL(CONVERT(VARCHAR,CAST(T4.LineTotal AS MONEY),1),'0') ARInvoices
, T4.DocCount [Inv Count]
, '$' + ISNULL(CONVERT(VARCHAR,CAST(T5.LineTotal AS MONEY),1),'0') ARCreditMemos
, T5.DocCount [Credit Count]
FROM OSLP T0
LEFT JOIN
(SELECT
SP
, SUM(LineTotal) LineTotal
, COUNT(DocNum) DocCount
FROM @myTable
WHERE DocType = 'Quote'
GROUP BY SP) T1 ON T0.SlpName = T1.SP
LEFT JOIN
(SELECT
SP
, SUM(LineTotal) LineTotal
, COUNT(DocNum) DocCount
FROM @myTable
WHERE DocType = 'SO'
GROUP BY SP) T3 ON T0.SlpName = T3.SP
LEFT JOIN
(SELECT
SP
, SUM(LineTotal) LineTotal
, COUNT(DocNum) DocCount
FROM @myTable
WHERE DocType = 'ARInvoice'
GROUP BY SP) T4 ON T0.SlpName = T4.SP
LEFT JOIN
(SELECT
SP
, SUM(LineTotal) LineTotal
, COUNT(DocNum) DocCount
FROM @myTable
WHERE DocType = 'ARCreditMemo'
GROUP BY SP) T5 ON T0.SlpName = T5.SP
WHERE T1.LineTotal > 0
OR T3.LineTotal > 0
OR T4.LineTotal > 0
OR T5.LineTotal > 0
ORDER BY T0.SlpName