SQL Query

Experts - May be this is a stupid question but my mind isnt working to crack this one up.

I have a leave table

LeaveID         SubmitterName        LeaveType        FromDate       ToDate
1                      ABC                              Full Day             2/7/2018         2/8/2018
2                      DEF                              Full Day             2/12/2018        2/12/2018
3                      XYZ                               Full Day            2/12/2018        2/16/2018

In the above table, ABC is taking leave for 2 days from 7th to 8th (both days inclusive), DEF is taking leave only for 1 day (2/12/2018) and XYZ is taking leave for 5 days from 2/12 to 2/16.

What I need is
Considering the above example,
1. First I will get the min & max date from above table to know the list of dates. Ex: 2/7/2018 to 2/16/2018. (which would be 10 rows).
2. Then I need to match from date & to date to that list of 10 days. Ex: only 7 days (2/7, 2/8, 2/12, 2/13, 2/14, 2/15, 2/16)
3. Then i need to fill the other data.

Example output

LeaveID   SubmitterName         LeaveType           LeaveDate
1                ABC                             Full Day                   2/7/2018
1                ABC                             Full Day                   2/8/2018
2                DEF                              Full Day                  2/12/2018
3               XYZ                               Full Day                   2/12/2018
3               XYZ                               Full Day                   2/13/2018
3               XYZ                               Full Day                   2/14/2018
3               XYZ                               Full Day                   2/15/2018
3               XYZ                               Full Day                   2/16/2018

Can someone help me please?
ManjuIT - Project ManagerAsked:
Who is Participating?
Ryan ChongCommented:
try this:

declare @leave table
	LeaveID int identity,
	SubmitterName varchar(100),
	LeaveType varchar(20),
	FromDate datetime,
	ToDate datetime
insert into @leave
('ABC', 'Full Day', '2/7/2018', '2/8/2018'),
('DEF', 'Full Day', '2/12/2018', '2/12/2018'),
('XYZ', 'Full Day', '2/12/2018', '2/16/2018');

select a.LeaveID, a.SubmitterName, a.LeaveType, dateadd(d, v.number, a.FromDate)
from @leave a
join master..spt_values v on v.type='P' and v.number between 0 and datediff(d, a.FromDate, a.ToDate)
order by 1, 4

Open in new window


SQL how to convert row with date range to many rows with each date
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.