Here it is 🙂

--===== If test table exists, drop it

IF OBJECT_ID('TempDB..PublicHols','U') IS NOT NULL

DROP TABLE PublicHols

--===== Create test table

CREATE TABLE PublicHols

(

ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,

Descr CHAR(64),

StartDate DATETIME,

EndDate DATETIME

)

--===== Special conditions

SET DATEFORMAT DMY

--===== Insert test data into test table

INSERT INTO PublicHols (Descr,StartDate,EndDate)

SELECT 'Fiestas Patrias','17/09/2007', '19/09/2007'

UNION ALL

SELECT 'Thanksgiving Break','22/11/2007', '23/11/2007'

UNION ALL

SELECT 'Australia Day','26/01/2010', '26/01/2010'

UNION ALL

SELECT 'Anzac Day','25/04/2010', '25/04/2010'

UNION ALL

SELECT 'Christmas Break','25/12/2010', '26/12/2010'

--==== Gather the data

select

h.ID,

h.Descr,

h.StartDate,

h.EndDate,

cast(h.EndDate-h.StartDate as integer)+1 as Days

from PublicHols h

--==== One solution to the problem (from another forum)

select

h.ID,

h.Descr,

dateadd(dd, n.number, h.StartDate) as HolDate,

1 as Days

from master..spt_values n

join PublicHols h on n.type = 'P'

and dateadd(dd,n.number,h.StartDate) between h.StartDate and h.EndDate