Sql select query filter out some dates

Question

0.00/5 (No votes)

See more:

First- Last- ID Exit_Date
Jan Klaas 5656 1-1-1900
Jan Klaas 5656 1-1-1900
Jan Klaas 5656 25-12-2017
Peter Jobs 1223 1-1-1900
Mark Mulder 4456 24-6-2017
Taylor Mulder 1235 30-11-2017
Big Al 4567 1-1-2017
Big Al 4567 30-10-2017
big Al 4567 5-1-2017
Mike Mulder 1232 30-8-2017

I want to have a table with only the people that are leaving in the future and only the one's that don't have a other active entry.

the problem is that a user can have more then one entry. 1-1-1900 is used in the database for infinity. User Big Al has one exit date in the future. I want the last date from him 30-10-2017. Jan Klaas has two infinity dates I don't want him.
Mark Mulder has a date in the past.

First- Last- ID Exit_Date
Taylor Mulder 1235 30-11-2017
Big Al 4567 30-10-2017
Mike Mulder 1232 30-8-2017

What I have tried:

have tried some select and group query's but i'm no SQL master if some one can please help or point met to the correct direction.

Posted 29-Jul-17 11:16am

Member 10376393

Updated 29-Jul-17 12:37pm

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

David_Wimbley · Answer 1 · 2017-07-29T12:37:00

I may not be understanding but it sounds like, if any person has a date of 1/1/1900 you don't want them, which should eliminate Jan Klass correct?

For the sake of this example, I'm ignoring the ID column since it appears to not matter in the example you've put forth.

SQL

DECLARE @ExitTable TABLE (
	FirstName varchar(200) NULL,
	LastName VARCHAR(25) NULL,
	Exit_Date DATETIME NULL
);

INSERT INTO @ExitTable( FirstName, LastName, Exit_Date )
VALUES  ( 'Jan', -- FirstName - varchar(200)
          'Klass', -- LastName - varchar(25)
          '1-1-1900'-- Exit_Date - datetime
          )
INSERT INTO @ExitTable( FirstName, LastName, Exit_Date )
VALUES  ( 'Jan', -- FirstName - varchar(200)
          'Klass', -- LastName - varchar(25)
          '1-1-1900'-- Exit_Date - datetime
          )
INSERT INTO @ExitTable( FirstName, LastName, Exit_Date )
VALUES  ( 'Jan', -- FirstName - varchar(200)
          'Klass', -- LastName - varchar(25)
          '12-25-2017'-- Exit_Date - datetime
          )
INSERT INTO @ExitTable( FirstName, LastName, Exit_Date )
VALUES  ( 'Peter', -- FirstName - varchar(200)
          'Jobs', -- LastName - varchar(25)
          '1-1-1900'-- Exit_Date - datetime
          )
INSERT INTO @ExitTable( FirstName, LastName, Exit_Date )
VALUES  ( 'Mark', -- FirstName - varchar(200)
          'Mulder', -- LastName - varchar(25)
          '6/24/2017'-- Exit_Date - datetime
          )
INSERT INTO @ExitTable( FirstName, LastName, Exit_Date )
VALUES  ( 'Taylor', -- FirstName - varchar(200)
          'Mulder', -- LastName - varchar(25)
          '11/30/2017'-- Exit_Date - datetime
          )
INSERT INTO @ExitTable( FirstName, LastName, Exit_Date )
VALUES  ( 'Mike', -- FirstName - varchar(200)
          'Mulder', -- LastName - varchar(25)
          '8/30/2017'-- Exit_Date - datetime
          )
INSERT INTO @ExitTable( FirstName, LastName, Exit_Date )
VALUES  ( 'Big', -- FirstName - varchar(200)
          'AL', -- LastName - varchar(25)
          '1-1-2017'-- Exit_Date - datetime
          )
INSERT INTO @ExitTable( FirstName, LastName, Exit_Date )
VALUES  ( 'Big', -- FirstName - varchar(200)
          'AL', -- LastName - varchar(25)
          '10/30/2017'-- Exit_Date - datetime
          )
INSERT INTO @ExitTable( FirstName, LastName, Exit_Date )
VALUES  ( 'Big', -- FirstName - varchar(200)
          'AL', -- LastName - varchar(25)
          '1/5/2017'-- Exit_Date - datetime
          )


SELECT * FROM @ExitTable AS A WHERE 
CAST(A.Exit_Date AS DATE) > CAST('7/29/2017' AS DATE) --7/29 can be swapped out for GETDATE()
AND NOT EXISTS (SELECT * FROM @ExitTable AS B WHERE CAST(B.Exit_Date AS DATE) = CAST('1/1/1900' AS DATE) AND B.FirstName = A.FirstName AND B.LastName = A.LastName)

The NOT EXISTS eliminates any record where they may have an entry of 1/1/1900

Expected output should be

Taylor	Mulder	2017-11-30 00:00:00.000
Mike	Mulder	2017-08-30 00:00:00.000
Big	AL	2017-10-30 00:00:00.000