SELECT last not null date

• PSB

  SSChampion

  Points: 13195

  More actions

  December 9, 2015 at 1:17 pm

  #306995

  CREATE TABLE #TEmp

  ( ID INT,

  String VARCHAR(MAX),

  Date1 DATETIME

  )

  INSERT INTO #Temp

  (ID ,

  String ,

  Date1

  )

  SELECT 1,'Rejected on: 4/20/2015 11:17:11 AM', '4/20/2015 11:17:11 AM' UNION ALL

  SELECT 1,'Rejected on: 5/20/2015 11:17:11 AM', '4/21/2015 11:17:11 AM' UNION ALL

  SELECT 1,'Resubmitted on: 6/20/2015 11:17:11 AM', '6/24/2015 11:17:11 AM' UNION ALL

  SELECT 2,'Rejected on: 7/20/2015 11:17:11 AM', '7/20/2015 11:17:11 AM' UNION ALL

  SELECT 2,'Resubmitted on: ', NULL UNION ALL

  SELECT 2,'Resubmitted on: 8/20/2015 11:17:11 AM' ,'8/20/2015 11:17:11 AM' UNION ALL

  SELECT 2,'Resubmitted on: ', NULL

  SELECT * FROM #TEMP

  --SELECT First REjected DAte and Last Not null and dateonly resubmitted date

  SELECT 1,'4/20/2015 11:17:11 AM' AS [Rejected on],'6/24/2015 11:17:11 AM' AS [Resubmitted on] UNION

  SELECT 2,'7/20/2015 11:17:11 AM' AS [Rejected on],'8/20/2015 11:17:11 AM' AS [Resubmitted on]

  DROP TABLE #TEMP

  Thanks,

  PSB

• Steve Jones - SSC Editor

  SSC Guru

  Points: 736270

  More actions

  December 9, 2015 at 1:30 pm

  #1845039

  I'm guessing the end set is what you want? Find the max date for each id

  SELECT Id

  , MAX(te.Date1) AS 'Resubmitted On'

  FROM #TEmp AS te

  GROUP BY id

  Then you can put this in a CTE, or make a correlated subquery that joins this to get the other data.

  WITH MyCTE(id, date1)

  AS

  (

  SELECT Id

  , MAX(te.Date1) AS 'Resubmitted On'

  FROM #TEmp AS te

  GROUP BY id

  )

  SELECT *

  FROM #TEmp AS te

  INNER JOIN MyCTE

  ON MyCTE.id = te.ID

  AND MyCTE.date1 = te.Date1

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  December 9, 2015 at 1:41 pm

  #1845045

  Probably a cross tab could do the trick:

  SELECT ID,

  MIN( CASE WHEN String LIKE 'Rejected%' THEN Date1 END) AS [Rejected on],

  MAX( CASE WHEN String LIKE 'Resubmitted%' THEN Date1 END) AS [Resubmitted on]

  FROM #TEMP

  WHERE Date1 IS NOT NULL

  GROUP BY ID;

  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

• Steve Jones - SSC Editor

  SSC Guru

  Points: 736270

  More actions

  December 9, 2015 at 1:50 pm

  #1845049

  Ah, good one Luis. I realize I misread this as first. You want to parse out related rows.

  I think Luis' solution will work, but only if your rules are for a particular id, you want the earliest rejection and the latest resubmit.

  However your results are confusing. Do you want the date in the string or the date1 for the resubmit date?

• PSB

  SSChampion

  Points: 13195

  More actions

  December 9, 2015 at 7:42 pm

  #1845089

  Date1 in the resubmit.