计算从一种状态到另一种状态所需的天数: SQL。

Question

请饱览我们数据库的当前结构。

​

​

我们的DBA目前离开了两个星期，我有非常有限的SQL知识，我喜欢停留在UI和中间层。

我们试图找出的是如何做以下工作，我们需要编写一个查询来计算所有佣金从“核实”到“付费”的平均期限(以天为单位)，目前的状态是

1. 已创建
2. 已验证
3. 已拒绝
4. 等待付款
5. 已付费
6. 退款

我认为这个查询需要直接针对委员会历史表吗？

我不确定我将如何编写这样的查询，因为我对SQL的了解是有限的。

任何帮助都会很好。

Answer 1

这里有一个方法来实现你想要的，尽管它可能不是最有效的。在我看来，这更像是你想要运行的一次性查询，而不是你将要频繁运行的、足以影响数据库性能的查询。

测试表设置：

CREATE TABLE Commission
(
    CommissionId INT,
    DealerId INT
)

CREATE TABLE CommissionHistory
(
    CommissionId INT,
    ActionDate DATETIME,
    NewPaymentStatusId INT
)

插入虚拟数据--1家经销商的5份佣金：

INSERT INTO dbo.Commission
        ( CommissionId ,
          DealerId
        )
VALUES ( 1 , 1 ),
       ( 2 , 1 ),
       ( 3 , 1 ),
       ( 4 , 1 ),
       ( 5 , 1 ),

INSERT INTO dbo.CommissionHistory
        ( CommissionId ,
          ActionDate ,
          NewPaymentStatusId
        )
VALUES ( 1 , GETDATE() -25, 1 ),
       ( 1 , GETDATE() -21, 2 ),
       ( 1 , GETDATE() -18, 3 ),
       ( 1 , GETDATE() -16, 4 ),
       ( 1 , GETDATE() -5, 5 ),
       ( 2 , GETDATE() -10, 1 ),
       ( 2 , GETDATE() -9, 2 ),
       ( 2 , GETDATE() -8, 3 ),
       ( 2 , GETDATE() -7, 4 ),
       ( 2 , GETDATE() -6, 5 ),
       ( 3 , GETDATE() -10, 1 ),
       ( 3 , GETDATE() -8, 2 ),
       ( 3 , GETDATE() -6, 3 ),
       ( 3 , GETDATE() -4, 4 ),
       ( 3 , GETDATE() -2, 5 ),
       ( 3 , GETDATE() -25, 6 ),
       ( 4 , GETDATE() -10, 1 ),
       ( 4 , GETDATE() -7, 2 ),
       ( 4 , GETDATE() -6, 3 ),
       ( 4 , GETDATE() -4, 4 ),
       ( 4 , GETDATE() -1, 5 ),
       ( 5 , GETDATE() -1, 1 ),
       ( 5 , GETDATE() -1, 2 )

因此，对于虚拟数据，委员会1，2和，4被归类为有效的记录，因为他们有状态2和5.3被排除，因为它被退回，5被排除，因为它没有支付。

为了生成平均值，我编写了以下查询：

-- set the required dealer id
DECLARE @DealerId INT = 1

-- return all CommissionId's in to a temp table that have statuses 2 and 5, but not 6
SELECT DISTINCT CommissionId
INTO #DealerCommissions
FROM dbo.CommissionHistory t1      
WHERE CommissionId IN (SELECT CommissionId 
                       FROM dbo.Commission 
                       WHERE    DealerId = @DealerId)
AND NOT EXISTS (SELECT CommissionId 
                FROM dbo.CommissionHistory t2 
                WHERE t2.NewPaymentStatusId = 6 AND t2.CommissionId = t1.CommissionId)
AND EXISTS (SELECT CommissionId 
            FROM dbo.CommissionHistory t2 
            WHERE t2.NewPaymentStatusId = 2 AND t2.CommissionId = t1.CommissionId)
AND EXISTS (SELECT CommissionId 
            FROM dbo.CommissionHistory t2 
            WHERE t2.NewPaymentStatusId = 5 AND t2.CommissionId = t1.CommissionId)

-- use the temp table to return average difference between the MIN & MAX date
;WITH cte AS (
    SELECT CommissionId FROM #DealerCommissions
)
SELECT  AVG(CAST(DaysToCompletion AS DECIMAL(10,8)))
FROM    (
         SELECT DATEDIFF(DAY, MIN(ch.ActionDate), MAX(ch.ActionDate)) DaysToCompletion
         FROM cte
         INNER JOIN dbo.CommissionHistory ch ON ch.CommissionId = cte.CommissionId
         GROUP BY ch.CommissionId
) AS averageDays

-- remove temp table
DROP TABLE #DealerCommissions

Answer 2

对于历史表中的每个佣金，您可以得到最大验证日期和最低支付日期，假设支付日期总是晚于验证日期。然后，您可以加入佣金表，按经销商id分组，以获得平均天数。

with comm as(
select 
    commissionid,
    max(case NewPamentStatus when 'Verified' then ActionDate else null end) as verified_date,  
    min(case NewPamentStatus when 'Paid' then ActionDate else null end) as paid_date
        --using max or min just incase that same status will be recorded more than one time.
from 
    CommissionHistory
group by 
    commistionid
)
select 
    c.DealerId,
    avg(datediff(day,comm.verified_date,comm.paid_date))
from
    comm
inner join
    commission c
on  c.commissionid = comm.commissionid
where 
    datediff(day,comm.verified_date,comm.paid_date)>0
    -- to get rid off the commissions with paid date before the verified date or in same day
group by
    c.DealerId