title: SQL - Joining a list of dates with observations within a date range
body: <p>I have a sample query below that uses GETDATE to pull the most recent estimates. The table has TWO date columns, effectiveDate and toDate. The problem? I want to pull a weekly value so I can have a time series of estimates. If I run the query now, I will end up with all the estimates as of today, but I also want to know what they were last week, the week before, etc. </p>
  
  <p>Should I create a new table containing the dates that I want and then join them against the results of the query. This is where I am stuck. Thank you.</p>
  
  <hr>
  
  <pre><code>select GETDATE() as observeDate
  , (select C.companyName from ciqCompany C where C.companyId = EP.companyId) as companyName
  , (select EPT.periodTypeName from ciqEstimatePeriodType EPT where EPT.periodTypeId = EP.periodTypeId) as periodTypeName
  , EP.fiscalYear
  , EB.brokerName as brokerName
  , EA.firstName+' '+EA.lastName as AnalystName
  , EDND.tradingitemid
  , (select DI.dataItemName from ciqdataitem DI where DI.dataitemid = EDND.dataitemid) as dataItemName
  , (select EAS.accountingStandardDescription from dbo.ciqEstimateAccountingStd EAS where EAS.accountingStandardId = EDND.accountingStandardId) as AccountingStandard
  , (select Cu.ISOCode from ciqCurrency Cu where Cu.currencyid = EDND.currencyid) as ISOCode
  , (select EST.estimateScaleName from ciqEstimateScaleType EST where EST.estimateScaleId = EDND.estimateScaleId) as estimateScaleName
  ,EDND.dataItemValue,EDND.effectiveDate,EDND.isExcluded
  from ciqEstimatePeriod EP
  --- link estimate period table to detailed numeric data table
  ----------------------------------------------------------
  join ciqEstimateDetailNumericData EDND
  on EDND.estimatePeriodId = EP.estimatePeriodId
  and GETDATE() between EDND.effectiveDate and EDND.toDate
  ----------------------------------------------------------
  left outer join ciqEstimateBroker EB
  on EB.estimateBrokerId = EDND.estimateBrokerId --- left outer join must be used if you receive any of the anonymous estimates packages
  left outer join ciqEstimateAnalyst EA
  on EA.estimateAnalystId = EDND.estimateAnalystId --- left outer join must be used if you receive any of the anonymous estimates packages
  where EP.companyId = 112350 -- IBM
  and EP.periodTypeId = 1 -- annual
  and EDND.dataItemId = 21634 --- EPS Normalized (Detailed)
  and EP.fiscalYear = 2010
  order by 4,5,6,10
  </code></pre>