Click here to Skip to main content
Rate this: bad
good
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 helpSmile | :)
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 8:03am
Mickt19852.2K

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 at 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
0 Sergey Alexandrovich Kryukov 625
1 OriginalGriff 285
2 George Jonsson 225
3 CPallini 210
4 Fredrik Bornander 150
0 OriginalGriff 5,835
1 Sergey Alexandrovich Kryukov 5,263
2 CPallini 4,750
3 George Jonsson 3,227
4 Gihan Liyanage 2,487


Advertise | Privacy | Mobile
Web04 | 2.8.140916.1 | Last Updated 16 Jan 2013
Copyright © CodeProject, 1999-2014
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