Post reply

HOW TO ADD THE NULL VALUE TO SELECT

  • September 15, 2011 at 12:46 pm

    #149688

    SELECT HREMP_Adp.EMPLNO, HREMP_Adp.LNAME + ', ' + HREMP_Adp.FNAME+ HREMP_Adp.MI AS [Full Name] FROM HREMP_Adp INNER JOIN HospitalRN ON HREMP_Adp.EMPLNO = HospitalRN.EMPLID WHERE (DATEDIFF(day, HREMP_Adp.HIRE_DATE, HREMP_Adp.TERMDATE) < 0) AND (HREMP_Adp.EMPSTATUS IS NULL) ORDER BY HREMP_Adp.LNAME

    fnAME, lNAME, mi

    PATRICIAWALSHL

    PATRICIAWALSHA

    PATRICIAWALSHM

    PATRICIAWALSH

    tHANKS.

  • September 15, 2011 at 9:00 pm

    #1382359

    Frances L (9/15/2011)

    SELECT HREMP_Adp.EMPLNO, HREMP_Adp.LNAME + ', ' + HREMP_Adp.FNAME+ HREMP_Adp.MI AS [Full Name] FROM HREMP_Adp INNER JOIN HospitalRN ON HREMP_Adp.EMPLNO = HospitalRN.EMPLID WHERE (DATEDIFF(day, HREMP_Adp.HIRE_DATE, HREMP_Adp.TERMDATE) < 0) AND (HREMP_Adp.EMPSTATUS IS NULL) ORDER BY HREMP_Adp.LNAME

    fnAME, lNAME, mi

    PATRICIAWALSHL

    PATRICIAWALSHA

    PATRICIAWALSHM

    PATRICIAWALSH

    tHANKS.

    I suspect what you mean is that not everyone has a middle initial in this table. The following will fix that. I suggest you read about "ISNULL" in Books Online (the "help" system for SQL Server).

    SELECT HREMP_Adp.EMPLNO,

    HREMP_Adp.LNAME + ', ' + HREMP_Adp.FNAME + ' ' + ISNULL(HREMP_Adp.MI,'') AS [Full Name]

    FROM HREMP_Adp

    INNER JOIN HospitalRN ON HREMP_Adp.EMPLNO = HospitalRN.EMPLID

    WHERE (DATEDIFF(day, HREMP_Adp.HIRE_DATE, HREMP_Adp.TERMDATE) < 0)

    AND (HREMP_Adp.EMPSTATUS IS NULL)

    ORDER BY HREMP_Adp.LNAME

    ;

    I also want to point out that the only time your DATEDIFF will work is if a person's TERMDATE is less than their HIREDATE. Not sure you meant to do that or not so I thought I'd bring it to your attention.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • September 16, 2011 at 6:29 am

    #1382504

    Thanks. the hire date and term date is for rehired.

  • September 18, 2011 at 12:08 pm

    #1383109

    Ah... understood. So, are you all set now?

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • September 18, 2011 at 6:12 pm

    #1383135

    yes. thanks.

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

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