SQL convert dates to weekday name

Question

I'm working in SQL Server and I have a table like so;

What I am trying to achieve; I am trying to collate a list of the most frequent days off sick. I need to convert the [Sick Leave Start Date] column to a day of the week. I have no problem achieving this using DATENAME(day, GETDATE()).

This works for getting the day of the week the user first went sick - however where [Total Days Off] is greater than 1, I need to iterate through each date and store the day name.

So I am looking to achieve something like the below;

Any pointers would be greatly appreciated!

Answer 1

First generate your sickness date ranges, then join with a calendar table (I generate one recursivelly here) and group by the name of each day.

;WITH RecursiveCalendar AS
(
    SELECT
        GeneratedDate = CONVERT(DATE, '2018-01-01')

    UNION ALL

    SELECT
        GeneratedDate = DATEADD(DAY, 1, C.GeneratedDate)
    FROM
        RecursiveCalendar AS C
    WHERE
        C.GeneratedDate < '2019-01-01'
),
SickRanges AS
(
    SELECT
        S.StartDate,
        EndDate = DATEADD(DAY, CEILING(S.DaysOff) - 1, S.StartDate)
    FROM
        YourTable AS S
)
SELECT
    Weekday = DATENAME(WEEKDAY, C.GeneratedDate),
    AmountSickDays = COUNT(1)
FROM
    SickRanges AS R
    INNER JOIN RecursiveCalendar AS C ON C.GeneratedDate BETWEEN R.StartDate AND R.EndDate
GROUP BY
    DATENAME(WEEKDAY, C.GeneratedDate)
ORDER BY
    COUNT(1) DESC
OPTION
    (MAXRECURSION 30000)

Replace your table and column on the 2nd CTE to run.