The case:
2 tables, with a FK from one to a statuses log table.
We need to select for each row in the first table the last status with it's datetime and the description of it, and also - in the same row - the datetime of the last specific stauts (in the example - 303).
there is no one absolut answer, it depend on the data in the tables and exists indexes, constraints, etc om the table.
-- create the Demo tables and data:
CREATE TABLE TableA (ID int identity(1,1) NOT NULL, SomeFkID int)
CREATE TABLE TableFk (SomeFkID int, SomeFkStatus int, SomeFkDatetime datetime, SomeFkDesc nvarchar(50))
INSERT INTO TableA (SomeFkID)
VALUES (1), (2), (3), (4)
INSERT INTO TableFk (SomeFkID, SomeFkStatus, SomeFkDatetime, SomeFkDesc)
VALUES
(1, 1, '2017-01-01', N'FK 1 1 1'),
(2, 1, '2017-01-02', N'FK 2 1 1'), (2, 303, '2017-01-03', N'FK 2 303 2'), (2, 3, '2017-01-04', N'FK 2 3 3'),
(2, 303, '2017-01-05', N'FK 2 303 4'), (2, 4, '2017-01-06', N'FK 2 4 5'),
(3, 1, '2017-01-01', N'FK 3 1 1'), (3, 2, '2017-01-03', N'FK 3 21'),
(4, 1, '2017-01-05', N'FK 4 1 1'), (4, 303, '2017-01-08', N'FK 4 303 1')
SELECT * FROM TableA
SELECT * FROM TableFk
--SELECT SomeFkID, SomeFkStatus, max(SomeFkDatetime) as maxSomeFkDatetime
--FROM TableFk f
--GROUP BY SomeFkID, SomeFkStatus
--ORDER BY SomeFkID, SomeFkStatus
--SELECT ROW_NUMBER() OVER (PARTITION BY SomeFkID ORDER BY SomeFkDatetime DESC) as rn,
-- ROW_NUMBER() OVER (PARTITION BY SomeFkID ORDER BY (case when SomeFkStatus = 303 then SomeFkDatetime else NULL end) DESC) as rn303,
-- SomeFkID, SomeFkStatus, SomeFkDatetime, SomeFkDesc
--FROM TableFk f
--ORDER BY SomeFkID, rn
-- option 1: CTE with 2 ROW_NUMBERs
; with rnFK as
(
SELECT ROW_NUMBER() OVER (PARTITION BY SomeFkID ORDER BY SomeFkDatetime DESC) as rn,
ROW_NUMBER() OVER (PARTITION BY SomeFkID ORDER BY (case when SomeFkStatus = 303 then SomeFkDatetime else NULL end) DESC) as rn303,
SomeFkID, SomeFkStatus, SomeFkDatetime, SomeFkDesc
FROM TableFk f
)
SELECT a.ID, a.SomeFkID,
f303.SomeFkDatetime AS YatzranStatusDate,
f.SomeFkDesc AS ErrorDesc,
f.SomeFkStatus as FileStatusID,
f.SomeFkDatetime
FROM TableA a
JOIN rnFK f ON a.SomeFkID = f.SomeFkID
AND f.rn = 1
LEFT JOIN rnFK f303 ON a.SomeFkID = f303.SomeFkID
AND f303.rn303 = 1
AND f303.SomeFkStatus = 303
-- option 2: two OUTER APPLYs - one for the last status and one for 303 status
SELECT a.ID, a.SomeFkID,
f303.SomeFkDatetime AS YatzranStatusDate,
f.SomeFkDesc AS ErrorDesc,
f.SomeFkStatus as FileStatusID,
f.SomeFkDatetime
FROM TableA a
OUTER APPLY
( select top 1 SomeFkID, SomeFkStatus, SomeFkDatetime, SomeFkDesc
from TableFk where SomeFkID = a.SomeFkID
order by SomeFkDatetime desc
) f
OUTER APPLY
( select top 1 SomeFkID, SomeFkStatus, SomeFkDatetime, SomeFkDesc
from TableFk where SomeFkID = a.SomeFkID and SomeFkStatus = 303
order by SomeFkDatetime desc
) f303
-- option 3: one OUTER APPLY for the last status and Subquery (select top 1 order by) for the 303 status column
SELECT a.ID, a.SomeFkID,
( select top 1 SomeFkDatetime
from TableFk where SomeFkID = a.SomeFkID and SomeFkStatus = 303
order by SomeFkDatetime desc
) AS YatzranStatusDate,
f.SomeFkDesc AS ErrorDesc,
f.SomeFkStatus as FileStatusID,
f.SomeFkDatetime
FROM TableA a
OUTER APPLY
( select top 1 SomeFkID, SomeFkStatus, SomeFkDatetime, SomeFkDesc
from TableFk where SomeFkID = a.SomeFkID
order by SomeFkDatetime desc
) f
-- option 4: one OUTER APPLY for the last status and Subquery (max(SomeFkDatetime)) for the 303 status column
SELECT a.ID, a.SomeFkID,
( select max(SomeFkDatetime)
from TableFk where SomeFkID = a.SomeFkID and SomeFkStatus = 303
) AS YatzranStatusDate,
f.SomeFkDesc AS ErrorDesc,
f.SomeFkStatus as FileStatusID,
f.SomeFkDatetime
FROM TableA a
OUTER APPLY
( select top 1 SomeFkID, SomeFkStatus, SomeFkDatetime, SomeFkDesc
from TableFk where SomeFkID = a.SomeFkID
order by SomeFkDatetime desc
) f
-- option 4: FIRST_VALUE and MAX functions:
SELECT DISTINCT
a.ID, a.SomeFkID,
MAX (case when f.SomeFkStatus = 303 then f.SomeFkDatetime else NULL end) OVER (PARTITION BY f.SomeFkID) AS YatzranStatusDate,
FIRST_VALUE(f.SomeFkDesc) OVER (PARTITION BY f.SomeFkID ORDER BY f.SomeFkDatetime desc) AS ErrorDesc,
FIRST_VALUE(f.SomeFkStatus) OVER (PARTITION BY f.SomeFkID ORDER BY f.SomeFkDatetime desc) as FileStatusID,
FIRST_VALUE(f.SomeFkDatetime) OVER (PARTITION BY f.SomeFkID ORDER BY f.SomeFkDatetime desc) as SomeFkDatetime
FROM TableA a
LEFT JOIN TableFk F ON A.SomeFkID = f.SomeFkID
-- try it with and without :
CREATE NONCLUSTERED INDEX [IX_TableFk] ON TableFk
(
[SomeFkDatetime] DESC
)
INCLUDE ([SomeFkID])
ON [PRIMARY]
DROP TABLE TableA
DROP TABLE TableFk
No comments:
Post a Comment