Блог

Для каждой уникальной комбинации BoxId и ревизии с одним идентификатором UnitTypeId равным 1 и одним идентификатором UnitTypeId равным 2, оба из которых имеют нулевой setNumber, назначьте setNumber равным 1.

Настройка таблицы и данных:

 IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[UnitTypes]') AND type in (N'U'))
Drop Table dbo.UnitTypes
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Tracking]') AND type in (N'U'))
DROP TABLE [dbo].[Tracking]
GO
CREATE TABLE dbo.UnitTypes
    (
    Id int NOT NULL,
    Notes varchar(80)
    )
GO
CREATE TABLE dbo.Tracking
    (
    Id int NOT NULL IDENTITY (1, 1),
    BoxId int NOT NULL,
    Revision int NOT NULL,
    UnitValue int NULL,
    UnitTypeId int NULL,
    SetNumber int NULL
    )
GO
ALTER TABLE dbo.Tracking ADD CONSTRAINT
    PK_Tracking PRIMARY KEY CLUSTERED 
    (
    Id
    )
GO  
Insert Into dbo.UnitTypes (Id, Notes) Values (1, 'X Coord'),
                                             (2, 'Y Coord'),
                                             (3, 'Weight'),
                                             (4, 'Length')
Go
Insert Into dbo.Tracking (BoxId, Revision, UnitValue, UnitTypeId, SetNumber)
              Values (1165, 1, 150, 1, NULL),
                     (1165, 1, 1477, 2, NULL),
                     (1165, 1, 31, 4, NULL),
                     (1166, 1, 425, 1, 1),
                     (1166, 1, 1146, 2, 1),
                     (1166, 1, 438, 1, NULL),
                     (1166, 1, 1163, 2, NULL),
                     (1167, 1, 560, 1, NULL),
                     (1167, 1, 909, 2, NULL),
                     (1167, 1, 12763, 3, NULL),
                     (1168, 1, 21, 1, NULL),
                     (1168, 1, 13109, 3, NULL)
  

Идеальными результатами были бы:

 Id  BoxId   Revision    UnitValue   UnitTypeId  SetNumber
 1   1165          1          150            1          1
 2   1165          1         1477            2          1
 3   1165          1           31            4          1
 4   1166          1          425            1          1
 5   1166          1         1146            2          1
 6   1166          1          438            1       NULL <--NULL Because there is already an existing Set
 7   1166          1         1163            2       NULL <--NULL Because there is already an existing Set
 8   1167          1          560            1          1
 9   1167          1          909            2          1
 10  1167          1        12763            3          1
 11  1168          1           21            1       NULL <--NULL Because there is not exactly one UnitTypeId of 1 and exactly one UnitTypeId of 2 for this BoxIdRevision combination.
 12  1168          1        13109            3       NULL <--NULL Because there is not exactly one UnitTypeId of 1 and exactly one UnitTypeId of 2 for this BoxIdRevision combination.
  

Редактировать:
Вопрос в том, как я могу обновить setNumber, учитывая приведенные выше ограничения, используя чистый TSQL?

Если я правильно понимаю ваш вопрос, вы могли бы сделать это с помощью подзапроса, который требует выполнения всех условий:

 update  t1
set     SetNumber = 1
from    dbo.Tracking t1
where   SetNumber is null
        and 1 = 
        (
        select  case 
                when count(case when t2.UnitTypeId = 1 then 1 end) <> 1 then 0
                when count(case when t2.UnitTypeId = 2 then 1 end) <> 1 then 0
                when count(t2.SetNumber) <> 0 then 0
                else 1 
                end
        from    dbo.Tracking t2
        where   t1.BoxId = t2.BoxId
                and t1.Revision = t2.Revision
        )
  

Это count(t2.SetNumber) немного сложно: это будет учитывать только строки, которых SetNumber нет null . Таким образом, это соответствует критерию, согласно которому не существует другого набора с таким же (BoxId, Revision) .

Попробуйте это, он возвращает те же результаты, которые вы дали. WITH Оператор устанавливает CTE для запроса. Функция ROW_NUMBER() — это функция разделения, которая делает то, что вы хотите:

 ;WITH BoxSets AS (
SELECT
    ID
    ,BoxId
    ,Revision
    ,UnitValue
    ,UnitTypeId
    ,CASE WHEN UnitTypeId IN (1,2) THEN 1 ELSE 0 END ValidUnit
    ,ROW_NUMBER() OVER (PARTITION BY BoxID,UnitTypeID ORDER BY BoxID,UnitTypeID,UnitValue ) SetNumber
FROM Tracking
)

SELECT 
    b.ID
    ,b.BoxId
    ,b.Revision
    ,b.UnitValue
    ,b.UnitTypeId
    ,CASE ISNULL(b1.ValidUnits,0) WHEN 0 THEN NULL ELSE CASE b.SetNumber WHEN 1 THEN b.SetNumber ELSE NULL END END
FROM BoxSets AS b
LEFT JOIN (SELECT
             BoxID
             ,SUM(ValidUnit) AS ValidUnits
             FROM BoxSets
             GROUP BY BoxId
             HAVING SUM(ValidUnit) > 1) AS b1 ON b.BoxId = b1.BoxId