StefanandClaude Sonnet 5
c1936a327e
Playwright Tests / test (push) Canceled after 0s
Add datetime_LocalToUTC / datetime_utcToLocal, and two widget query functions (fds_wdg_MFRSync, fds_wgd_lastEmails). Remove the unreferenced backup__fds__* function files superseded by their current counterparts, and suppress SQL71501 (unresolved cross-database/composite references) project-wide. Co-Authored-By: Claude Sonnet 5 <noreply@anthropic.com>
33 lines
1.1 KiB
Transact-SQL
33 lines
1.1 KiB
Transact-SQL
CREATE FUNCTION dbo.fds_wdg_MFRSync ()
|
|
RETURNS TABLE
|
|
AS
|
|
RETURN
|
|
(
|
|
WITH LastPerTableAction AS
|
|
(
|
|
SELECT
|
|
[table],
|
|
[action],
|
|
[timestamp],
|
|
TRY_CAST(JSON_VALUE([info], '$.count') AS INT) AS ItemCount,
|
|
ROW_NUMBER() OVER (PARTITION BY [table], [action] ORDER BY [timestamp] DESC) AS rn
|
|
FROM [dbo].[fds__log]
|
|
WHERE --[action] IN (N'imdu_reset', N'imdu_short', N'update_reset', N'update_short')
|
|
[timestamp] > DATEADD(DAY, -3, GETUTCDATE())
|
|
),
|
|
Formatted AS
|
|
(
|
|
SELECT
|
|
[table],
|
|
[action],
|
|
CONCAT(FORMAT([dbo].[datetime_utcToLocal]([timestamp], null), 'dd.MM. HH:mm', 'de-DE'), 'Uhr - (', ISNULL(CONVERT(varchar(20), ItemCount), 'n/a'), ' Datensätze)') AS Cell
|
|
FROM LastPerTableAction
|
|
WHERE rn = 1
|
|
)
|
|
SELECT [Info] = SUBSTRING([table], 6, 100), [imdu_reset], [imdu_short], [update_reset], [update_short]
|
|
FROM Formatted
|
|
PIVOT
|
|
(
|
|
MAX(Cell) FOR [action] IN ([imdu_reset], [imdu_short], [update_reset], [update_short])
|
|
) AS pvt
|
|
); |