当前位置: 动力学知识库 > 问答 > 编程问答 >

sql - How to add numbers to grouped columns that might repeat, in chronological order

问题描述:

Query:

DECLARE @EmploymentLength TABLE

(

EmployeeID INT,

Date DATE,

DateFlag CHAR(1),

RowNumber INT

);

INSERT INTO @EmploymentLength

(

EmployeeID,

Date,

DateFlag

)

SELECT z.EmployeeID,

z.Date,

z.DateFlag

FROM (SELECT EmployeeId,

HireDate AS Date,

'H' AS DateFlag

FROM dbo.Employment

WHERE EmployeeId = 328195

AND HireDate IS NOT NULL

UNION

SELECT EmployeeId,

TerminationDate AS Date,

'T' AS DateFlag

FROM dbo.Employment

WHERE EmployeeId = 328195

AND TerminationDate IS NOT NULL) z;

SELECT *

FROM @EmploymentLength

ORDER BY Date;

Result:

I need this to end up like this:

After this is done, I can group by the RowNumber to get the MAX() and MIN() of each row number group (1, 2, 3...).

If the last 2 records were "T", then I'd have 2 4's and so on.

EDIT

To clarify, I need to group each DateFlag and add a number to each group but it has to be in order ... (by date).

So in this example, you have 2 records that fall into the first group (group 1).

Then one record for group 2 (T)

Then one record for group 3 (H)

Then one record for group 4 (T)

网友答案:

You can do this with a difference of row_number() values to describe the group and then an additional dense_rank() to enumerate them. I think the following works:

select el.*, dense_rank() over (partition by EmployeeId order by grp)
from (select el.*,
             (row_number() over (partition by EmployeeId order by date) - 
              row_number() over (partition by EmployeeId, DateFlag order by date)
             ) as grp
      from @EmploymentLength el
     ) el;

There are situations where the grp value might actually repeat for different groups within an employee. In that case, it is better to use the minimum date for each group for the enumeration:

select el.*, dense_rank() over (partition by EmployeeId, order by grpdate)
from (select el.*, min(date) over (partition by EmployeeId, DateFlag, grp) as grpdate
      from (select el.*,
                   (row_number() over (partition by EmployeeId order by date) - 
                    row_number() over (partition by EmployeeId, DateFlag order by date)
                   ) as grp
            from @EmploymentLength el
           ) el
     ) el
分享给朋友:
您可能感兴趣的文章:
随机阅读: