polkadot (10/2/2013)
YIKES, I see the error. The where should be d.d>=and not dbj.starttime >=
This works:
select * from cannedbackupjobs dbj
full outer join _dates d on d.d = CONVERT(VARCHAR(10), dbj.starttime, 120)
where d.d >= (select min(starttime) from cannedbackupjobs);
I'll look at ridding the calculations in the where clause for performance, now. I understand I don't want to be using functions against values that I am filtering for. I'll use a better calendar lookup table. Thanks!
Your best bet is to follow Gail's suggestion:
FROM CalenderTable LEFT OUTER JOIN TableWithOtherDatesInIt
Avoid referencing TableWithOtherDatesInIt in the WHERE clause - you will end up with an INNER join. Build filters into the JOIN condition instead.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden