CODEWITHSUNDEEP
sqlsql-server
wellwellwell
wellwellwell

Reputation: 23

How to return empty data even if there isn't any for a specific range?

I would like to return data from a table for a specific date range. For that I have a stored procedure that accepts @fromDate and @toDate parameters.

The sql looks like this:

DECLARE @fromDate DATETIME, @toDate DATETIME

SET @fromDate = '2017-04-01'
SET @toDate = '2017-04-07'

SELECT MD.ID, MD.Name, RT.Result, RT.DateOn
FROM MainData MD
  LEFT JOIN ResultsTable RT
  RT.MainDataID = MD.ID
WHERE RT.DateOn >= @fromDate AND RT.DateOn <= @toDate

This works as expected and returns the results in this range, but my problem is that I want to return empty results even if there are no results in the ResultsTable. Basically, if I request results for a period of 7 days, I should get 7 results, where some or all could be non-existent.

I tried to create a dummy table with the dates in this range, but I am not sure how to join it with the other sql:

INSERT INTO #tempDates
SELECT  DATEADD(DAY, nbr - 1, @fromDate)
FROM    ( SELECT    ROW_NUMBER() OVER ( ORDER BY c.object_id ) AS Nbr
          FROM      sys.columns c
        ) nbrs
WHERE   nbr - 1 <= DATEDIFF(DAY, @fromDate, @toDate)

Any help will be greatly appreciated

Upvotes: 2

Views: 59

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521168

Move the date conditions from the WHERE clause to the ON clause:

SELECT MD.ID, MD.Name, RT.Result, RT.DateOn
FROM MainData MD
LEFT JOIN ResultsTable RT
    ON RT.MainDataID = MD.ID AND
       RT.DateOn BETWEEN @fromDate AND @toDate

Upvotes: 2

Related Questions