查询连续N天有考勤记录的数据
作者:admin 日期:2013-06-07
查询连续N天有考勤记录的数据
with t
as(
select
px=DATEADD(DD,-ROW_NUMBER()over(partition by Pid
order by [RDate] asc),[RDate]),
[Pid], [RDate]
from Att_Raw_Data
where [RDate]>'2013-05-01'
group by rdate ,pid
),
m as(
select [Pid],MIN([RDate]) as StartTime,MAX([RDate]) as EndTime
from t
group by px,[Pid]
)
select [Pid],DATEDIFF(DD,StartTime,EndTime)+1 as 连续天数,StartTime, EndTime
from m
where DATEDIFF(DD,StartTime,EndTime)>=6 --连续六天
order by pid asc,连续天数 desc
结果:
取各分类前三条记录
select *
from HR_EMAGAZINE a
where title in (select top 3 title from HR_EMAGAZINE where Catalog_ID=a.Catalog_ID order by Catalog_ID asc) order by Catalog_ID asc
复制内容到剪贴板
程序代码

with t
as(
select
px=DATEADD(DD,-ROW_NUMBER()over(partition by Pid
order by [RDate] asc),[RDate]),
[Pid], [RDate]
from Att_Raw_Data
where [RDate]>'2013-05-01'
group by rdate ,pid
),
m as(
select [Pid],MIN([RDate]) as StartTime,MAX([RDate]) as EndTime
from t
group by px,[Pid]
)
select [Pid],DATEDIFF(DD,StartTime,EndTime)+1 as 连续天数,StartTime, EndTime
from m
where DATEDIFF(DD,StartTime,EndTime)>=6 --连续六天
order by pid asc,连续天数 desc
结果:
取各分类前三条记录
复制内容到剪贴板
程序代码

select *
from HR_EMAGAZINE a
where title in (select top 3 title from HR_EMAGAZINE where Catalog_ID=a.Catalog_ID order by Catalog_ID asc) order by Catalog_ID asc
[本日志由 admin 于 2013-06-07 08:57 PM 更新]






评论: 0 | 引用: 0 | 查看次数: 4133
发表评论