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
| exec sp_executesql N'
SELECT DISTINCT
CASE WHEN VOITURE.nom = '' OR VOITURE.nom IS NULL THEN RTRIM(VOITURE.nom) ELSE RTRIM(VOITURE.nom) END + ' ' + RTRIM(VOITURE.marque) AS identite_VOITURE,
(DATEDIFF(MONTH,CONVERT(DATETIME, VOITURE.datefabrication, 103), GETDATE()) - CASE WHEN DAY(CONVERT(DATETIME, datefabrication, 103)) = DAY(GETDATE()) THEN 1 ELSE 0 END) / 12 AS age_VOITURE,
VOITURE.carrosserie AS carrosserie_VOITURE, PARAM_SERVICE.Libelle AS service_VOITURE,
PARAM_SERVICE.Code AS codeservice_VOITURE, EMPLACEMENT.noEMPLACEMENT AS EMPLACEMENT_VOITURE, EMPLACEMENT.typeEMPLACEMENT AS typeEMPLACEMENT_VOITURE,
RTRIM(MECANICIEN.nom) + ' ' + RTRIM(MECANICIEN.marque) AS MECANICIEN_VOITURE, RESERVATION.motif AS REPARATION_VOITURE,
RESERVATION.dateadmission AS dateentree_VOITURE,REPARATION.heuredebut AS dateREPARATION_VOITURE, RESERVATION.datesortie AS datesortie_VOITURE,
CADRAN.idCADRAN AS CADRAN_VOITURE, PARAMSPECIALITE.nomspecialite,
CASE WHEN RESERVATION.modeentree LIKE '%Hospitalisation%' THEN 'Hospi' ELSE CASE WHEN RESERVATION.modeentree LIKE '%Ambulatoire%' THEN 'Ambu' ELSE
CASE WHEN RESERVATION.modeentree LIKE '%urgence%' then 'Urg' else CASE WHEN RESERVATION.modeentree LIKE '%externe%' then 'Ext' end END END END AS mode
FROM CADRAN INNER JOIN
MECANICIEN ON CADRAN.idnchir = MECANICIEN.idnational INNER JOIN
RESERVATION ON CADRAN.idCADRAN = RESERVATION.idCADRAN INNER JOIN
PARAMSPECIALITE ON RESERVATION.idspecialite = PARAMSPECIALITE.idspecialite left outer JOIN
REPARATION ON RESERVATION.idreservation = REPARATION.idreservation INNER JOIN
VOITURE ON CADRAN.ipp = VOITURE.IPP LEFT OUTER JOIN
PLANNING ON RESERVATION.idreservation = PLANNING.idreservation INNER JOIN
EMPLACEMENT ON PLANNING.idEMPLACEMENT = EMPLACEMENT.idEMPLACEMENT INNER JOIN
PARAM_SERVICE ON EMPLACEMENT.idservice = PARAM_SERVICE.IdService
WHERE (CADRAN.dateentree <= '23/03/2011 08:00') AND (CADRAN.datesortie >= '23/03/2011 08:00') AND (PLANNING.dateheuredebut <= '23/03/2011 08:00') AND
(PLANNING.dateheurefin >= '23/03/2011 08:00') AND (RESERVATION.idreservation NOT IN
(SELECT RESERVATION_1.idreservation
FROM RESERVATION AS RESERVATION_1 INNER JOIN
RESERVATION_STATUT ON RESERVATION_1.idreservation = RESERVATION_STATUT.idreservation INNER JOIN
STATUT ON RESERVATION_STATUT.idstatut = STATUT.idstatut
WHERE (STATUT.codestatut = 'annulee') AND (STATUT.typestatut = 'us')))
AND EXISTS
(SELECT MAX(dateREPARATION)
FROM REPARATION AS t
WHERE t.idreservation = RESERVATION.idreservation)
ORDER BY identite_VOITURE',N'@parameter1 datetime',@parameter1='23/03/2011 08:00' |
Partager