Max date and associated row data

• mmcardle

  SSCommitted

  Points: 1543

  More actions

  June 28, 2010 at 5:37 am

  #220546

  I've been scratching my head all morning about how to do something that at first glance seems easy. I'm losing the plot I think. I could come up with a solution, but using temp tables to do it isn't something I like, so I wondered if you good people had an elegant solution?

  This sets up the data and selects it:

  DECLARE @wostage TABLE (wos_ordref CHAR(8), wos_line INT, wos_startdate SMALLDATETIME)

  DECLARE @woitem TABLE (woi_ordref CHAR(8), woi_line INT, woi_product VARCHAR(40))

  DECLARE @xTOOLS TABLE (tool VARCHAR(40))

  INSERT INTO @wostage

  SELECT 'WO288167', 10, '09-12-2009' UNION ALL

  SELECT 'WO288167', 100, '20/12/2009' UNION ALL

  SELECT 'WO288168', 10, '12/11/2009' UNION ALL

  SELECT 'WO288153', 30, '11/08/2009' UNION ALL

  SELECT 'WO288100', 20, '05/09/2009'

  INSERT INTO @woitem

  SELECT 'WO288167', 50, 'T0001' UNION ALL

  SELECT 'WO288168', 40, 'T0001' UNION ALL

  SELECT 'WO288153', 60, 'T0010' UNION ALL

  SELECT 'WO288100', 30, 'T0010'

  INSERT INTO @xTOOLS

  SELECT 'T0001' UNION ALL

  SELECT 'T0010'

  SELECT woi_product, woi_ordref, wos_startdate

  FROM @woitem JOIN @xTOOLS

  ON woi_product = tool JOIN @wostage

  ON woi_ordref = wos_ordref AND wos_line =

  (SELECT MAX(wos_line)

  FROM @wostage

  WHERE wos_ordref = woi_ordref

  AND wos_line < woi_line)

  ORDER BY woi_product, wos_startdate

  Which returns:

  T0001WO2881682009-11-12 00:00:00

  T0001WO2881672009-12-09 00:00:00

  T0010WO2881532009-08-11 00:00:00

  T0010WO2881002009-09-05 00:00:00

  Just to explain, the @wostage records link to @woitem on the xxx_ordref field, but not on xxx_line. The join here seeks to match up woi_line to the highest wos_line value (don't think you need to know why, but I can explain further if you want).

  What I want, is a list of each tool, with the latest date found in the data and the wo that it relates to. In other words, from the above data, I would want this as my result:

  T0001WO2881672009-12-09 00:00:00

  T0010WO2881002009-09-05 00:00:00

  It seemed easy when I started, but I've spent hours trying to find a slick method without much luck.

  After this bit, the next step is to get the same result from a UNION of two queries, each with the same data structure and holding the same type of data, but one is live and the other is archive. So, @xTOOL will remain the same, but there will be @wostage_live, @wostage_arch, @woitem_live and @woitem_arch.

  Hope that makes sense.

  Regards,

  Mark

• mister.magoo

  SSC-Forever

  Points: 47174

  More actions

  June 28, 2010 at 6:41 am

  #1185682

  Try this

  SELECT tool AS product, wos_ordref, wos_startdate

  FROM @xTOOLS

  OUTER APPLY (SELECT TOP 1 wos_ordref,wos_startdate

  FROM @wostage

  JOIN @woitem ON wos_ordref = woi_ordref

  WHERE woi_product = tool

  ORDER BY wos_startdate desc) wos

  ORDER BY tool,wos_startdate

  MM
  

  ---

  
  select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

• Forum Etiquette: How to post Reporting Services problems
[/url]
• Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]
• How to Post Performance Problems - by Gail Shaw
[/url]