Need help with a query

Question

Post reply

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
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]

imex Ten Centuries Points: 1234 More actions · Answer 1

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 · Answer 2

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 · Answer 3

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);