如何根据当前sg选择最早的日期

I need to come with a SQL (SQL Server) that gets the earliest date according to the current sg. Below is the sample table where current sg is 4-3 (current because the end date is 1900-01-01). I want to get the date where the sg actually started (which is 2015-01-01) per employee. I really need some help on this.

我需要提供一个SQL (SQL Server)，根据当前的sg获得最早的日期。下面是示例表，其中当前sg为4-3(当前，因为结束日期为1900-01-01)。我想知道sg实际开始的日期(2015-01-01)。我真的需要一些帮助。

empno      position             sg           date_from         date_to
4508       ADMIN AIDE IV        4-3          2017-01-01        1900-01-01
4508       ADMIN AIDE IV        4-3          2016-01-01        2016-12-31
4508       ADMIN AIDE IV        4-3          2015-01-01        2015-12-31
4508       ADMIN AIDE IV        4-2          2014-01-01        2014-12-31
4508       ADMIN AIDE IV        4-2          2013-01-01        2013-12-31
1207       AIRCRAFT MECHANIC I  6-1          1988-01-01        1989-06-30
1207       AIRCRAFT MECHANIC II 8-7          2006-05-08        2015-12-31
1207       AIRCRAFT MECHANIC II 8-8          2016-01-01        1900-01-01
0889       DATA ENTRY OPERATOR  1-1          2000-12-12        2001-06-30
0889       ADMIN ASSISTANT VI   12-5         2017-03-10        1900-01-01
0889       ADMIN ASSISTANT VI   12-5         2016-01-01        2016-12-31

4 个解决方案

#1

If I understand you correctly, you can do that both with an inner query

如果我理解正确，您可以使用内部查询实现这两个操作

select  min(date_from)
from    yourTable
where   sg = (
            select  sg
            from    yourTable
            where   date_to = '1900-01-01'
        )

or with a join

或加入

select  min(t1.date_from)
from    yourTable t1
join    yourTable t2
on      t1.sg = t2.sg
where   t2.date_to = '1900-01-01'

Edit

编辑

To get the minimum date for each position, the easiest way is tweaking the second query into this:

为了获得每个位置的最小日期，最简单的方法是将第二个查询调整为:

select  t1.position, min(t1.date_from)
from    yourTable t1
join    yourTable t2
on      t1.sg = t2.sg and
        t1.position = t2.position
where   t2.date_to = '1900-01-01'
group by t1.position

Edit 2

编辑2

Since the requirement is to get the minimum date for each value in the the empno column, what you need to do is this

由于需求是为empno列中的每个值获取最小日期，所以您需要做的就是这样

select  t1.empno, min(t1.date_from)
from    yourTable t1
join    yourTable t2
on      t1.sg = t2.sg and
        t1.empno = t2.empno
where   t2.date_to = '1900-01-01'
group by t1.empno

You can see it at work here

你可以在这里看到它。

#2

If a sg can't have multiple positions you could use the next query

如果一个sg不能有多个位置，你可以使用下一个查询。

SELECT t.position, t.sg, MIN(date_from) AS date_from
FROM @t AS t
    INNER JOIN 
      (select sg from @t where date_to = '19000101') as cur
    ON t.sg = cur.sg
GROUP BY t.position, t.sg

#3

THE SOLUTION:

解决方案:

SELECT TOP 1 *
FROM YourTableNameHere
ORDER BY Sg DESC, Date_From

More Details:

更多的细节:

Use ORDER BY in combination with TOP 1. Below I used a temp table that you can run in a NEW QUERY window.

使用顺序与顶部1结合。下面我使用了一个临时表，您可以在新的查询窗口中运行它。

Try this out:

试试这个:

CREATE TABLE #TempTable(
 Position varchar(20),
 Sg varchar(3),
 Date_From datetime,
 Date_To datetime)

INSERT INTO #TempTable (Position, Sg, Date_From, Date_To) 

SELECT 
        'ADMIN AIDE IV', '4-3', '2017-01-01', '1900-01-01'
UNION
SELECT 
        'ADMIN AIDE IV', '4-3', '2016-01-01', '2016-12-31'
UNION
SELECT 
        'ADMIN AIDE IV', '4-3', '2015-01-01', '2015-12-31'
UNION
SELECT 
        'ADMIN AIDE IV', '4-2', '2014-01-01', '2014-12-31'
UNION
SELECT 
        'ADMIN AIDE IV', '4-2', '2013-01-01', '2013-12-31'


SELECT TOP 1 *
FROM #TempTable
ORDER BY Sg DESC, Date_From

#4

Please use the below query :

请使用以下查询:

    DECLARE @t TABLE 
    (ID INT IDENTITY (1,1),position VARCHAR(20), sg VARCHAR(10),date_from DATE,date_to DATE)

    INSERT INTO @t 
    VALUES
    ('ADMIN AIDE IV','4-3','2017-01-01','1900-01-01'),
    ('ADMIN AIDE IV','4-3','2016-01-01','2016-12-31'),
    ('ADMIN AIDE IV','4-3','2015-01-01','2015-12-31'),
    ('ADMIN AIDE IV','4-2','2014-01-01','2014-12-31'),
    ('ADMIN AIDE IV','4-2','2013-01-01','2013-12-31')

    SELECT  
        MIN(t1.date_from) AS [Earliest Date]
    FROM    
        @t t1 INNER JOIN @t t2 ON t1.sg = t2.sg
    WHERE   
        t2.date_to ='1900-01-01'

#1