Samuele E. Locatelli
53 lines
2.0 KiB
SQL
53 lines
2.0 KiB
SQL
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
|