DECLARE @StartDate datetime
DECLARE @EndDate   datetime

-- first day of next month
SET @StartDate = DATEADD(m,1,GETDATE()-DAY(GETDATE()) + 1)
-- last day of next month
SET @EndDate = DATEADD(m,2,GETDATE()-DAY(GETDATE()) + 1)-1

DECLARE @emp TABLE (EmpID INT, EmpName VARCHAR(50))
INSERT INTO @emp (EmpID, EmpName)
SELECT 1, 'Jacob'

DECLARE @v_un TABLE (EmpID INT, Shift INT)
INSERT INTO @v_un (EmpID, Shift)
SELECT 1, 11 UNION ALL
SELECT 1, 12 UNION ALL
SELECT 1, 22 UNION ALL
SELECT 1, 23 UNION ALL
SELECT 1, 33 UNION ALL
SELECT 1, 34 UNION ALL
SELECT 1, 45 UNION ALL
SELECT 1, 51

-- process for last 30 days
SELECT
    e.*,
    @StartDate + Number,
    DATENAME(dw,@StartDate + number) AS DayOfWeek,
    CASE WHEN DATENAME(dw,@StartDate + number) = 'Sunday' THEN
        CASE
            WHEN EmpID IN (SELECT EmpID FROM @v_un WHERE Shift IN(45,51)) THEN 1
            WHEN EmpID IN (SELECT EmpID FROM @v_un WHERE Shift IN(11,12)) THEN 2
            WHEN EmpID IN (SELECT EmpID FROM @v_un WHERE Shift IN(22,23)) THEN 3
            WHEN EmpID IN (SELECT EmpID FROM @v_un WHERE Shift=33) THEN 4
            WHEN EmpID IN (SELECT EmpID FROM @v_un WHERE Shift=34) THEN 5
        END
    END AS SundayValue,
    CASE WHEN DATENAME(dw,@StartDate + number) = 'Monday' THEN
        CASE
           WHEN EmpID IN (SELECT EmpID FROM @v_un WHERE Shift IN(45,34)) THEN 1
            WHEN EmpID IN (SELECT EmpID FROM @v_un WHERE Shift IN(51,11)) THEN 2
            WHEN EmpID IN (SELECT EmpID FROM @v_un WHERE Shift IN(12,22)) THEN 3
            WHEN EmpID IN (SELECT EmpID FROM @v_un WHERE Shift=23) THEN 4
            WHEN EmpID IN (SELECT EmpID FROM @v_un WHERE Shift=33) THEN 5
        END
    END AS MondayValue,
    CASE WHEN DATENAME(dw,@StartDate + number) = 'Tuesday' THEN
        CASE
           WHEN EmpID IN (SELECT EmpID FROM @v_un WHERE Shift IN(33,34)) THEN 1
            WHEN EmpID IN (SELECT EmpID FROM @v_un WHERE Shift IN(45,51)) THEN 2
            WHEN EmpID IN (SELECT EmpID FROM @v_un WHERE Shift IN(12,22)) THEN 3
            WHEN EmpID IN (SELECT EmpID FROM @v_un WHERE Shift=23) THEN 4
            WHEN EmpID IN (SELECT EmpID FROM @v_un WHERE Shift=33) THEN 5
        END
    END AS Tuesday,
    CASE WHEN DATENAME(dw,@StartDate + number) = 'Wednesday' THEN
        CASE
            WHEN EmpID IN (SELECT EmpID FROM @v_un WHERE Shift IN(22,23)) THEN 1
            WHEN EmpID IN (SELECT EmpID FROM @v_un WHERE Shift IN(33,34)) THEN 2
            WHEN EmpID IN (SELECT EmpID FROM @v_un WHERE Shift IN(45,51)) THEN 3
            WHEN EmpID IN (SELECT EmpID FROM @v_un WHERE Shift=11) THEN 4
            WHEN EmpID IN (SELECT EmpID FROM @v_un WHERE Shift=12) THEN 5
        END
    END AS Wednesday,
    CASE WHEN DATENAME(dw,@StartDate + number) = 'Thursday' THEN
        CASE
            WHEN EmpID IN (SELECT EmpID FROM @v_un WHERE Shift IN(22,33)) THEN 1
            WHEN EmpID IN (SELECT EmpID FROM @v_un WHERE Shift IN(34,45)) THEN 2
            WHEN EmpID IN (SELECT EmpID FROM @v_un WHERE Shift IN(51,11)) THEN 3
            WHEN EmpID IN (SELECT EmpID FROM @v_un WHERE Shift=12) THEN 4
            WHEN EmpID IN (SELECT EmpID FROM @v_un WHERE Shift=22) THEN 5
        END
    END AS Thursday,
    CASE WHEN DATENAME(dw,@StartDate + number) = 'Friday' THEN
        CASE
          WHEN EmpID IN (SELECT EmpID FROM @v_un WHERE Shift IN(12,22)) THEN 1
            WHEN EmpID IN (SELECT EmpID FROM @v_un WHERE Shift IN(23,33)) THEN 2
            WHEN EmpID IN (SELECT EmpID FROM @v_un WHERE Shift IN(34,45)) THEN 3
            WHEN EmpID IN (SELECT EmpID FROM @v_un WHERE Shift=51) THEN 4
            WHEN EmpID IN (SELECT EmpID FROM @v_un WHERE Shift=11) THEN 5
        END
    END AS FridayValue,
    CASE WHEN DATENAME(dw,@StartDate + number) = 'Saturday' THEN
        CASE
           WHEN EmpID IN (SELECT EmpID FROM @v_un WHERE Shift IN(22,33)) THEN 1
            WHEN EmpID IN (SELECT EmpID FROM @v_un WHERE Shift IN(34,45)) THEN 2
            WHEN EmpID IN (SELECT EmpID FROM @v_un WHERE Shift IN(51,11)) THEN 3
            WHEN EmpID IN (SELECT EmpID FROM @v_un WHERE Shift=12) THEN 4
            WHEN EmpID IN (SELECT EmpID FROM @v_un WHERE Shift=22) THEN 5
        END
    END AS SaturdayValue
FROM @emp e
CROSS JOIN master.dbo.spt_values sv
WHERE type = 'P' AND number < (DATEDIFF(DAY,@startdate,@enddate)) + 1

CASE WHEN DATENAME(dw,@StartDate + number) = 'Friday' THEN
        CASE
           WHEN EmpID IN (SELECT EmpID FROM @v_un WHERE Shift=11 THEN 1 
         only if it null then value 99
            WHEN EmpID IN (SELECT EmpID FROM @v_un WHERE Shift=12 THEN 2
           only if it null then value 98
            WHEN EmpID IN (SELECT EmpID FROM @v_un WHERE Shift=22 THEN 3
           only if it null then value 97
            WHEN EmpID IN (SELECT EmpID FROM @v_un WHERE Shift=51) THEN 4
            WHEN EmpID IN (SELECT EmpID FROM @v_un WHERE Shift=11) THEN 5
        END