Join question 2

[xxing]

Hi

I have a periods table with the following:

#Periods
EndDate DatePeriod
31 March 2008 1 March 2008 to 31 March 2008
30 April 2008 1 April 2008 to 30 April 2008


I have a customer table as follows:

#Customer
Type EndDate
Customer Survey 7 March 2008
Litigation 15 April 2008

I would like to see the following

#FinalResult
Type EndDate Dateperiod
Customer Survey 7 March 2008 1 March 2008 to 31 March 2008
Customer Survey Null 1 April 2008 to 30 April 2008
Litigation Null 1 March 2008 to 30 March 2008
Litigation 15 April 2008 1 April 2008 to 30 April 2008

Thank you for any assistance
Mark

 

[jrbarnett]

SELECT #customer.Type, 
       #Customer.EndDate, 
       #EndDate.DatePeriod
FROM  #Customer CROSS JOIN #Periods

John

 

[PDreyer]

select t.Type, c.EndDate, p.DatePeriod
from #Periods p cross join (select distinct Type from #Customer)t
left join #Customer c
  on c.Type=t.type
 and c.EndDate between dateadd(dd,-datepart(dd,p.EndDate)+1, p.EndDate) and p.EndDate

 

[xxing]

I used the solution by PDreyer. A star for both of you. Thank you for your assistence

Mark