CREATE VIEW export.v_allData
AS

SELECT  InterventiMtz.numIntMtz
	, CAST(InterventiMtz.dataLav AS DATE) as dataLav
	, InterventiMtz.turnoLav
	, InterventiMtz.matr
	, InterventiMtz.richiesta
	, AnagImpianti.codImpianto
	, AnagImpianti.nomeImpianto
	, AnagMacchine.codMacchina
	, AnagMacchine.nomeMacchina
	, InterventiMtz.descrizione
	, InterventiMtz.inizioIntervento
	, InterventiMtz.fineIntervento
	, ISNULL(InterventiMtz.descrizioneIntervento, 'n.d. (report mtz)') AS descrizioneIntervento
	, ISNULL(AnagCausali.descrCausale, 'n.d. (causale)') AS descrCausale
	, ROUND(CAST(ISNULL(v_interventiErogati.totMinErogati, 0) AS FLOAT) / 60, 2) AS minErogati
	, CAST(ISNULL(vri.TotImport, 0) AS DECIMAL(9, 3)) AS ImportoTot
	, dbo.f_diffOreLavorative(InterventiMtz.inizioIntervento, InterventiMtz.fineIntervento, InterventiMtz.idxMacchina) AS durataMtz
	, CASE (isFermo)
		WHEN 1
			THEN dbo.f_diffOreLavorative(dbo.InterventiMtz.guasto, dbo.InterventiMtz.fineIntervento, InterventiMtz.idxMacchina)
		WHEN 0
			THEN dbo.f_diffOreLavorative(dbo.InterventiMtz.inizioIntervento, dbo.InterventiMtz.fineIntervento, InterventiMtz.idxMacchina)
		END AS durataOff
	, InterventiMtz.guasto
	, InterventiMtz.idxStato
	, InterventiMtz.idxImpianto
	, InterventiMtz.idxMacchina
	, InterventiMtz.isFermo
	, InterventiMtz.scheduled
FROM InterventiMtz
INNER JOIN AnagAmbitoGuasto
	ON InterventiMtz.idxAmbito = AnagAmbitoGuasto.idxAmbito
INNER JOIN AnagImpianti
	ON InterventiMtz.idxImpianto = AnagImpianti.idxImpianto
INNER JOIN AnagMacchine
	ON InterventiMtz.idxMacchina = AnagMacchine.idxMacchina
INNER JOIN AnagTipoGuasto
	ON InterventiMtz.idxTipo = AnagTipoGuasto.idxTipo
INNER JOIN AnagPriorita
	ON InterventiMtz.idxPriorita = AnagPriorita.idxPriorita
INNER JOIN AnagStati
	ON InterventiMtz.idxStato = AnagStati.idxStato
LEFT JOIN v_interventiErogati
	ON InterventiMtz.numIntMtz = v_interventiErogati.numIntMtz
LEFT JOIN v_risorseImpiegate AS vri
	ON InterventiMtz.numIntMtz = vri.numIntMtz
LEFT JOIN AnagCausali
	ON InterventiMtz.idxCausale = AnagCausali.idxCausale
--ORDER BY InterventiMtz.numIntMtz DESC