查找sql查询以获取每个分组的最新关联日期

I have a sql table of payroll data that has wage rates and effective dates associated with those wage rates, as well as hours worked on various dates. It looks somewhat like this:

我有一个关于工资数据的sql表，其中包含工资率和与这些工资率相关的有效日期，以及不同日期的工作时间。它看起来有点像这样:

EMPID  DateWorked  Hours  WageRate  EffectiveDate

1      1/1/2010     10     7.00      6/1/2009
1      1/1/2010     10     7.25      6/10/2009
1      1/1/2010     10     8.00      2/1/2010
1      1/10/2010 ...
2      1/1/2010  ...

...

…

And so on. Basically, the data has been combined in such a way that for every day worked, all of the employee's wage history is joined together, and I want to grab the wage rate associated with the LATEST effective date that is not later than the date worked. So in the example above, the rate of 7.25 that become effective on 6/10/2009 is what I want.

等等。基本上，数据是这样组合的，每天工作，所有员工的工资历史都连接在一起，我想获取与最近有效日期相关的工资率，不迟于工作日期。所以在上面的例子中，在6/10/2009年生效的7.25是我想要的。

What kind of query can I put together for this? I can use MAX(EffectiveDate) alongwith a criteria based on being before the work date, but that only gives me the latest date itself, I want the associated wage. I am using Sql Server for this.

我可以将哪种查询放在一起?我可以使用MAX(有效日期)和一个基于在工作日期之前的标准，但那只给我最近的日期本身，我想要相关的工资。我使用的是Sql Server。

Alternatively, I have the original tables that were used to create this data. One of them contains the dates worked, and the hours as well as EMPID, the other contains the list of wage rates and effective dates. Is there a way to join these instead that would correctly apply the right wage rate for each work day?

或者，我有用于创建此数据的原始表。其中一个包含工作日期、工作时间以及不温不火的时间，另一个包含工资率和生效日期的列表。有没有一种方法可以代替这些方法来正确地应用每个工作日的工资率?

I was thinking that I'd want to group by EMPID and then DateWorked, and do something from there. I want to get a result that gives me the wage rate that actually is the latest effective rate for each date worked

我在想，我想通过EMPID和datework进行分组，然后做点什么。我想要得到的结果是我的工资率实际上是每个工作日期的最新有效工资率

4 个解决方案

#1

select p.*
from (
    select EMPID, DateWorked, Max(EffectiveDate) as MaxEffectiveDate
    from Payroll
    where EffectiveDate <= DateWorked
    group by EMPID, DateWorked
) pm
inner join Payroll p on pm.EMPID = p.EMPID and pm.DateWorked = p.DateWorked and pm.MaxEffectiveDate = p.EffectiveDate

Output:

输出:

EMPID       DateWorked              Hours       WageRate                                EffectiveDate
----------- ----------------------- ----------- --------------------------------------- -----------------------
1           2010-01-01 00:00:00.000 10          7.25                                    2009-06-10 00:00:00.000

#2

try this:

试试这个:

DECLARE @YourTable table (EMPID int, DateWorked datetime, Hours int
                         ,WageRate numeric(6,2), EffectiveDate datetime)
INSERT INTO @YourTable VALUES (1,'1/1/2010' ,10,     7.00,  '6/1/2009')
INSERT INTO @YourTable VALUES (1,'1/1/2010' ,10,     7.25, '6/10/2009')
INSERT INTO @YourTable VALUES (1,'1/1/2010' ,10,     8.00,  '2/1/2010')
INSERT INTO @YourTable VALUES (1,'1/10/2010',10,     20.00,'12/1/2010')
INSERT INTO @YourTable VALUES (2,'1/1/2010' ,8 ,     12.00, '2/1/2009')

SELECT
    e.EMPID,e.WageRate,e.EffectiveDate
    FROM @YourTable  e
        INNER JOIN (SELECT
                        EMPID,MAX(EffectiveDate) AS EffectiveDate
                        FROM @YourTable
                        WHERE EffectiveDate<GETDATE()+1
                        GROUP BY EMPID
                   ) dt ON e.EMPID=dt.EMPID AND e.EffectiveDate=dt.EffectiveDate
   ORDER BY e.EMPID

OUTPUT

输出

EMPID       WageRate                                EffectiveDate
----------- --------------------------------------- -----------------------
1           8.00                                    2010-02-01 00:00:00.000
2           12.00                                   2009-02-01 00:00:00.000

(2 row(s) affected)

#3

Something like this ought to work:

像这样的东西应该有用:

SELECT T.* FROM T
INNER JOIN (
    SELECT EMPID, MAX(EFFECTIVEDATE) EFFECTIVEDATE
    FROM T
    WHERE DATEWORKED <= EFFECTIVEDATE
    GROUP BY EMPID) t2
  ON T2.EMPID = T.EMPID 
  AND T2.EFFECTIVEDATE = T.EFFECTIVEDATE

#4

SELECT TOP 1 EMPID, WageRate
 FROM wages
WHERE ...... 
ORDER BY EffectiveDate DESC

#1

select p.*
from (
    select EMPID, DateWorked, Max(EffectiveDate) as MaxEffectiveDate
    from Payroll
    where EffectiveDate <= DateWorked
    group by EMPID, DateWorked
) pm
inner join Payroll p on pm.EMPID = p.EMPID and pm.DateWorked = p.DateWorked and pm.MaxEffectiveDate = p.EffectiveDate