sqlserver获取时间段之间的日期明细

-----------获取日期段的 每一天明细天数数据 ------declare &＃64;cuid int,&＃64;mealid varchar(30),&＃64;residedate datetime,&＃64;planleavedate datetime,&＃64;residedays int,&＃64;bbid varchar(10),&＃64;roomname varchar(20),&＃64;fdate datetime ,&＃64;ldate datetime--第一天,最后一天天SELECT &＃64;fdate&＃61;DATEADD(MM,DATEDIFF(MM,0,&＃64;bdate),0),&＃64;ldate &＃61; DATEADD(MS,-3,DATEADD(MM, DATEDIFF(m,0,&＃64;bdate)&＃43;1, 0))----有N个客户N个时间段&＃xff0c;游标循环插入临时表DECLARE pre_ScheduleList cursor local for select a.roomname,a.bid,a.ResideDate,a.cuid,b.entryid,a.planleavedate &＃43; 1 as planleavedate ,datediff(day,a.ResideDate,a.planleavedate) residedays from tn_bas_customer a join tn_crm_order_h b on b.cuid &＃61; a.cuidwhere a.status in(2,3,4)and a.bid &＃61; &＃64;bidand (a.ResideDate <&＃61; &＃64;ldate and a.planleavedate >&＃61; &＃64;fdate) ;OPEN pre_ScheduleList; FETCH NEXT FROM pre_ScheduleList INTO &＃64;roomname,&＃64;bbid,&＃64;residedate,&＃64;cuid,&＃64;mealid,&＃64;planleavedate,&＃64;residedays;WHILE (&＃64;&＃64;FETCH_STATUS &＃61; 0)BEGIN--获取明细天数 写入临时表insert into #tn_bas_roomscheduler(CuId,RsDate,PreOrderId,bid,roomname)select &＃64;cuid,CONVERT(VARCHAR (10),dateadd(d,number,&＃64;residedate),23)AS RsDate,&＃64;mealid,&＃64;bbid,&＃64;roomnamefrom master.dbo.spt_values where type &＃61;&＃39;p&＃39; and number between 0 and &＃64;residedays FETCH NEXT FROM pre_ScheduleList INTO &＃64;roomname,&＃64;bbid, &＃64;residedate,&＃64;cuid,&＃64;mealid,&＃64;planleavedate,&＃64;residedays;END;close pre_ScheduleList deallocate pre_ScheduleList