Sales Summary by Sales Employee (current month)

[pcorp01]

My desired goal is to have a query like this:

Sales Employee | Total Sales (Invoiced) | Total Sales (Open Orders)

John Doe......................55,955.75 ......................... 3,975.01

And the date range will be beginning of current month to current day/present. So if Sept, all orders that were invoiced in Sept. And Open orders placed in Sept.)

If we can include prior year's Sept Total Sales data in the query, that would be awesome!

[MD1]

PLEASE TRY THIS

SELECT
DISTINCT T1."SlpCode", T1."SlpName" AS "Sales Employee",
(SELECT
SUM(l1."DocTotal")
FROM OINV L1
WHERE T1."SlpCode" = L1."SlpCode"
AND IFNULL(MONTH(L1."DocDate"), 0) = Month(CURDATE())
AND IFNULL(YEAR(L1."DocDate"), 0) = YEAR(CURDATE())
AND IFNULL(MONTH(L1."DocDate"),0) = IFNULL(MONTH(L1."DocDate"),0)) AS "Current Month"
FROM OSLP T1
GROUP BY T1."SlpCode", T1."SlpName"
Having
(SELECT
SUM(l1."DocTotal")
FROM OINV L1
WHERE T1."SlpCode" = L1."SlpCode"
AND IFNULL(MONTH(L1."DocDate"), 0) = Month(CURDATE())
AND IFNULL(YEAR(L1."DocDate"), 0) = YEAR(CURDATE())
AND YEAR(L1."DocDate") = YEAR(L1."DocDate")
AND IFNULL(MONTH(L1."DocDate"),0) = IFNULL(MONTH(L1."DocDate"),0)) !='0'

[pcorp01]

Hi Mohd! Thanks so much for the query.. had to modify it a bit as I don't have HANA and found out some sql functions are not recognized in SAP B1... the below query works! However, I was looking at the built in sales analysis report and some figures don't match up..

SELECT
DISTINCT T1."SlpCode", T1."SlpName" AS "Sales Employee",
(SELECT
SUM(l1."DocTotal")
FROM OINV L1
WHERE T1."SlpCode" = L1."SlpCode"
AND ISNULL(MONTH(L1."DocDate"), 0) = Month(GETDATE())
AND ISNULL(YEAR(L1."DocDate"), 0) = YEAR(GETDATE())
AND ISNULL(MONTH(L1."DocDate"),0) = ISNULL(MONTH(L1."DocDate"),0)) AS "Current Month"
FROM OSLP T1
GROUP BY T1."SlpCode", T1."SlpName"
Having
(SELECT
SUM(l1."DocTotal")
FROM OINV L1
WHERE T1."SlpCode" = L1."SlpCode"
AND ISNULL(MONTH(L1."DocDate"), 0) = Month(GETDATE())
AND ISNULL(YEAR(L1."DocDate"), 0) = YEAR(GETDATE())
AND YEAR(L1."DocDate") = YEAR(L1."DocDate")
AND ISNULL(MONTH(L1."DocDate"),0) = ISNULL(MONTH(L1."DocDate"),0)) !='0'

[MD1]

let me check,wait please

[MD1]

data you want SQL /HANA

[pcorp01]

Hi Mohd! I'm using SAP Business One.. so SQL?