T-SQL : develop date ranges or date sequences from an existing table and input parameters, WITHOUT using DB objects

Question

I have a scenario in SQL Server 2016, using T-SQL language.

I have a source table, as below:

While the date range for ABC and HDF are incomplete in the month (of April), the range for PQR is the full month (perfectly complete), and that for RST is complete with 2 sequences (without any break in the month).

I have two parameters to pass for this table, both date values (usually the beginning and the end of a month).

DECLARE @RangeStart AS date = '2021-04-01'
DECLARE @RangeEnd AS date = '2021-04-30'

I need to change the above source table to the two output tables as shown below, covering the entire date range from Apr 1 to Apr 30:

Can someone provide me a T-SQL query to develop the two output tables using the Source table?

There is a limitation in my report application, I cannot use any database object such as temp tables (#), table variables (@), CTE (WITH), etc.

I need a purely ad-hoc query, developing only derived tables as shown below:

Example:

SELECT * 
FROM
    (SELECT ...
     FROM tableX
     INNER JOIN tableY ON ...
     ) DerivedTable

I need all data manipulation to be done within the 'DerivedTable', without using any database objects.

If you can provide me a solution to develop even one of the two tables, it would be great.

Answer 1

Here's an answer to the second result set.

Similar approach to @David Browne - Microsoft, used a different approach to "make" a date table

Create the sample data, shown in the question

create table src (
     id char(3)
    ,name char(3)
    ,value int
    ,startDate date
    ,endDate date
)
go
insert into src values 
('abc','xyz',250,'20210410','20210415')
,('abc','xyz',150,'20210424','20210430')

Description: I created 2 derived tables a, b which have matching dates to the "date" table, and all dates, respectively. This was needed in order to get all the dates to show up in the final result set. Getting the "value" from the matching table allows for you to get the values for those dates in which we have a value. The 4th field, date, can really be obtained from either derived table

Query:

DECLARE @RangeStart AS date = '2021-04-01';
DECLARE @RangeEnd AS date = '2021-04-30';

select 
     b.id
    ,b.name
    ,a.value
    ,a.date
from 
(
SELECT
        id
        ,name
        ,value
        ,DateTable.Date
    FROM 
    (
    SELECT  TOP (DATEDIFF(DAY, @RangeStart, @RangeEnd) + 1)
            Date = DATEADD(DAY, ROW_NUMBER() OVER(ORDER BY a.object_id) - 1, @RangeStart)
    FROM    sys.objects a
    )DateTable 
    left outer join src
    ON DateTable.Date between src.startdate and src.enddate
) a

inner join (
    SELECT TOP (DATEDIFF(DAY, @RangeStart, @RangeEnd) + 1)
        id
        ,name
        ,NULL as value 
        ,DateTable.Date
    FROM 
    (
    SELECT  TOP (DATEDIFF(DAY, @RangeStart, @RangeEnd) + 1)
            Date = DATEADD(DAY, ROW_NUMBER() OVER(ORDER BY a.object_id) - 1, @RangeStart)
    FROM    sys.objects a
    )DateTable 
    inner join src ON 1=1
    )b on a.date = b.date 

UPDATE 8/11/2021: Modified to account for multiple ID's What Changed:

• The second nested query (which brings back all dates with no values) was modified to have a DISTINCT instead of a TOP, as the old way would cap it at 30 records. Without the TOP you'd have a Cartesian Product, hence the DISTINCT clause which brings back one record per day/id.
• The join between the two derived tables was changed from INNER to a RIGHT join, as the inner would only bring dates where there was no record in the left table (only one, even if multiple ID's need it), though we want to bring back all of them
• The outermost query changed to have the DATE field come from the "right" table instead.
• The join between the two derived tables now joins on both the date and the ID, as we'd otherwise get a Cartesian Product

QUERY:

DECLARE @RangeStart AS date = '2021-04-01';
DECLARE @RangeEnd AS date = '2021-04-30';

SELECT 
     b.id
    ,b.name
    ,a.value
    ,b.[date]
FROM 
(
SELECT
         id
        ,name
        ,value
        ,DateTable.[Date]
    FROM 
    (
    SELECT  TOP (DATEDIFF(DAY, @RangeStart, @RangeEnd) + 1)
            Date = DATEADD(DAY, ROW_NUMBER() OVER(ORDER BY a.object_id) - 1, @RangeStart)
    FROM    sys.objects a
    )DateTable 
    left outer join src
    ON DateTable.Date between src.startdate and src.enddate
) a

RIGHT OUTER JOIN (
    SELECT DISTINCT 
         id
        ,name
        ,NULL as value 
        ,DateTable.Date
    FROM 
    (
    SELECT  TOP (DATEDIFF(DAY, @RangeStart, @RangeEnd) + 1)
            Date = DATEADD(DAY, ROW_NUMBER() OVER(ORDER BY a.object_id) - 1, @RangeStart)
    FROM    sys.objects a
    )DateTable 
    INNER JOIN src ON 1=1
    )
    b ON a.[date] = b.[date] 
    AND a.id = b.id
ORDER BY 1,4;