Calcolo in T-SQL delle ore tra due date escludendo sabati, domeniche e festività
di
pubblicato il 28-04-2011 alle 12:18 (6045 Visite)
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