November 26, 2011 at 2:54 am
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'
November 26, 2011 at 3:02 am
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
November 26, 2011 at 4:47 am
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