on 04-15-2018 12:41 PM
hey,
i wrote this query and it shows only the sales man that have invoces by their name for the dates given .
i want to see the whole list of sales man with '0' or '-' for the ones that don't have invoices yet .
SELECT
T1.[SlpName], sum( T2.[DocTotal])/1.17
FROM OCRD T0
INNER JOIN OSLP T1 ON T0.SlpCode = T1.SlpCode
Inner join ORCT T2 on T0.cardcode = T2.cardcode
WHERE T2.[DocDate] >=[%0] and T2.[DocDate] <=[%1]
GROUP BY T1.[SlpName]
THANK YOU !!
Hi,
Please try this:
SELECT T1.[SlpName], ISNULL(sum( T2.[DocTotal])/1.17, 0) AS Total
FROM OSLP T1
LEFT OUTER JOIN OCRD T0 ON T1.SlpCode = T0.SlpCode
LEFT OUTER JOIN ORCT T2 on T0.cardcode = T2.cardcode
WHERE T2.[DocDate]>=[%0]
and T2.[DocDate]<=[%1]
GROUPBY T1.[SlpName]
Regards,
Johan
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi,
It is probably the WHERE clause. We can fix this by using an inline sub query: Please try this:
/* select * from ORCT x */
DECLARE @FROM AS DATETIME
DECLARE @TO AS DATETIME
SET @FROM = /* x.DocDate */ [%0]
SET @TO = /* x.DocDate */ [%1]
SELECT T1.[SlpName]
, ISNULL((SELECT ISNULL(sum(T2.[DocTotal])/1.17, 0) FROM ORCT T2 WHERE T2.cardcode = T0.CardCode AND T2.[DocDate] BETWEEN @FROM AND @TO), 0) AS Total
FROM OSLP T1
LEFT OUTER JOIN OCRD T0 ON T1.SlpCode = T0.SlpCode
regards,
Johan
Change your join to Left Outer Join because inner join returns values which are in both tables while left outer join will return all values from first table irrespective of second table.. if value is not in second table if will shows first table value only...
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi,
Try this query,
SELECT T1.[SlpName], sum( T2.[DocTotal])/1.17 FROM OCRD T0 INNER JOIN OSLP T1 ON T0.SlpCode = T1.SlpCode Inner join ORCT T2 on T0.cardcode = T2.cardcode WHERE T2.[DocDate] >=[%0] and T2.[DocDate] <=[%1] AND (T1.Slpname Like '%%O%%' OR T1.Slpcode = '-') GROUP BY T1.[SlpName]
Regards,
Nagarajan
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi,
I want the full list of salesman and the sum of invoces that have been paid in a given time
this Query suposed to help us know how much bonus to give the salesman that worked well on the money collection
and during the month - to know to which one to give a call since he didn't collect any money.
thanks
User | Count |
---|---|
86 | |
7 | |
6 | |
4 | |
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.