Need help with a query

• Chinna

  SSCertifiable

  Points: 5408

  More actions

  December 1, 2014 at 4:19 pm

  #295517

  I need to retrieve the pts from table #test1 based on the fields when mapped..

  Name & Type columns should match ..Based on the data below the output should be

  id NameTypeCode CityIType BPS

  1EGFN432 HY F2 10

  2 EG FN 432 ON F1 20

  3 EG FN 433 On F1 30

  It has order of priority to get points

  1.When all of the field matches then get the respective pts .

  2.When name and Type matched and rest fields doesnt match but is null in #test2 table then retrieve those pts

  --drop table #test

  --drop table #test1

  Create table #test

  (

  id int identity(1,1) not null,

  Name varchar(100) NULL,

  Type varchar(100) NULL,

  Code varchar(100) NULL,

  City varchar(100) NULL,

  IType varchar(100) NULL

  )

  Insert into #test

  Select 'EG','FN','432','HY','F2' union all

  Select 'EG','FN','432','ON','F1' union all

  Select 'EG','FN','433','ON','F1'

  Create table #test1

  (

  id int identity(1,1) not null,

  Name varchar(100) NULL,

  Type varchar(100) NULL,

  Code varchar(100) NULL,

  City varchar(100) NULL,

  IType varchar(100) NULL,

  Pts INT NULL

  )

  Insert into #test1

  Select 'EG','FN','432','HY','F2',10 union all

  Select 'EG','FN','432',NULL,NULL ,20 union all

  Select 'EG','FN',NULL,NULL,NULL,30 union all

  Select 'EG','FN','433','HY','F2',40

  SELECT *

  FROM #test

  SELECT *

  FROM #test1

  Thanks,
  Chinna

  Its the Journey which gives you Happiness not the Destination-- Dan Millman

• imex

  Ten Centuries

  Points: 1234

  More actions

  December 2, 2014 at 5:37 am

  #1762009

  Try:

  select

  *

  from #test as t

  cross apply

  (

  select top(1)

  t1.Pts

  from #test1 as t1

  where

  t1.Name = t.Name and

  t1.Type = t.Type and

  (t1.Code = t.Code or t1.Code is null) and

  (t1.City = t.City or t1.City is null) and

  (t1.IType = t.IType or t1.IType is null)

  order by

  t1.Code desc,

  t1.City desc,

  t1.IType desc

  ) as t1

  Hope this helps.

  https://www.imoveisemexposicao.com.br/imobiliarias-em-guarulhos

• Chinna

  SSCertifiable

  Points: 5408

  More actions

  December 2, 2014 at 3:12 pm

  #1762238

  Worked like a charm .Thank you

  Thanks,
  Chinna

  Its the Journey which gives you Happiness not the Destination-- Dan Millman

• mister.magoo

  SSC-Forever

  Points: 47174

  More actions

  December 2, 2014 at 5:07 pm

  #1762254

  Sri8143 (12/2/2014)

  ---

  Its the Journey which gives you Happiness not the Destination-- Dan Millman

  ...my guess is that Dan Millman never had to fly economy to go on holiday or he wouldn't have said that!

  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]