select sum(case when DATEPART(weekday,getdate()) not in(1,7) then 1 else 0 end) from tb
先上传到 CSDN空间 再引用地址
select 合评接收日 from tb where DATEPART(weekday,合评接收日) not in(1,7)
---------------------------------------------------------------- -- Author :fredrickhu(小F,向高手学习) -- Date :2011-09-26 11:34:23 -- Verstion: -- Microsoft SQL Server 2008 R2 (RTM) - 10.50.1617.0 (Intel X86) -- Apr 22 2011 11:57:00 -- Copyright (c) Microsoft Corporation -- Enterprise Evaluation Edition on Windows NT 6.1 <X64> (Build 7600: ) (WOW64) -- ---------------------------------------------------------------- --> 测试数据:[tb] if object_id('[tb]') is not null drop table [tb] go create table [tb]([组织编号] int,[合评接收日] datetime) insert [tb] select 7945,'2011-07-05 8:55:31' union all select 7945,'2011-07-05 8:55:31' union all select 7961,'2011-07-22 15:00:00' union all select 8115,'2011-08-04 9:00:00' union all select 8134,'2011-09-05 10:30:00' union all select 8182,'2011-08-15 14:25:00' union all select 8158,'2011-08-16 11:30:00' --------------开始查询-------------------------- select 合评接收日 from tb where DATEPART(weekday,合评接收日) not in(1,7) and DATEDIFF(dd,合评接收日,GETDATE())>=0 ----------------结果---------------------------- /* 合评接收日 ----------------------- 2011-07-05 08:55:31.000 2011-07-05 08:55:31.000 2011-07-22 15:00:00.000 2011-08-04 09:00:00.000 2011-09-05 10:30:00.000 2011-08-15 14:25:00.000 2011-08-16 11:30:00.000(7 行受影响) */
---------------------------------------------------------------- -- Author :fredrickhu(小F,向高手学习) -- Date :2011-09-26 11:34:23 -- Verstion: -- Microsoft SQL Server 2008 R2 (RTM) - 10.50.1617.0 (Intel X86) -- Apr 22 2011 11:57:00 -- Copyright (c) Microsoft Corporation -- Enterprise Evaluation Edition on Windows NT 6.1 <X64> (Build 7600: ) (WOW64) -- ---------------------------------------------------------------- --> 测试数据:[tb] if object_id('[tb]') is not null drop table [tb] go create table [tb]([组织编号] int,[合评接收日] datetime) insert [tb] select 7945,'2011-07-05 8:55:31' union all select 7945,'2011-07-05 8:55:31' union all select 7961,'2011-07-22 15:00:00' union all select 8115,'2011-08-04 9:00:00' union all select 8134,'2011-09-05 10:30:00' union all select 8182,'2011-08-15 14:25:00' union all select 8158,'2011-08-16 11:30:00' --------------开始查询-------------------------- select count(合评接收日) as 天数 from tb where DATEPART(weekday,合评接收日) not in(1,7) and DATEDIFF(dd,合评接收日,GETDATE())>=0 ----------------结果---------------------------- /*天数 ----------- 7(1 行受影响)*/
--> 测试数据: [tb] if object_id('[tb]') is not null drop table [tb] create table [tb] (组织编号 int,合评接收日 datetime) insert into [tb] select 7945,'2011-07-05 8:55:31' union all select 7945,'2011-07-05 8:55:31' union all select 7961,'2011-07-22 15:00:00' union all select 8115,'2011-08-04 9:00:00' union all select 8134,'2011-09-05 10:30:00' union all select 8182,'2011-08-15 14:25:00' union all select 8158,'2011-08-16 11:30:00' union all select 8182,'2011-09-13 14:25:00' union all select 8182,'2011-09-12 14:25:00' union all select 8182,'2011-09-11 14:25:00' union all select 8182,'2011-09-10 14:25:00' --开始查询 select *, 截止到今天的工作日=case when datepart(weekday,合评接收日)=6 then datediff(day,合评接收日,getdate())-(datediff(week,合评接收日,getdate())-1)*2-1 else datediff(day,合评接收日,getdate())-(datediff(week,合评接收日,getdate()))*2 end from tb--结束查询 drop table [tb]
合评接收日
from
tb
where
DATEPART(weekday,合评接收日) not in(1,7)
-- Author :fredrickhu(小F,向高手学习)
-- Date :2011-09-26 11:34:23
-- Verstion:
-- Microsoft SQL Server 2008 R2 (RTM) - 10.50.1617.0 (Intel X86)
-- Apr 22 2011 11:57:00
-- Copyright (c) Microsoft Corporation
-- Enterprise Evaluation Edition on Windows NT 6.1 <X64> (Build 7600: ) (WOW64)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([组织编号] int,[合评接收日] datetime)
insert [tb]
select 7945,'2011-07-05 8:55:31' union all
select 7945,'2011-07-05 8:55:31' union all
select 7961,'2011-07-22 15:00:00' union all
select 8115,'2011-08-04 9:00:00' union all
select 8134,'2011-09-05 10:30:00' union all
select 8182,'2011-08-15 14:25:00' union all
select 8158,'2011-08-16 11:30:00'
--------------开始查询--------------------------
select
合评接收日
from
tb
where
DATEPART(weekday,合评接收日) not in(1,7)
and
DATEDIFF(dd,合评接收日,GETDATE())>=0
----------------结果----------------------------
/* 合评接收日
-----------------------
2011-07-05 08:55:31.000
2011-07-05 08:55:31.000
2011-07-22 15:00:00.000
2011-08-04 09:00:00.000
2011-09-05 10:30:00.000
2011-08-15 14:25:00.000
2011-08-16 11:30:00.000(7 行受影响)
*/
-- Author :fredrickhu(小F,向高手学习)
-- Date :2011-09-26 11:34:23
-- Verstion:
-- Microsoft SQL Server 2008 R2 (RTM) - 10.50.1617.0 (Intel X86)
-- Apr 22 2011 11:57:00
-- Copyright (c) Microsoft Corporation
-- Enterprise Evaluation Edition on Windows NT 6.1 <X64> (Build 7600: ) (WOW64)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([组织编号] int,[合评接收日] datetime)
insert [tb]
select 7945,'2011-07-05 8:55:31' union all
select 7945,'2011-07-05 8:55:31' union all
select 7961,'2011-07-22 15:00:00' union all
select 8115,'2011-08-04 9:00:00' union all
select 8134,'2011-09-05 10:30:00' union all
select 8182,'2011-08-15 14:25:00' union all
select 8158,'2011-08-16 11:30:00'
--------------开始查询--------------------------
select
count(合评接收日) as 天数
from
tb
where
DATEPART(weekday,合评接收日) not in(1,7)
and
DATEDIFF(dd,合评接收日,GETDATE())>=0
----------------结果----------------------------
/*天数
-----------
7(1 行受影响)*/
--> 测试数据: [tb]
if object_id('[tb]') is not null drop table [tb]
create table [tb] (组织编号 int,合评接收日 datetime)
insert into [tb]
select 7945,'2011-07-05 8:55:31' union all
select 7945,'2011-07-05 8:55:31' union all
select 7961,'2011-07-22 15:00:00' union all
select 8115,'2011-08-04 9:00:00' union all
select 8134,'2011-09-05 10:30:00' union all
select 8182,'2011-08-15 14:25:00' union all
select 8158,'2011-08-16 11:30:00' union all
select 8182,'2011-09-13 14:25:00' union all
select 8182,'2011-09-12 14:25:00' union all
select 8182,'2011-09-11 14:25:00' union all
select 8182,'2011-09-10 14:25:00' --开始查询
select *,
截止到今天的工作日=case when datepart(weekday,合评接收日)=6
then datediff(day,合评接收日,getdate())-(datediff(week,合评接收日,getdate())-1)*2-1
else datediff(day,合评接收日,getdate())-(datediff(week,合评接收日,getdate()))*2 end
from tb--结束查询
drop table [tb]