HANA Query to track how many sales documents each user has created over a one week period?

[azacc]

Hi,

I'm trying to write this query to show me how many Sales documents each user has created and in a set period but it' seems to be multiplying to results when I add each additional table?

If anyone can assist in writing the correctly please?

Here is the query I have:

SELECT 
T0."U_NAME",
COUNT(T2."DocNum") AS "SO",
COUNT(T3."AbsEntry") AS "Pick",
COUNT(T4."DocNum") AS "DN",
COUNT(T5."DocNum") AS "IN",
COUNT(T6."DocNum") AS "CR"

FROM
OUSR T0
LEFT JOIN ORDR T2 ON T0."USERID" = T2."UserSign"
LEFT JOIN OPKL T3 ON T0."USERID" = T3."OwnerCode"
LEFT JOIN ODLN T4 ON T0."USERID" = T4."UserSign"
LEFT JOIN OINV T5 ON T0."USERID" = T5."UserSign"
LEFT JOIN ORIN T6 ON T0."USERID" = T6."UserSign"

GROUP BY T0."U_NAME"

Thanks!

[SonTran · Accepted solution]

Hi,

You can refer this for the same

https://forum.sapb1.vn/community/systemtools/cau-truy-van-tong-hop-so-luong-chung-tu-duoc-tao-theo-t...

Hope this helps,

SonTran

[azacc · Accepted solution]

That is perfect thank you, I now need to add if possible a date range, I tried adding T2."DocDate" in the WHERE clause but it doesn't like it

[azacc · Accepted solution]

Added the Date prompt now, was getting caught out by the different case in CreateDate and createDate! 

Completed query below in case anyone else wants it:

SELECT "U_NAME" "User Name",
sum(case when "DocType" = 'SO' then "Count" else 0 end) as "Sales Order",
sum(case when "DocType" = 'PL' then "Count" else 0 end) as "Pick List",
sum(case when "DocType" = 'DN' then "Count" else 0 end) as "Delivery Note",
sum(case when "DocType" = 'AR' then "Count" else 0 end) as "AR Invoice",
sum(case when "DocType" = 'CN' then "Count" else 0 end) as "Credit Note",
sum(case when "DocType" = 'PO' then "Count" else 0 end) as "Purchase Order",
sum(case when "DocType" = 'PD' then "Count" else 0 end) as "Goods Rec PO",
sum(case when "DocType" = 'PU' then "Count" else 0 end) as "AP Invoice",
sum(case when "DocType" = 'PC' then "Count" else 0 end) as "AP Credit Note",
sum(case when "DocType" = 'SC' then "Count" else 0 end) as "Service Call"

FROM
(
SELECT T1."U_NAME", 'SO' "DocType", COUNT(T2."DocNum") AS "Count" FROM OUSR T1 LEFT JOIN ORDR T2 ON T1."USERID" = T2."UserSign"
WHERE T2."CreateDate" >= [%0] AND T2."CreateDate" <= [%1]
GROUP BY T1."U_NAME"
Union
SELECT T1."U_NAME", 'PL' "DocType", COUNT(T2."AbsEntry") AS "Count" FROM OUSR T1 LEFT JOIN OPKL T2 ON T1."USERID" = T2."OwnerCode"
WHERE T2."CreateDate" >= [%0] AND T2."CreateDate" <= [%1]
GROUP BY T1."U_NAME"
Union
SELECT T1."U_NAME", 'DN' "DocType", COUNT(T2."DocNum") AS "Count" FROM OUSR T1 LEFT JOIN ODLN T2 ON T1."USERID" = T2."UserSign"
WHERE T2."CreateDate" >= [%0] AND T2."CreateDate" <= [%1]
GROUP BY T1."U_NAME"
Union
SELECT T1."U_NAME", 'AR' "DocType", COUNT(T2."DocNum") AS "Count" FROM OUSR T1 LEFT JOIN OINV T2 ON T1."USERID" = T2."UserSign"
WHERE T2."CreateDate" >= [%0] AND T2."CreateDate" <= [%1]
GROUP BY T1."U_NAME"
Union
SELECT T1."U_NAME", 'CN' "DocType", COUNT(T2."DocNum") AS "Count" FROM OUSR T1 LEFT JOIN ORIN T2 ON T1."USERID" = T2."UserSign"
WHERE T2."CreateDate" >= [%0] AND T2."CreateDate" <= [%1]
GROUP BY T1."U_NAME"
Union
SELECT T1."U_NAME", 'PO' "DocType", COUNT(T2."DocNum") AS "Count" FROM OUSR T1 LEFT JOIN OPOR T2 ON T1."USERID" = T2."UserSign"
WHERE T2."CreateDate" >= [%0] AND T2."CreateDate" <= [%1]
GROUP BY T1."U_NAME"
Union
SELECT T1."U_NAME", 'PD' "DocType", COUNT(T2."DocNum") AS "Count" FROM OUSR T1 LEFT JOIN ORPC T2 ON T1."USERID" = T2."UserSign"
WHERE T2."CreateDate" >= [%0] AND T2."CreateDate" <= [%1]
GROUP BY T1."U_NAME"
Union
SELECT T1."U_NAME", 'PU' "DocType", COUNT(T2."DocNum") AS "Count" FROM OUSR T1 LEFT JOIN OPCH T2 ON T1."USERID" = T2."UserSign"
WHERE T2."CreateDate" >= [%0] AND T2."CreateDate" <= [%1]
GROUP BY T1."U_NAME"
Union
SELECT T1."U_NAME", 'PC' "DocType", COUNT(T2."DocNum") AS "Count" FROM OUSR T1 LEFT JOIN ORPC T2 ON T1."USERID" = T2."UserSign"
WHERE T2."CreateDate" >= [%0] AND T2."CreateDate" <= [%1]
GROUP BY T1."U_NAME"
Union
SELECT T1."U_NAME", 'SC' "DocType", COUNT(T2."DocNum") AS "Count" FROM OUSR T1 LEFT JOIN OSCL T2 ON T1."USERID" = T2."assignee"
WHERE T2."createDate" >= [%0] AND T2."createDate" <= [%1]
GROUP BY T1."U_NAME"
)

GROUP BY "U_NAME"
HAVING
sum(case when "DocType" = 'SO' then "Count" else 0 end)>0 or
sum(case when "DocType" = 'PL' then "Count" else 0 end)>0 or
sum(case when "DocType" = 'DN' then "Count" else 0 end)>0 or
sum(case when "DocType" = 'AR' then "Count" else 0 end)>0 or
sum(case when "DocType" = 'CN' then "Count" else 0 end)>0 or
sum(case when "DocType" = 'PO' then "Count" else 0 end)>0 or
sum(case when "DocType" = 'PU' then "Count" else 0 end)>0 or
sum(case when "DocType" = 'PC' then "Count" else 0 end)>0 or
sum(case when "DocType" = 'SC' then "Count" else 0 end)>0

ORDER BY "U_NAME"