idea01 (6/2/2015)
Ozzmodiar (6/2/2015)
I see you have a perfectly valid solution, so I won't bother typing it all up unless you're interested.There is a very elegant solution to this problem using the LAG function, It wouldn't require the CTE or the Ranking function.
Cheers!
While I do have a working answer, I'm always looking for alternate ways to do things. I value all the input here.
Then here's another way:
DECLARE @status AS TABLE (
[server] [nvarchar](50) NULL,
[component] [nvarchar](50) NULL,
[status] [nvarchar](50) NULL,
[date] [datetime] NULL
);
INSERT INTO @status VALUES (N'servA', N'comp1', N'good', '2015-06-01 11:03:40.417');
INSERT INTO @status VALUES (N'servA', N'comp1', N'bad', '2015-06-01 07:03:45.360');
INSERT INTO @status VALUES (N'servA', N'comp2', N'bad', '2015-06-01 07:03:45.360');
INSERT INTO @status VALUES (N'servB', N'comp3', N'good', '2015-06-01 07:03:45.360');
INSERT INTO @status VALUES (N'servB', N'comp3', N'bad', '2015-06-01 07:03:44.417');
INSERT INTO @status VALUES (N'servA', N'comp2', N'good', '2015-05-31 11:07:44.433');
INSERT INTO @status VALUES (N'servA', N'comp2', N'bad', '2015-05-31 01:20:39.540');
INSERT INTO @status VALUES (N'servC', N'comp2', N'bad', '2015-05-30 12:23:43.234');
WITH MAX_DATES AS (
SELECT [server], component, MAX([date]) AS MAX_DATE
FROM @status
GROUP BY [server], component
)
SELECT SB.*
FROM @status AS SB
INNER JOIN MAX_DATES AS MD
ON SB.[server] = MD.[server]
AND SB.component = MD.component
AND SB.[date] = MD.MAX_DATE
WHERE SB.[status] = 'bad';
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)