Click here to Skip to main content
12,699,123 members (25,136 online)
Rate this:
 
Please Sign up or sign in to vote.
See more: SQL-Server
Hey guys, im stumpted with this all day and nothing I have tried has worked. I need to place the condition in the where clause as a condition for my join i.e left outer join dbo.ExchangeRate based on these conditions can someone please help:)
From 	CTE cdr Inner Join
	dbo.Carrier c on c.CarrierId = cdr.CarrierId Inner Join	
	dbo.RouteRanges rr With(NoLock) On cdr.RouteRangeId = rr.RouteRangeId Inner Join 
	dbo.Route r With(NoLock) On rr.RouteId = r.RouteId Inner Join 
	dbo.Switch s With(NoLock) On r.SwitchId = s.SwitchId  Inner Join
	dbo.Destination d With(NoLock) On cdr.DestinationId = d.DestinationId Inner Join 
    dbo.DestinationBand db on db.DestinationBandId = d.DestinationBandId Inner Join 
    dbo.RateBatch b on b.BatchId = cdr.BatchId Inner Join 
    dbo.RateDeployBatch rdb on b.DeployBatchid = rdb.DeployBatchId Left Outer Join 
    dbo.ExportRoamingDetails e on e.FileDate = rdb.RoamingDate And e.CarrierId = cdr.CarrierId And e.RAP = 0 Left Outer Join 
    dbo.ExportRoamingExchangeRate e2 On rdb.RoamingDate = e2.FileDate Left Outer Join 
    dbo.ExchangeRate er on e2.ExchangeRateId = er.ExchangeRateId Left Outer Join
    dbo.ExportFile ef on ef.ExportFileId = e.ExportFileId Inner Join
	dbo.Prefix p With(NoLock) On cdr.PrefixId = p.PrefixId Inner Join 
	dbo.AccessTypeCode atc With(NoLock) On cdr.AccessTypeCodeId = atc.AccessTypeCodeId Inner Join
	dbo.CallClassCode ccc With(NoLock) On cdr.CallClassCodeId = ccc.CallClassCodeId Inner Join
	dbo.PartialCallCode pcc With(NoLock) On cdr.PartialCallCodeId = pcc.PartialCallCodeId Inner Join 
	dbo.QualityOfServiceCode qofc With(NoLock) On cdr.QualityOfServiceCodeId = qofc.QualityOfServiceCodeId Inner Join 
	dbo.TrafficTypeCode ttc With(NoLock) On cdr.TrafficTypeCodeId = ttc.TrafficTypeCodeId Inner Join 
	dbo.CDRFile f With(NoLock) On cdr.FileId = f.FileId Inner Join
	#tempba ba on cdr.CarrierId = ba.CarrierId and cdr.DestinationId = ba.DestinationId 
Where	er.TargetCurrencyId = @CurrencyId
And	er.CarrierId Is Null and er.Deleted = 0


Cheers Guys
Posted 16-Jan-13 9:03am
frostcox2.5K

1 solution

Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

You should be fine just adding it to the conditions on your Left Outer Join:

From 	CTE cdr Inner Join
dbo.Carrier c on c.CarrierId = cdr.CarrierId Inner Join	
dbo.RouteRanges rr With(NoLock) On cdr.RouteRangeId = rr.RouteRangeId Inner Join 
dbo.Route r With(NoLock) On rr.RouteId = r.RouteId Inner Join 
dbo.Switch s With(NoLock) On r.SwitchId = s.SwitchId  Inner Join
dbo.Destination d With(NoLock) On cdr.DestinationId = d.DestinationId Inner Join 
dbo.DestinationBand db on db.DestinationBandId = d.DestinationBandId Inner Join 
dbo.RateBatch b on b.BatchId = cdr.BatchId Inner Join 
dbo.RateDeployBatch rdb on b.DeployBatchid = rdb.DeployBatchId Left Outer Join 
dbo.ExportRoamingDetails e on e.FileDate = rdb.RoamingDate And e.CarrierId = cdr.CarrierId And e.RAP = 0 Left Outer Join 
dbo.ExportRoamingExchangeRate e2 On rdb.RoamingDate = e2.FileDate Left Outer Join 

dbo.ExchangeRate er on e2.ExchangeRateId = er.ExchangeRateId 
				       and er.TargetCurrencyId = @CurrencyId 
				       and er.CarrierId Is Null 
				       and er.Deleted = 0 

Left Outer Join
dbo.ExportFile ef on ef.ExportFileId = e.ExportFileId Inner Join
dbo.Prefix p With(NoLock) On cdr.PrefixId = p.PrefixId Inner Join 
dbo.AccessTypeCode atc With(NoLock) On cdr.AccessTypeCodeId = atc.AccessTypeCodeId Inner Join
dbo.CallClassCode ccc With(NoLock) On cdr.CallClassCodeId = ccc.CallClassCodeId Inner Join
dbo.PartialCallCode pcc With(NoLock) On cdr.PartialCallCodeId = pcc.PartialCallCodeId Inner Join 
dbo.QualityOfServiceCode qofc With(NoLock) On cdr.QualityOfServiceCodeId = qofc.QualityOfServiceCodeId Inner Join 
dbo.TrafficTypeCode ttc With(NoLock) On cdr.TrafficTypeCodeId = ttc.TrafficTypeCodeId Inner Join 
dbo.CDRFile f With(NoLock) On cdr.FileId = f.FileId Inner Join
#tempba ba on cdr.CarrierId = ba.CarrierId and cdr.DestinationId = ba.DestinationId 
  Permalink  
v4
Comments
Mickt1985 16-Jan-13 14:49pm
   
Thank you so much!!

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Mobile
Web02 | 2.8.170118.1 | Last Updated 16 Jan 2013
Copyright © CodeProject, 1999-2017
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100