sspintux

La funzione T-SQL che vado a presentare permette di calcolare
il numero di ore trascorse tra due date
(per esempio dal 20101231 14:00 al 20110104 16:00)
escludendo sabati, domeniche e festività.

Si suppone che le festività siano registrate nella tabella dbo.Festivita che ha un solo
campo DataFest char(8) valorizzato con le date in formato YYYYMMDD;

quelle ricorrenti hanno l'anno sempre uguale a YYYY;
ecco un esempio:

DataFest
----------------------------------
20110424 (Pasqua)
20110425 (Pasquetta)
YYYY1225 (Natale)
YYYY0101 (Capodanno)

Si utilizza anche la tabella dbo.tbNumConsecutivi formata dal solo campo Num (int)
che contiene i numeri sequenziali da 1 fino a .... quello che vi serve.

E' da notare che la dbo.tbNumConsecutivi non è strettamente necessaria e può essere sostituita sfruttando uno dei tanti metodi
alternativi che si trovano in rete per la generazione di numeri sequenziali;
si veda per esempio https://sqlperformance.com/2013/01/t...nerate-a-set-1.

Tra tutte mi piace ricordarne una tra quelle proposte dal geniale
Itzik Ben Gan funzionante anche in sql server 7 e 2000 e che riporto in fondo.

Si ipotizza anche che la dbo.tbNumConsecutivi contenga tutti i records
necessari a coprire l'intervallo tra le date iniziale e finale;
se viene meno questa ipotesi il risultato sarà ovviamente errato.

Nulla vieta di aggiungere questa verifica nella function stessa.

Ultima cosa da notare è il calcolo del sabato e della domenica indipendente dall'impostazione del DATEFIRST

Ecco la function, scritta con qualche ridondanza per renderla più comprensibile.

create function dbo.fn_CalcNumOre(@Da datetime,@A datetime)
returns int
as

begin

declare @Res int -- il risultato finale

declare @dIni varchar(8),@dFin varchar(8) -- date senza ora in formato YYYYMMDD

set @dIni=convert(varchar(8), @Da,112)
set @dFin=convert(varchar(8), @A,112)

select @Res=Sum(
            case when GgCurr=@dIni 
              then 24 - datediff(hour,GgCurr,@Da)
              else 
                case when GgCurr=@dFin
                 then datediff(hour,GgCurr,@A)
                 else 24 end
            end
           )
from
( -- genero tutte le date comprese nell'intervallo
    select 
       convert(varchar(8),dateadd(day,T.Num,@dIni),112) as GgCurr
      ,'YYYY' + substring(convert(varchar(8),dateadd(day,T.Num,@dIni),112),5,4) as GgCurrYMD
      , case @@DateFirst when 7 then 7 else 7-@@DateFirst end as dwSab
      , 7-@@DateFirst+1 as dwDom
    from 
    (
      select Num from dbo.tbNumConsecutivi
       WHERE Num <=datediff(day,@dIni,@dFin)
    )
    as T
 ) Q
 where datepart(dw,GgCurr) not in (dwSab,dwDom)
 and not exists 
     (
        select 
        1
        from dbo.Festivita f
        where f.DataFest=Q.GgCurr or f.DataFest=Q.GgCurrYMD
     )

set @Res=isnull(@Res,0)

return @Res

end

/*
 esempio di utilizzo
select dbo.fn_CalcNumOre('20101231 14:00','20110104 16:00')
*/

--------------------------------------------------------------------------------
Soluzione di Itzik Ben Gan al problema della generazione di numeri
sequenziali

SELECT
(bit0 * 1 +
bit1 * 2 +
bit2 * 4 +
bit3 * 8 +
bit4 * 16 +
bit5 * 32 +
bit6 * 64 +
bit7 * 128 +
bit8 * 256 +
bit9 * 512) + 1 as Num
FROM (SELECT 0 AS bit0
UNION ALL
SELECT 1) AS Bits0
CROSS JOIN
(SELECT 0 AS bit1
UNION ALL
SELECT 1) AS Bits1
CROSS JOIN
(SELECT 0 AS bit2
UNION ALL
SELECT 1) AS Bits2
CROSS JOIN
(SELECT 0 AS bit3
UNION ALL
SELECT 1) AS Bits3
CROSS JOIN
(SELECT 0 AS bit4
UNION ALL
SELECT 1) AS Bits4
CROSS JOIN
(SELECT 0 AS bit5
UNION ALL
SELECT 1) AS Bits5
CROSS JOIN
(SELECT 0 AS bit6
UNION ALL
SELECT 1) AS Bits6
CROSS JOIN
(SELECT 0 AS bit7
UNION ALL
SELECT 1) AS Bits7
CROSS JOIN
(SELECT 0 AS bit8
UNION ALL
SELECT 1) AS Bits8
CROSS JOIN
(SELECT 0 AS bit9
UNION ALL
SELECT 1) AS Bits9