日期:2014-05-18 浏览次数:20525 次
( --作用:获取两个时间之内的所有日期 select dateadd(dd,number,'2012-02-01') as timeSerial,datename(weekday,dateadd(dd,number,'2012-02-01'))weekName from master..spt_values where type='p' and dateadd(dd,number,'2012-02-01')<='2012-02-29' ) TableSerial
( --作用:统计某科室周一 到 周日 ,每日设置预约人数上限 --shift 预约设置主表;ID:主键,deptID科室ID --(这2个字段这里暂时没用:BeginTime:起效时间--EndTime终止时间) --shiftDetail 预约设置明细 shiftID:主表对应ID;menCount:每个时段预约人数上限 ;weekID(1..7) --(这里暂时没用:PeriodID 分时段ID) --weekList 星期列表;weekID(1..7) weekName(星期一..星期日) select weekName,sumCount,deptID from weekList, (select sum(menCount) sumCount,weekID,s.deptID from shift s,shiftDetail sd where sd.shiftID = s.id and s.deptID = 3 group by weekID,s.deptID) TempA where weekList.weekID = TempA.weekID ) TempB
select TableSerial.weekName,Isnull( tempB.sumCount,0) sumCount,Isnull( tempB.deptID,3)deptID,TableSerial.timeSerial from ( --作用:获取两个时间之内的所有日期 select dateadd(dd,number,'2012-02-01') as timeSerial,datename(weekday,dateadd(dd,number,'2012-02-01') ) weekName from master..spt_values where type='p' and dateadd(dd,number,'2012-02-01')<='2012-02-29' ) TableSerial left join ( --作用:统计某科室周一 到 周日 ,每日设置预约人数上限 --shift 预约设置主表;ID:主键,deptID科室ID --(这2个字段这里暂时没用:BeginTime:起效时间--EndTime终止时间) --shiftDetail 预约设置明细 shiftID:主表对应ID;menCount:每个时段预约人数上限 ;weekID(1..7) --(这里暂时没用:PeriodID 分时段ID) --weekList 星期列表;weekID(1..7) weekName(星期一..星期日) select weekName,sumCount,deptID from weekList, (select sum(menCount) sumCount,weekID,s.deptID from shift s,shiftDetail sd where sd.shiftID = s.id and s.deptID = 3 group by weekID,s.deptID) TempA where weekList.weekID = TempA.weekID ) TempB on TempB.weekName = TableSerial.weekName