Query assistance....
-
I really do appreciate anyone's willingness to give me direction.
I need to select from a table that looks like this:
In this table, you see that servA/comp1 went from bad > good and the latest date/time shows good.
You will also see this with servB/comp3.
With servA/comp2 it goes from bad > good > bad with the latest date/time showing bad status.
What would my select statement look like to get just the highlighted row?
Thanks again for any assistance.
IDEA01
-
Do you need to get Server/component compound that change from bad to good and again to bad? Or would you include the ones that go from good to bad and good again? Is it possible to have an additional row with the same status as the previous one?
Could you post DDL and sample data in the form of Insert statements? This article can show you how: http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/
-
If you're looking only for those rows where the 'latest' status is 'bad', something like this should work:
if object_id('tempdb..#GoodBad', 'U') is not nulldrop table #GoodBad;
create table #GoodBad
(
Svr varchar(10)
,Component varchar(10)
,Stat varchar(10)
,dt datetime
);
insert #GoodBad
(
Svr
,Component
,Stat
,dt
)
values (N'servA', N'comp1', N'good', cast(N'2015-06-01 11:03:40.417' as datetime)),
(N'servA', N'comp1', N'bad', cast(N'2015-06-01 07:03:45.360' as datetime)),
(N'servA', N'comp2', N'bad', cast(N'2015-06-01 07:03:45.360' as datetime)),
(N'servB', N'comp3', N'good', cast(N'2015-06-01 07:03:45.360' as datetime)),
(N'servB', N'comp3', N'bad', cast(N'2015-06-01 07:03:44.417' as datetime)),
(N'servA', N'comp2', N'good', cast(N'2015-05-31 11:07:44.433' as datetime)),
(N'servA', N'comp2', N'bad', cast(N'2015-05-31 01:20:39.540' as datetime)),
(N'servC', N'comp2', N'bad', cast(N'2015-05-30 12:23:43.234' as datetime));
with OrderedRows
as (
select ro = row_number() over (partition by gb.Svr, gb.Component order by gb.Svr, gb.Component, gb.dt desc)
,gb.*
from #GoodBad gb
)
select *
from OrderedRows
where ro = 1
and OrderedRows.Stat = 'Bad';
Edit: modified to include your sample data.
The absence of evidence is not evidence of absence.
Martin ReesYou can lead a horse to water, but a pencil must be lead.
Stan Laurel -
This is the table script and some sample data:
USE [tempdb]
GO
--DROP table [dbo].[status]
CREATE TABLE [dbo].[status](
[server] [nvarchar](50) NULL,
[component] [nvarchar](50) NULL,
[status] [nvarchar](50) NULL,
[date] [datetime] NULL
) ON [PRIMARY]
GO
INSERT INTO [dbo].[status] VALUES (N'servA', N'comp1', N'good', CAST(N'2015-06-01 11:03:40.417' AS DateTime))
GO
INSERT INTO [dbo].[status] VALUES (N'servA', N'comp1', N'bad', CAST(N'2015-06-01 07:03:45.360' AS DateTime))
GO
INSERT INTO [dbo].[status] VALUES (N'servA', N'comp2', N'bad', CAST(N'2015-06-01 07:03:45.360' AS DateTime))
GO
INSERT INTO [dbo].[status] VALUES (N'servB', N'comp3', N'good', CAST(N'2015-06-01 07:03:45.360' AS DateTime))
GO
INSERT INTO [dbo].[status] VALUES (N'servB', N'comp3', N'bad', CAST(N'2015-06-01 07:03:44.417' AS DateTime))
GO
INSERT INTO [dbo].[status] VALUES (N'servA', N'comp2', N'good', CAST(N'2015-05-31 11:07:44.433' AS DateTime))
GO
INSERT INTO [dbo].[status] VALUES (N'servA', N'comp2', N'bad', CAST(N'2015-05-31 01:20:39.540' AS DateTime))
GO
INSERT INTO [dbo].[status] VALUES (N'servC', N'comp2', N'bad', CAST(N'2015-05-30 12:23:43.234' AS DateTime))
GO
This is a select all of the table & data above:
+--------+-----------+--------+-------------------------+
| server | component | status | date |
+--------+-----------+--------+-------------------------+
| servA | comp1 | good | 2015-06-01 11:03:40.417 |
| servA | comp1 | bad | 2015-06-01 07:03:45.360 |
| servA | comp2 | bad | 2015-06-01 07:03:45.360 |
| servB | comp3 | good | 2015-06-01 07:03:45.360 |
| servB | comp3 | bad | 2015-06-01 07:03:44.417 |
| servA | comp2 | good | 2015-05-31 11:07:44.433 |
| servA | comp2 | bad | 2015-05-31 01:20:39.540 |
| servC | comp2 | bad | 2015-05-30 12:23:43.234|
+--------+-----------+--------+-------------------------+
This is what Iām trying to obtain:
Just the rows that are currently bad (have not good status after the last bad status for the server/component)
+--------+-----------+--------+-------------------------+
| server | component | status | date |
+--------+-----------+--------+-------------------------+
| servA | comp2 | bad | 2015-06-01 07:03:45.360 |
| servC | comp2 | bad | 2015-05-30 12:23:43.234|
+--------+-----------+--------+-------------------------+
-
Thanks Phil, that was very helpful!
-
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!
-
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!
Go for it. You have all the DDL and sample data, so shouldn't take you long.
The absence of evidence is not evidence of absence.
Martin ReesYou can lead a horse to water, but a pencil must be lead.
Stan Laurel -
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!
So, post the code! A test harness has already been provided in this thread!
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
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.
-
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)
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply