DECLARE @id int = 25
DECLARE @from smalldatetime = '11/01/2016'
DECLARE @to smalldatetime = '11/30/2016'

DECLARE @MonthDates TABLE ([date] date)

INSERT INTO @MonthDates
SELECT Date = DateAdd( Day, n1.number * 10 + n0.number, @from )
FROM (SELECT 1 AS number UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 0) as n0
CROSS JOIN
(SELECT 1 AS number UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 0) as n1
WHERE   DateAdd( Day, n1.number * 10 + n0.number, @from ) BETWEEN @from AND @to


SELECT
    ISNULL(Pruchody.id_zaznamu,0)
    ,ISNULL(Pruchody.id_zamestnance,0)
    ,ISNULL(P.popis,'nezadáno')
    ,ISNULL(Pruchody.datum_pruchodu,'1900-01-01 00:00:00')
    ,ISNULL(P2.popis,'nezadáno')
    ,ISNULL(Pruchody.datum_odchodu,'1900-01-01 00:00:00')
    ,ISNULL(Pruchody.cetnost_pristupu,0)
    ,ISNULL(Pruchody.datum_zapisu,'1900-01-01 00:00:00')
    ,ISNULL(Pruchody.zapsal_login,'noone')
    ,CASE
        WHEN ((Pruchody.datum_pruchodu is null) or (Pruchody.datum_odchodu is null)) THEN convert(numeric(10,2),0)
        WHEN Pruchody.typ = 'P' THEN convert(numeric(10,2),round((dbo.VypocetOdprDoby(Pruchody.datum_pruchodu,Pruchody.datum_odchodu,Pruchody.id_zamestnance)/60.00 * 4), 0)/4)
        ELSE convert(numeric(10,2),round((DATEDIFF(MINUTE, Pruchody.datum_pruchodu, Pruchody.datum_odchodu)/60.00 * 4), 0)/4)
    END
    ,convert(varchar,MonthDates.Date,104)
    ,ISNULL(Pruchody.typ,'X')
    ,ISNULL(Pruchody.typ_pruchodu,0)
    ,ISNULL(Pruchody.typ_odchodu,0)
    ,ISNULL(Pruchody.manualne,0)
FROM
    @MonthDates MonthDates
    LEFT JOIN Pruchody ON (convert(varchar,MonthDates.Date,104) = convert(varchar,Pruchody.datum_pruchodu, 104) or convert(varchar,MonthDates.Date,104) = convert(varchar,Pruchody.datum_odchodu, 104) or Pruchody.datum_pruchodu = NULL) AND Pruchody.id_zamestnance = @id
    LEFT JOIN c_Pruchody P on Pruchody.typ_pruchodu = P.id_pruchodu
    LEFT JOIN c_Pruchody P2 on Pruchody.typ_odchodu = P2.id_pruchodu
WHERE convert(varchar,MonthDates.Date,104)  not in (SELECT CASE WHEN meniSe = 1 THEN convert(varchar,datum_svatku,104) WHEN meniSe = 0 THEN convert(varchar,DATEADD(YEAR,DATEDIFF(YEAR,datum_svatku,@from),datum_svatku),104) END FROM dbo.c_Svatky WHERE (DATENAME(DW, DATEADD(YEAR,DATEDIFF(YEAR,datum_svatku,@from),datum_svatku)) not in ('Saturday','Sunday') and meniSe = 0) OR (meniSe = 1 and DATEPART(YEAR,datum_svatku) = DATEPART(year,@from)))
UNION ALL
SELECT
    -1
    ,@id
    ,'Příchod'
    , CASE
        WHEN meniSe = 1 THEN DATEADD(hour,7,datum_svatku)
        WHEN meniSe = 0 THEN DATEADD(hour,7,DATEADD(YEAR,DATEDIFF(YEAR,datum_svatku,@from),datum_svatku))
        END
    ,'Odchod'
    ,CASE
        WHEN meniSe = 1 THEN DATEADD(minute,480,DATEADD(hour,7,datum_svatku))
        WHEN meniSe = 0 THEN DATEADD(minute,480,DATEADD(hour,7,DATEADD(YEAR,DATEDIFF(YEAR,datum_svatku,@from),datum_svatku)))
        END
    ,1
    ,GETDATE()
    ,'System'
    ,8
    ,CASE
        WHEN meniSe = 1 THEN convert(varchar,c_Svatky.datum_svatku,104)
        WHEN meniSe = 0 THEN convert(varchar,DATEADD(YEAR,DATEDIFF(YEAR,datum_svatku,@from),datum_svatku),104)
    END
    ,'K'
    ,1
    ,2
    ,convert(bit,0)
FROM c_Svatky
WHERE ((DATENAME(DW, datum_svatku) not in ('Saturday','Sunday')) AND datum_svatku between @from and @to) OR (meniSe = 0 AND (DATEADD(YEAR,DATEDIFF(YEAR,datum_svatku,@from),datum_svatku) between @from and @to/*GETDATE()*/) AND DATENAME(DW, DATEADD(YEAR,DATEDIFF(YEAR,datum_svatku,@from),datum_svatku)) not in ('Saturday','Sunday'))
UNION ALL
SELECT
    ISNULL(Pruchody.id_zaznamu,0)
    ,ISNULL(Pruchody.id_zamestnance,0)
    ,ISNULL(P.popis,'nezadáno')
    ,ISNULL(Pruchody.datum_pruchodu,'1900-01-01 00:00:00')
    ,ISNULL(P2.popis,'nezadáno')
    ,ISNULL(Pruchody.datum_odchodu,'1900-01-01 00:00:00')
    ,ISNULL(Pruchody.cetnost_pristupu,0)
    ,ISNULL(Pruchody.datum_zapisu,'1900-01-01 00:00:00')
    ,ISNULL(Pruchody.zapsal_login,'noone')
    ,CASE
        WHEN ((Pruchody.datum_pruchodu is null) or (Pruchody.datum_odchodu is null)) THEN convert(numeric(10,2),0)
        WHEN Pruchody.typ = 'P' THEN convert(numeric(10,2),round((dbo.VypocetOdprDoby(Pruchody.datum_pruchodu,Pruchody.datum_odchodu,Pruchody.id_zamestnance)/60.00 * 4), 0)/4)
        ELSE convert(numeric(10,2),round((DATEDIFF(MINUTE, Pruchody.datum_pruchodu, Pruchody.datum_odchodu)/60.00 * 4), 0)/4)
    END
    ,convert(varchar,MonthDates.Date,104)
    ,ISNULL(Pruchody.typ,'X')
    ,ISNULL(Pruchody.typ_pruchodu,0)
    ,ISNULL(Pruchody.typ_odchodu,0)
    ,ISNULL(Pruchody.manualne,0)
FROM
    @MonthDates MonthDates
    INNER JOIN Pruchody ON (convert(varchar,MonthDates.Date,104) = convert(varchar,Pruchody.datum_pruchodu, 104) or convert(varchar,MonthDates.Date,104) = convert(varchar,Pruchody.datum_odchodu, 104) or Pruchody.datum_pruchodu = NULL) AND Pruchody.id_zamestnance = @id
    INNER JOIN c_Pruchody P on Pruchody.typ_pruchodu = P.id_pruchodu
    INNER JOIN c_Pruchody P2 on Pruchody.typ_odchodu = P2.id_pruchodu
    INNER JOIN c_Svatky SV on (SV.meniSe = 1 and SV.datum_svatku = [date]) or (SV.meniSe = 0 and DATEADD(YEAR,DATEDIFF(YEAR,SV.datum_svatku,@to),SV.datum_svatku) = [date])
ORDER BY 11