on 07-20-2011 5:04 PM
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
89 | |
7 | |
6 | |
3 | |
3 | |
3 | |
3 | |
3 | |
3 | |
2 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.