Click here to Skip to main content
15,867,278 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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:)
SQL
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

1 solution

You should be fine just adding it to the conditions on your Left Outer Join:
SQL
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 
 
Share this answer
 
v4
Comments
frostcox 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)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900