if exists (select * from dbo.sysobjects where id = object_id(N'[tb_Holiday]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [tb_Holiday]
GO

--定义节假日表
CREATE TABLE tb_Holiday(
HDate smalldatetime primary key clustered, --节假日期
Name nvarchar(50) not null)             --假日名称
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_WorkDay]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[f_WorkDay]
GO

--计算两个日期之间的工作天数
CREATE FUNCTION f_WorkDay(
@dt_begin datetime,  --计算的开始日期
@dt_end  datetime   --计算的结束日期
)RETURNS int
AS
BEGIN
    IF @dt_begin>@dt_end
        RETURN(DATEDIFF(Day,@dt_begin,@dt_end)
            +1-(
                SELECT COUNT(*) FROM tb_Holiday
                WHERE HDate BETWEEN @dt_begin AND @dt_end))
    RETURN(-(DATEDIFF(Day,@dt_end,@dt_begin)
        +1-(
            SELECT COUNT(*) FROM tb_Holiday
            WHERE HDate BETWEEN @dt_end AND @dt_begin)))
END
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_WorkDayADD]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[f_WorkDayADD]
GO

--在指定日期上增加工作天数
CREATE FUNCTION f_WorkDayADD(
@date    datetime,  --基础日期
@workday int       --要增加的工作日数
)RETURNS datetime
AS
BEGIN
    IF @workday>0
        WHILE @workday>0
            SELECT @date=@date+@workday,@workday=count(*)
            FROM tb_Holiday
            WHERE HDate BETWEEN @date AND @date+@workday
    ELSE
        WHILE @workday<0
            SELECT @date=@date+@workday,@workday=-count(*)
            FROM tb_Holiday
            WHERE HDate BETWEEN @date AND @date+@workday
    RETURN(@date)
END

create function WorkdayDiff(@d1 datetime, @d2 datetime, @id int)
returns decimal(5,1) as
begin

if @d1>@d2 select @d1=@d1+@d2, @d2=@d1-@d2, @d1=@d1-@d2

declare @wet int, @wd int
select @wet=WeekEndType from 表A where id=@id

declare @T table (date datetime, wd int, workday decimal(5,1))

while @d1<=@d2
begin
    set @wd=(@@datefirst+datepart(weekday,@d1)-1)%7
    insert @T select @d1, @wd, case @wd
        --Sunday
        when 0 then (sign(@wet&4)+sign(@wet&8))*0.5
        --Saturday
        when 6 then case when @wet&3=3 then 1 else 0.5 end
    set @d1=@d1+1
end

update @T set workday=case b.Dtype when 1 then 0 else 1 end from @T a join 表B b on a.date=b.DDate

return(select sum(workday) from @T)

end

DECLARE @dt datetime
SET @dt=GETDATE()

DECLARE @number int
SET @number=3

--1．指定日期该年的第一天或最后一天
--A. 年的第一天
SELECT CONVERT(char(5),@dt,120)+'1-1'

--B. 年的最后一天
SELECT CONVERT(char(5),@dt,120)+'12-31'


--2．指定日期所在季度的第一天或最后一天
--A. 季度的第一天
SELECT CONVERT(datetime,
    CONVERT(char(8),
        DATEADD(Month,
            DATEPART(Quarter,@dt)*3-Month(@dt)-2,
            @dt),
        120)+'1')

--B. 季度的最后一天（CASE判断法）
SELECT CONVERT(datetime,
    CONVERT(char(8),
        DATEADD(Month,
            DATEPART(Quarter,@dt)*3-Month(@dt),
            @dt),
        120)
    +CASE WHEN DATEPART(Quarter,@dt) in(1,4)
        THEN '31'ELSE '30' END)

--C. 季度的最后一天（直接推算法）
SELECT DATEADD(Day,-1,
    CONVERT(char(8),
        DATEADD(Month,
            1+DATEPART(Quarter,@dt)*3-Month(@dt),
            @dt),
        120)+'1')


--3．指定日期所在月份的第一天或最后一天
--A. 月的第一天
SELECT CONVERT(datetime,CONVERT(char(8),@dt,120)+'1')

--B. 月的最后一天
SELECT DATEADD(Day,-1,CONVERT(char(8),DATEADD(Month,1,@dt),120)+'1')

--C. 月的最后一天（容易使用的错误方法）
SELECT DATEADD(Month,1,DATEADD(Day,-DAY(@dt),@dt))


--4．指定日期所在周的任意一天
SELECT DATEADD(Day,@number-DATEPART(Weekday,@dt),@dt)


--5．指定日期所在周的任意星期几
--A.  星期天做为一周的第1天
SELECT DATEADD(Day,@number-(DATEPART(Weekday,@dt)+@@DATEFIRST-1)%7,@dt)

--B.  星期一做为一周的第1天
SELECT DATEADD(Day,@number-(DATEPART(Weekday,@dt)+@@DATEFIRST-2)%7-1,@dt) 


Trackback: http://tb.blog.csdn.net/TrackBack.aspx?PostId=2203195

* 关于log记录配对
 *     log 通常包括portid / sessionid, 该id通常会被重用，但同一时刻不会重复
 *     一组记录通常包含两条：先入，后出
 */

declare @logdata table (
    logid char(36) not null, -- 类似 portid / sessionid, 该id通常会被重用，但同一时刻系统中不会重复出现
    logtype tinyint, -- 方向， 1: login|入, 0: logout|出
    logtime datetime -- 时间点
)

declare @logI table (id int identity(1,1), logid char(36), logItime datetime)
insert into @logI
-- 各个有效的进入时间点
select logid, logItime=logtime from @logdata a where logtype = 1
and exists (
    -- 本次退出的时间
    select min(logtime)
    from @logdata c
    where c.logid=a.logid and c.logtype=0 and c.logtime>a.logtime
    )
and not (
        -- 本次退出的时间
        (
        select min(logtime)
        from @logdata c
        where c.logid=a.logid and c.logtype=0 and c.logtime>a.logtime
        ) >
        -- 下次进入的时间
        (
        select min(logtime) 
        from @logdata b 
        where logid=a.logid and logtype=1 and b.logtime>a.logtime
        )
    )
order by logid, logItime

declare @logO table (id int identity(1,1), logid char(36), logOtime datetime)
insert into @logO
-- 各个有效的退出时间点
select logid, logOtime=logtime from @logdata a where logtype = 0
and exists (
    -- 本次进入的时间
    select max(logtime)
    from @logdata c
    where c.logid=a.logid and c.logtype=1 and c.logtime<a.logtime
    )
and not (
        -- 本次进入的时间
        (
        select max(logtime)
        from @logdata c
        where c.logid=a.logid and c.logtype=1 and c.logtime<a.logtime
        ) <
        -- 上次退出的时间
        (
        select max(logtime) 
        from @logdata b 
        where logid=a.logid and logtype=1 and b.logtime>a.logtime
        )
    )
order by logid, logOtime

-- 配对：
select a.logid, loginTime = logItime, logoutTime = logOtime
from @logI a, @logO b 
where a.id=b.id

/** 假设数据存储于表 cdr, 每条记录包括 bgnTime为该记录的开始时间， endTime为该记录的结束时间*/
declare @bgnTime datetime, @endTime datetime

set @bgnTime = '2008-4-15 10:00:00.000'
set @endTime = '2008-4-15 10:59:59.997'


-- 指定时段内的记录数 -----------------------------------------------
-- 按开始时间计算
select count(1) from cdr where bgnTime>=@bgnTime and bgnTime<@endTime
-- 按结束时间计算
select count(1) from cdr where endTime>@bgnTime and endTime<=@endTime
-- 按时间相交计算
select count(1) from cdr where endTime>@bgnTime and bgnTime<@endTime


-- 指定时段内的累计时长
select datediff(second
        ,case when bgnTime>@bgnTime then bgnTime else @bgnTime end
        ,case when endTime<@endTime then endTime else @endTime end
        )
from cdr where endTime>@bgnTime and bgnTime<@endTime


-- 指定时段内的最大并发 -----------------------------------------------
-- 各个时间点的并发数
select top 1 * from (
    select timepoint, cnt = (select count(1) from cdr b where bgnTime<=timepoint and endTime>timepoint)
    from (select distinct timepoint=bgnTime from cdr where bgnTime<@endTime and endTime>@bgnTime) as a 
) as a
order by cnt desc


-- 时间连续性分组 -----------------------------------------------
declare @tbgn table (id int identity(1,1), bgnTime datetime)
-- 该表每一数据的前一刻没有任何使用记录
insert into @tbgn 
select distinct bgnTime 
from cdr a 
where not exists (
    select 1 from cdr b where b.bgnTime<a.bgnTime and b.endTime>=a.bgnTime) 
order by bgnTime

declare @tend table (id int identity(1,1), endTime datetime)
-- 该表每一数据的后一刻没有任何使用记录
insert into @tend 
select distinct endTime 
from cdr a 
where not exists (
    select 1 from cdr b where b.bgnTime<=a.endTime and b.endTime>a.endTime) 
order by endTime

-- 分组整合
select a.id, bgnTime, endTime from @tbgn a, @tend b where a.id=b.id