cancel
Showing results for 
Search instead for 
Did you mean: 

Query for Count of documents created

Former Member
0 Kudos

Hello Experts,

I want a query which will show how many documents created by users for all document types. I created below query

SELECT count (distinct T0.[DocNum]) as 'Total Inv', T1.[U_Name] FROM OINV T0  INNER JOIN OUSR T1 ON T0.UserSign = T1.USERID  GROUP BY T1.[U_Name]

union all

SELECT count (distinct T2.[DocNum]) as 'Total Order', T3.[U_Name] FROM ORDR T2  INNER JOIN OUSR T3 ON T2.UserSign = T3.USERID  GROUP BY T3.[U_Name]

The results are appearing in one column

I want results in separate column Like below

Total Inv     Usersign         Total Order

1984          1                     

487            13                  312

263            14                  14 

619            15                   409

3                16                   43

1                19

474             20                   368

Please advise.

Thanks

Deepak

Accepted Solutions (1)

Accepted Solutions (1)

kothandaraman_nagarajan
Active Contributor
0 Kudos

Hi,

Try this:

Select [S] as 'order',  [D],[E], [F]

from(

SELECT  T1.[U_NAME] as A, T0.[DocNum] as T, (case when T0.[ObjType] = 17 then 'Salesorder' end ) as S  FROM ORDR T0  INNER JOIN OUSR T1 ON T0.UserSign = T1.USERID GROUP BY T1.[U_NAME],T0.[DocNum],T0.[ObjType] ) as ORDR

Pivot

( count(T) for A in ([D],[E],[F])) P 

union all

Select [S] as 'order', [D],[E], [F]

from(

SELECT T1.[U_NAME] as A, T0.[DocNum] as T,(case when T0.[ObjType] = 13 then 'Invoice' end )  as S FROM OINV T0  INNER JOIN OUSR T1 ON T0.UserSign = T1.USERID GROUP BY T1.[U_NAME],T0.[DocNum],T0.[ObjType] )  as OINV

Pivot

( count(T) for A in ([D],[E],[F])) P

Note: Replace D,E,and F with your actual user name. To find user name run below query:

SELECT T0.[U_NAME] FROM OUSR T0

Hope helpful.

Thanks & Regards,

Nagarajan

Answers (0)