DEBUG - Top Variants SQL:
SELECT ranked.pack_id,
ranked.Make,
ranked.Model,
ranked.effective_rental,
ranked.effective_vmf_norm,
ranked.has_grant_any
FROM (
SELECT candidate.*, ROW_NUMBER() OVER (PARTITION BY candidate.Make, candidate.Model ORDER BY CASE WHEN candidate.offer_uid IS NOT NULL AND candidate.offer_uid <> '' AND candidate.offer_uid <> '0' THEN 0 ELSE 1 END ASC, candidate.pack_id ASC) AS make_model_rank
FROM (
SELECT
base.pack_id,
base.Make,
base.Model,
base.Fuel,
base.offer_uid,
base.Electric_Range_Model,
base.Lease_Rental AS effective_rental,
base.vmf_norm AS effective_vmf_norm,
EXISTS (SELECT 1 FROM Rates_Friday gf
WHERE gf.Make = base.Make
AND gf.Model = base.Model
AND gf.grant_type IS NOT NULL) AS has_grant_any
FROM Rates_Friday base
INNER JOIN (SELECT inner_main.Make, inner_main.Model, MIN(CASE WHEN inner_main.offer_uid IS NOT NULL AND inner_main.offer_uid <> '' AND inner_main.offer_uid <> '0' THEN inner_main.Lease_Rental - 1000000 ELSE inner_main.Lease_Rental END) AS selection_metric
FROM Rates_Friday inner_main
WHERE inner_main.url_make = 'toyota' AND 1=1 AND 1=1 AND 1=1 AND 1=1 AND 1=1 AND 1=1 AND COALESCE(inner_main.Electric_Range, 0) >= '0' AND inner_main.COMMERCIAL != 'Y' AND inner_main.Lease_Rental BETWEEN '83.33' AND '1666.67' AND inner_main.Active = 1
GROUP BY inner_main.Make, inner_main.Model) metric ON metric.Make = base.Make AND metric.Model = base.Model AND CASE WHEN base.offer_uid IS NOT NULL AND base.offer_uid <> '' AND base.offer_uid <> '0' THEN base.Lease_Rental - 1000000 ELSE base.Lease_Rental END = metric.selection_metric
WHERE base.url_make = 'toyota' AND 1=1 AND 1=1 AND 1=1 AND 1=1 AND 1=1 AND 1=1 AND COALESCE(base.Electric_Range, 0) >= '0' AND base.COMMERCIAL != 'Y' AND base.Lease_Rental BETWEEN '83.33' AND '1666.67' AND base.Active = 1
) AS candidate
) AS ranked
WHERE ranked.make_model_rank = 1
ORDER BY ranked.Make, ranked.effective_rental ASC
LIMIT 0, 15
DEBUG - Detail Variants SQL:
SELECT
detail.pack_id,
detail.Cap,
detail.Make,
detail.Model,
detail.Mileage,
detail.Term,
detail.url_make,
detail.url_model,
detail.Electric_Range_Model,
detail.Rental,
detail.Lease_Rental,
detail.spread AS spread,
detail.spread AS Spread,
detail.offer_uid,
detail.source_uid AS quote_source_uid,
source_meta.source_name AS source_name,
detail.Active,
detail.Lease_Rental AS effective_rental,
detail.vmf_norm AS effective_vmf_norm,
EXISTS (SELECT 1 FROM Rates_Friday gf
WHERE gf.Make = detail.Make
AND gf.Model = detail.Model
AND gf.grant_type IS NOT NULL) AS has_grant_any
FROM Rates_Friday detail
LEFT JOIN sources source_meta ON source_meta.source_id = detail.source_uid
WHERE detail.pack_id IN ('371443','468478','371239','468465','468530','672282','372259','468490','468489','468505','468502')
ORDER BY FIELD(detail.pack_id, '371443','468478','371239','468465','468530','672282','372259','468490','468489','468505','468502')
Avail Vehicle Contracts are regulated and authorised by the Financial Conduct Authority (FCA) FRN: 730110.
VAT registration: 928787755. ICO: Z1273373.
Registered in England and Wales. Company Reg No 06523008.
Registered office: Clouds Harrow, Brampton Abbotts, Ross-on-Wye, HR9 7JE.
Avail Vehicle Contracts are a Credit Broker, not a Lender. We are not regulated for insurance products.
Our Trading address is The Office, Clouds Harrow, Brampton Abbotts, Ross On Wye, HR9 7JE.
Figures are provided both exclusive and inclusive of VAT, depending on whether the agreement is for business or personal use.
All figures are subject to credit approval and contractual terms, and do not constitute an offer.
If you wish to make a
complaint,
please write to us at our registered office as above.
We use cookies to ensure you get the best experience on our website and to support essential site functionality.
With your permission, we'll also use cookies to track your usage via Google Analytics and internal code.
https://www.availvehiclecontracts.co.uk/img/costofelectric-main.jpg
