Query HELP - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Query HELP

Author

Topic

nhess80
Yak Posting Veteran

83 Posts

Posted - 2010-03-17 : 15:27:41

Ok so I have given an example below of how the query works as of right now and I have also listed the results I’m trying to produce.

Declare @LergData table(lata varchar(4),ocn varchar(4),npa varchar(3),nxx varchar(3),y varchar(2))
insert into @LergData select 224,4804,201,204,0
insert into @LergData select 224,8700,201,204,1
insert into @LergData select 224,6664,201,204,2
insert into @LergData select 224,325E,201,204,3
insert into @LergData select 224,389C,201,204,4
insert into @LergData select 224,389C,201,204,5
insert into @LergData select 224,6664,201,204,6
insert into @LergData select 224,6664,201,204,7
insert into @LergData select 224,6623,201,204,8
insert into @LergData select 224,4804,201,204,9
insert into @LergData select 224,389C,201,204,'A'

Declare @OCN Table (lata varchar(4),ocn varchar(4),inter decimal(18, 7),intra decimal(18, 7))
insert into @OCN select 224,325E,0.0167,0.0167
insert into @OCN select 224,389C,0.0165,0.0165
insert into @OCN select 224,4804,0.0155,0.0155
insert into @OCN select 224,6623,0.0145,0.0145
insert into @OCN select 224,6664,0.0167,0.0167
insert into @OCN select 224,8700,0.0165,0.0165

SELECT b.npa +''+ b.nxx +''+ REPLACE(b.y,'A','') AS NPANXXY, a.inter,a.intra
FROM @OCN AS a INNER JOIN @LergData AS b ON a.lata = b.lata AND a.ocn = b.ocn
ORDER BY NPANXXY

CURRENT RESULTS

201204 0.0165000 0.0165000
2012040 0.0155000 0.0155000
2012041 0.0165000 0.0165000
2012042 0.0167000 0.0167000
2012043 0.0167000 0.0167000
2012044 0.0165000 0.0165000
2012045 0.0165000 0.0165000
2012046 0.0167000 0.0167000
2012047 0.0167000 0.0167000
2012048 0.0145000 0.0145000
2012049 0.0155000 0.0155000

DESIRED RESULTS

201204 0.0165000 0.0165000
2012040 0.0165000 0.0165000
2012041 0.0165000 0.0165000
2012042 0.0165000 0.0165000
2012043 0.0165000 0.0165000
2012044 0.0165000 0.0165000
2012045 0.0165000 0.0165000
2012046 0.0165000 0.0165000
2012047 0.0165000 0.0165000
2012048 0.0165000 0.0165000
2012049 0.0165000 0.0165000

Basically I want the 201204 Rate to supersede the other rates and just use the rate that goes with 201204. The "A" in the y column identifies this. This is just a small amount of data, the real data will contain others.

For example:

201210,
2012101,
2012102,
2012103,
2012104

and whatever rate matches with 201210 will also be used with the others that match those first 6 digits.

Please let me know if I did not explain this well

Thanks for your help,

Nick

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-03-17 : 17:02:50

I think this should work:
SELECT b.npa +''+ b.nxx +''+ REPLACE(b.y,'A','') AS NPANXXY, dt.inter,dt.intra
FROM @OCN AS a
INNER JOIN @LergData AS b ON a.lata = b.lata AND a.ocn = b.ocn
cross join (select o.inter,o.intra from @LergData l join @OCN o on l.lata = o.lata AND l.ocn = o.ocn and l.y='A')dt
ORDER BY NPANXXY

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-03-18 : 04:01:50

I have made a correction, this should work:

SELECT b.npa + b.nxx + REPLACE(b.y,'A','') AS NPANXXY, dt.inter,dt.intra
FROM @OCN AS a 
INNER JOIN @LergData AS b ON a.lata = b.lata AND a.ocn = b.ocn
inner join (select l.npa + l.nxx as ttt, 
            o.inter,
            o.intra 
            from @LergData l 
            join @OCN o on l.lata = o.lata AND l.ocn = o.ocn and l.y='A')dt
on dt.ttt = b.npa + b.nxx
ORDER BY NPANXXY

No, you're never too old to Yak'n'Roll if you're too young to die.

nhess80
Yak Posting Veteran

83 Posts

Posted - 2010-03-18 : 12:53:08

Thank you very much, it worked perfect

quote:

Originally posted by webfred

I have made a correction, this should work:

SELECT b.npa + b.nxx + REPLACE(b.y,'A','') AS NPANXXY, dt.inter,dt.intra
FROM @OCN AS a 
INNER JOIN @LergData AS b ON a.lata = b.lata AND a.ocn = b.ocn
inner join (select l.npa + l.nxx as ttt, 
            o.inter,
            o.intra 
            from @LergData l 
            join @OCN o on l.lata = o.lata AND l.ocn = o.ocn and l.y='A')dt
on dt.ttt = b.npa + b.nxx
ORDER BY NPANXXY

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-03-19 : 04:30:53

quote:
Originally posted by nhess80

Thank you very much, it worked perfect

quote:
Originally posted by webfred

I have made a correction, this should work:
SELECT b.npa + b.nxx + REPLACE(b.y,'A','') AS NPANXXY, dt.inter,dt.intra
FROM @OCN AS a
INNER JOIN @LergData AS b ON a.lata = b.lata AND a.ocn = b.ocn
inner join (select l.npa + l.nxx as ttt,
o.inter,
o.intra
from @LergData l
join @OCN o on l.lata = o.lata AND l.ocn = o.ocn and l.y='A')dt
on dt.ttt = b.npa + b.nxx
ORDER BY NPANXXY

No, you're never too old to Yak'n'Roll if you're too young to die.

welcome

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources