New to SAP queries trying to Create a BP List using the Parameters

[amswain]

Here is the query , only when we put in the CreateDate and the Slp Name , we are not returning all the results. it is filtering some out.

SELECT DISTINCT T0.[CardCode], T0.[CardName], T2.[State], T1.[SlpName] AS 'Regional Manager', T3.[GroupName] AS 'Customer Group', T0.[CreateDate] AS 'Date Created',T0.[U_U_Lead_Type] FROM OCRD T0 INNER JOIN OSLP T1 ON T0.[SlpCode] = T1.[SlpCode] INNER JOIN CRD1 T2 ON T0.[CardCode] = T2.[CardCode] INNER JOIN OCRG T3 ON T0.[GroupCode] = T3.[GroupCode] WHERE (T1.[SlpName] = [%0]) And T0.[CardType] = [%1] And T0.[CardCode] NOT LIKE 'E%' And (T0.[CreateDate] >= [%2] and T0.[CreateDate]<=[%3])

[kothandaraman_nagarajan · Accepted solution]

Hi,

If you use CRD1 table, it pulls state from bill to and ship to field. You have to add condition to get from either ship to or bill to. Otherwise, the records are duplicated.

Regards,

Nagarajan

[Johan_H]

Hi Anthony,

By leaving the Sales Person blank, you are effectively searching for leads where a sales person was not determined.

Please try this:

SELECT DISTINCT T0.[CardCode]
              , T0.[CardName]
              , T2.[State]
              , T1.[SlpName] AS 'Regional Manager'
              , T3.[GroupName] AS 'Customer Group'
              , T0.[CreateDate] AS 'Date Created'
              , T0.[U_U_Lead_Type] 
FROM OCRD T0
     INNER JOIN OSLP T1 ON T0.[SlpCode] = T1.[SlpCode]
     INNER JOIN CRD1 T2 ON T0.[CardCode] = T2.[CardCode]
     INNER JOIN OCRG T3 ON T0.[GroupCode] = T3.[GroupCode] 
WHERE ISNULL(T1.[SlpName], '') LIKE '%[%0]%'
  AND T0.[CardType] = '[%1]'
  AND T0.[CardCode] NOT LIKE 'E%'
  AND T0.[CreateDate] BETWEEN [%2] AND [%3]

regards,

Johan

[amswain]

Johan Hakkesteegt

Thank you that worked better, but it still is not pulling all results. it only gave us 22 results when there are actually 52

for the the choices of all sales people , Type = L and date range

[jimmyl]

Hi Anthony,

Your query uses "Select Distinct" statement, will that be why the duplicates are eliminated?

Will you be able to provide some screenshots of the results returned and those 52 records?

Thanks,

Jimmy

[Johan_H]

Hi Anthony,

INNER JOINs will also effectively limit results. If you have double checked that the expected 52 leads all fall within the given parameters, then the following test should return 52 records. Please give this a try:

SELECT T0.[CardCode]
, T0.[CardName]
--, T2.[State]
, T1.[SlpName] AS'Regional Manager'
--, T3.[GroupName] AS'Customer Group'
, T0.[CreateDate] AS'Date Created'
, T0.[U_U_Lead_Type]
FROM OCRD T0
     INNER JOIN OSLP T1 ON T0.[SlpCode]= T1.[SlpCode]
     --INNER JOIN CRD1 T2 ON T0.[CardCode]= T2.[CardCode]
     --INNER JOIN OCRG T3 ON T0.[GroupCode]= T3.[GroupCode]
WHERE ISNULL(T1.[SlpName],'') LIKE '%[%0]%'
  AND T0.[CardType]='[%1]'
  AND T0.[CardCode] NOT LIKE 'E%'
  AND T0.[CreateDate] BETWEEN [%2] AND [%3]

Please note how I commented out some of the lines. If this test still does not return the expected leads, please use this method, first to comment out the last parameter, then the second-to-last, and finally the third-to-last. Run the query each time, and analyze the result set to see which records do not fall within the given parameters.

Regards,

Johan

[amswain]

Jimmy Liang,

Conditions are correct.

the results are if we choose Type = Lead leave the Sales Person Blank and then choose our date range. it is not pulling all the leads for that range and some of the those records that it does pull are duplicated.

[jimmyl]

Hi Anthony,

Your query has different conditions as well as parameters, such as 1) Sales person, 2) BP Type, 3) BP number not starting with "E", and 4) a creation date range.

Are these conditions correct, or would you mind to specify why the result returned is not right or not complete?

Thanks,

Jimmy