sql-server-2005 - 对一个月的记录数进行汇总和分组

Question

我有下表，其中有员工缺勤:

RecordId    EmpID       ActivityCode  DateFrom    DateTo
---------------------------------------------------------------
666542      1511        AB            29/01/2011  02/02/2011
666986      1511        AB            11/11/2011  11/11/2011
666996      1511        EL            13/11/2011  17/11/2011
755485      1787        SL            01/11/2011  14/11/2011
758545      1787        SL            15/11/2011  03/12/2011
796956      1954        AB            09/11/2011  09/11/2011
799656      1367        AB            09/11/2011  09/11/2011
808845      1527        EL            16/11/2011  16/11/2011
823323      1527        EL            17/11/2011  17/11/2011
823669      1527        EL            18/11/2011  18/11/2011
899555      1123        AB            09/11/2011  09/12/2011
990990      1511        AB            12/11/2011  12/11/2011

现在我想要一个由存储过程生成的报告来总结给定年份每个月的特定缺勤代码的所有缺勤天数，例如如果我想从上一个表中知道 2011 年的缺勤总数将得到类似于:

Month        TotalDays
---------------------------------
JAN 2011     201
FEB 2011     36
MAR 2011     67
APR 2011     91
....

存储过程将有两个参数(@Year INT、@AbsCode NVARCHAR(3))。
请注意，有时记录会与另一个月重叠(例如示例表中的第一行)，因此每个月都应单独计算。我试过使用循环但没有运气。我的 TSQL 太弱了。

更新
现在我正在使用一个标量值用户函数和一个存储过程来完成这项工作，它丑陋且难以追踪。这是任何方式:
用户函数:

ALTER FUNCTION [dbo].[GetActivityTotalDaysInMonth]
(
     @ActivityCode CHAR(3)
     ,@Year INT
     ,@Month INT
)
RETURNS INT
AS

BEGIN

DECLARE @FirstDayOfMonth DATETIME
DECLARE @LastDayOfMonth DATETIME
SET @FirstDayOfMonth = CAST(CAST(@Year AS varchar) + '-' + CAST(@Month AS varchar) + '-' + CAST(1 AS varchar) AS DATETIME)
SET @LastDayOfMonth = DATEADD(s, -1, DATEADD(M, 1, @FirstDayOfMonth))    

DECLARE @TotalDays INT

SELECT @TotalDays = 
SUM(DATEDIFF(DAY, 
  (CASE WHEN ActivityDateFrom < @FirstDayOfMonth THEN @FirstDayOfMonth ELSE ActivityDateFrom END)
, (CASE WHEN ActivityDateTo > @LastDayOfMonth THEN @LastDayOfMonth ELSE ActivityDateTo END))+1)

FROM Activities 
WHERE 
ActivityCode=@ActivityCode
AND ((ActivityDateFrom < @FirstDayOfMonth AND ActivityDateTo >= @FirstDayOfMonth)
OR (ActivityDateFrom >= @FirstDayOfMonth AND ActivityDateTo <= @LastDayOfMonth)
OR (ActivityDateFrom <= @LastDayOfMonth AND ActivityDateTo > @LastDayOfMonth))
RETURN @TotalDays
END

现在，我在存储过程的循环内调用此函数:

ALTER PROCEDURE GetAnnualActivityTotalDays
(
    @ActivityCode CHAR(3)
    ,@Year INT
)
AS
BEGIN
SET NOCOUNT ON;

DECLARE @Stats TABLE
([Month] NVARCHAR(50), TotalDays INT)

DECLARE @MonthNo INT
DECLARE @Month DATETIME

SET @MonthNo = 1

WHILE @MonthNo <= 12
BEGIN

    SET @Month = CAST(CAST(@Year AS varchar) + '-' + CAST(@MonthNo AS varchar) + '-' + CAST(1 AS varchar) AS DATETIME)

    INSERT INTO @Stats ([Month], TotalDays)
    SELECT UPPER(SUBSTRING(DATENAME(mm, @Month), 1, 3)) + ', ' + CAST(@Year AS NVARCHAR),
    dbo.GetActivityTotalDaysInMonth(@ActivityCode
                ,@Year
                ,@MonthNo
                ,@Base)

    SET @MonthNo = @MonthNo + 1
END

SELECT * FROM @Stats
END

如您所见，这是一段丑陋的代码，我相信它可以用更简单的方式完成。有什么建议吗？

Answer 1

您需要创建一个日历表，以便您轻松计算开始日期和结束日期包含的每个月的天数。例如 recordid = 666542 在 1 月有 3 天，在 2 月有 2 天。您可以通过如下查询获取该号码

select calyear, calmonth, caldate
from calendar
    join activities on calendar.caldate between activities.activitydatefrom and activities.activitydateto
where activitycode = 'AB'

如果您将其包装在一个公共(public)表表达式中，您可以随后在 CTE 上执行聚合查询。

with mycte as (
select calyear, calmonth, caldate
from calendar
    join activities on calendar.caldate between activities.activitydatefrom and activities.activitydateto
where activitycode = 'AB'
)

select calyear, calmonth, count(caldate)
from mycte
group by calyear, calmonth
order by calyear, calmonth

要生成日历表，您可以使用类似的代码

create table calendar (calyear, calmonth, caldate)

declare @numdays int --number of days to generate in the calendar
declare @datestart datetime --the date to begin from in the calendar

set @numdays = 365 
set @datestart = 'jan 1 2011';

with num as (
select 0 number
union
select 1 number
union
select 2 number
union
select 3 number
union
select 4 number
union
select 5 number
union
select 6 number
union
select 7 number
union
select 8 number
union
select 9 number

),

numberlist as (
select ((hundreds.number * 100) + (tens.number * 10) + ones.number) n
from num hundreds
    cross join num tens 
    cross join num ones 
where ((hundreds.number * 100) + (tens.number * 10) + ones.number) < @numdays
)

insert into calendar (calyear, calmonth, caldate)
select 
    datepart(yy,dateadd(dd,n,@datestart)) calyear, 
    datepart(mm,dateadd(dd,n,@datestart)) calmonth, 
    dateadd(dd,n,@datestart)caldate
from numberlist