cancel
Showing results for 
Search instead for 
Did you mean: 

Sales Order Row Count

former_member834429
Participant
0 Kudos

Hi Experts

I want to analyse how many sales order rows are going on the system between set periods and which users are inputting the rows.

Can anyone suggest a query which will simply show me a number of how many rows each user has created for a user input period of time?

Regards

Geoff

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

Hi Geoff,

Try:

SELECT T2.U_Name, COUNT(T0.Linenum) LineCount

FROM RDR1 T0

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

INNER JOIN OUSR T2 ON T2.UserID=T1.UserSign

WHERE T1.DocDate >= [%0\] AND T1.DocDate <= [%1\]

Group By T2.U_Name

Thanks,

Gordon

former_member834429
Participant
0 Kudos

thanks Gordon

This is perfect for what I need. I have tried to expand on it a little to show 2 more columns so I can show 3 different periods in total. I don't really understand SQL in this respect but I think I need to use a couple of next SELECT statements. Gordon - do you think you would show me how you would show the same query but for example January in one column, February in the next and finally march?

many thanks

geoff

Former Member
0 Kudos

It can be done. However, the date selection will be difficult in this requirement.

How about you just allow input one date and then show 3 period before or after it?

former_member834429
Participant
0 Kudos

Hi Gordon

I think having 3 set perioud before would be fine.

thanks

Geoff

Former Member
0 Kudos

Try:

Declare @ToDate as datetime

SET @TODATE = /SELECT T5.Docdate FROM ORDR T5 WHERE T5.DocDate=/ [%0\]

SELECT DISTINCT T0.U_Name, (SELECT COUNT(T2.Linenum) FROM ORDR T1

INNER JOIN RDR1 T2 ON T2.DocEntry=T1.DocEntry WHERE T0.UserID=T1.UserSign AND DateDiff(D,T1.DocDate,@ToDate) Between 0 AND 30) LineCount1,

(SELECT COUNT(T2.Linenum) FROM ORDR T1

INNER JOIN RDR1 T2 ON T2.DocEntry=T1.DocEntry WHERE T0.UserID=T1.UserSign AND DateDiff(D,T1.DocDate,@ToDate) Between 31 AND 60) LineCount2,

(SELECT COUNT(T2.Linenum) FROM ORDR T1

INNER JOIN RDR1 T2 ON T2.DocEntry=T1.DocEntry WHERE T0.UserID=T1.UserSign AND DateDiff(D,T1.DocDate,@ToDate) Between 61 AND 90) LineCount3

FROM OUSR T0

Answers (1)

Answers (1)

Former Member
0 Kudos

Hi Goeff......

I dont know what you require exactly but please try this.......

SELECT Count(T0.[LineNum]), T2.U_Name FROM RDR1 T0  INNER JOIN ORDR T1 ON T0.DocEntry = T1.DocEntry INNER JOIN OUSR T2 ON T1.UserSign = T2.USERID Where (T1.DocDate>='[%0]' and T1.DocDate<='[%1]')
GROUP BY T2.U_Name

Hope this may help you.........

Regards,

Rahul

Edited by: RAHUL MOUNDEKAR on Jul 20, 2011 11:04 PM