declare @p_date date = getdate(),
        @p_idRestaurant int = 44,
        @firstDateOfWeek date = null,
        @lastDateOfWeek date = null

set @firstDateOfWeek = dateadd(dd, (DATEPART(dw, @p_date) * -1) + 1, @p_date)
set @lastDateOfWeek = dateadd(dd, 6, @firstDateOfWeek)

select  mec.id as idEmployeeContract,
        me.id as idEmployee,
        me.firstName,
        me.lastName1,
        me.lastName2,
        r.id as idRestaurant,
        r.bkCode,
        r.name as restaurantName,
        mecrw.dateStart,
        mecrw.dateEnd,
        (
            select  isnull(sum(mecrwh2.totalHour), 0) as totalHour
            from    mgtEmployeeContractRestaurantWorkingHour mecrwh2 with(nolock)
            where   @p_date between @firstDateOfWeek and @lastDateOfWeek
                    and mec.id = mecrwh2.idEmployeeContract
        ) as totalAlreadyWorkingHourPerWeek,
        mwtl.numberHour as numHourPerWeek,
        mwtl.numberHour * 0.60 as numComplementaryHourPerWeek,
        mecrwh.totalHour as numWorkingHourForDate,
        mecrwh.idEmployeeStatus as idEmployeeStatus,
        mes.*
from    mgtEmployeeContract mec with(nolock)
inner   join mgtEmployee me with(nolock)
        on mec.idEmployee = me.id
inner   join mgtWorkTimeList mwtl with(nolock)
        on mec.idWorkTimeList = mwtl.id
inner   join mgtEmployeeContractRestaurantWorking mecrw with(nolock)
        on mec.id = mecrw.idEmployeeContract
inner   join restaurant r with(nolock)
        on mecrw.idRestaurantDestination = r.id
left    join mgtEmployeeContractRestaurantWorkingHour mecrwh with(nolock)
        on mec.id = mecrwh.idEmployeeContract
        and mecrwh.date = @p_date
        and mecrwh.idRestaurant = @p_idRestaurant
left    join mgtEmployeeStatus mes with(nolock)
        on mecrwh.idEmployeeStatus = mes.id
where   @p_date between mecrw.dateStart and isnull(mec.dateEnd, '20991231')
        and mec.indActive = 1
        and mec.idRestaurant = @p_idRestaurant