count all staff employed at mid year, null date problem

  • Hi

    I'm using SQLS 2008.

    I want to select all the staff who were employed by a company at a mid year point in time. In this example I use 15/07/1985 as the mid year point. In the example table below there are 5 staff defined by staffid with start date and end date. If I select all staff where start date is before '1985-07-15' and end date is after '1985-07-15' then I get 2 results from the table below, staffid 1 and 5. However staffid 2 has a null end date. Currently this excludes this staffid from the result but I want to define all staff with a null end date as still currently employed and so included in my results. How can I do this? I think the nulliff function is an option but I'm not sure of the syntax.

    thanks

    CREATE TABLE midyearexample

    (

    staffid VARCHAR(20) NULL,

    startdate DATE NULL,

    enddate DATE NULL,

    )

    GO

    INSERT INTO midyearexample (staffid, startdate, enddate)

    VALUES (1, '1970-08-28', '2010-02-23');

    INSERT INTO midyearexample (staffid , startdate, enddate)

    VALUES (2, '1983-01-26', null );

    INSERT INTO midyearexample (staffid, startdate, enddate)

    VALUES (3, '1991-09-03', '2008-07-18' );

    INSERT INTO midyearexample (staffid, startdate, enddate)

    VALUES (4, '1965-05-10', '1982-12-10');

    INSERT INTO midyearexample (staffid, startdate, enddate)

    VALUES (5, '1970-11-24', '2009-02-06');

    GO

    SELECT *

    FROM midyearexample

    WHERE startdate < '1985-07-15'

    AND enddate > '1985-07-15'

  • Hi,

    You can change your Where condition.

    SELECT *

    FROM midyearexample

    WHERE

    startdate < '1985-07-15' AND (enddate > '1985-07-15' OR enddate IS Null)

    Shatrughna

  • sounds good

    thanks

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

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