T-SQL/查找多个重叠挂起任务的挂起总时间/需要忽略重叠和周末

Question

我希望能得到一些帮助，从这个查询中删除周末。我有一些案例有多个挂起的任务，这些任务可能在时间上重叠。这个查询有效，但我希望删除周末。

DECLARE @pends table (id varchar(50), d1 datetime, d2 datetime);
INSERT @pends
--- Play below me --- This is the query that pulls the pend tasks.
    SELECT
        dc.fldReferenceNumber,
        dt.fldCreatedTime,
        dt.fldDoneDate
    FROM ([Centerpoint_Reporting_Prod].[dbo].[dim_case] dc
    INNER JOIN ([Centerpoint_Reporting_Prod].[dbo].[dim_task] dt
    INNER JOIN ([Centerpoint_Reporting_Prod].[dbo].[fact_task] ft
    INNER JOIN [Centerpoint_Reporting_Prod].[dbo].[dim_task_type] dtt
        ON ft.fldTaskTypeIdTaskTypeKey = dtt.TaskTypeSK)
        ON ft.TaskSK = dt.TaskSK)
        ON ft.CaseKey = dc.CaseSK)
    WHERE dtt.Name_enUS LIKE 'Pending%' AND dt.fldDoneDate <> 0 and dc.fldReferenceNumber = 'CDCR-4998513'
--- Play above me ---
SELECT id, SUM (n) AS hold_days
FROM (
   SELECT DISTINCT
       t1.id,
       t1.d1 AS date,
       -DATEDIFF(DAY, (SELECT MIN(d1) FROM @pends), t1.d1)  AS n
   FROM @pends t1
   LEFT JOIN @pends t2
      ON t2.ID = t1.ID
      AND t2.d1 <> t1.d1
      AND t1.d1 BETWEEN t2.d1 AND t2.d2
   GROUP BY t1.ID, t1.d1, t1.d2
   HAVING COUNT(t2.ID) = 0
   UNION ALL
   SELECT DISTINCT
       t1.id,
       t1.d2 AS date,
       DATEDIFF(DAY, (SELECT MIN(d1) FROM @pends), t1.d2) AS n
   FROM @pends t1
   LEFT JOIN @pends t2
      ON t2.ID = t1.ID
      AND t2.d2 <> t1.d2
      AND t1.d2 BETWEEN t2.d1 AND t2.d2
   GROUP BY t1.ID, t1.d1, t1.d2
   HAVING COUNT(t2.ID) = 0
) s
GROUP BY ID;

Answer 1

查看bol中的DATENAME，还有一个函数返回1-7周中的哪一天，其中1是星期日，7是星期六。

DATENAME ( datepart , date )