Sql选择查询过滤掉一些日期 [英] Sql select query filter out some dates

问题描述

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



我想要一张桌子，只有那些正在离开的人未来，只有那个没有其他活跃条目的人。



问题是用户可以有多个条目。 1-1-1900用于数据库中的无穷大。用户Big Al将来有一个退出日期。我希望他的最后约会时间为30-10-2017。 Jan Klaas有两个我不想要他的无限日期。

Mark Mulder过去约会。



First- Last-ID Exit_Date

Taylor Mulder 1235 30-11-2017

Big Al 4567 30 -10-2017

Mike Mulder 1232 30-8-2017



我的尝试：



尝试了一些选择和组查询，但我不是SQL主人，如果有人可以请求帮助或指向正确的方向。

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.

推荐答案

我可能不理解，但听起来，如果任何人的日期为1/1/1900你不想要它们，这应该消除Jan Klass的正确吗？



为了这个例子，我忽略了ID列，因为在你提出的例子中它似乎无关紧要。

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.

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)

NOT EXISTS消除了他们可能有1/1/1900条目的记录



预期输出应该是

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

这篇关于Sql选择查询过滤掉一些日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！