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
....

The stored proc will have two params (@Year INT, @AbsCode NVARCHAR(3)).
Please Note, Sometimes a record overlaps another month (like the first row in the example table) and that should be counted separately for each month. I have tried using loops but with no luck. I am so weak in TSQL.

UPDATE
Right now I am using a scalar value user function and a stored procedure to do the job, Its ugly and hard to trace. Here it is any way:
The User function:

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

你需要建立一个日历表,使你能够很容易地计算每个月的起步日期和结束日期。例如,记录=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

If you wrap that in a common table expression you can perform aggregation queries afterwards on the 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

友情链接