SQL统计需求：指定团队某日在办申请数（转移时防重复计数）

Got it, let's tackle this problem step by step. The key challenge here is making sure transferred applications on the target date are only counted for the receiving team, with no duplicates. First, let's clarify the necessary table structures (since the original Application table was incomplete, I'll add the fields we need for transfer tracking):

Table Definitions #

First, here are the full table structures we’ll need to implement the logic correctly:

• Users:

  Column	Type	Description
  Id	INT	Unique user ID
  Name	VARCHAR(50)	User's name
  TeamId	INT	ID of the team the user belongs to

• Application:

  Column	Type	Description
  Id	INT	Unique application ID
  Name	VARCHAR(100)	Application name
  Status	VARCHAR(20)	Application status (use InProgress for active, in-process applications)
  CreatedDateTime	DATETIME	Timestamp when the application was created
  AssigneeId	INT	Current user assigned to handle the application

• ApplicationTransfer (critical for tracking transfer history):

  Column	Type	Description
  Id	INT	Unique transfer record ID
  ApplicationId	INT	ID of the transferred application
  FromUserId	INT	User who initiated the transfer
  ToUserId	INT	User who received the application
  TransferDateTime	DATETIME	Timestamp when the transfer was completed

SQL Query to Count Applications #

This query will calculate the number of in-progress applications for a specified team on a target date, handling transfer day logic correctly:

-- Set your target parameters here
DECLARE @TargetDate DATE = '2024-05-20';
DECLARE @TargetTeamId INT = 2;

WITH ApplicationDailyOwnership AS (
    SELECT
        a.Id AS ApplicationId,
        -- Determine which team owns the application on the target date
        COALESCE(
            -- If the app was transferred on the target date, use the LAST transfer's receiving team
            (SELECT TOP 1 u.TeamId
             FROM ApplicationTransfer t
             JOIN Users u ON t.ToUserId = u.Id
             WHERE t.ApplicationId = a.Id
               AND CAST(t.TransferDateTime AS DATE) = @TargetDate
             ORDER BY t.TransferDateTime DESC),
            -- If no transfer that day, use the current assignee's team
            (SELECT u.TeamId FROM Users u WHERE u.Id = a.AssigneeId)
        ) AS OwnedTeamId
    FROM Application a
    WHERE a.Status = 'InProgress' -- Only count active, in-process applications
      AND CAST(a.CreatedDateTime AS DATE) <= @TargetDate -- Ensure the app existed by the target date
)
SELECT COUNT(DISTINCT ApplicationId) AS InProgressApplicationCount
FROM ApplicationDailyOwnership
WHERE OwnedTeamId = @TargetTeamId;

Stored Procedure for Reusability #

If you need to run this calculation frequently, wrap the logic in a stored procedure for easier reuse:

CREATE PROCEDURE GetTeamInProgressApplications
    @TargetDate DATE,
    @TargetTeamId INT,
    @ApplicationCount INT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;

    WITH ApplicationDailyOwnership AS (
        SELECT
            a.Id AS ApplicationId,
            COALESCE(
                (SELECT TOP 1 u.TeamId
                 FROM ApplicationTransfer t
                 JOIN Users u ON t.ToUserId = u.Id
                 WHERE t.ApplicationId = a.Id
                   AND CAST(t.TransferDateTime AS DATE) = @TargetDate
                 ORDER BY t.TransferDateTime DESC),
                (SELECT u.TeamId FROM Users u WHERE u.Id = a.AssigneeId)
            ) AS OwnedTeamId
        FROM Application a
        WHERE a.Status = 'InProgress'
          AND CAST(a.CreatedDateTime AS DATE) <= @TargetDate
    )
    SELECT @ApplicationCount = COUNT(DISTINCT ApplicationId)
    FROM ApplicationDailyOwnership
    WHERE OwnedTeamId = @TargetTeamId;
END;

How to Use the Stored Procedure: #

DECLARE @ResultCount INT;
EXEC GetTeamInProgressApplications 
    @TargetDate = '2024-05-20', 
    @TargetTeamId = 2, 
    @ApplicationCount = @ResultCount OUTPUT;
SELECT @ResultCount AS InProgressApplications;

Key Logic Breakdown #

• Transfer Day Handling: For applications transferred on the target date, we prioritize the last transfer's receiving team to ensure the app is only counted for the team that ends up with it that day—no duplicates across teams.
• Duplicate Prevention: Using COUNT(DISTINCT ApplicationId) ensures even if an application is transferred multiple times in a single day, it’s only counted once.
• In-Progress Filter: We only include applications marked as InProgress that existed on or before the target date, so we don’t count apps created after the date in question.

内容的提问来源于stack exchange，提问作者Ady B