如下圖，在這個表中有員工的狀態（職位rank_code、所屬分店branch），與狀態變化的生效日期（effective_date）。
程序會按照某個月份某间分店來獲取員工資料。
假如我現在要獲取所有在這個表內分店：05126，2014年2月份的員工最新資料（假如2月同一個員工有超過1條記錄，則以当月最大effective_date爲準）該如何寫？或者我生成一個臨時表增加一個列叫effective_date_enddate的話又可以怎麼做update?
--創建數據
CREATE TABLE [dbo].[t](
[staffid] [int] NULL,
[effective_date] [datetime] NULL,
[rank_code] [varchar](10) NULL,
[rank_desc] [varchar](100) NULL,
[branch] [varchar](10) NULL,
[staff_type] [varchar](10) NULL,
[effective_seq] [int] NULL
) ON [PRIMARY]
INSERT [dbo].[t] ([staffid], [effective_date], [rank_code], [rank_desc], [branch], [staff_type], [effective_seq]) VALUES (269617, CAST(0x0000A19300000000 AS DateTime), N'W10', N'主任(9+1)', N'05137', N'F', 0)
INSERT [dbo].[t] ([staffid], [effective_date], [rank_code], [rank_desc], [branch], [staff_type], [effective_seq]) VALUES (269617, CAST(0x0000A2F900000000 AS DateTime), N'W13A', N'兼職 (418)', N'05208', N'P', 0)
INSERT [dbo].[t] ([staffid], [effective_date], [rank_code], [rank_desc], [branch], [staff_type], [effective_seq]) VALUES (977205, CAST(0x0000A19300000000 AS DateTime), N'W10', N'主任(9+1)', N'05139', N'F', 0)
INSERT [dbo].[t] ([staffid], [effective_date], [rank_code], [rank_desc], [branch], [staff_type], [effective_seq]) VALUES (977205, CAST(0x0000A2C500000000 AS DateTime), N'W13A', N'兼職 (418)', N'05139', N'P', 0)
INSERT [dbo].[t] ([staffid], [effective_date], [rank_code], [rank_desc], [branch], [staff_type], [effective_seq]) VALUES (1047839, CAST(0x0000A19300000000 AS DateTime), N'W11', N'侍應生/壽司學徒(9+1)', N'05126', N'F', 0)
INSERT [dbo].[t] ([staffid], [effective_date], [rank_code], [rank_desc], [branch], [staff_type], [effective_seq]) VALUES (1047839, CAST(0x0000A2E100000000 AS DateTime), N'Q503', N'店務員/壽司製作員(9.5+1)', N'05001', N'F', 0)
INSERT [dbo].[t] ([staffid], [effective_date], [rank_code], [rank_desc], [branch], [staff_type], [effective_seq]) VALUES (1047839, CAST(0x0000A2F400000000 AS DateTime), N'Q503', N'店務員/壽司製作員(9.5+1)', N'05001', N'F', 0)
INSERT [dbo].[t] ([staffid], [effective_date], [rank_code], [rank_desc], [branch], [staff_type], [effective_seq]) VALUES (1637641, CAST(0x0000A1EE00000000 AS DateTime), N'R217', N'廚師 (9+1)', N'05207', N'F', 0)
INSERT [dbo].[t] ([staffid], [effective_date], [rank_code], [rank_desc], [branch], [staff_type], [effective_seq]) VALUES (1637641, CAST(0x0000A2C500000000 AS DateTime), N'R210', N'廚房主任(9+1)', N'05207', N'F', 0)
INSERT [dbo].[t] ([staffid], [effective_date], [rank_code], [rank_desc], [branch], [staff_type], [effective_seq]) VALUES (1637641, CAST(0x0000A2E000000000 AS DateTime), N'R210', N'廚房主任(9+1)', N'05207', N'F', 0)
------解决方案--------------------
是这样吗：
select *
from
(
select *,
ROW_NUMBER() over(partition by branch order by effective_date desc) rownum
from t
where branch ='05126' and convert(varchar(5),effective_date,120)='2014-02'
)t
where rownum = 1