Hi, Does anyone experience with the following coding for reporting purpose: LEFT JOIN Table5..Setup_Countries U ON LEFT(A.EMPLOYID, 2)=U.COUNTRYCODE WHERE A.DOC_STATUS NOT IN (1, 8, 11) AND LEFT (A.EMPLOYID, 2 ) IN ('SG', 'AU', 'IN', 'AT', 'UK', 'US') AND A.REPDT >= @PERIODFROM AND A.PERIODENDDATE <=@PERIODTO and u.country in (@Country) In my SSRS, i select country : Singapore, it will generate all employid with SG% however, due to some discrepancy, there is employid with SI% but countrycode for Singapore is SG and not SI Hope the detail provided is clear. Is it possible to add on criteria such as ... AND LEFT(A.EMPLOYID, 2) IN ('SI') = (@Country) IN 'Singapore'

Did you try to change LEFT JOIN Table5..Setup_Countries U ON LEFT(A.EMPLOYID, 2)=U.COUNTRYCODE to INNER JOIN Table5..Setup_Countries U ON LEFT(A.EMPLOYID, 2)=U.COUNTRYCODERemember to mark as an answer if this post has helped you.

However, my countrycode for Singapore is SG, so I need to set a criteria to indicate that i have employee ID with SIxxx, so when i select Country = Singapore, it will also retrieve (employid, 2) = SG and SI To add on, i have temp table for countries, i was wondering if i can create for database together with country, meaning this country on database SIN and the rest database, as long as there is (employid, 2) = SI and SG, generate it for country Singapore

Add the SI also in condition ,This will also give you the list of employees with SI*.But this is for all countries WHERE A.DOC_STATUS NOT IN (1, 8, 11) AND LEFT (A.EMPLOYID, 2 ) IN ('SI','SG', 'AU', 'IN', 'AT', 'UK', 'US') AND A.REPDT >= @PERIODFROM AND A.PERIODENDDATE <=@PERIODTO and u.country in (@Country) If you want only SI to be listed for Singapore better to add a new query with UNION with the required WHERE clause.

Here is the syntax for UNION . the first query fetches record Country <> 'SG' and Second query fetches the records with country 'SG' and employid starts with 'SI' SELECT * FROM <Table> WHERE A.DOC_STATUS NOT IN (1, 8, 11) AND LEFT (A.EMPLOYID, 2 ) IN ('SG', 'AU', 'IN', 'AT', 'UK', 'US') AND A.REPDT >= @PERIODFROM AND A.PERIODENDDATE <=@PERIODTO and u.country in (@Country) AND U.Country <> 'SG' UNION SELECT * FROM <Table> WHERE A.DOC_STATUS NOT IN (1, 8, 11) AND LEFT (A.EMPLOYID, 2 ) IN ('SI') AND A.REPDT >= @PERIODFROM AND A.PERIODENDDATE <=@PERIODTO and u.country in (@Country) AND U.Country = 'SG'

Hi Sorna, Kindly advise if union is possible if @country is variable in this statement?

Thanks Sona, in another report, i have a temp table to select all employees for selected countries, in this case, for my Singapore, the countrycode is 'SG', and i also wish to pick up when employid 'SI' when i select country : Singapore, but countrycode for Country Singapore is SG. Any advise? WHEN ... AND SUBSTRING(U.EMPLOYID,1,2) IN (SELECT CountryCode FROM #Countries)