求连续日期天数

jyh070207 2011-08-23 02:21:21

求连续日期天数

求连续日期天数,同时,到计数到8后,
又要重1开始计数.有日期空缺的也从1重新开始计算

数据如下:(同一日期可以有多条记录,有多条时只算一条即可)
dt
2011-08-01
2011-08-02
2011-08-02
2011-08-03
2011-08-04
2011-08-05
2011-08-06
2011-08-07
2011-08-08
2011-08-08
2011-08-09
2011-08-10
2011-08-12
2011-08-13
2011-08-14
2011-08-15
2011-08-16
2011-08-17
2011-08-18
2011-08-19
2011-08-20
2011-08-21

结果如下:
dt/天数
2011-08-01 1
2011-08-02 2
2011-08-02 2
2011-08-03 3
2011-08-04 4
2011-08-05 5
2011-08-06 6
2011-08-07 7
2011-08-08 8
2011-08-08 8
2011-08-09 1--计数到8后,下一个日期从1开始计数
2011-08-10 2
2011-08-12 1--中间没有11号资料,重新计数
2011-08-13 2
2011-08-14 3
2011-08-15 4
2011-08-16 5
2011-08-17 6
2011-08-18 7
2011-08-19 8
2011-08-20 1--计数到8后,下一个日期从1开始计数
2011-08-21 2

示例数据



create table #t(id int identity(1,1),dt datetime )

insert into #t(dt)

select '2011-08-01'

union all

select '2011-08-02'

union all

select '2011-08-02'

union all

select '2011-08-03'

union all

select '2011-08-04'

union all

select '2011-08-05'

union all

select '2011-08-06'

union all

select '2011-08-07'

union all

select '2011-08-08'

union all

select '2011-08-08'

union all

select '2011-08-09'

union all

select '2011-08-10'

union all

select '2011-08-12'

union all

select '2011-08-13'

union all

select '2011-08-14'

union all

select '2011-08-15'

union all

select '2011-08-16'

union all

select '2011-08-17'

union all

select '2011-08-18'

union all

select '2011-08-19'

union all

select '2011-08-20'

union all

select '2011-08-21'

...全文

250 22 打赏收藏转发到动态举报

写回复

用AI写文章

22 条回复

切换为时间正序

请发表友善的回复…

发表回复

jyh070207 2011-08-23

打赏
举报

感谢各位帮忙,尤其就ssp2009及小三兄，两位的结果都是正确的，谢谢！！！

怪众生太美丽 2011-08-23

打赏
举报

俺是风骚哥...

gw6328 2011-08-23

打赏
举报

[Quote=引用 18 楼 guoweifyj 的回复:]

小三兄的代码算是看懂了...哈哈哈哥每次这都是帮楼主问问题啊..别误会哥不是楼主...
引用 17 楼 jinfengyiye 的回复:
SQL code

;with cte as (
select dr=dense_rank() over(order by a.dt),a.*,DATEDIFF(dd,'2011-01-01',a.dt) as x from #t a lef……
[/Quote]
那你是游标哥？

gw6328 2011-08-23

打赏
举报

[Quote=引用 17 楼 jinfengyiye 的回复:]

SQL code

;with cte as (
select dr=dense_rank() over(order by a.dt),a.*,DATEDIFF(dd,'2011-01-01',a.dt) as x from #t a left join #t b on a.id=b.id-1
)
select rn=(dense_rank() over(partition by X-dr ……
[/Quote]

不好思，把中间那个连接不要，刚才没有删到代码





;with cte as (

 select dr=dense_rank() over(order by a.dt),a.*,DATEDIFF(dd,'2011-01-01',a.dt) as x from #t a

)

select rn=(dense_rank() over(partition by X-dr order by dr)-1)%8+1,a.id,a.dt from cte a;



/*

rn                   id          dt

-------------------- ----------- -----------------------

1                    1           2011-08-01 00:00:00.000

2                    2           2011-08-02 00:00:00.000

2                    3           2011-08-02 00:00:00.000

3                    4           2011-08-03 00:00:00.000

4                    5           2011-08-04 00:00:00.000

5                    6           2011-08-05 00:00:00.000

6                    7           2011-08-06 00:00:00.000

7                    8           2011-08-07 00:00:00.000

8                    9           2011-08-08 00:00:00.000

8                    10          2011-08-08 00:00:00.000

1                    11          2011-08-09 00:00:00.000

2                    12          2011-08-10 00:00:00.000

1                    13          2011-08-12 00:00:00.000

2                    14          2011-08-13 00:00:00.000

3                    15          2011-08-14 00:00:00.000

4                    16          2011-08-15 00:00:00.000

5                    17          2011-08-16 00:00:00.000

6                    18          2011-08-17 00:00:00.000

7                    19          2011-08-18 00:00:00.000

8                    20          2011-08-19 00:00:00.000

1                    21          2011-08-20 00:00:00.000

2                    22          2011-08-21 00:00:00.000

*/

怪众生太美丽 2011-08-23

打赏
举报

小三兄的代码算是看懂了...哈哈哈哥每次这都是帮楼主问问题啊..别误会哥不是楼主...
[Quote=引用 17 楼 jinfengyiye 的回复:]
SQL code

;with cte as (
select dr=dense_rank() over(order by a.dt),a.*,DATEDIFF(dd,'2011-01-01',a.dt) as x from #t a left join #t b on a.id=b.id-1
)
select rn=(dense_rank() over(partition by ……
[/Quote]

gw6328 2011-08-23

打赏
举报



;with cte as (

 select dr=dense_rank() over(order by a.dt),a.*,DATEDIFF(dd,'2011-01-01',a.dt) as x from #t a left join #t b on a.id=b.id-1

)

select rn=(dense_rank() over(partition by X-dr order by dr)-1)%8+1,a.id,a.dt from cte a;

-- 参考上面小三的代码

-- 楼主是否是游标哥？

怪众生太美丽 2011-08-23

打赏
举报

游标确实不太好用,这样以后就可以借鉴借鉴...[Quote=引用 14 楼 ssp2009 的回复:]
update也是逐行更新，每更新一行，都赋值给变量，用变量跟下一条对比
[/Quote]

怪众生太美丽 2011-08-23

打赏
举报

为人民服务...AcHerat也可以,没看懂,继续观摩中^
[Quote=引用 13 楼 ssp2009 的回复:]
引用 7 楼 guoweifyj 的回复:
我还以为得用游标...哈哈哥又学到了..

引用 3 楼 ssp2009 的回复:
SQL code

select *,no=null into #tb from #t

declare @num int=0,@dt datetime
update #tb set no=@num,
@num=case when @num<8 an……
[/Quote]

快溜 2011-08-23

打赏
举报

update也是逐行更新，每更新一行，都赋值给变量，用变量跟下一条对比

快溜 2011-08-23

打赏
举报

[Quote=引用 7 楼 guoweifyj 的回复:]
我还以为得用游标...哈哈哥又学到了..

引用 3 楼 ssp2009 的回复:
SQL code

select *,no=null into #tb from #t

declare @num int=0,@dt datetime
update #tb set no=@num,
@num=case when @num<8 and datediff(day,@dt,dt)=……
[/Quote]要交钱的

怪众生太美丽 2011-08-23

打赏
举报

[Quote=引用 11 楼 jyh070207 的回复:]
引用 3 楼 ssp2009 的回复:
SQL code
select *,no=null into #tb from #t

declare @num int=0,@dt datetime
update #tb set no=@num,
@num=case when @num<8 and datediff(day,@dt,dt)=1 then @num+1
wh……

ssp2……
[/Quote]
他这代码写的跟游标的作用是相同的,第一次更新的时候两dt天数差不为一的取else值，
后面每次就是取当前dt和赋值后的上一个dt作对比..

jyh070207 2011-08-23

打赏
举报

[Quote=引用 3 楼 ssp2009 的回复:]
SQL code
select *,no=null into #tb from #t

declare @num int=0,@dt datetime
update #tb set no=@num,
@num=case when @num<8 and datediff(day,@dt,dt)=1 then @num+1
wh……
[/Quote]
ssp2009 你好,你的结果是正确的,烦请解释一下，那句update是如何更新的,谢谢！！！

geniuswjt 2011-08-23

打赏
举报

mark

怪众生太美丽 2011-08-23

打赏
举报

ssp方法好啊...

AcHerat 元老 2011-08-23

打赏
举报



--2005



create table #t(id int identity(1,1),dt datetime )

insert into #t(dt)

select '2011-08-01'

union all

select '2011-08-02'

union all

select '2011-08-02'

union all

select '2011-08-03'

union all

select '2011-08-04'

union all

select '2011-08-05'

union all

select '2011-08-06'

union all

select '2011-08-07'

union all

select '2011-08-08'

union all

select '2011-08-08'

union all

select '2011-08-09'

union all

select '2011-08-10'

union all

select '2011-08-12'

union all

select '2011-08-13'

union all

select '2011-08-14'

union all

select '2011-08-15'

union all

select '2011-08-16'

union all

select '2011-08-17'

union all

select '2011-08-18'

union all

select '2011-08-19'

union all

select '2011-08-20'

union all

select '2011-08-21'

go



declare @md datetime

select @md = min(dt) from #t

;with ach as

(

	select convert(varchar(10),dt,120) dt,

		(datediff(dd,@md,convert(varchar(10),dt,120))-row_number() over (order by convert(varchar(10),dt,120))) as rid

	from #t

	group by convert(varchar(10),dt,120)

)



select a.id,a.dt,b.px

from #t a left join 

	(

	select *,(row_number() over (partition by rid order by dt)-1)%8 + 1 as px

	from ach

) b on convert(varchar(10),a.dt,120) = b.dt



drop table #t



/***********************



id          dt                      px

----------- ----------------------- --------------------

1           2011-08-01 00:00:00.000 1

2           2011-08-02 00:00:00.000 2

3           2011-08-02 00:00:00.000 2

4           2011-08-03 00:00:00.000 3

5           2011-08-04 00:00:00.000 4

6           2011-08-05 00:00:00.000 5

7           2011-08-06 00:00:00.000 6

8           2011-08-07 00:00:00.000 7

9           2011-08-08 00:00:00.000 8

10          2011-08-08 00:00:00.000 8

11          2011-08-09 00:00:00.000 1

12          2011-08-10 00:00:00.000 2

13          2011-08-12 00:00:00.000 1

14          2011-08-13 00:00:00.000 2

15          2011-08-14 00:00:00.000 3

16          2011-08-15 00:00:00.000 4

17          2011-08-16 00:00:00.000 5

18          2011-08-17 00:00:00.000 6

19          2011-08-18 00:00:00.000 7

20          2011-08-19 00:00:00.000 8

21          2011-08-20 00:00:00.000 1

22          2011-08-21 00:00:00.000 2



(22 行受影响)

怪众生太美丽 2011-08-23

打赏
举报

我还以为得用游标...哈哈哥又学到了..[Quote=引用 3 楼 ssp2009 的回复:]
SQL code

select *,no=null into #tb from #t

declare @num int=0,@dt datetime
update #tb set no=@num,
@num=case when @num<8 and datediff(day,@dt,dt)=1 then @num+1
……
[/Quote]

jyh070207 2011-08-23

打赏
举报

[Quote=引用 5 楼 shmilywcd 的回复:]
SQL code

select *, row_number() over(PARTITION BY (id-1)/8 order by dt asc) from #t
[/Quote]
1:同一日期可以有多条记录,有多条时只算一条即可
2:如果中间日期有中断,需要从1重新开始计算,如示例中没有8.11

天-笑 2011-08-23

打赏
举报



select  *, row_number() over(PARTITION BY (id-1)/8 order by dt asc)  from #t

天-笑 2011-08-23

打赏
举报



create table #t(id int identity(1,1),dt datetime )

insert into #t(dt)

select '2011-08-01'

union all

select '2011-08-02'

union all

select '2011-08-03'

union all

select '2011-08-04'

union all

select '2011-08-05'

union all

select '2011-08-06'

union all

select '2011-08-07'

union all

select '2011-08-08'

union all

select '2011-08-09'

union all

select '2011-08-10'

union all

select '2011-08-11'

union all

select '2011-08-12'

union all

select '2011-08-13'

union all

select '2011-08-14'

union all

select '2011-08-15'

union all

select '2011-08-16'

union all

select '2011-08-17'

union all

select '2011-08-18'

union all

select '2011-08-19'

union all

select '2011-08-20'

union all

select '2011-08-21'





select  *, row_number() over(PARTITION BY convert(int,(id-1)/8)  order by dt asc)  from #t 



drop table #t

---------------------------------



1	2011-08-01 00:00:00.000	1

2	2011-08-02 00:00:00.000	2

3	2011-08-03 00:00:00.000	3

4	2011-08-04 00:00:00.000	4

5	2011-08-05 00:00:00.000	5

6	2011-08-06 00:00:00.000	6

7	2011-08-07 00:00:00.000	7

8	2011-08-08 00:00:00.000	8

9	2011-08-09 00:00:00.000	1

10	2011-08-10 00:00:00.000	2

11	2011-08-11 00:00:00.000	3

12	2011-08-12 00:00:00.000	4

13	2011-08-13 00:00:00.000	5

14	2011-08-14 00:00:00.000	6

15	2011-08-15 00:00:00.000	7

16	2011-08-16 00:00:00.000	8

17	2011-08-17 00:00:00.000	1

18	2011-08-18 00:00:00.000	2

19	2011-08-19 00:00:00.000	3

20	2011-08-20 00:00:00.000	4

21	2011-08-21 00:00:00.000	5

快溜 2011-08-23

打赏
举报

select *,no=null into #tb from #t



declare @num int=0,@dt datetime

update #tb set no=@num,

               @num=case when @num<8 and datediff(day,@dt,dt)=1 then @num+1

                when datediff(day,@dt,dt)=0 then @num else 1 end,

                @dt=dt

select * from #tb         

drop table #tb

/*

id          dt                      no

----------- ----------------------- -----------

1           2011-08-01 00:00:00.000 1

2           2011-08-02 00:00:00.000 2

3           2011-08-02 00:00:00.000 2

4           2011-08-03 00:00:00.000 3

5           2011-08-04 00:00:00.000 4

6           2011-08-05 00:00:00.000 5

7           2011-08-06 00:00:00.000 6

8           2011-08-07 00:00:00.000 7

9           2011-08-08 00:00:00.000 8

10          2011-08-08 00:00:00.000 8

11          2011-08-09 00:00:00.000 1

12          2011-08-10 00:00:00.000 2

13          2011-08-12 00:00:00.000 1

14          2011-08-13 00:00:00.000 2

15          2011-08-14 00:00:00.000 3

16          2011-08-15 00:00:00.000 4

17          2011-08-16 00:00:00.000 5

18          2011-08-17 00:00:00.000 6

19          2011-08-18 00:00:00.000 7

20          2011-08-19 00:00:00.000 8

21          2011-08-20 00:00:00.000 1

22          2011-08-21 00:00:00.000 2



(22 行受影响)