Filtering inside the PIVOT command

Question

I'm trying to filter before I PIVOT. I only want the Fuel Surcharge from the ManifestRate table when the enddate is 12-31-9999. Problem is, the rest of the columns are in that table have enddates as well. Should I use a CASE statement or something? This is what I have so far:

SELECT * into #test1 FROM
(
select distinct m1.State, m1.Market, l.LabName, m.MnifestName, mr.ManifestId, r.RateName, mr.Value
from Manifests m
left join ManifestRate mr on m.ID = mr.ManifestId
inner join Markets m1 on m.Marketid = m1.ID
inner join labs l on l.labid = m.LabId
left join RateType r on mr.RateTypeId = r.RateTypeId
where m.IsActive = 1
and mr.Enddate = '12-31-9999'
) tp
PIVOT
(
max(Value)
FOR [RateName] in ([Fuel Surcharge %],[Supply Cost],[Linehaul Billing],[Linehaul Cost],[Dry Ice Billing],[Dry Ice Cost],[Supply Charge],[COB])
) p

Answer 1

Thanks for your response. I will try this today ...

Answer 2

You would need to use CASE or OR condition, e.g.
SELECT * into #test1 FROM
(
select distinct m1.State, m1.Market, l.LabName, m.MnifestName, mr.ManifestId, r.RateName, mr.Value
from Manifests m
left join ManifestRate mr on m.ID = mr.ManifestId
inner join Markets m1 on m.Marketid = m1.ID
inner join labs l on l.labid = m.LabId
left join RateType r on mr.RateTypeId = r.RateTypeId
where m.IsActive = 1
and ((mr.Enddate = '12-31-9999' and r.RateName = 'Fuel Surcharge %' )
OR r.RateName <> 'Fuel Surcharge %' )
) tp
PIVOT
(
max(Value)
FOR [RateName] in ([Fuel Surcharge %],[Supply Cost],[Linehaul Billing],[Linehaul Cost],[Dry Ice Billing],[Dry Ice Cost],[Supply Charge],[COB])
) p