1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74
| SELECT
ltrim(rtrim(tgPtoVenta.DescripCorto)) as 'Soc/Lieu',
tcVeh.NumStock AS 'No Stock',
(cast(convert(char(12),'1-1-2050',103) as datetime) - CONVERT(CHAR(12),TCVEH.FECCOMPRA,103)) AS 'Nb Jrs de Stock',
TcVeh.VendedorRecompra as 'Acheteur',
(Select tgmarca.descrip from tgmarca
where tgmarca.marca=tcveh.marca) as 'Marque',
(Select ltrim(rtrim(tgversion.descrip)) from tgversion
where tgversion.marca=tcveh.marca
and tgversion.modelo=tcveh.modelo
and tgversion.version=tcveh.version) as 'Modele/Version',
replace(tcVeh.Combustible,'NULL','') AS 'Energie',
CONVERT(CHAR(12), TCVEH.FECMATRICPRIMERA, 103) AS 'Date Mec',
tcVeh.Km AS 'Kms',
(SELECT SUM(CompraPrecio * Cdad) From tcVehOpcion
Where tcVehOpcion.Emp = tcVeh.Emp
AND tcVehOpcion.Numinterno = tcVeh.NumInterno
AND tcVehOpcion.TipoLinea = 'A') As "Tot Achat",
(select Rtrim(Ltrim(tcColor.Descrip)) from tccolor
where TcColor.Marca = tcveh.marca
and TcColor.Color = Tcveh.Color) AS 'Couleur',
replace(tcVeh.PotenciaFiscal,'NULL','') AS 'Pu Fisc',
ltrim(rtrim(replace(tcVeh.Matric,'NULL',''))) AS 'Immat',
tcveh.AutCirculacion AS 'Type Mine',
tcVeh.Chasis AS 'N° Chassis',
(Select tcvodestino.descrip from tcvodestino
where tcvodestino.codigo=tcveh.vodestino) AS 'Destination VO',
--'Vendu' = CASE tcVeh.Status WHEN 30 THEN 'OUI' ELSE 'NON' END,
Case TcVeh.Reservado WHEN 1 Then 'OUI'
ELSE 'NON'
END AS 'Reservé',
Case When tcVeh.Rebu <> 1 Then TcvehOpcion.VentaPrecio * (1+((Select Porcen from tgIvaPor Where tcVehOpcion.IvaVenta= tgIvaPor.Codigo) /100))
Else TcvehOpcion.VentaPrecio
End AS 'Prix Vte Prevu TTC',
(SELECT tcvehopciongeneric.descrip FROM tcvehopciongeneric, tcveh
WHERE tcvehopciongneric.numinterno=tcveh.numinterno) AS 'Option',
replace(replace(replace(tcVeh.Obser,CHAR(13),''),CHAR(10),''),CHAR(9),' ') AS 'Remarque'
FROM tcVeh ,
tgEmpresa,
TgPtoVenta ,
tcVehOpcion
WHERE tcVehOpcion.TipoLinea='A'
and tcVeh.Emp = tcVehOpcion.Emp
and tcVeh.NumInterno = tcVehOpcion.Numinterno
And TgEmpresa.Emp=Tcveh.Emp
And TgPtoVenta.PuntoVenta = Tcveh.PuntoVenta
--AND ((tcveh.baja = 0) or (tcveh.baja is null))
AND (((tcVeh.baja = 0) or (tcVeh.baja is null)) or (cast(convert(char(12),tcVeh.bajafec,103) as datetime) <= cast(convert(char(12),'1-1-2050',103) as datetime)))
-- Options de liste A ajouter
and TCVEH.EsVO = 1 -- Que les Veh Occase
--and TCVEH.EsVO = 0 -- Que les Veh Neufs
--and TCVEH.Status = 30 -- Que les Vehicules Vendus
--and TCVEH.Status < 30 -- Que les Vehicules Non Vendus
--and TCVEH.Reservado = 0 -- Que les Vehicules Non Reserves
--and TCVEH.Reservado = 1 -- Que les Vehicules Reserves
--And tcVeh.NumStock <> 0 -- Pas les Véhicules en Commande
-- Date d'achat inférieure ou égale à la date de référence
AND (cast(convert(char(12),TCVEH.FECCOMPRA,103) as datetime) <= cast(convert(char(12),'1-1-2050',103) as datetime)) -- DATE d'ACHAT
-- Date de vente Supérieure à la date de référence ou date de vente Nulle
AND ((Cast(CONVERT(CHAR(12), TCVEH.FecVenta, 103) as DateTime) > cast(convert(char(12),'1-1-2050',103) as datetime)) OR (TCVEH.FecVenta is Null) OR (Rtrim(Ltrim(tcVeh.FecVenta))='')) -- DATE DE VENTE
ORDER BY tcVeh.Emp,
tgPtoVenta.DescripCorto,
tcVeh.NumStock,
tcVeh.Marca,
TCVEH.FECCOMPRA -- date d'achat |
Partager