SET xact_abort ON
GO

BEGIN TRANSACTION
GO

SET ANSI_NULLS ON
GO

ALTER VIEW v_elencoIntervFilt
AS
SELECT     TOP (100) PERCENT dbo.InterventiMtz.numIntMtz, dbo.InterventiMtz.richiesta, dbo.AnagImpianti.codImpianto, dbo.AnagImpianti.nomeImpianto, 
                      dbo.AnagMacchine.codMacchina, dbo.AnagMacchine.nomeMacchina, dbo.InterventiMtz.descrizione, dbo.InterventiMtz.inizioIntervento, 
                      dbo.InterventiMtz.fineIntervento, ISNULL(dbo.InterventiMtz.descrizioneIntervento, 'n.d. (report mtz)') AS descrizioneIntervento, ISNULL(dbo.AnagCausali.descrCausale, 
                      'n.d. (causale)') AS descrCausale, ISNULL(dbo.v_interventiErogati.totMinErogati / 60, 0) AS minErogati, ISNULL(DATEDIFF(hour, dbo.InterventiMtz.inizioIntervento, 
                      dbo.InterventiMtz.fineIntervento), 0) AS durataMtz, CASE (isFermo) WHEN 1 THEN ISNULL(DATEDIFF(hour, dbo.InterventiMtz.guasto, dbo.InterventiMtz.fineIntervento), 
                      0) WHEN 0 THEN ISNULL(DATEDIFF(hour, dbo.InterventiMtz.inizioIntervento, dbo.InterventiMtz.fineIntervento), 0) END AS durataOff, dbo.InterventiMtz.guasto, 
                      dbo.InterventiMtz.idxStato, dbo.InterventiMtz.idxImpianto, dbo.InterventiMtz.idxMacchina
FROM         dbo.InterventiMtz INNER JOIN
                      dbo.AnagAmbitoGuasto ON dbo.InterventiMtz.idxAmbito = dbo.AnagAmbitoGuasto.idxAmbito INNER JOIN
                      dbo.AnagImpianti ON dbo.InterventiMtz.idxImpianto = dbo.AnagImpianti.idxImpianto INNER JOIN
                      dbo.AnagMacchine ON dbo.InterventiMtz.idxMacchina = dbo.AnagMacchine.idxMacchina INNER JOIN
                      dbo.AnagTipoGuasto ON dbo.InterventiMtz.idxTipo = dbo.AnagTipoGuasto.idxTipo INNER JOIN
                      dbo.AnagPriorita ON dbo.InterventiMtz.idxPriorita = dbo.AnagPriorita.idxPriorita INNER JOIN
                      dbo.AnagStati ON dbo.InterventiMtz.idxStato = dbo.AnagStati.idxStato LEFT OUTER JOIN
                      dbo.v_interventiErogati ON dbo.InterventiMtz.numIntMtz = dbo.v_interventiErogati.numIntMtz LEFT OUTER JOIN
                      dbo.AnagCausali ON dbo.InterventiMtz.idxCausale = dbo.AnagCausali.idxCausale
ORDER BY dbo.InterventiMtz.numIntMtz DESC
GO

COMMIT
GO



-- registro versione...
INSERT INTO [dbo].[LogUpdateDb] ([Versione],[Data]) VALUES(4, GETDATE())
GO