WebGIM/GimDB/dbo/Views/v_elencoIntervFilt.sql

CREATE 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, ROUND(CAST(ISNULL(dbo.v_interventiErogati.totMinErogati, 0) AS float) / 60, 2) 
                         AS minErogati, dbo.f_diffOreLavorative(dbo.InterventiMtz.inizioIntervento, dbo.InterventiMtz.fineIntervento, dbo.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, 
                         dbo.InterventiMtz.guasto, dbo.InterventiMtz.idxStato, dbo.InterventiMtz.idxImpianto, dbo.InterventiMtz.idxMacchina, dbo.InterventiMtz.isFermo, 
                         dbo.InterventiMtz.scheduled
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
EXECUTE sp_addextendedproperty @name = N'MS_DiagramPane1', @value = '[0E232FF0-B466-11cf-A24F-00AA00A3EFFF, 1.00]
Begin DesignProperties = 
   Begin PaneConfigurations = 
      Begin PaneConfiguration = 0
         NumPanes = 4
         Configuration = "(H (1[40] 4[20] 2[20] 3) )"
      End
      Begin PaneConfiguration = 1
         NumPanes = 3
         Configuration = "(H (1 [50] 4 [25] 3))"
      End
      Begin PaneConfiguration = 2
         NumPanes = 3
         Configuration = "(H (1 [50] 2 [25] 3))"
      End
      Begin PaneConfiguration = 3
         NumPanes = 3
         Configuration = "(H (4 [30] 2 [40] 3))"
      End
      Begin PaneConfiguration = 4
         NumPanes = 2
         Configuration = "(H (1 [56] 3))"
      End
      Begin PaneConfiguration = 5
         NumPanes = 2
         Configuration = "(H (2 [66] 3))"
      End
      Begin PaneConfiguration = 6
         NumPanes = 2
         Configuration = "(H (4 [50] 3))"
      End
      Begin PaneConfiguration = 7
         NumPanes = 1
         Configuration = "(V (3))"
      End
      Begin PaneConfiguration = 8
         NumPanes = 3
         Configuration = "(H (1[56] 4[18] 2) )"
      End
      Begin PaneConfiguration = 9
         NumPanes = 2
         Configuration = "(H (1 [75] 4))"
      End
      Begin PaneConfiguration = 10
         NumPanes = 2
         Configuration = "(H (1[66] 2) )"
      End
      Begin PaneConfiguration = 11
         NumPanes = 2
         Configuration = "(H (4 [60] 2))"
      End
      Begin PaneConfiguration = 12
         NumPanes = 1
         Configuration = "(H (1) )"
      End
      Begin PaneConfiguration = 13
         NumPanes = 1
         Configuration = "(V (4))"
      End
      Begin PaneConfiguration = 14
         NumPanes = 1
         Configuration = "(V (2))"
      End
      ActivePaneConfig = 0
   End
   Begin DiagramPane = 
      Begin Origin = 
         Top = 0
         Left = 0
      End
      Begin Tables = 
         Begin Table = "AnagAmbitoGuasto"
            Begin Extent = 
               Top = 11
               Left = 82
               Bottom = 89
               Right = 255
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "AnagImpianti"
            Begin Extent = 
               Top = 123
               Left = 219
               Bottom = 231
               Right = 370
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "AnagMacchine"
            Begin Extent = 
               Top = 190
               Left = 826
               Bottom = 298
               Right = 977
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "AnagTipoGuasto"
            Begin Extent = 
               Top = 90
               Left = 1143
               Bottom = 168
               Right = 1294
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "AnagPriorita"
            Begin Extent = 
               Top = 23
               Left = 958
               Bottom = 116
               Right = 1109
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "AnagStati"
            Begin Extent = 
               Top = 263
               Left = 90
               Bottom = 356
               Right = 241
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "v_interventiErogati"
            Begin Extent = 
               Top = 3
               Left = 739
               Bottom = 81
               Right = 890
  ', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'VIEW', @level1name = N'v_elencoIntervFilt';


GO
EXECUTE sp_addextendedproperty @name = N'MS_DiagramPane2', @value = '          End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "AnagCausali"
            Begin Extent = 
               Top = 291
               Left = 1011
               Bottom = 369
               Right = 1162
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "InterventiMtz"
            Begin Extent = 
               Top = 8
               Left = 486
               Bottom = 354
               Right = 671
            End
            DisplayFlags = 280
            TopColumn = 0
         End
      End
   End
   Begin SQLPane = 
   End
   Begin DataPane = 
      Begin ParameterDefaults = ""
      End
      Begin ColumnWidths = 30
         Width = 284
         Width = 915
         Width = 1815
         Width = 690
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1665
         Width = 1665
         Width = 1500
         Width = 1500
         Width = 1050
         Width = 1020
         Width = 1125
         Width = 1815
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
      End
   End
   Begin CriteriaPane = 
      Begin ColumnWidths = 11
         Column = 1440
         Alias = 900
         Table = 1170
         Output = 720
         Append = 1400
         NewValue = 1170
         SortType = 1350
         SortOrder = 1410
         GroupBy = 1350
         Filter = 1350
         Or = 1350
         Or = 1350
         Or = 1350
      End
   End
End
', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'VIEW', @level1name = N'v_elencoIntervFilt';


GO
EXECUTE sp_addextendedproperty @name = N'MS_DiagramPaneCount', @value = 2, @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'VIEW', @level1name = N'v_elencoIntervFilt';