22,207
社区成员
发帖
与我相关
我的任务
分享
declare @date datetime='2013-05-01' --判断月份
declare @date1 datetime=dateadd(month,1,@date);
with tc as
(select date=DATEADD(day,number,@date)
from master..spt_values
where type='p' and DATEADD(day,number,@date) <@date1)
,td as
(select * from tc left join tb a on tc.date>=a.excutedate)
select *
from td a
where id=(select max(id) from td where date=a.date)
[/quote] 这个看懂了
大神 怎么联系你 留个联系方式啊
declare @date datetime='2013-05-01' --判断月份
declare @date1 datetime=dateadd(month,1,@date);
with tc as
(select date=DATEADD(day,number,@date)
from master..spt_values
where type='p' and DATEADD(day,number,@date) <@date1)
,td as
(select * from tc left join tb a on tc.date>=a.excutedate)
select *
from td a
where id=(select max(id) from td where date=a.date)
if exists(select 1 from sys.tables where name='tb')
drop table tb
go
with tb(ID,beginTime,endTime,excutedate,week1,week2,week3,week4,week5,week6,week7)as(
select 1,'8:00','18:00','2013-1-1',1,1,1,1,1,0,0 union all
select 2,'8:30','17:30','2013-5-4',1,1,1,1,1,1,0 union all
select 3,'8:30','18:00','2013-5-20',1,1,1,1,1,0,1)
select * into tb from tb
go
declare @date datetime='2013-05-01' --判断月份
declare @date1 datetime=dateadd(month,1,@date);
with tc as
(select date=DATEADD(day,number,@date)
from master..spt_values
where type='p' and DATEADD(day,number,@date) <@date1)
,td as
(select * from tc left join tb a on tc.date>=a.excutedate)
select convert(date,date,120) 日期,DATEPART(WEEKDAY,date-1) 星期几,
case when CHARINDEX(convert(varchar,datepart(weekday,date)-1),
(case when week1=1 then '1' else '' end+
case when week2=1 then '2' else '' end+
case when week3=1 then '3' else '' end+
case when week4=1 then '4' else '' end+
case when week5=1 then '5' else '' end+
case when week6=1 then '6' else '' end+
case when week7=1 then '0' else '' end))>0 then '上班'
else '不上班'end 是否上班,begintime 上班时间,endtime 下班时间
from td a
where not exists
(select 1 from td where a.date=date and ID>a.ID)
/*
日期 星期几 是否上班 上班时间 下班时间
---------- ----------- ------ ---- -----
2013-05-01 3 上班 8:00 18:00
2013-05-02 4 上班 8:00 18:00
2013-05-03 5 上班 8:00 18:00
2013-05-04 6 上班 8:30 17:30
2013-05-05 7 不上班 8:30 17:30
2013-05-06 1 上班 8:30 17:30
2013-05-07 2 上班 8:30 17:30
2013-05-08 3 上班 8:30 17:30
2013-05-09 4 上班 8:30 17:30
2013-05-10 5 上班 8:30 17:30
2013-05-11 6 上班 8:30 17:30
2013-05-12 7 不上班 8:30 17:30
2013-05-13 1 上班 8:30 17:30
2013-05-14 2 上班 8:30 17:30
2013-05-15 3 上班 8:30 17:30
2013-05-16 4 上班 8:30 17:30
2013-05-17 5 上班 8:30 17:30
2013-05-18 6 上班 8:30 17:30
2013-05-19 7 不上班 8:30 17:30
2013-05-20 1 上班 8:30 18:00
2013-05-21 2 上班 8:30 18:00
2013-05-22 3 上班 8:30 18:00
2013-05-23 4 上班 8:30 18:00
2013-05-24 5 上班 8:30 18:00
2013-05-25 6 不上班 8:30 18:00
2013-05-26 7 上班 8:30 18:00
2013-05-27 1 上班 8:30 18:00
2013-05-28 2 上班 8:30 18:00
2013-05-29 3 上班 8:30 18:00
2013-05-30 4 上班 8:30 18:00
2013-05-31 5 上班 8:30 18:00
(31 行受影响)
*/
好费时啊...
declare @date datetime='2013-05-01' --判断月份
declare @date1 datetime=dateadd(month,1,@date);
with tc as
(select date=DATEADD(day,number,@date)
from master..spt_values
where type='p' and DATEADD(day,number,@date) <@date1)
,td as
(select * from tc left join tb a on tc.date>=a.excutedate)
select * from td a
你看看不做任何处理的数据,就知道是啥意思了