| SQL |
SELECT tblSFIncentives.PK_autSFIncentiveID,
tblSFIncentives.txtMake,
tblSFIncentives.txtModel,
tblSFIncentives.lngModelYear,
tblSFIncentives.lngTotalCash,
tblSFIncentives.txtVehicleHints,
tblSFIncentives.txtVehicleHintsForExclusion,
tblSFIncentives.dtiStopDate,
tblSFIncentiveSpecials.PK_autSFIncentiveSpecialID,
tblSFIncentiveSpecials.txtSpecialTitle,
tblSFIncentiveSpecials.lngSpecialCash,
tblSFIncentiveSpecials.txtSpecialBody,
tblSFIncentiveSpecials.txtSpecialQualify,
tblSFIncentiveSpecials.txtSpecialRestrictions,
tblSFIncentiveSpecials.lngType,
tblSFIncentives.lngTotalCash +
(SELECT SUM(S2.lngSpecialCash)
FROM tblSFIncentiveSpecials S2
WHERE S2.FK_lngSFIncentiveID = tblSFIncentives.PK_autSFIncentiveID
AND S2.ysnActive = 1
AND S2.lngType = 2) AS TotalRebateOpportunity,
(SELECT SUM(Cash.lngCash)
FROM tblSFIncentiveCash Cash
WHERE Cash.FK_lngSFIncentiveID = tblSFIncentives.PK_autSFIncentiveID
AND Cash.ysnActive = 1) AS CashTotal,
BestAPR.lngTime AS BestTime,
BestAPR.txtValue AS BestRate,
BestAPR.lngCash AS BestCash,
BestAPR.txtAndOR AS BestAndOr,
BestAPR.lngCostPer1000 AS BestCost
FROM tblSFIncentives
LEFT JOIN tblSFIncentiveSpecials
ON tblSFIncentives.PK_autSFIncentiveID = tblSFIncentiveSpecials.FK_lngSFIncentiveID
AND tblSFIncentiveSpecials.ysnActive = 1
AND tblSFIncentiveSpecials.lngType = 2
LEFT JOIN tblSFIncentiveCodeLink
ON tblSFIncentives.PK_autSFIncentiveID = tblSFIncentiveCodeLink.FK_lngSFIncentiveID
AND tblSFIncentiveCodeLink.txtStyleID = '0'
OUTER APPLY (
SELECT TOP 1 tblSFIncentiveAPRs.lngTime,
tblSFIncentiveAPRs.txtValue,
tblSFIncentiveAPRs.lngCash,
tblSFIncentiveAPRs.txtAndOr,
tblSFIncentiveAPRs.lngCostPer1000
FROM tblSFIncentiveAPRs
WHERE tblSFIncentiveAPRs.FK_lngSFIncentiveID = tblSFIncentives.PK_autSFIncentiveID
AND tblSFIncentiveAPRs.ysnActive = 1
ORDER BY tblSFIncentiveAPRs.txtValue, tblSFIncentiveAPRs.lngTime DESC
) AS BestAPR
WHERE tblSFIncentives.lngModelYear = '2026'
AND tblSFIncentives.txtMake = 'Hyundai'
-- AND tblSFIncentives.FK_lngDealerID = '4'
AND (
tblSFIncentiveCodeLink.txtStyleID IS NOT NULL
OR
tblSFIncentives.txtModel = 'Tucson'
OR (
(tblSFIncentives.txtVehicleHints LIKE '%MODEL:Tucson[,}]%' OR tblSFIncentives.txtVehicleHints NOT LIKE '%MODEL:%')
AND (
tblSFIncentives.txtVehicleHints LIKE '%MODEL_CODE:TC3AAL9AWDAS[,}]%'
OR (
tblSFIncentives.txtVehicleHints NOT LIKE '%MODEL_CODE:%'
AND
tblSFIncentives.txtVehicleHintsForExclusion NOT LIKE '%MODEL_CODE:TC3AAL9AWDAS[,}]%'
)
)
AND (
tblSFIncentives.txtVehicleHints LIKE '%TRIM:SEL[,}]%'
OR (
tblSFIncentives.txtVehicleHints NOT LIKE '%TRIM:%'
AND
tblSFIncentives.txtVehicleHintsForExclusion NOT LIKE '%TRIM:SEL[,}]%'
)
)
)
)
AND tblSFIncentives.ysnActive = 1
AND tblSFIncentives.dtiStopDate >= Convert(DateTime, Floor(Convert(Float, getdate())))
ORDER BY tblSFIncentives.txtMake, tblSFIncentives.lngModelYear DESC, tblSFIncentives.txtModel |