SELECT dbo.cross_main.id, dbo.cross_main.techosmotr,
CONVERT(int, CASE WHEN CHARINDEX('/', dbo.cross_main.weight)= 0 THEN dbo.cross_main.weight ELSE substring(dbo.cross_main.weight, 1, CHARINDEX('/', dbo.cross_main.weight) - 1) END) AS weight,
dbo.cross_main.price_type, dbo.cross_main.tip, dbo.cross_main.kod, dbo.car_year.name AS yname, dbo.car_firm.name AS fname, dbo.car_price_type.name AS tname,
dbo.car_capacity.cnt, dbo.car_capacity.name as cname, dbo.cross_main.chassisM, dbo.cross_main.zalog, dbo.cross_main.price, dbo.cross_main.priceO, dbo.cross_main.dtsale,
dbo.sklad.wsklad, CASE WHEN dtsale <= { fn now() } OR dtsale IS NULL THEN 0 ELSE 1 END AS sales, dbo.cross_main.place, dbo.cross_main.sost, dbo.cross_main.komiss, dbo.car_firm.catalog,
CASE WHEN tip > 4 THEN '?? ???' ELSE CASE WHEN wsklad = 1 THEN sklad.name ELSE CASE WHEN wsklad = 2 THEN '? ??' ELSE '? ??????' END END END AS sklad,
dbo.cross_main.motor_power, dbo.cross_main.motor_volume, dbo.car_wheel.name as wname FROM dbo.sklad RIGHT OUTER JOIN dbo.cross_main LEFT OUTER JOIN dbo.car_wheel ON dbo.cross_main.wheel = dbo.car_wheel.link LEFT OUTER JOIN dbo.car_capacity ON dbo.cross_main.car_capacity = dbo.car_capacity.link ON dbo.sklad.link = dbo.cross_main.place LEFT OUTER JOIN dbo.car_firm ON dbo.cross_main.firm = dbo.car_firm.link LEFT OUTER JOIN dbo.car_year ON dbo.cross_main.year = dbo.car_year.link LEFT OUTER JOIN dbo.car_price_type ON dbo.cross_main.price_type = dbo.car_price_type.link
WHERE (dbo.cross_main.date_sale IS NULL)
AND (dbo.cross_main.sost <= 1)
AND (dbo.cross_main.price_type = 2)
AND (dbo.cross_main.remont = 0) AND (dbo.cross_main.hino = 0)
AND (dbo.cross_main.quvere = 0) AND (dbo.cross_main.finance = 0)
AND (dbo.cross_main.razborka = 0) AND (dbo.car_firm.catalog = 88)
AND (dbo.cross_main.tip < 4) AND dbo.cross_main.tip>4
AND (dbo.cross_main.photo = 1) OR (dbo.cross_main.price_type = 2)
AND (dbo.car_firm.catalog = 88) AND (dbo.cross_main.tip > 4)
AND (dbo.cross_main.tip < 8) AND (dbo.cross_main.techosmotr = 1)
AND (dbo.cross_main.photo = 1) AND (dbo.cross_main.hide = 0)
AND (LEN(dbo.cross_main.price)>3)
ORDER BY dbo.cross_main.sost DESC, dbo.cross_main.tip DESC, dbo.cross_main.zalog, dbo.car_capacity.cnt, CONVERT(int, CASE WHEN CHARINDEX('/', dbo.cross_main.weight)= 0 THEN dbo.cross_main.weight ELSE substring(dbo.cross_main.weight, 1, CHARINDEX('/', dbo.cross_main.weight) - 1) END), dbo.cross_main.chassisM
