cancel
Showing results for 
Search instead for 
Did you mean: 

how to show the whole list of sales man even the ones with no resolute for the calculation

0 Kudos

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 !!

Accepted Solutions (0)

Answers (3)

Answers (3)

Johan_H
Active Contributor

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

0 Kudos

thanks for trying to help

but i still don't get the full list of sales man

have any idea why ?

Johan_H
Active Contributor
0 Kudos

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

0 Kudos

thank you!

this time it gave me the full list

but thousands of lines and some had 2 different results :

Johan_H
Active Contributor
0 Kudos

I see. It may be enough to just add a simple GROUP BY clause at the end of the query:

GROUP BY T1.[SlpName]

Regards,

Johan

Abdul
Active Contributor
0 Kudos

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...

0 Kudos

i tried, the result is the same

Abdul
Active Contributor
0 Kudos

You need invoices or payments because ORCT is incoming Payments Table ?

0 Kudos

the payments

Abdul
Active Contributor
0 Kudos

Then Please check on Customer Master Data each business partner is assigned sales employee i think user is assigning sales employee on invoice and only some business partners has assigned default sales employee

kothandaraman_nagarajan
Active Contributor
0 Kudos

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

0 Kudos

it doesn't find any matching data ...

kothandaraman_nagarajan
Active Contributor
0 Kudos

Hi,

Basically you want list of AR Invoices which is not paid right?

Regards,

Nagarajan

0 Kudos

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