DECLARE @BDATE DATETIME,@EDATE DATETIME SELECT @BDATE='2010-01-01',@EDATE='2010-03-28'SELECT SUM(CASE DATEPART(WEEKDAY,DATEADD(DAY,NUMBER,@BDATE)) WHEN 1 THEN 0 WHEN 7 THEN 4 ELSE 8 END ) FROM (SELECT TOP(DATEDIFF(DAY,@BDATE,@EDATE)+1) NUMBER=ROW_NUMBER() OVER (ORDER BY T1.NUMBER)-1 FROM MASTER..SPT_VALUES T1,MASTER..SPT_VALUES T2 ) T WHERE DATEADD(DAY,NUMBER,@BDATE)<=@EDATE /* 540 */
declare @fromdate datetime declare @todate datetime set @fromdate = '2010-5-5' set @todate = '2010-5-8'select sum(case fweek when 7 then 4 when 0 then 0 else 8 end) from (select dateadd(dd,number,@fromdate) fdate,datepart(dw,dateadd(dd,number,@fromdate)) fweek from master..spt_values a where a.type = 'p' and dateadd(dd,number,@fromdate) <= @todate) t/*----------- 28(1 行受影响) */
--星期天是1,写错了,改一下declare @fromdate datetime declare @todate datetime set @fromdate = '2010-5-5' set @todate = '2010-5-8'select sum(case fweek when 7 then 4 when 1 then 0 else 8 end) from (select dateadd(dd,number,@fromdate) fdate,datepart(dw,dateadd(dd,number,@fromdate)) fweek from master..spt_values a where a.type = 'p' and dateadd(dd,number,@fromdate) <= @todate) t/*----------- 28(1 行受影响) */
http://topic.csdn.net/u/20091130/21/fb718680-98ff-4afb-98d8-cff2f8293ed5.html?24281
SELECT @BDATE='2010-01-01',@EDATE='2010-03-28'SELECT SUM(CASE DATEPART(WEEKDAY,DATEADD(DAY,NUMBER,@BDATE)) WHEN 1 THEN 0 WHEN 7 THEN 4 ELSE 8 END )
FROM
(SELECT TOP(DATEDIFF(DAY,@BDATE,@EDATE)+1) NUMBER=ROW_NUMBER() OVER (ORDER BY T1.NUMBER)-1 FROM MASTER..SPT_VALUES T1,MASTER..SPT_VALUES T2
) T
WHERE DATEADD(DAY,NUMBER,@BDATE)<=@EDATE
/*
540
*/
SELECT @BDATE='2010-01-01',@EDATE='2010-03-31'SELECT 'HOURS'=CASE DATEPART(WEEKDAY,DATEADD(DAY,NUMBER,@BDATE)) WHEN 1 THEN 0 WHEN 7 THEN 4 ELSE 8 END
,'NOWDATE'=DATEADD(DAY,NUMBER,@BDATE),DATENAME(WEEKDAY,DATEADD(DAY,NUMBER,@BDATE))
FROM
(SELECT TOP(DATEDIFF(DAY,@BDATE,@EDATE)+1) NUMBER=ROW_NUMBER() OVER (ORDER BY T1.NUMBER)-1 FROM MASTER..SPT_VALUES T1,MASTER..SPT_VALUES T2
) T
WHERE DATEADD(DAY,NUMBER,@BDATE)<=@EDATE
/*
这是原始计算数据,你验证一下对不对
8 2010-01-01 00:00:00.000 星期五
4 2010-01-02 00:00:00.000 星期六
0 2010-01-03 00:00:00.000 星期日
8 2010-01-04 00:00:00.000 星期一
8 2010-01-05 00:00:00.000 星期二
8 2010-01-06 00:00:00.000 星期三
8 2010-01-07 00:00:00.000 星期四
8 2010-01-08 00:00:00.000 星期五
4 2010-01-09 00:00:00.000 星期六
0 2010-01-10 00:00:00.000 星期日
8 2010-01-11 00:00:00.000 星期一
8 2010-01-12 00:00:00.000 星期二
8 2010-01-13 00:00:00.000 星期三
8 2010-01-14 00:00:00.000 星期四
8 2010-01-15 00:00:00.000 星期五
4 2010-01-16 00:00:00.000 星期六
0 2010-01-17 00:00:00.000 星期日
8 2010-01-18 00:00:00.000 星期一
8 2010-01-19 00:00:00.000 星期二
8 2010-01-20 00:00:00.000 星期三
8 2010-01-21 00:00:00.000 星期四
8 2010-01-22 00:00:00.000 星期五
4 2010-01-23 00:00:00.000 星期六
0 2010-01-24 00:00:00.000 星期日
8 2010-01-25 00:00:00.000 星期一
8 2010-01-26 00:00:00.000 星期二
8 2010-01-27 00:00:00.000 星期三
8 2010-01-28 00:00:00.000 星期四
8 2010-01-29 00:00:00.000 星期五
4 2010-01-30 00:00:00.000 星期六
0 2010-01-31 00:00:00.000 星期日
8 2010-02-01 00:00:00.000 星期一
8 2010-02-02 00:00:00.000 星期二
8 2010-02-03 00:00:00.000 星期三
8 2010-02-04 00:00:00.000 星期四
8 2010-02-05 00:00:00.000 星期五
4 2010-02-06 00:00:00.000 星期六
0 2010-02-07 00:00:00.000 星期日
8 2010-02-08 00:00:00.000 星期一
8 2010-02-09 00:00:00.000 星期二
8 2010-02-10 00:00:00.000 星期三
8 2010-02-11 00:00:00.000 星期四
8 2010-02-12 00:00:00.000 星期五
4 2010-02-13 00:00:00.000 星期六
0 2010-02-14 00:00:00.000 星期日
8 2010-02-15 00:00:00.000 星期一
8 2010-02-16 00:00:00.000 星期二
8 2010-02-17 00:00:00.000 星期三
8 2010-02-18 00:00:00.000 星期四
8 2010-02-19 00:00:00.000 星期五
4 2010-02-20 00:00:00.000 星期六
0 2010-02-21 00:00:00.000 星期日
8 2010-02-22 00:00:00.000 星期一
8 2010-02-23 00:00:00.000 星期二
8 2010-02-24 00:00:00.000 星期三
8 2010-02-25 00:00:00.000 星期四
8 2010-02-26 00:00:00.000 星期五
4 2010-02-27 00:00:00.000 星期六
0 2010-02-28 00:00:00.000 星期日
8 2010-03-01 00:00:00.000 星期一
8 2010-03-02 00:00:00.000 星期二
8 2010-03-03 00:00:00.000 星期三
8 2010-03-04 00:00:00.000 星期四
8 2010-03-05 00:00:00.000 星期五
4 2010-03-06 00:00:00.000 星期六
0 2010-03-07 00:00:00.000 星期日
8 2010-03-08 00:00:00.000 星期一
8 2010-03-09 00:00:00.000 星期二
8 2010-03-10 00:00:00.000 星期三
8 2010-03-11 00:00:00.000 星期四
8 2010-03-12 00:00:00.000 星期五
4 2010-03-13 00:00:00.000 星期六
0 2010-03-14 00:00:00.000 星期日
8 2010-03-15 00:00:00.000 星期一
8 2010-03-16 00:00:00.000 星期二
8 2010-03-17 00:00:00.000 星期三
8 2010-03-18 00:00:00.000 星期四
8 2010-03-19 00:00:00.000 星期五
4 2010-03-20 00:00:00.000 星期六
0 2010-03-21 00:00:00.000 星期日
8 2010-03-22 00:00:00.000 星期一
8 2010-03-23 00:00:00.000 星期二
8 2010-03-24 00:00:00.000 星期三
8 2010-03-25 00:00:00.000 星期四
8 2010-03-26 00:00:00.000 星期五
4 2010-03-27 00:00:00.000 星期六
0 2010-03-28 00:00:00.000 星期日
8 2010-03-29 00:00:00.000 星期一
8 2010-03-30 00:00:00.000 星期二
8 2010-03-31 00:00:00.000 星期三
*/
declare @todate datetime
set @fromdate = '2010-5-5'
set @todate = '2010-5-8'select
sum(case fweek when 7 then 4 when 0 then 0 else 8 end)
from
(select dateadd(dd,number,@fromdate) fdate,datepart(dw,dateadd(dd,number,@fromdate)) fweek
from master..spt_values a
where a.type = 'p'
and dateadd(dd,number,@fromdate) <= @todate) t/*-----------
28(1 行受影响)
*/
--星期天是1,写错了,改一下declare @fromdate datetime
declare @todate datetime
set @fromdate = '2010-5-5'
set @todate = '2010-5-8'select
sum(case fweek when 7 then 4 when 1 then 0 else 8 end)
from
(select dateadd(dd,number,@fromdate) fdate,datepart(dw,dateadd(dd,number,@fromdate)) fweek
from master..spt_values a
where a.type = 'p'
and dateadd(dd,number,@fromdate) <= @todate) t/*-----------
28(1 行受影响)
*/