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为连续的整数,表示周数.
(
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为连续的整数,表示周数.
insert rili
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-17',1 select
d_date_f=case when min(cFlag)=0 then min(d_date) end,
d_date_t=case when max(cFlag)=1 then max(d_date) end
from
riLi
group by
datepart(week,d_date)drop table riLi/*
d_date_f d_date_t
------------------------------------
2007-03-02 2007-03-03
2007-03-06 2007-03-09
2007-03-13 2007-03-17
*/
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-17',1 select
d_date_f=case when min(cFlag)=0 then min(d_date) end,
d_date_t=case when max(cFlag)=1 then max(d_date) end
from
riLi
group by
year(d_date),month(d_date),datepart(week,d_date)drop table riLi/*
d_date_f d_date_t
------------------------------------
2007-02-27 2007-02-28
2007-03-02 2007-03-03
2007-03-06 2007-03-09
2007-03-13 2007-03-17
*/
你的方法还勉强行得通,但离我的要求还差一点.我再具体描述下:
1 自然周是指星期一到星期七;
2 cflageDate没有取出出来,它为下一周或以后的cflag标志为1的最小日期;
3 endDate要取标志位为1的大于当前日期并与当前日期在一周的并在本月的日期.
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 adrop 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
*/
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 adrop 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
*/