using first occurrence of a found record in a join

[cwbrumett]

I've been working on this all day and just can't seem to figure it out, i'll try to explain the best i can.

I have a table that holds price points by week, by year, by brand and sometimes by event. I have to join via left outer join as orders don't have to have to have a price point.

the problem I'm having is my join logic can pull multiple price points for one order, because it satisfies the join based on date AND on date with event.

IE: tblpricepoint
pricepointID pricepoint leadsource eventloc week_f year_f
1 999 LS SLC 20 2007
2 1999 LS NULL 20 2007


tblOrderDetail
ordid leadsource itemid eventloc ordDate
1 LS 1111 NULL 2007-05-14
2 LS 2222 SLC 2007-05-14
3 LS 3333 Chi 2007-05-14


the expected results should be.
ordid pricepoint
1 1999 * there isn't an event so pull default
2 999 * these IS an event so pull event price
3 1999 * no event in price point table pull default



i'll include my semi working code, all logic work minus getting the correct price point

select b.EventLocation, pp.pricepointID,
	c.contactid,
	case c.newedulevel
		when 'U0' then 'UnderGrad'
		when 'U1' then 'UGrad-PTS'
		when 'ASC' then 'Assoc'
		when 'MST' then 'Masters'
		when 'PHD' then 'PhD'
	end as EduLvl,

	year(c.WebStart) as StartYr,
	c.WebLeadSrc as RealBrand,
	case 
		when br.abrev = 'SM' and c.webstart >= '04/01/2005' and c.webstart<'06/11/2005' then '495'
		when br.abrev = 'SM' and c.webstart >= '06/11/2005' and c.webstart<'11/12/2005' then '995'
		when br.abrev = 'SM' and c.webstart >= '11/12/2005' then '1995'
		when c.WebLeadSrc in ('LAX','WFC') and c.webstart<'11/12/2005' then '1999'

	--begin pulling non hardcoded price points
		when c.WebLeadSrc = coalesce(lsj.L_LS, lsj.L_ML) 
				AND ISNULL(pp.LeadSource,'') = coalesce(lsj.L_LS, lsj.L_ML)
			--by date
			AND datepart(wk,c.webstart) = pp.Week_f	--by week
			AND datepart(qq,c.webstart) = pp.Quarter_f --by qtr
			AND datepart(yyyy,c.webstart) = pp.Year_f --by year
			
			--by event
			AND b.EventLocation = pp.Event --by city
		then 'EVENT WE GOT ONE ' + pp.pricepoint

		--get price point when there is NO event
		when c.WebLeadSrc = coalesce(lsj.L_LS, lsj.L_ML) 
				AND ISNULL(pp.LeadSource,'') = coalesce(lsj.L_LS, lsj.L_ML)
			--by date
			AND datepart(wk,c.webstart) = pp.Week_f	--by week
			AND datepart(qq,c.webstart) = pp.Quarter_f --by qtr
			AND datepart(yyyy,c.webstart) = pp.Year_f --by year
		then 'WE GOT ONE ' + pp.pricepoint

		else 'Norm'	--catch all
	end as PricePoint,

	c.initexp,
	max(t.expdate) as MaxExp,
	min(t2.seqnum) as firstren,
	max(t2.expdate) as RenExp

from ed.dbo.tmp_Renew_Custs c
	left outer join ed..tmp_Renew_Times t on t.contactid = c.contactid
	left outer join ed..tmp_Renew_Times t2 on t2.contactid = c.contactid and t2.rentype <> 'extend'
	left outer join aries..tblbrand br on br.brandid = c.custbrand
	join aries..tblOrderDetails od on od.orderid = isnull(c.InitWebOrderId,c.Good1stOrderId)
	join aries..tblProductCategoryLink pcl on pcl.productid = od.productid and pcl.ProductCategoriesID in (382,383,384)
	
	left outer join ed..tbl_PricePoint_xref lsj ON c.WebLeadSrc = lsj.L_LS

	--get events to orderID .. if ANY product on order has an event, then attach it to orderID
	left outer join 
	( select db.OrderID, lb.EventID, eb.EventLocation, eb.Brand 
		from Aries..tblOrderDetails db
	  	left join Aries..tblEventProductLink lb on db.ProductID = lb.ProductID 
		left join Aries..tblEvents eb on lb.EventID = eb.EventID
		WHERE eb.EventID is not null
	) b  ON od.OrderID = b.OrderID

	--join price point
	left outer join ed..tbl_pricepoint pp on 
			datepart(wk,c.webstart) = pp.Week_f	--by week
			AND datepart(qq,c.webstart) = pp.Quarter_f --by qtr
			AND datepart(yyyy,c.webstart) = pp.Year_f --by year
			--by brand
			AND c.WebLeadSrc = lsj.L_LS 
			AND pp.LeadSource = coalesce(lsj.L_LS, lsj.L_ML) --by brand						
			-- need to join on event BUT it will skip price points if i do
						


where 
--testing slice of data	
 c.WebLeadSrc = 'INV'
and datepart(yyyy,c.webstart) = 2007
and datepart(qq,c.webstart) = 2
and c.newedulevel = 'ASC'

group by b.EventLocation,pp.pricepointID,
	c.contactid,
	case c.newedulevel
		when 'U0' then 'UnderGrad'
		when 'U1' then 'UGrad-PTS'
		when 'ASC' then 'Assoc'
		when 'MST' then 'Masters'
		when 'PHD' then 'PhD'
		end,
	year(c.WebStart),
	c.WebLeadSrc,
	case 
		when br.abrev = 'SM' and c.webstart >= '04/01/2005' and c.webstart<'06/11/2005' then '495'
		when br.abrev = 'SM' and c.webstart >= '06/11/2005' and c.webstart<'11/12/2005' then '995'
		when br.abrev = 'SM' and c.webstart >= '11/12/2005' then '1995'
		when c.WebLeadSrc IN ('LAX','WFC') and c.webstart<'11/12/2005' then '1999'

		when c.WebLeadSrc = coalesce(lsj.L_LS, lsj.L_ML) 
				AND ISNULL(pp.LeadSource,'') = coalesce(lsj.L_LS, lsj.L_ML)
			--by date
			AND datepart(wk,c.webstart) = pp.Week_f	--by week
			AND datepart(qq,c.webstart) = pp.Quarter_f --by qtr
			AND datepart(yyyy,c.webstart) = pp.Year_f --by year
			--by event
			AND b.EventLocation = pp.Event --by city
		then 'EVENT WE GOT ONE ' + pp.pricepoint


		when c.WebLeadSrc = coalesce(lsj.L_LS, lsj.L_ML) 
				AND ISNULL(pp.LeadSource,'') = coalesce(lsj.L_LS, lsj.L_ML)
			--by date
			AND datepart(wk,c.webstart) = pp.Week_f	--by week
			AND datepart(qq,c.webstart) = pp.Quarter_f --by qtr
			AND datepart(yyyy,c.webstart) = pp.Year_f --by year
		then 'WE GOT ONE ' + pp.pricepoint

		else 'Norm'	--catch all
		end,
	c.initexp