/* La funzione calcola la differenza tra due date in termini di ore lavorative (considera orario lavorativo dalle 9 alle 18).
Per saltare i weekend, sfrutta la funzione  dbo.DifferenzaDateGiorniLav vista qui e la funzione dbo.CreaData vista qui (quindi per farla funzionare è necessario creare anche quelle funzioni).
 */


CREATE FUNCTION [dbo].[DifferenzaOreLavorative](@Data1 AS DATETIME, @Data2 AS DATETIME)

RETURNS DECIMAL(18,2)

AS
BEGIN
 
IF @Data1 IS NULL RETURN NULL
IF @Data2 IS NULL RETURN NULL
 
DECLARE @GiorniCompleti as decimal(18,2), @OreGI as decimal(18,2), @OreGF as decimal(18,2), @OreGM as decimal(18,2), @Ore as decimal(18,2)

-- restituisci NULL se una data è NULL
IF @Data1 IS NULL
   RETURN NULL
IF @Data2 IS NULL
   RETURN NULL

-- restituisci 0 se Data1 è maggiore di Data2
IF @Data1 >= @Data2
   RETURN 0
 
 -- se time prima delle 9 calcolo a partire dalle 9, se dopo le 18 riporto alle 18
IF DATEPART(hour,@Data1)<9
   SET @Data1 = dbo.CreaData(DATEPART(YEAR,@Data1),DATEPART(MONTH,@Data1),DATEPART(DAY,@Data1),9,0,0)

IF DATEPART(hour,@Data2)<9
   SET @Data2 = dbo.CreaData(DATEPART(YEAR,@Data2),DATEPART(MONTH,@Data2),DATEPART(DAY,@Data2),9,0,0)

IF DATEPART(hour,@Data1)>=18
   SET @Data1 = dbo.CreaData(DATEPART(YEAR,@Data1),DATEPART(MONTH,@Data1),DATEPART(DAY,@Data1),18,0,0)

IF DATEPART(hour,@Data2)>=18
   SET @Data2 = dbo.CreaData(DATEPART(YEAR,@Data2),DATEPART(MONTH,@Data2),DATEPART(DAY,@Data2),18,0,0)

-- Calcolo giorni completi di gestione (quindi tutti esclusi il primo e l'ultimo giorno) e traduco in ore
SET @GiorniCompleti = dbo.DifferenzaDateGiorniLav(@Data1,@Data2)-1
IF @GiorniCompleti < 0
   SET @GiorniCompleti = 0
 
SET @OreGM = @GiorniCompleti*8
 
-- se data1 = data2 calcolo solo le ore
IF convert(date,@Data2) = convert(date,@Data1)
BEGIN
  SET @Ore = CAST(DATEDIFF(minute,@Data1,@Data2) AS decimal(18,2))/60

  -- se maggiore di 5 tolgo un'ora (pausa pranzo)
  IF @Ore >= 5
SET @Ore = @Ore - 1

 RETURN @Ore
END
     
-- Calcolo ore del giorno iniziale
SET @OreGI = CAST(DATEDIFF(minute,@Data1,dbo.CreaData(DATEPART(YEAR,@Data1),DATEPART(MONTH,@Data1),DATEPART(DAY,@Data1),18,0,0)) AS decimal(18,2))/60

-- se maggiore di 5 tolgo un'ora (pausa pranzo)
IF @OreGI >= 5
SET @OreGI = @OreGI - 1

-- Calcolo ore del giorno finale 
SET @OreGF = CAST(DATEDIFF(minute,dbo.CreaData(DATEPART(YEAR,@Data2),DATEPART(MONTH,@Data2),DATEPART(DAY,@Data2),9,0,0),@Data2) AS decimal(18,2))/60

-- se maggiore di 5 tolgo un'ora (pausa pranzo)
IF @OreGF >= 5
SET @OreGF = @OreGF - 1
   
-- calcolo ore totali
SET @Ore = @OreGI+@OreGM+@OreGF
 
RETURN @Ore

END