Блог

Создание запроса check / DQ, который проверяет, было ли лицензировано использование чего-либо в течение этого времени использования.

Лицензионная запись может перекрываться, но обычно находится в последовательности, но также может иметь промежутки времени между лицензиями. Это может показаться немного сумасшедшим, но это потому, что лицензия может иметь категории, которые находятся в разное время, но я игнорирую это на данный момент.

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

Тестирование — это период использования, который полностью существует в лицензионных диапазонах.

• U1 — сбой, поскольку это только часть LA
• U2 — передача с использованием LA
• U3 — передача с использованием LA
• U4 — передача с использованием LB ИЛИ LA
• U5 — передача с использованием LB ИЛИ LA amp; LC
• U6 — передача с использованием LB amp; LD
• U7 — сбой части перекрытия LD
• U8 — сбой без перекрытий
• U9 — сбой части перекрытия файла
• U10 — сбой перекрывает оба файла и LF, но не полностью
• U11 — передача с использованием LH

В моем случае не имеет значения, какие лицензии были приняты, только то, что они были лицензированы во время использования, хотя это было бы полезно для отладки.

Я не уверен, с каких сторон атаковать это — хотя при сравнении периодов дат есть много вопросов, выполнение всего этого одновременно сэкономило бы время и в упрощенном ответе было бы полезно.

Одним из предположений было бы сначала упростить периоды L до суммированных диапазонов L (с использованием CTE), а затем выполнить not in range в вариантах использования? 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

По сути, вам нужно проверить дату начала, дату окончания и все промежуточные даты лицензий. В частности, вас волнует на 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<> .