Post reply

Case in a where Clause

  • November 27, 2008 at 2:14 am

    #154956

    Hi! Guys

    I would like to evaluate to characters in a string and return results based on that.

    First the string is a Identity number, from which the first six numbers is dateofbirth (YYMMDD). So I would like to ensure that this date is captured correctly.

    If the fifth character = 0 then the sixth character should not be 0.

    I've tried something like this and I'm not winning--->

    SELECT Headcountdec.Cluster, Headcountdec.Consolidate, Headcountdec.Level3, Headcountdec.Division, Headcountdec.Region, Headcountdec.Branch,

    Headcountdec.BranchID, Headcountdec.StaffNo, Headcountdec.CallName, Headcountdec.Surname, Staff.NationalID, LEN(Staff.NationalID) AS IDLenth,

    Staff.Passport, LEN(Staff.Passport) AS PassportLenth

    FROM Headcountdec INNER JOIN

    Staff ON Headcountdec.StaffNo = Staff.Staffno AND Headcountdec.Period = Staff.Period

    WHERE (Headcountdec.Period = 200810)

    AND (Headcountdec.headcount = 'Headcount')

    AND (Headcountdec.StaffNo IS NOT NULL)

    AND (Staff.NationalID = '')

    AND (SubString(Staff.Passport,1,1)) between char(48) AND char(57)

    AND (substring(Staff.Passport,5,1) < Char(52))

    AND (substring(Staff.Passport,3,1) < Char(51))

    AND Case When (substring(Staff.Passport,5,1) = Char(48)) Then (substring(Staff.Passport,6,1) != Char(48)) end

    OR (Staff.NationalID IS NULL)

    Please help

  • November 27, 2008 at 2:36 am

    #904192

    Hi Thatok

    If you are intending to eliminate from your result using the condition

    If the fifth character = 0 then the sixth character should not be 0.

    then shouldn't this do the trick?

    AND NOT substring(Staff.Passport,5,2) = '00'

    Cheers

    ChrisM

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • November 27, 2008 at 2:41 am

    #904193

    Thanks Chris thas exactly what I needed

  • November 28, 2008 at 7:03 am

    #904762

    Using a 2 digit year for DOB seems like it could cause you problems down the road, as people can live to be over 100 (and a few other scenarios where it could become a factor). In reality, you'll probably be fine for many years to come, but it is something to keep in mind.

    Seth Phelabaum

    Consistency is only a virtue if you're not a screwup. 😉

    Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply