گزارش مدت زمان صرف شده برای انجام هر فعالیت

صورت مسئله:

فرض کنید نیاز است گزارشی از زمان صرف شده بین زمان ایجاد فعالیت تا زمان تکمیل آن را براساس جست و جوی اقدام کننده ی هر فعالیت و بازه ی زمانی انتخابی استخراج نمایید.

راه حل:

برای این کار میتوانید از کوئری زیر استفاده نمایید.

DECLARE 
    @FullTitle NVARCHAR(100) = N'نام اقدام‌کننده مورد نظر',
    @StartDate DATETIME = '2024-01-01',  -- تاریخ شروع بازه
    @EndDate   DATETIME = '2024-12-31';  -- تاریخ پایان بازه

WITH FilteredTaskHistories AS (
    SELECT 
        h.TaskId,
        h.Type,
        h.CreatedOn
    FROM bpm_TaskHistories h
    WHERE h.Type IN (0, 2)
),
TaskCreateComplete AS (
    SELECT 
        TaskId,
        MIN(CASE WHEN Type = 0 THEN CreatedOn END) AS CreatedDate,
        MAX(CASE WHEN Type = 2 THEN CreatedOn END) AS CompletedDate
    FROM FilteredTaskHistories
    GROUP BY TaskId
)
SELECT
    p.SerialId AS N'شماره سریال',
    t.Name AS N'عنوان فعالیت',
    s.FullTitle AS N'اقدام کننده',
    Didgah_Common.dbo.com_udfGetSolarDateTime(tc.CreatedDate) AS N'زمان ایجاد',
    Didgah_Common.dbo.com_udfGetSolarDateTime(tc.CompletedDate) AS N'زمان تکمیل',

    DATEDIFF(MINUTE, tc.CreatedDate, tc.CompletedDate) AS N'مدت زمان (دقیقه)',
    DATEDIFF(HOUR, tc.CreatedDate, tc.CompletedDate) AS N'مدت زمان (ساعت)',
    DATEDIFF(DAY, tc.CreatedDate, tc.CompletedDate) AS N'مدت زمان (روز)'

FROM [Didgah_BPMS].[dbo].bpm_tasks t
INNER JOIN TaskCreateComplete tc ON t.Id = tc.TaskId
INNER JOIN [Didgah_BPMS].[dbo].bpm_ProcessInstances p ON t.BusinessKey = p.BusinessKey
INNER JOIN  Didgah_Common.dbo.com_staff s ON t.OwnerId = s.Id

WHERE 
    tc.CreatedDate IS NOT NULL 
    AND tc.CompletedDate IS NOT NULL
    AND s.FullTitle LIKE '%' + @FullTitle + '%'  -- جستجو بر اساس نام اقدام‌کننده
    AND tc.CreatedDate BETWEEN @StartDate AND @EndDate  -- فیلتر بازه زمانی بر اساس زمان ایجاد

ORDER BY p.SerialId DESC;