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
|
SELECT demande_ou_projet.IdClientDemande
AS DemandeClient, forfait_budget.CODE_FORFAIT_BUDGET
AS Projet, demande_ou_projet.Type_demande
AS Cor_Evo,
SUM(ordre_de_travail.charge_consommee_totale)
AS Charge, DATE_FORMAT(demande_se_trouve_dans_etat.Date_entree, '%Y/%m/%d')
AS Date_cloture,
LEFT(SUBSTRING_INDEX(SUBSTRING_INDEX(demande_ou_projet.Libelle,'|', -1),'|', 1),60)
AS Libelle, ressource_tma.Equipe
AS Site, demande_ou_projet.Reponse_technique
AS Obervation, SUBSTRING_INDEX(SUBSTRING_INDEX(demande_ou_projet.Libelle,'|', -2),'|', 1)
AS Pays, SUBSTRING_INDEX( SUBSTRING_INDEX( demande_ou_projet.Libelle , 'CALL', -1 ) , '|', 1 )
AS NumCall, charge_vendue
AS ChargeVendu
FROM (sous_systeme
INNER JOIN (forfait_budget
INNER JOIN (demande_ou_projet
INNER JOIN ordre_de_travail
ON demande_ou_projet.IdDemande = ordre_de_travail.IdDemande)
ON (ordre_de_travail.ID_FORFAIT_BUDGET = forfait_budget.ID_FORFAIT_BUDGET)
AND (forfait_budget.ID_FORFAIT_BUDGET = demande_ou_projet.REF_FORFAIT_BUDGET))
ON sous_systeme.IdSousSyst = forfait_budget.REF_SOUS_SYSTEME)
INNER JOIN demande_se_trouve_dans_etat
ON demande_ou_projet.IdDemande = demande_se_trouve_dans_etat.IdDemande
INNER JOIN ressource_tma
ON ordre_de_travail.Ressource = ressource_tma.IdRessource
WHERE (((ordre_de_travail.charge_consommee_totale)<>0)
AND (demande_se_trouve_dans_etat.Date_entree)
Between '2011-05-01' And '2011-05-30'
AND (forfait_budget.ID_FORFAIT_BUDGET='149')
AND (demande_se_trouve_dans_etat.Code_etat='TER'))
GROUP BY demande_ou_projet.IdClientDemande, ressource_tma.Equipe |
Partager