Get overlapping dates between two date ranges (in columns) - WITHOUT creating database objects

Question

I have a tricky situation in Microsoft SQL Server 2016, in which I need to get a list of dates that an employee was in Leave of Absence (LOA) in a PayPeriod, with fixed PeriodStart and fixed PeriodEnd columns.

See the figure below (the source dataset):

I have 4 employees in 5 rows of a dataset.

PeriodStart and PeriodEnd are fixed always, with the values Dec 15 and Dec 22 respectively (for 2020). I have each employee's LOA Start Date and LOA End Date in separate columns. The source dataset will have only one set of PeriodStart and PeriodEnd dates at any given time. Say, in the above case, it is ALWAYS Dec 15 and Dec 22. In someother cases, it will be Dec 22 and Dec 29. But only one range at a given time. The source dataset cannot contain Dec 15 - Dec 22 for Employee X, and Dec 22 - Dec 29 for Employee Y.

The desired output is as below:

The challenge here is, I am using our client's Query Builder, which cannot use T-SQL objects such as Temp tables (#), Table Variables (@), Common Table Expressions (CTE), User Defined Functions or even Views.

This is purely ad-hoc reporting, where you can ONLY create derived tables (or subqueries) and have an alias name and use it as a dataset. Such a dataset can be used in JOINs, and other regular stuff.

For example:

SELECT a, b

FROM

(SELECT t1.a, t2.b
FROM table1 t1
INNER JOIN table2 t2
ON t1.ID = t2.ID) XYZ

The derived table (or sub query) XYZ is the main dataset for me.

I need my desired output to be aliased XYZ.

Can anyone help me achieve this?

Answer 1

SELECT
  yourTable.EmployeeID,
  DATEADD(DAY, calendar.date_id, yourTable.PeriodStart)
FROM
(
  SELECT
    ROW_NUMBER() OVER (ORDER BY the_primary_key) - 1 AS date_id    
  FROM
    any_big_enough_table
)
  AS calendar
INNER JOIN
  yourTable
    ON  calendar.date_id <= DATEDIFF(DAY, yourTable.PeriodStart, yourTable.PeriodEnd)
    AND calendar.date_id >= DATEDIFF(DAY, yourTable.PeriodStart, yourTable.LOAStartDate)
    AND calendar.date_id <= DATEDIFF(DAY, yourTable.PeriodStart, yourTable.LOAEndDate)

Please excuse typos, I'm on my phone.