时间戳

首页 > 解决方案 > 检查多个接受期间的日期期间（例如许可期间与使用期间）

sql - 检查多个接受期间的日期期间（例如许可期间与使用期间）

问题描述

创建一个检查/DQ 查询，以检查在该使用期间某项的使用是否已获得许可。

许可记录可能重叠，但通常按顺序排列，但许可之间也可能存在时间间隔。这可能听起来有点疯狂，但这是因为许可证可能有不同时间的类别，但我暂时忽略了这一点。

Licensed:       |------A-----------------||-C-|                     |-----E------|    |---F--|  |--G--|
                             |----------B----------||-D-|                                  |---H---|
Use:        xxxx1-||--2--| |-3-| |-4-| |--5--|    |-6-||-7xxx x8x xx9-|        |--x10x--|   |-11--|

测试是使用期限完全存在于许可范围内。

在我的情况下，返回它被接受的许可证并不重要，只是它在用例期间获得了许可，尽管它对调试很有用。

我不确定从哪个角度来解决这个问题——虽然在比较日期期间有很多 Q，但同时这样做会节省时间，而且在简化的答案中会很有用。

一个假设是先将 L 个周期简化为汇总的 L 个范围（使用 CTE），然后对用例进行不在范围内？IE：

Licensed:       |------A---------------------------|             |-----B------|   |-------C------|
Use:        xxx-1-||--2--| |-3-| |-4-| |--5--|   |-6xxx xx7x  xx8-|        |--x9x--|   |-10--|

尽管在预过滤涵盖用例的许可证时会更加简化。IE：

U2 将仅查看 A，因为 L 日期范围仅涵盖 LA。

Licensed:       |------A-----------------|
Use:                 |--2--|

希望这是一个有趣的示例，它将作为应用程序中的实时检查发生 - 所以虽然我可以使用代码来执行此操作，但我希望它在 SQL 中会更快、更明智/可读，我可能会将其变成 DQ 报告。

你会怎么做？例子。

使用 SQL Server 2016 和/或 VB.Net。

示例数据：

DROP TABLE IF EXISTS #License
DROP TABLE IF EXISTS #Use

--License table
CREATE TABLE #License 
(
     ID char(1) NOT NULL, 
     FromDate date NOT NULL, 
     ToDate date NOT NULL
)

INSERT INTO #License SELECT 'A', '2020-01-01','2020-03-01'
INSERT INTO #License SELECT 'B', '2020-02-01','2020-03-10'
INSERT INTO #License SELECT 'C', '2020-03-02','2020-03-05'
INSERT INTO #License SELECT 'D', '2020-03-11','2020-03-20'
INSERT INTO #License SELECT 'E', '2020-06-01','2020-07-01'
INSERT INTO #License SELECT 'F', '2020-08-01','2020-08-15'
INSERT INTO #License SELECT 'G', '2020-09-01','2020-09-15'
INSERT INTO #License SELECT 'H', '2020-08-10','2020-09-10'
--SELECT *, DATEDIFF(dd,FromDate,ToDate) AS Days FROM #License

--Use Cases
CREATE TABLE #Use 
(
     ID int NOT NULL, 
     FromDate date NOT NULL, 
     ToDate date NOT NULL, 
     ExpectedResult bit NOT NULL
)

INSERT INTO #Use SELECT 1, '2019-12-01','2020-01-15', 0
INSERT INTO #Use SELECT 2, '2020-01-16','2020-01-20', 1
INSERT INTO #Use SELECT 3, '2020-01-20','2020-02-05', 1
INSERT INTO #Use SELECT 4, '2020-02-10','2020-02-20', 1
INSERT INTO #Use SELECT 5, '2020-03-01','2020-03-04', 1
INSERT INTO #Use SELECT 6, '2020-03-08','2020-03-15', 1
INSERT INTO #Use SELECT 7, '2020-03-18','2020-03-25', 0
INSERT INTO #Use SELECT 8, '2020-04-01','2020-04-30', 0
INSERT INTO #Use SELECT 9, '2020-05-20','2020-06-10', 0
INSERT INTO #Use SELECT 10, '2020-06-10','2020-08-10', 0
INSERT INTO #Use SELECT 11, '2020-08-12','2020-09-08', 1
--SELECT *, DATEDIFF(dd,FromDate,ToDate) AS Days FROM #Use

-- Fails
--SELECT U.ID, ExpectedResult
--    , CASE WHEN L.ID IS NOT NULL THEN 1 ELSE 0 END AS Result
--    , CASE WHEN ExpectedResult = (CASE WHEN L.ID IS NOT NULL THEN 1 ELSE 0 END) THEN 1 ELSE 0 END AS Success
--    ,*
--FROM #Use U
--LEFT JOIN #License L ON 
--    U.FromDate BETWEEN L.FromDate AND L.ToDate 
--    AND U.ToDate BETWEEN L.FromDate AND L.ToDate
----WHERE U.ID = 1
;
-- Using Gordons answer.
WITH lt AS (
    SELECT dateadd(day, 1, l.ToDate) AS test_date --, ID
    FROM #License l
)
SELECT Results.*, U.ExpectedResult, CASE WHEN U.ExpectedResult = Results.Result THEN 1 ELSE 0 END AS ExpectedIsResult
FROM (
    SELECT u.ID --, *
    , COUNT(*) AS DateTests -- the amount of dates tested against licenses (USE and LICENSE To+1 within USE)
    , COUNT(l.ID) AS TestsMatchedLicense -- the amount that matched a LICENSE
    , (CASE WHEN COUNT(*) = COUNT(l.ID) THEN 1 ELSE 0 END) AS Result -- any date that didn't match that was required Fails the result.

    FROM #Use u CROSS APPLY
         (SELECT v.test_date
          FROM (values (u.FromDate), (u.ToDate) ) AS v(test_date)
          UNION ALL
          SELECT lt.test_date --Check the the U goes past a license
          FROM lt
          WHERE lt.test_date >= u.FromDate AND
                lt.test_date <= u.ToDate
         ) AS slt 
         --Match USE range dates to a LICENSE   AND   LICENSE TO+1 within USE range matches a LICENSE - as the LICENSE range may not cover the whole USE period but another license may.
         LEFT JOIN #License l ON slt.test_date BETWEEN l.FromDate AND l.ToDate 
    GROUP BY u.ID
) AS Results INNER JOIN #Use U ON Results.ID = U.ID

除了 U6 之外，使用上面的简单尝试获得了很多成功 - 但是并非所有案例都匹配所有匹配的许可证，即：U5

标签： sqlsql-servervb.nettsql

解决方案

基本上，您需要测试开始日期、结束日期以及介于两者之间的所有许可日期。特别是，您比结束日期更关心 1 - 潜在的非许可期的开始。

with lt as (
      select l.*, dateadd(day, 1, l.todate) as test_date
      from license l
     )
select u.id,
       (case when count(*) = count(l.id) then 'Pass'
             else 'Fail'
        end)
from uset u cross apply
     (select v.test_date
      from (values (u.fromdate), (u.todate)
           ) v(test_date)
      union all
      select lt.test_date
      from lt
      where lt.fromdate >= u.fromdate and
            lt.todate <= u.todate
     ) lt left join
     license l
     on lt.test_date between l.fromdate and l.todate
group by u.id;

这是一个 db<>fiddle。

推荐阅读