Nearest in SQL

[Moptop]

Hi
How would you code a query in T-SQL where you were looking for the nearest match.
As an example - assume that you have a Product table and a Year table. Each product has a year. However not every year is populated, in the Year table.
Therefore assume that every year between 1980 and 1990 except for 1984 is populated in the Year table
If a Product's year was 1983, I would want it to join to the 1983 row in the Year table. However, if a Product's year was 1984, I would want it to join to 1983 as there isn't a 1984 row.
Thanks
Mark

 

[RiverGuy]

You could use a subquery.

SELECT p.*,
(SELECT MAX([Year]) FROM Years y WHERE y.[Year] <= p.[Year]) AS MaxYear
FROM Products p

 

[Moptop]

Thanks for the response.
This would work when there was always a record that was less than or equal - but what if there wasn't?

I think that this query would work - but it gets complex pretty quickly.

SELECT p.*, coalesce(
(SELECT MAX([Year]) FROM Years y WHERE y.[Year] <= p.[Year]) , (SELECT MAX([Year]) FROM Years y WHERE y.[Year] > p.[Year])) AS MaxYear
FROM Products p

 

[Moptop]

For info, and just so that you can share my pain, here is the query that I am trying to fix:
SELECT
genpo, dealer, cast(sintModelYear AS varchar) + '_' + cast(modelID AS varchar) + '_' + cast(bodyID AS varchar) + '_' + cast(badgeID AS varchar) + '_' + cast(engineID AS varchar) + '_' + cast(colourID AS varchar) + '.jpg' AS 'XPICFILNAM',
sintModelYear AS 'NCARFAMYR', modelID AS 'NCARFAMMOD', mdl.vchrDescription + ' ' + cast(sintModelYear AS varchar) + ' ' + bdy.vchrDescription + ' ' + eng.vchrdescription + ' ' + bdg.vchrDescription + ' ' + transDescription AS 'XDESC',
bodyID AS 'NCARFAMBDY', mdl.vchrDescription AS 'XMOD', transDescription AS 'XTRSM', eng.vchrDescription AS 'XGRADE',
rtrim(ltrim(substring(colourDescription,charIndex(' ',colourDescription),len(colourDescription)))) AS 'XEXTECOL',
isnull(trimDescription,'Standard') AS 'XINTECOL', regmark AS 'XREGNUM', VIN AS 'NVIN', badgeID AS 'NPPBDG', engineID AS 'NPPENGN',
transmissionID AS 'NPPTRSM', dateAdded AS 'DATE_ADDED'
FROM (


SELECT
genpo, vehreg.chrHUKDealership dealer, vehreg.vchrRegistration regmark, vehreg.chrVIN VIN,mto.sintModelYear,modelID, bodyID, badgeID, engineID,
colourID, transmissionID, transDescription, colourDescription, trimDescription, vehreg.datAdded dateAdded
FROM tblVehicleRegistration vehreg
INNER JOIN tblMTOE mtoe
on mtoe.sintModel = vehreg.sintModel
INNER JOIN tblMTO mto
on mtoe.vchrESIModel = mto.vchrESIModel and mtoe.chrESIType = mto.chrESIType and mtoe.chrESIOption = mto.chrESIOption
INNER JOIN tblvehicle veh
on veh.chrvin = vehreg.chrvin
INNER JOIN (
SELECT pr.chrgenpocode genpo, mtoce.chrm m, mtoce.chrt t, mtoce.chro o,
substring(col.vchrDescription,0,charindex(' ',col.vchrdescription)) c2, colall.chrC c, euro.chreurooptioncode e,
pr.sintmodelyear, pr.intmodelid modelID, pr.intbodyid bodyID, pr.intbadgeid badgeID, pr.intengineid engineID,
col.intcolourid colourID, col.vchrDescription colourDescription, trans.vchrdescription t2, pr.intpriceid,
pr.inttransmissionID transmissionID, trans.vchrDescription transDescription,trm.vchrName trimDescription
FROM peper..tblmtoce mtoce
INNER JOIN peper..tblprice pr
on pr.intpriceid = mtoce.intpriceid and pr.chrgenpocode = mtoce.chrgenpocode and pr.datDeleted is null
INNER JOIN peper..tblcolourallocation colall
on mtoce.intcolourallocationid = colall.intcolourallocationid and colall.chrgenpocode = mtoce.chrgenpocode
and colall.datDeleted is null
INNER JOIN peper..tblcolour col
on col.intcolourid = colall.intcolourid and col.chrgenpocode = colall.chrgenpocode and col.datDeleted is null
LEFT OUTER JOIN peper..tbleurooption euro
on euro.inteurooptionid = mtoce.inteurooptionid and euro.datDeleted is null
INNER JOIN peper..tbltransmission trans
on trans.inttransmissionid = pr.inttransmissionid and trans.datDeleted is null
LEFT OUTER JOIN peper..tbltrimallocation trimall
on pr.chrgenpocode = trimall.chrgenpocode and pr.sintmodelyear = trimall.sintmodelyear
and pr.intmodelid = trimall.intmodelid and pr.intbodyid = trimall.intbodyid and pr.intbadgeid = trimall.intbadgeid
and col.intcolourid = trimall.intcolourid and trimall.datDeleted is null
LEFT OUTER JOIN peper..tbltrim trm
on pr.chrgenpocode = trm.chrgenpocode and trimall.inttrimid = trm.inttrimid
WHERE mtoce.chrgenpocode = '006' AND mtoce.datDeleted is null
) masterlookup
on mtoe.vchrESIModel = isnull(nullif(masterlookup.m,''), 'NON') AND mtoe.chrESIType = isnull(nullif(masterlookup.t,''), 'NON')
AND mtoe.chrESIOption = isnull(nullif(masterlookup.o,''), 'NON') AND mtoe.chrESIEuroOption = isnull(nullif(masterlookup.e,''), 'NON')
AND veh.chrcolourhes = isnull(nullif(masterlookup.c2,''), 'NON')
AND case when(mto.sintModelYear = masterlookup.sintModelYear) then 1 else 0 end

-- find closest model year match by absolute subtracting one model year from another and taking the lowest uimxe 07/04/2009
--AND
^ case when(
str(masterlookup.sintModelYear,4,0) = ( select right(min_abs_year, 4) min_abs_year_only
from ( select min(abs_year) min_abs_year
from ( select str(abs(mto.sintModelYear -pr_2.sintModelYear), 4, 0)
+ str(pr_2.sintModelYear, 4, 0) abs_year
from peper..tblprice pr_2
where pr_2.intpriceid = masterlookup.intpriceid
and pr_2.chrgenpocode = '006'
and pr_2.datDeleted is null
) abs_year_sq
) abs_year_sq2 )
) then 1 else 0 end = 1

WHERE vehreg.datRegistrationEnd is null
AND vehreg.chrHUKDealership is not null
AND vehreg.chrHUKDealership <> '0'
AND vehreg.vchrBusinessArea in ('DS300','DS301')
AND vehreg.datAdded > '2009-01-16 13:36:59.633'



) lookup1
INNER JOIN peper..tblModel mdl
on genpo = mdl.chrgenpocode and modelID = mdl.intmodelid
INNER JOIN peper..tblBody bdy
on genpo = bdy.chrgenpocode and bodyID = bdy.intbodyid
INNER JOIN peper..tblEngine eng
on genpo = eng.chrgenpocode and engineID = eng.intengineid
INNER JOIN peper..tblBadge bdg
on genpo = bdg.chrgenpocode and badgeID = bdg.intbadgeid