SQL : How to show all dates between a given range?

Question

I have a table-valued function which returns something like this:

Now, I have a date range as a parameter, let's say '2020-03-01' and '2020-04-15'
What I want is to also add a column that displays all the date from '2020-03-01' up to '2020-04-15'
It should look like this:

    Overtime          | Status               | DTR        | DATE_START    |   DATE
    1.  NULL          | NULL                 | NULL       | NULL          | 2020-03-01
    2.  NULL          | NULL                 | NULL       | NULL          | 2020-03-02
    ...
    ...
    ...
    30. 6:32PM-9:32PM | Pending For Approval | NULL       | 2020-03-30    | 2020-03-30
    ...
    ...
    ...

My code goes like this ,,Dont mind the 95476237 :

SELECT CAST(a.IndividualDate as date)
FROM DateRange('d', @start, @end) as a 
LEFT JOIN support.dbo.overtimeReport('95476237', @start, @end) as b on CAST(a.IndividualDate as date) = CAST(b.DATE_START as date)

As a result, I only got this,

UPDATE: Small mistake, I forgot to mention the rest of columns in the SELECT statement. Thanks to @iamdave

You'll need to use a Calendar Table for this, then join the calendar table with the results you require. See answer [here](https://stackoverflow.com/questions/5635594/how-to-create-a-calendar-table-for-100-years-in-sql). — Attie Wagner, Mar 30 '20 at 08:39
If you require further assistance with this, please provide sample data and expected results, then we'll assist... — Attie Wagner, Mar 30 '20 at 08:40
@Birel I already provided a sample data, expected output, and the actual output. Respectively — deymbwoi, Mar 30 '20 at 08:42
Thank you @amm, the sample data you provided is a screenshot. In order for us to easily assist you, steer clear from using screenshots, except with your expected results... — Attie Wagner, Mar 30 '20 at 08:46
You have only included one column in your `select` statement which is why you are only getting one column in your output... — iamdave, Mar 30 '20 at 08:46
A [calendar table](https://www.sqlservercentral.com/steps/bones-of-sql-the-calendar-table) is definitely what you want here. Build one of those, and you likely have your answer too. — Thom A, Mar 30 '20 at 08:49
@amm You need to specify *all* columns you want returned in your `select`. You have made them available through your `left join`, but you have not actually added any of them to your `select`. — iamdave, Mar 30 '20 at 08:50
@iamdave Thanks man! I thought left join automatically displays all the matched records. — deymbwoi, Mar 30 '20 at 08:59
It must be the case that you do something like `SELECT *` or select multiple columns (`SELECT x,y,z`) from your TVF. To write only one column in your select list and then come on SO and ask why you're only getting one column in the output isn't *really* a question.. — Caius Jard, Mar 30 '20 at 08:59
JOINs have nothing to do with choosing which columns to display ("which column" is driven by the SELECT list) - they're more about choosing which related rows to display! — Caius Jard, Mar 30 '20 at 09:02

score 1 · Accepted Answer · answered Mar 30 '20 at 08:52

You just need to add all the columns you want to output into your select statement. The rest of the query seems to be okay (ignoring all the bad smells of selecting from a TVF and using cast on both sides of your join criteria...):

SELECT b.Overtime
      ,b.Status
      ,b.DTR
      ,DATE_STARTCAST(a.IndividualDate as date) AS [Date]
FROM DateRange('d', @start, @end) as a 
LEFT JOIN support.dbo.overtimeReport('95476237', @start, @end) as b
    ON CAST(a.IndividualDate as date) = CAST(b.DATE_START as date)

Also, please don't alias your tables with a, b, c etc. Use something that actually tells anyone reading this code which table is being referred to.

Thanks man! I thought left join automatically displays all the matched records. — deymbwoi, Mar 30 '20 at 08:59

SQL : How to show all dates between a given range?

1 Answers1