So I need to calculate the amount of Clients who's LastName start with the same letter, then use that count as part of an Reference number.

I.e. something like this:

declare @AmountOfSimilarSurnames int;
set @AmountOfSimilarSurnames = (...)

declare @Ref nvarchar(100);
set @Ref = 'CLT00' + @AmountOfSimilarSurnames

I need help setting that AmountOfSimilarSurnames

So far I got this but it is working:

select COUNT(*) FROM Client c1
join Client c2 on RIGHT(c1.Client_LName,1) = RIGHT(c2.Client_LName,1)
  • RIGHT will compare the last character rather than the first so you probably want to use LEFT instead. This is a many-to-many join so you will count the same name more than once when more than one name starts with the same character. Consider using EXISTS rather than JOIN. This is an odd requirement to use the count in a reference number since the count will change as clients are inserted, updated, or removed. – Dan Guzman Apr 14 '19 at 11:19
  • Aaah I see. Yeah the idea behind the count is to give the user 'n user facing reference, it's arbitrary for the back end but it's client require. – Marnus Steyn Apr 14 '19 at 12:51
  • Also, how would that Exists query look like? – Marnus Steyn Apr 14 '19 at 12:55

Below is an example using RIGHT (starting character) and EXISTS (so that matching names are counted only once).

DECLARE @Ref nvarchar(100) = 'CLT00'
    + CAST(
        (SELECT COUNT(*)
        FROM Client c1
            SELECT 1
            FROM Client c2
                LEFT(c1.Client_LName,1) = LEFT(c2.Client_LName,1)
        ) AS varchar(10));

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.