一个很复杂的函数

Question

有表1：营业时间
字段：SHOP_CD            店铺code    
      DAY_OF_WEEK_CD     星期几（0:周日,1:周1,2:周2,3:周3,4:周4,5:周5,6:周6）
      OPENING_TIME       营业开始时间
      CLOSING_TIME       营业结束时间
      HOLIDAY_FLG        是否休息(0:不休息，1:休息)
      WEEK_NO            周番号
周番号
0 全部
1 第1周
2 第2周
3 第3周
4 第4周
5 第5周
其中周番号代表的是每个月中的第几周，如DAY_OF_WEEK_CD=3 ，WEEK_NO=2 ，HOLIDAY_FLG=1 就是指只有当月中的第二个星期三才休息表2：店铺临时营业时间
字段：SHOP_CD            店铺code    
      DATE               日期
      OPENING_TIME       营业开始时间
      CLOSING_TIME       营业结束时间
      HOLIDAY_FLG        是否休息(0:不休息，1:休息)
这个函数（店铺营业日一览取得）参数：
传入：shop_cd(店铺code)，year(年份)
传出：DATE               营业日期
      OPENING_TIME       营业开始时间
      CLOSING_TIME       营业结束时间
　　　DAY_OF_WEEK_CD　　　星期几
现在就是先看表２(店铺临时营业时间)中有，就先取表2 的，没有就去看表1中的周设置。 要求把一年中的所有营业日取出来，传出的参数可以是一个表的形势
小弟在这里先谢谢大家了

Answer 1

HOLIDAY_FLG        是否休息(0:不休息，1:休息)
------------
这个是什么意思?临时营业还有 休息马?

Answer 2

营业时间表会不会有矛盾的记录，比如SHOP_CD DAY_OF_WEEK_CD  OPENING_TIME CLOSING_TIME  HOLIDAY_FLG WEEK_NO 
1       1               8：30        17：30        1           1      -表示第一周周一
1       1               8：00        17：00        1           0      -表示其它周一

Answer 3

--未测试，不知道对不对
Create function fn_test(
@shop_cd varchar(20)，
@year int
)
returns @r table (
DATE datetime,
OPENING_TIME varchar(20),
CLOSING_TIME varchar(20),
DAY_OF_WEEK_CD int
)
as
begin
--产生一年的日期表
declare @t table (
date datetime,
DAY_OF_WEEK_CD int,
WEEK_NO int
)insert @t
select dateadd(day,a,cast(@year as varchar)+'-01-01'),
datepart(weekday,dateadd(day,a,cast(@year as varchar)+'-01-01'))-1,0
from (
) as t--计算周番号
update t set WEEK_NO=(select count(*) from @t where month(date)=month(t.date) and DAY_OF_WEEK_CD=t.DAY_OF_WEEK_CD and date<=t.DAte)
from @t t--查出结果
insert @r
select t.DATE,
isnull(l.OPENING_TIME,y.OPENING_TIME) as OPENING_TIME,
isnull(l.CLOSING_TIME,y.CLOSING_TIME) as CLOSING_TIME,
t.DAY_OF_WEEK_CD
from @t t left join 店铺临时营业时间 l
on t.DATE=l.DATE and l.shop_cd=@shop_cd
left join 营业时间 y
on t.DAY_OF_WEEK_CD=y.DAY_OF_WEEK_CD and (t.WEEK_NO=y.WEEK_NO or y.WEEK_NO=0) and y.shop_cd=@shop_cd--返回
returnend
go--调用方法(查2006年店铺号001)
select * from dbo.fn_test('001',2006)

Answer 4

刚才贴了个未完成的Create function fn_test(
@shop_cd varchar(20)，
@year int
)
returns @r table (
DATE datetime,
OPENING_TIME varchar(20),
CLOSING_TIME varchar(20),
DAY_OF_WEEK_CD int
)
as
begin
--产生一年的日期表
declare @t table (
date datetime,
DAY_OF_WEEK_CD int,
WEEK_NO int
)insert @t
select dateadd(day,a+10*b+100*c,cast(@year as varchar)+'-01-01'),
datepart(weekday,dateadd(day,a+10*b+100*c,cast(@year as varchar)+'-01-01'))-1,0
from (
select 0 as a
union all select 1
union all select 2
union all select 3
union all select 4
union all select 5
union all select 6
union all select 7
union all select 8
union all select 9
) as t1,(
select 0 as b
union all select 1
union all select 2
union all select 3
union all select 4
union all select 5
union all select 6
union all select 7
union all select 8
union all select 9
) as t2,(
select 0 as c
union all select 1
union all select 2
union all select 3
union all select 4
union all select 5
union all select 6
union all select 7
union all select 8
union all select 9
) as t3
where dateadd(day,a+10*b+100*c,cast(@year as varchar)+'-01-01')<cast(cast(@year as varchar)+'-01-01' as datetime)
--计算周番号
update t set WEEK_NO=(select count(*) from @t where month(date)=month(t.date) and DAY_OF_WEEK_CD=t.DAY_OF_WEEK_CD and date<=t.DAte)
from @t t--查出结果
insert @r
select t.DATE,
isnull(l.OPENING_TIME,y.OPENING_TIME) as OPENING_TIME,
isnull(l.CLOSING_TIME,y.CLOSING_TIME) as CLOSING_TIME,
t.DAY_OF_WEEK_CD
from @t t left join 店铺临时营业时间 l
on t.DATE=l.DATE and l.shop_cd=@shop_cd
left join 营业时间 y
on t.DAY_OF_WEEK_CD=y.DAY_OF_WEEK_CD and (t.WEEK_NO=y.WEEK_NO or y.WEEK_NO=0) and y.shop_cd=@shop_cd--返回
returnendgo--调用方法(查2006年店铺号001)
select * from dbo.fn_test('001',2006)

Answer 5

对不起了，打扰一下 Yang_(扬帆破浪)，生成一年的日期的代码是不是有问题呢？

Answer 6

别看了，原来的很多错误，贴个测试过的--建立环境
create table 营业时间 (
SHOP_CD varchar(20),   
DAY_OF_WEEK_CD int,
OPENING_TIME varchar(20),
CLOSING_TIME varchar(20),
HOLIDAY_FLG int,
WEEK_NO int
)go
create table 店铺临时营业时间(
SHOP_CD varchar(20),   
Date datetime,
OPENING_TIME varchar(20),
CLOSING_TIME varchar(20),
HOLIDAY_FLG int
)go--插入测试数据
insert  营业时间
select '001',0,null,null,1,0       --星期天休息
union all 
select '001',1,'8:00','17:00',0,0       --星期1上班
union all 
select '001',2,'8:00','17:00',0,0       --星期2上班
union all 
select '001',3,'8:00','17:00',0,0       --星期3上班
union all 
select '001',4,'8:00','17:00',0,0       --星期4上班
union all 
select '001',5,'8:00','17:00',0,0       --星期5上班
union all 
select '001',6,'8:00','12:00',0,0       --星期6上半天班
union all 
select '002',0,'9:00','18:00',0,0       --星期天上班
union all 
select '002',1,'9:00','18:00',0,0       --星期1上班
union all 
select '002',2,'9:00','18:00',0,0       --星期2上班
union all 
select '002',3,'9:00','18:00',0,0       --星期3上班
union all 
select '002',4,'9:00','18:00',0,0       --星期4上班
union all 
select '002',5,'9:00','18:00',0,1       --第一个星期5上班
union all 
select '002',5,null,null,1,2       --第二个星期5休息
union all 
select '002',5,'9:00','18:00',0,3       --第三个星期5上班
union all 
select '002',5,null,null,1,4       --第四个星期5休息
union all 
select '002',5,'9:00','18:00',0,5       --第五个星期5上班
union all 
select '002',6,null,null,1,0       --星期6休息goinsert 店铺临时营业时间
select '001','2006-12-10','7:00','16:00',0      
union all
select '001','2006-12-11',null,null,1
union all
select '002','2006-12-15',null,null,1
union all
select '002','2006-12-16','8:30','17:30',0go--修改后的函数
alter function fn_test(
@shop_cd varchar(20),
@year int
)
returns @r table (
DATE datetime,
OPENING_TIME varchar(20),
CLOSING_TIME varchar(20),
DAY_OF_WEEK_CD int
)
as
begin
--产生一年的日期表
declare @t table (
date datetime,
DAY_OF_WEEK_CD int,
WEEK_NO int
)insert @t
select dateadd(day,a+10*b+100*c,cast(@year as varchar)+'-01-01'),
datepart(weekday,dateadd(day,a+10*b+100*c,cast(@year as varchar)+'-01-01'))-1,0
from (
select 0 as a
union all select 1
union all select 2
union all select 3
union all select 4
union all select 5
union all select 6
union all select 7
union all select 8
union all select 9
) as t1,(
select 0 as b
union all select 1
union all select 2
union all select 3
union all select 4
union all select 5
union all select 6
union all select 7
union all select 8
union all select 9
) as t2,(
select 0 as c
union all select 1
union all select 2
union all select 3
union all select 4
union all select 5
union all select 6
union all select 7
union all select 8
union all select 9
) as t3
where dateadd(day,a+10*b+100*c,cast(@year as varchar)+'-01-01')<cast(cast(@year+1 as varchar)+'-01-01' as datetime)
--计算周番号
update t set WEEK_NO=(select count(*) from @t where month(date)=month(t.date) and DAY_OF_WEEK_CD=t.DAY_OF_WEEK_CD and date<=t.DAte)
from @t t--插入结果，筛选上班的日期
insert @r
select t.DATE,
isnull(l.OPENING_TIME,y.OPENING_TIME) as OPENING_TIME,
isnull(l.CLOSING_TIME,y.CLOSING_TIME) as CLOSING_TIME,
t.DAY_OF_WEEK_CD
from @t t left join 店铺临时营业时间 l
on t.DATE=l.DATE and l.shop_cd=@shop_cd
left join 营业时间 y
on t.DAY_OF_WEEK_CD=y.DAY_OF_WEEK_CD and (t.WEEK_NO=y.WEEK_NO or y.WEEK_NO=0) and y.shop_cd=@shop_cd
where isnull(isnull(l.HOLIDAY_FLG,y.HOLIDAY_FLG),1)=0  --未指定算休息--返回
returnend
go--调用测试1
select * from dbo.fn_test('001',2006)
order by date
go--调用测试2
select * from dbo.fn_test('002',2006)
order by date
go--删除环境
drop function fn_test
go
drop table 营业时间
go
drop table 店铺临时营业时间
go