DECLARE @birthday datetime,@stat datetime,@end datetime,@statbirthday datetime,@endbirthday datetime,@thirdbirthday datetime,@firthbirthday datetime, @year int,@month int , @day int,@str varchar(20),@total int,@firthmonth int,@thirmonth int,@now datetime,@vipno nvarchar(10) --参数set @vipno='8888888'set @now='2014-01-25'set @year=Year(@now)select @birthday=birthday from vip1 where vipno=@vipnoset @month=month(@birthday)set @day=day(@birthday)set @str=cast(@year as varchar(4))+'-'+cast(@month as varchar(2))+'-'+cast(@day as varchar(2))set @birthday=cast(@str as datetime)--會員生日有效生首日起至3個月內有效set @firthbirthday=DATEADD(mm, DATEDIFF(mm,0,@birthday)-2, 0)--DATEADD(mm, DATEDIFF(mm,0,@birthday), 0) --当月的第一天--set @thirdbirthday=DATEADD(ms,-3,DATEADD(mm, DATEDIFF(m,0,getdate())+3, 0)) --后三个月最后的一天SELECT @firthbirthday=DATEADD(MONTH,-2,DATEADD(MONTH,DATEDIFF(MONTH,0,@birthday),0))--select cast(@firthbirthday as datetime)--set @firthbirthday=DATEADD(MONTH,-2,DATEADD(MONTH,DATEDIFF(MONTH,0,@birthday),0))--DATEADD(Month,0,CONVERT(datetime,CONVERT(char(8),getdate(),120)+'1'))--select @firthbirthday--DATEADD(mm,0, DATEDIFF(mm,0,@birthday), 0) --前三个月的第一天--set @firthbirthday=convert(datetime, @thirdbirthday)--SELECT 三個月有效 塗聚文 涂聚文set @firthmonth=month(@now)-2set @thirmonth=month(@now)set @thirdbirthday=DATEADD(ms,-3,DATEADD(mm, DATEDIFF(mm,0,@now)+1, 0)) --当月最后的一天set @thirdbirthday=cast(@thirdbirthday as datetime)set @statbirthday=DATEADD(mm, DATEDIFF(mm,0,@birthday)-13, 0)set @endbirthday=DATEADD(ms,-3,DATEADD(mm, DATEDIFF(mm,0,@birthday)-1, 0))--select @statbirthday,@endbirthday,@firthbirthday,@thirdbirthday,@birthday,@firthmonth,@thirmonth--以月份來吧SELECT * FROM View_birthdayVipdlyList WHERE DATEDIFF(month,NowBirthday,@now)>=0 and DATEDIFF(month,NowBirthday,@now)<=2 and vipno=@vipno order by VipBirthdayDate desc--SELECT * FROM vip1 WHERE indate>@statbirthday AND indate<@endbirthday and month(birthday)>=@firthmonth and month(birthday)<=@thirmonth --and vipno=@vipno--SELECT @total=ISNULL(SUM(amount),0) FROM View_birthdayVipdlyList WHERE indate>@statbirthday AND indate<@endbirthday and month(birthday)>=@firthmonth and month(birthday)<=@thirmonth and vipno=@vipno--SELECT @total=@total*.01 SELECT count(*) as 'total' FROM View_birthdayVipdlyList WHERE DATEDIFF(month,NowBirthday,getdate())>=0 and DATEDIFF(month,NowBirthday,getdate())<=2 and vipno=@vipnoif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetVipNowBirthday]') and xtype in (N'FN', N'IF', N'TF'))drop function [dbo].[GetVipNowBirthday]GOCREATE function GetVipNowBirthday( @vipno nvarchar(10), --参数 @now datetime)returns datetimeASbegindeclare @NowBirthday datetime, @birthday datetime,@stat datetime,@end datetime,@statbirthday datetime,@endbirthday datetime,@thirdbirthday datetime,@firthbirthday datetime, @year int,@month int , @day int,@str varchar(40),@total decimalselect @birthday=birthday from vip1 where vipno=@vipno and birthday is not nullset @year=Year(@now)if month(@now)=1 and month(@birthday)=12begin set @year=@year-1endif month(@now)=1 and month(@birthday)=11begin set @year=@year-1endif month(@now)=2 and month(@birthday)=12 begin set @year=@year-1endset @month=month(@birthday)set @day=day(@birthday)set @str=cast(@year as varchar(4))+'-'+cast(@month as varchar(2))+'-'+cast(@day as varchar(2))set @NowBirthday=convert(datetime,@str) ---cast(@str as datetime)return @NowBirthdayendGOselect top 100 vipno,birthday,[dbo].[GetVipNowBirthday](vipno,'2014-03-23') as 'now' from dbo.vip1 where month(birthday)=12 or month(birthday)=11
