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 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97
|
create table #T
(Gamme varchar(10), id_materiel varchar(10), date_appel datetime, fait_le datetime)
insert into #T
SELECT GMAO_Prevoir.gamme, gmao_demande.id_materiel
, CONVERT(varchar(12), MAX(GMAO_Demande.Date_appel), 103) AS date_appel
,gmao_gamme.date_precedente as Faite_le
FROM GMAO_DEMANDE
INNER JOIN GMAO_OI
ON GMAO_OI.id_demande = GMAO_Demande.ID
LEFT OUTER JOIN GMAO_Prevoir
ON GMAO_OI.Id = GMAO_Prevoir.id_oi
inner join gmao_gamme
on gmao_prevoir.gamme = Gmao_gamme.gamme
and gmao_demande.id_materiel = gmao_gamme.machine
WHERE (GMAO_Demande.ID_Intervention = '1')
AND (GMAO_Prevoir.gamme IS NOT NULL)
GROUP BY GMAO_Prevoir.gamme, gmao_demande.id_materiel,gmao_gamme.date_precedente
order by gamme
-------------------------
Create table #r
(Gamme varchar(10), id_materiel varchar(10),date_appel datetime, faite_le datetime, Id varchar(10))
insert into #r
Select v.Gamme, #t.id_materiel,v.date_appel,#t.fait_le, v.ID
From (SELECT GMAO_Prevoir.gamme
, CONVERT(varchar(12), MAX(GMAO_Demande.Date_appel), 103) AS date_appel
, max(GMAO_Demande.ID) as ID
FROM GMAO_DEMANDE
INNER JOIN GMAO_OI
ON GMAO_OI.id_demande = GMAO_Demande.ID
LEFT OUTER JOIN GMAO_Prevoir
ON GMAO_OI.Id = GMAO_Prevoir.id_oi
WHERE (GMAO_Demande.ID_Intervention = '1')
AND (GMAO_Prevoir.gamme IS NOT NULL)
GROUP BY GMAO_Prevoir.gamme
) as v
inner join #t
on #t.gamme = v.gamme
and #t.date_appel = v.date_appel
-----------------------------------
DECLARE @Gamme varchar(10), @id_materiel varchar(10), @fait_le datetime, @id varchar(10)
DECLARE Up_gmao CURSOR FOR
select Gamme, id_materiel, @fait_le, id
from #r
OPEN Up_gmao
FETCH NEXT FROM Up_gmao
INTO @gamme, @id_materiel, @fait_le, @id
WHILE @@FETCH_STATUS = 0
BEGIN
update gmao_demande
set Date_appel = @fait_le
From gmao_demande
inner join #r
on gmao_demande.id = @id
and gmao_demande.id_materiel = @id_materiel
FETCH NEXT FROM Up_gmao
INTO @gamme, @id_materiel, @fait_le, @id
END
CLOSE Up_gmao
DEALLOCATE Up_gmao
----
select * from #r
Drop table #t, #r |
Partager