create table riLi
(
d_date datetime,
cFlag smallint
)
create table t_week
(
weekNum int,
startDate datetime,
endDate dateTime,
flagDate datetime
)
上面是表结构,要求以riLi表的数据查询生成t_week表,具体条件是:
1.以自然周为分隔,一周中cflag为1的最小日期为startDate ,
一周中cflag为1的最大日期为endDate,下一周的cflag为1的最小日期为flagDate,
2.对于跨月的周要拆分为两周来处理.同样,对于跨年的周也要拆开来处理.
3.weekNum为连续的整数,表示周数.
------解决方案--------------------
--暂进只能做到同一周的，下班了...
create table riLi(d_date datetime,cFlag smallint)
insert rili
select '2007-2-27 ',0 union all
select '2007-2-28 ',1 union all
select '2007-3-2 ',0 union all
select '2007-3-3 ',1 union all
select '2007-3-6 ',0 union all
select '2007-3-8 ',1 union all
select '2007-3-9 ',1 union all
select '2007-3-14 ',0 union all
select '2007-3-13 ',0 union all
select '2007-3-16 ',1 union all
select '2007-3-17 ',1
select
weekNum=identity(int,1,1),
startDate=min(case when cFlag=0 then d_date end),
endDate=max(case when cFlag=1 then d_date end),
flagDate=min(case when cFlag=1 then d_date end)
into
#1
from
riLi
group by
year(d_date),month(d_date),datepart(week,d_date)
select
weekNum ,
startDate,
endDate,
flagDate=(select min(flagDate) from #1 where weekNum > a.weekNum)
from
#1 a
drop table riLi,#1
/*
weekNum startDate endDate flagDate
----------------------
1 2007-02-27 2007-02-28 2007-03-03
2 2007-03-02 2007-03-03 2007-03-08
3 2007-03-06 2007-03-09 2007-03-16
4 2007-03-13 2007-03-17 NULL
*/