tsql help..

• etirem

  SSCommitted

  Points: 1655

  More actions

  August 1, 2012 at 1:05 pm

  #392872

  Hi,

  I have data in 2 temp tables below SQL and need the output as:

  

  IF OBJECT_ID('Tempdb..#tTable1') IS NOT NULL

  DROP TABLE #tTable1

  CREATE TABLE #tTable1( ID INT, ADT DATETIME)

  INSERT INTO #tTable1(ID, ADT)

  SELECT 1,'2012-07-09 08:40:00.000'

  UNION

  SELECT 1,'2012-05-18 15:00:00.000'

  UNION

  SELECT 1,'2012-05-18 10:30:00.000'

  UNION

  SELECT 1,'2012-02-03 15:50:00.000'

  UNION

  SELECT 1,'2012-01-19 09:30:00.000'

  UNION

  SELECT 1,'2012-01-10 14:00:00.000'

  UNION

  SELECT 1,'2011-11-25 12:30:00.000'

  IF OBJECT_ID('Tempdb..#tTable2') IS NOT NULL

  DROP TABLE #tTable2

  CREATE TABLE #tTable2( ID INT, RDT DATETIME, VALUE INT)

  INSERT INTO #tTable2(ID, RDT, VALUE)

  SELECT 1,'2012-05-18 10:48:55.170', 100

  UNION

  SELECT 1,'2012-01-10 15:25:40.073', 120

  UNION

  SELECT 1,'2010-05-05 09:50:47.420', 250

  UNION

  SELECT 1,'2008-03-11 14:05:00.683', 50

  SELECT * FROM #tTable1

  ORDER BY ADT DESC

  SELECT * FROM #tTable2

  ORDER BY RDT DESC

  DROP TABLE #tTable1

  DROP TABLE #tTable2

• ColdCoffee

  SSC-Dedicated

  Points: 39972

  More actions

  August 1, 2012 at 1:13 pm

  #1520490

  Good job posting almost 90% of the information required..

  ONe question: WHats the logic behind the expected reuslt?

• etirem

  SSCommitted

  Points: 1655

  More actions

  August 1, 2012 at 1:20 pm

  #1520493

  Th logic is between the dates ADT and RDT.

  ADT date range should have the most recent of the RDT.

  

• ColdCoffee

  SSC-Dedicated

  Points: 39972

  More actions

  August 1, 2012 at 1:31 pm

  #1520496

  Gotcha!

  Here is what you are looking for:

  SELECT *

  FROM #tTable1 OtrTbl

  OUTER APPLY

  (

  SELECT TOP 1 InrTab.RDT , InrTab.VALUE

  FROM #tTable2 InrTab

  WHERE DATEADD( DD, DATEDIFF(DD , 0 ,OtrTbl.ADT) ,0) >= DATEADD( DD, DATEDIFF(DD , 0 ,InrTab.RDT) ,0)

  ORDER BY InrTab.RDT DESC

  ) OtrApp

• etirem

  SSCommitted

  Points: 1655

  More actions

  August 1, 2012 at 1:55 pm

  #1520502

  Thank you...But is there any simple way of writing this. I have lot of ID`s and it has to be implemented for those too.

• ColdCoffee

  SSC-Dedicated

  Points: 39972

  More actions

  August 1, 2012 at 1:57 pm

  #1520504

  This is the simplest way, but i sense may not be the most efficient way. Let me see if there is another way to tackle this.