Well, I don't like this solution very much, so perhaps someone else will come up with something better.
First, your DATE column appears to be of the datatype DATE, not DATETIME. And that's probably good. Otherwise you will have time values messing up the dates.
Second, you need to provide a table with all of the possible dates. No doubt there are many ways to programatically create the table, including temporary tables. To keep it simple, I would just create a table named PossibleDates. I'll use your column name
of DATE even though that makes it confusing. CREATE TABLE PossibleDates (DATE DATE NOT NULL). And populate that table with all the dates that interest you. This is laborious, but a one-time activity. And it brings up another problem. Did all wells start gathering
data at the same time? If not, you will have to wipe out past dates for those wells that did not exist yet.
Third, query the WellTable joining the Possible Dates table. Use an outer join to get all dates, even if there is no OffWell data.
SELECT
CASE
WHEN WELL_ID IS NULL THEN 'w1'
ELSE WELL_ID
END AS WELL_ID
, DATE,
CASE
WHEN OFF_WELL IS NULL THEN 0
ELSE OFF_WELL
END AS OFF_WELL
FROM WellTable
RIGHT OUTER JOIN PossibleDates ON WellTable.OFF_WELL_DATE = PossibleDates.DATE
WHERE WELL_ID = 'w1' OR WELL_ID IS NULL
UNION
SELECT
CASE
WHEN WELL_ID IS NULL THEN 'w2'
ELSE WELL_ID
END AS WELL_ID
, DATE,
CASE
WHEN OFF_WELL IS NULL THEN 0
ELSE OFF_WELL
END AS OFF_WELL
FROM WellTable
RIGHT OUTER JOIN PossibleDates ON WellTable.OFF_WELL_DATE = PossibleDates.DATE
WHERE WELL_ID = 'w2' OR WELL_ID IS NULL
ORDER BY WELL_ID, DATE
Last, I assume you want to terminate the list at today's date, or yesterdays? You don't want to assume that all future wells had 0 off times, just because you have no data for them.
Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty
