Hi everyone,I have two tables - caseinfo and steps.Caseinfo shows when a particular case entered and exited a particular project. If the project hasn't ended yet, then the end date is NULL.Steps shows the steps the case has gone through and the dates of those particular steps.I need to join the tables to show the steps the case went through during a particular project, but I'm having trouble with the NULL values in the end dates.If I join the tables so that the step date is between the start and end dates of the project, then I get no step information for the cases where the end date is NULL (that is, where the project hasn't ended yet). Does anybody have any ideas?Here are my tables, the query that shows the main idea (with the wrong result), and my expected results.Thank you for reading.--- create sample dataset dateformat ymddeclare @caseinfo table (caseid int, startdate smalldatetime, enddate smalldatetime)insert @caseinfoselect 10, '2006-12-23', '2006-12-27' union allselect 20, '2006-12-23', NULL union allselect 30, '2006-12-23', NULL union allselect 40, '2007-1-15', '2007-3-4'declare @steps table (caseid int, stepnumber int, stepdate smalldatetime)insert @stepsselect 10, 1, '2006-12-24' union allselect 10, 2, '2007-1-3' union allselect 10, 3, '2007-2-5' union allselect 20, 1, '2006-12-26' union allselect 20, 2, '2007-1-7' union allselect 20, 3, '2007-1-9' union allselect 30, 1, '2007-1-14' union allselect 40, 1, '2007-1-23' union allselect 40, 2, '2007-3-2' union allselect 40, 3, '2007-4-16'--- the main idea (with the wrong results)select * from @caseinfo c left join @steps s on s.caseid = c.caseid and s.stepdate between c.startdate and c.enddate--- expected resultdeclare @expresult table (caseidexp int, startdateexp smalldatetime, enddateexp smalldatetime, stepnumberexp int, stepdateexp smalldatetime)insert @expresultselect 10, '2006-12-23', '2006-12-27', 1, '2006-12-24' union allselect 20, '2006-12-23', NULL, 1, '2006-12-26' union allselect 20, '2006-12-23', NULL, 2, '2007-1-7' union allselect 20, '2006-12-23', NULL, 3, '2007-1-9' union allselect 30, '2006-12-23', NULL, 1, '2007-1-14' union allselect 40, '2007-1-15', '2007-3-4', 1, '2007-1-23' union allselect 40, '2007-1-15', '2007-3-4', 2, '2007-3-2' select *from @expresult