Joseph Doherty
26ff8d9b4f
Set up repository with legacy .NET Framework 4.8 source (OLD/), new .NET 10 Blazor solution (NEW/), OpenSpec specifications, documentation, and project configuration.
28 lines
798 B
Transact-SQL
Executable File
28 lines
798 B
Transact-SQL
Executable File
CREATE VIEW [dbo].[LastDataUpdates]
|
|
AS
|
|
WITH UPDATE_CTE
|
|
AS (
|
|
SELECT du.TableName,
|
|
du.UpdateType,
|
|
du.StartDT,
|
|
ROW_NUMBER() OVER (PARTITION BY du.TableName, du.UpdateType ORDER BY du.StartDT DESC) AS RN
|
|
FROM dbo.DataUpdate du
|
|
WHERE du.WasSuccessful = 1)
|
|
SELECT TableName,
|
|
COALESCE([3], '1970-01-01') AS MassUpdateDT,
|
|
COALESCE(COALESCE([2], [3]), '1970-01-01') AS DailyUpdateDT,
|
|
COALESCE(COALESCE(COALESCE([1], [2]), [3]), '1970-01-01') AS HourlyUpdateDT
|
|
FROM
|
|
(
|
|
SELECT UPDATE_CTE.TableName,
|
|
UPDATE_CTE.UpdateType,
|
|
UPDATE_CTE.StartDT,
|
|
UPDATE_CTE.RN
|
|
FROM UPDATE_CTE
|
|
WHERE RN = 1
|
|
) AS Source
|
|
PIVOT
|
|
(
|
|
MAX(StartDT)
|
|
FOR UpdateType IN ([1], [2], [3])
|
|
) AS PivotTable |