关于生日在一个时间段内出现的次数，求高手，最简单的方法

[jiemo587]

遇到一个问题，有客户表（customer），数据量较大，含字段：唯一标识会员卡号（customersid）,注册时间（create_time），生日（birthday）。现在我要统计：会员注册之后截止至时间2011年4月30日的会员经历的生日次数。

[ssp2OO9]

select 会员卡号,次数=datediff(year,birthday,'2011-04-30')-
                    datediff(year,create_time,'2011-04-30')
from tb

[xuam]

色狼出来了.
[Quote=引用 1 楼 ssp2oo9 的回复:]
SQL code
select 会员卡号,次数=datediff(year,birthday,'2011-04-30')-
datediff(year,create_time,'2011-04-30')
from tb
[/Quote]

[jiemo587]

求高手，在线等，看好需求，不是那么简单，召唤牛人

[ssp2OO9]

--修改下

select 会员卡号,次数=datediff(year,birthday,'2011-04-30')-
                    datediff(year,birthday,create_time)
from tb

[jiemo587]

希望明天早上能够看到我想要的答案.
注意月份和日期，和会员是否从注册时间到截止时间过几个生日是有关系的，如，会员生日：1988-04-01，注册时间：2009-03-01，截止时间：2011-04-30，顾客所以在注册会员后可以有3次生日，并送礼品。
来高手吧，我明天还要等着下菜了，效率越高越好。

[riyao]

select datediff(dd,birthday,'2011-04-30')/365+1

[chinasingle]

[Quote=引用 4 楼 ssp2oo9 的回复:]

--修改下SQL code
select 会员卡号,次数=datediff(year,birthday,'2011-04-30')-
datediff(year,birthday,create_time)
from tb
[/Quote]
~

[yyoinge]

select customersid, year('2011-04-30') - year(create_time) -1 
+ case when convert(varchar(5), birthday, 101) >= convert(varchar(5), create_time, 101) then 1 else 0 end
+ case when convert(varchar(5), birthday, 101) <= convert(varchar(5), cast('2011-04-30' as datetime), 101) then 1 else 0 end
from customer

[yyoinge]

select customersid,
case when birthday > '2011-04-30' or '2011-04-30' < create_time then 0 --截止日期小于注册日期，或者生日在截止日期之后，则为0
else 
	--1、中间年份有多少年则过几次生日（从生日或者注册日期中年份较晚的那个算起）
	 year('2011-04-30') - case when year(create_time) < year(birthday) then year(birthday) else year(create_time) end -1  
	--2、注册那年有没有过生日
	+ case when convert(varchar(5), birthday, 101) >= convert(varchar(5), create_time, 101) then 1 else 0 end
	--3、截止日期那年有没有过生日
	+ case when convert(varchar(5), birthday, 101) <= convert(varchar(5), cast('2011-04-30' as datetime), 101) then 1 else 0 end 
end as 生日次数
from customer

[riyao]

考虑全排列~

declare @regdate datetime,@birthday datetime,@todate datetime

set @regdate='2010-04-29'
set @birthday='1988-04-30'
set @todate='2011-04-30'


select case 
--a 注册日期 b 生日 c 给定日期
--abc
when right(convert(varchar(10),@birthday,120),5)>=right(convert(varchar(10),@regdate,120),5) and right(convert(varchar(10),@birthday,120),5)<=right(convert(varchar(10),@todate,120),5) then datediff(year,@regdate,@todate)+1
--bac
when right(convert(varchar(10),@birthday,120),5)<=right(convert(varchar(10),@regdate,120),5) and right(convert(varchar(10),@regdate,120),5)<right(convert(varchar(10),@todate,120),5) then datediff(year,@regdate,@todate)
--acb
when right(convert(varchar(10),@regdate,120),5)<right(convert(varchar(10),@todate,120),5) and right(convert(varchar(10),@birthday,120),5)>=right(convert(varchar(10),@todate,120),5) and datename(year,@regdate)<>datename(year,@todate) then datediff(year,@regdate,@todate)
--bca
when right(convert(varchar(10),@birthday,120),5)<=right(convert(varchar(10),@todate,120),5) and right(convert(varchar(10),@todate,120),5)<right(convert(varchar(10),@regdate,120),5)  and datename(year,@regdate)<>datename(year,@todate) then datediff(year,@regdate,@todate)
--cab
when right(convert(varchar(10),@todate,120),5)<right(convert(varchar(10),@regdate,120),5) and right(convert(varchar(10),@regdate,120),5)<=right(convert(varchar(10),@birthday,120),5) and datename(year,@regdate)<>datename(year,@todate) then datediff(year,@regdate,@todate)
--cba
when right(convert(varchar(10),@todate,120),5)<=right(convert(varchar(10),@birthday,120),5) and right(convert(varchar(10),@birthday,120),5)<=right(convert(varchar(10),@regdate,120),5) and datename(year,@regdate)<>datename(year,@todate) then datediff(year,@regdate,@todate)-1
else 0 end 

[jiemo587]

只是举个例子，我需要判断customer表（大约数据50W）每个顾客的加入会员后过的生日次数，那运行起来岂不是很慢？

[coleling]

--方法一
with cte as
(
	select a.*,dt=dateadd(yy,b.number,a.birthday) 
	from customer a, master..spt_values b 
	where b.type='P' and b.number between year(create_time)-year(birthday) and 2011-year(birthday)
)
select customerid,count(*) from cte where dt between create_time and '2011-04-01' group by customerid

--方法二
select *,cnt=datediff(yy,create_time,'2011-04-01')
	-case when dateadd(yy,2011-year(birthday),birthday)>=dateadd(yy,2011-year(create_time),create_time) then 0 else 1 end
	+case when dateadd(yy,2011-year(birthday),birthday)>'2011-04-01' then 0 else 1 end
from customer