sql server-每个分区的第一行和最后一行编号

Question

我有这个查询，它可以很好地检索与每个分区的最早[Pay Begin Period]相关联的第一个[LastName]。但是，我现在想要最后一个[姓氏]。我该怎么做？

SELECT PR.SSN as SSN, 
MAX(CASE WHEN seqnum = 1 THEN PR.[Last Name] END) as [Last Name],
MIN(PR.Address) as [Address], 
MIN(PR.City) as [City], 
MIN(PR.State) as [State], 
MIN(PR.Zip) as [Zip],
MIN(cast(PR.[Pay Begin Period] as date)) as [Pay Begin Period],
MAX(cast(PR.[Pay End Period] as date)) as [Pay End Period],
FROM (SELECT pr.*,
         ROW_NUMBER() OVER (PARTITION BY PR.SSN ORDER BY [Pay Begin Period]) 
         as seqnum
      FROM Payroll.dbo.[Table1] PR
 ) pr
WHERE (cast(PR.[Pay Begin Period] as date) > '1/1/2013' AND
    cast(PR.[Pay End Period] as date) < '12/31/2013')
GROUP BY PR.SSN;

这是我的尝试，显然不起作用：

SELECT PR.SSN as SSN, 
MAX(CASE WHEN seqnum = 1 THEN PR.[Last Name] END) as [Earliest Last Name],
MAX(CASE WHEN seqnum = MAX(seqnum) THEN PR.[Last Name] END) as [Latest Last Name],
MIN(PR.Address) as [Address], 
MIN(PR.City) as [City], 
MIN(PR.State) as [State], 
MIN(PR.Zip) as [Zip],
MIN(cast(PR.[Pay Begin Period] as date)) as [Pay Begin Period],
MAX(cast(PR.[Pay End Period] as date)) as [Pay End Period],
FROM (SELECT pr.*,
         ROW_NUMBER() OVER (PARTITION BY PR.SSN ORDER BY [Pay Begin Period]) 
         as seqnum
      FROM Payroll.dbo.[Table1] PR
 ) pr
WHERE (cast(PR.[Pay Begin Period] as date) > '1/1/2013' AND
    cast(PR.[Pay End Period] as date) < '12/31/2013')
GROUP BY PR.SSN;

Answer 1

类似的东西（适用于SQL Server 2012+）：

SELECT PR.SSN as SSN, 
MAX([Earliest Last Name]),
MAX([Latest Last Name]),
MIN(PR.Address) as [Address], 
MIN(PR.City) as [City], 
MIN(PR.State) as [State], 
MIN(PR.Zip) as [Zip],
MIN(cast(PR.[Pay Begin Period] as date)) as [Pay Begin Period],
MAX(cast(PR.[Pay End Period] as date)) as [Pay End Period],
FROM (SELECT pr.*,
  first_value([Last Name]) over(partition by SSN order by [Pay Begin Period]
   range between unbounded preceding  and current row) as [Earliest Last Name],
  last_value([Last Name]) over(partition by SSN order by [Pay Begin Period]
   range between current row and unbounded following) as [Latest Last Name]
  FROM Payroll.dbo.[Table1] PR
 ) pr
WHERE (cast(PR.[Pay Begin Period] as date) > '1/1/2013' AND
    cast(PR.[Pay End Period] as date) < '12/31/2013')
GROUP BY PR.SSN;