General SQL question/assistance

• woody_rd

  Ten Centuries

  Points: 1012

  More actions

  January 30, 2020 at 9:47 pm

  #3721797

  Hey all.  Novice SQL guy here and need a little assistance with a query.  Here's a sample data set:

  CREATE TABLE #temp_dat1 (

  Id int null,

  Code nvarchar(50) null,

  CompletedDate datetime null)

  INSERT INTO #temp_dat1 VALUES (1, 'Other', '2020-01-02 17:14:52.000')

  INSERT INTO #temp_dat1 VALUES (1, 'Verified', '2020-01-02 16:36:09.000')

  INSERT INTO #temp_dat1 VALUES (1, 'Loaner', '2020-01-02 17:21:45.000')

  INSERT INTO #temp_dat1 VALUES (1, 'Not Found', '2020-01-02 17:21:49.000')

  INSERT INTO #temp_dat1 VALUES (1, 'Demo', '2020-01-02 19:03:52.000')

  select Id, MAX(CompletedDate) from #temp_dat1 GROUP BY Id

   

  So I wold get the following values from the select:

  1  2020-01-02 19:03:52.000

  What I want to do is not have a result returned if the Code "Not Found" is associated with the MAX(CompletedDate) value.  Data would look like this:

  CREATE TABLE #temp_dat1 (

  Id int null,

  Code nvarchar(50) null,

  CompletedDate datetime null)

  INSERT INTO #temp_dat1 VALUES (1, 'Other', '2020-01-02 17:14:52.000')

  INSERT INTO #temp_dat1 VALUES (1, 'Verified', '2020-01-02 16:36:09.000')

  INSERT INTO #temp_dat1 VALUES (1, 'Loaner', '2020-01-02 17:21:45.000')

  INSERT INTO #temp_dat1 VALUES (1, 'Demo', '2020-01-02 17:21:49.000')

  INSERT INTO #temp_dat1 VALUES (1, 'Not Found', '2020-01-02 19:03:52.000')

  select Id, MAX(CompletedDate) from #temp_dat1 GROUP BY Id

  What's the best way of excluding (1, 'Not Found', '2020-01-02 19:03:52.000') without selecting the Code column in the query.  A where clause for (Code != 'Not Found') only excludes that record and brings back the next MAX(CompletedDate).  I want the result to return nothing if 'Not Found' is the last record for that date and Id.

  Any help is appreciated!

   

• ScottPletcher

  SSC Guru

  Points: 101134

  More actions

  January 30, 2020 at 10:55 pm

  #3721808

  select Id, CompletedDate, Code 
  from (
      select *, row_number() over(partition by id order by completeddate desc) AS row_num
      from #temp_dat1
  ) as query1
  where row_num = 1 and code <> 'Not Found'

  SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".