Как свести данные sql и сжать результаты в ненулевые строки по дате на идентификатор

Плохое название для поста, но, надеюсь, оно привлечет внимание.

У меня очень сложная ситуация в T-SQL, которую я не могу выполнить. Я надеюсь, что кто-то с опытом знает элегантное и быстрое решение, чтобы моя производительность не пострадала. Я имею дело с миллиардами строк.

ПРЕДИСЛОВИЕ

У меня есть таблица Customers с уникальным идентификатором. У этих клиентов есть файлы, у файлов есть свойства, и каждому свойству Имя соответствует одно значение.

Таблицы:

• Клиенты
• Файлы -
• Свойство — содержит как имя, так и значение

Идентификатор клиента присутствует во всех этих таблицах, как и поля аудита, такие как UpdatedDtm и CreationDtm.

ПРИМЕР ИСПОЛЬЗОВАНИЯ

Мне нужно присоединить всех клиентов к их файлам (фильтруя несколько), а затем привязать каждый файл к их свойствам (снова фильтруя их). Это просто, но приводит к большому количеству строк, по одной для каждого клиента x файла x свойства.

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

ПРОБЛЕМА Во-первых, у свойств есть DateTime, когда они были изменены (UpdatedDtm), и мне нужно вернуть все измененное с 1 часа даты создания. (CreationDtm) в таблице файлов.

Это приводит к тому, что я сокращаю свой список потенциальных свойств, но теперь у меня есть таблица с RowNumber() для каждого идентификатора, и нет хорошего способа повернуть и выбрать первое, которое не является нулевым и все еще сохранить количество столбцов для определения таблицы. Это важно, потому что я использую Dynamic SQL и помещаю его в проиндексированную временную таблицу с составным ключом для CustomerID и FileName. .

ДО ПОВОРОТА

| UpdatedDtm | CustomerID | FileName   | Property | Value          |
| ---------- | ---------- | ---------- | -------- | -------------- |
| 1/1/2015   | 1          | FileOne    | Size     | NULL           |
| 1/1/2015   | 1          | FileOne    | Format   | JPG            |
| 1/7/2015   | 1          | FileOne    | Size     | 88KB           |
| 1/7/2015   | 1          | FileOne    | Format   | JPG            |
| 1/7/2015   | 1          | FileOne    | Comment  | NULL           |
| 1/11/2015  | 1          | FileOne    | Comment  | NULL           |
| 1/1/2015   | 1          | FileTwo    | Size     | 91KB           |
| 1/1/2015   | 1          | FileTwo    | Format   | PNG            |
| 1/11/2015  | 1          | FileTwo    | Comment  | NULL           |
| 1/2/2015   | 2          | FileThree  | Size     | 74KB           |
| 1/2/2015   | 2          | FileThree  | Format   | XLS            |
| 1/2/2015   | 2          | FileThree  | State    | Open           |
| 1/7/2015   | 2          | FileThree  | State    | Closed         |
| 1/10/2015  | 2          | FileThree  | Comment  | NULL           |
| 1/1/2015   | 3          | FileFour   | Size     | 2KB            |
| 1/2/2015   | 3          | FileFour   | Size     | 10KB           |
| 1/3/2015   | 3          | FileFour   | Size     | 13KB           |
| 1/4/2015   | 3          | FileFour   | Size     | 21KB           |
| 1/5/2015   | 3          | FileFour   | Size     | 27KB           |
| 1/6/2015   | 3          | FileFour   | Size     | 32KB           |
| 1/7/2015   | 3          | FileFour   | Size     | 39KB           |
| 1/8/2015   | 3          | FileFour   | Size     | 44KB           |
| 1/1/2015   | 3          | FileFour   | Format   | TXT            |
| 1/1/2015   | 3          | FileFour   | Comment  | NULL           |

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

ПОСЛЕ РАЗВИТИЯ (ожидание)

| CustomerID | FileName   | Size | Format | State  | Comment |
| ---------- | ---------- | ---- | ------ | ------ | ------- |
| 1          | FileOne    | 88KB | JPG    | NULL   | NULL    |
| 1          | FileTwo    | 91KB | PNG    | NULL   | NULL    |
| 2          | FileThree  | 74KB | XLS    | Closed | NULL    |
| 3          | FileFour   | 44KB | TXT    | NULL   | NULL    |

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

КОД (моя попытка)

IF Object_id('tempdb..#FilesQuery') IS NOT NULL DROP TABLE #FilesQuery;
CREATE TABLE #FilesQuery (
    SeqNum          int,
    CustomerID      numeric(16,0),
    FileName        varchar(64),
    PropertyName    varchar(64),
    PropertyValue   varchar(64)
)
INSERT INTO #FilesQuery
SELECT
     CASE WHEN P.[Value] IS NOT NULL
          THEN ROW_NUMBER() OVER (partition by C.CustomerID order by UpdatedDtm)
          ELSE 0
     END as SeqNum,
     C.CustomerID
    ,F.Name  as FileName
    ,P.Name  as PropertyName
    ,P.Value as PropertyValue

FROM Customers C
INNER JOIN Files F ON F.CustomerID = C.CustomerID
LEFT JOIN Properties P
    ON P.CustomerID = C.CustomerID
    AND P.FileID = F.FileID

WHERE F.FileName IN ('FileOne','FileTwo','FileThree','FileFour')
    AND P.Name IN ('Size','Format','State','Comment')

--PIVOT
DECLARE @cols AS nvarchar(MAX)
SELECT @cols = STUFF(
    (SELECT DISTINCT ',' + QUOTENAME(PropertyName)
       FROM #FilesQuery fq
        FOR XML PATH(''),TYPE).value('.','NVARCHAR(MAX)'),1,1,'')

DECLARE @dynSql AS nvarchar(MAX)
SET @dynSql = '
    SELECT DISTINCT *
    FROM (
        SELECT
            fq.CustomerID,
            fq.FileName,
            fq.PropertyName,
            fq.PropertyValue
        FROM #FilesQuery fq
    ) SRC
    PIVOT (
        Max([PropertyValue])
        FOR PropertyName IN (' + @cols + ')
    ) PVT
'

IF Object_id('tempdb..#Results') IS NOT NULL DROP TABLE #Results;
CREATE TABLE #Results (
    CustomerID      varchar(16) NOT NULL,
    FileName        varchar(64) NOT NULL,
    FileSize        varchar(64) NULL,
    FileFormat      varchar(64) NULL,
    FileState       varchar(64) NULL,
    FileComment     varchar(64) NULL,
    CONSTRAINT pk_CustDoc PRIMARY KEY (CustomerID,FileName)
)
INSERT INTO #Results EXEC @dynSql;

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

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

FileOne NULL NULL Open NULL
FileOne NULL JPG  NULL NULL

и так далее...

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

У кого-нибудь есть хорошее решение? Я слишком много думаю?