Query assistance....

• idea01

  Grasshopper

  Points: 20

  More actions

  June 1, 2015 at 10:50 am

  #304552

  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

• Luis Cazares

  SSC Guru

  Points: 183698

  More actions

  June 1, 2015 at 11:07 am

  #1802088

  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/

  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2

• Phil Parkin

  SSC Guru

  Points: 246712

  More actions

  June 1, 2015 at 11:34 am

  #1802096

  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 null

  drop 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.

  If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

• idea01

  Grasshopper

  Points: 20

  More actions

  June 1, 2015 at 11:38 am

  #1802100

  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|

  +--------+-----------+--------+-------------------------+

• idea01

  Grasshopper

  Points: 20

  More actions

  June 1, 2015 at 2:32 pm

  #1802162

  Thanks Phil, that was very helpful!

• Ozzmodiar

  Ten Centuries

  Points: 1315

  More actions

  June 2, 2015 at 1:16 pm

  #1802422

  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!

• Phil Parkin

  SSC Guru

  Points: 246712

  More actions

  June 2, 2015 at 1:34 pm

  #1802430

  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.

  If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

• Jeff Moden

  SSC Guru

  Points: 1003873

  More actions

  June 2, 2015 at 7:59 pm

  #1802482

  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)
  

• idea01

  Grasshopper

  Points: 20

  More actions

  June 2, 2015 at 8:20 pm

  #1802487

  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.

• sgmunson

  SSC Guru

  Points: 110639

  More actions

  June 16, 2015 at 11:20 am

  #1805805

  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)