CODEWITHSUNDEEP
sqlsql-serverdatetimerecursive-query
Amy
Amy

Reputation: 641

SQL - Create multiple records from 1 record based on days between 2 dates

I have a table that holds an employee's leave. If an employee takes more than 1 day off in a row for example 22-05-2020 to the 26-05-2020 this will be displayed as one record. I am trying to get this displayed as 5 records, one for each day they were off.

My table is called: Emp_Annual_Leave and has the following fields

emp_no      leave_type      leave_year      half_day    start_date  end_date    days_of_leave
12345       Annual          2020            N           22/05/2020  26/05/2020  5

above is how it is currently displayed and I am trying to display the above record like below:

emp_no  leave_type  leave_year  half_day    start_date  end_date    days_of_leave   leave_date
12345   Annual      2020        N           22/05/2020  26/05/2020  1               22/05/2020
12345   Annual      2020        N           22/05/2020  26/05/2020  1               23/05/2020
12345   Annual      2020        N           22/05/2020  26/05/2020  1               24/05/2020
12345   Annual      2020        N           22/05/2020  26/05/2020  1               25/05/2020
12345   Annual      2020        N           22/05/2020  26/05/2020  1               26/05/2020

Does anyone know I would go about doing this? I have a feeling I need to use ROW_NUMBER() OVER(PARTITION BY) but any attempts I have made haven't worked well for me.

Thanks in advance,

EDIT: the table I need to create here is a subquery in a bigger query and needs to be joined back to other queries and tables in my DB. I didn't include this as part of my original question, updated to include now incase this impacts the methods I need to use

Upvotes: 0

Views: 626

Answers (1)

GMB
GMB

Reputation: 222702

You could use a recursive query:

with cte as (
    select emp_no, leave_type, leave_year, half_day, start_date, end_date, days_of_leave, start_date as leave_date from emp_annual_leave 
    union all
    select emp_no, leave_type, leave_year, half_day, start_date, end_date, days_of_leave, dateadd(day, 1, leave_date)
    from cte
    where leave_date < end_date
)
select * from cte

If a given leave may span over more than 100 days, you need to add option (maxrecursion 0) at the end of the query.

Upvotes: 2

Related Questions