我的下表有员工缺席的情况：
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
....
存储的proc将具有两个参数（@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
如您所见，这是丑陋的代码，我相信可以用更简单的方法来完成。
最佳答案
您需要创建一个日历表，以便轻松计算开始日期和结束日期所包含的每个月的天数。例如，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'
如果将其包装在公用表表达式中，则可以随后在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
关于sql-server-2005 - 汇总和分组一个月中的记录数，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/8853797/