title: Join Query based on Date
body: <p>I have a table that has time scaled values and I need to be able to scale the values. I am trying to make this as simple as possible however speed of execution is a big player for me. </p>
  
  <p>Let me give you an example of the tblTSS_DataCollection:</p>
  
  <pre><code>SELECT TOP 5
     [DataPointID]
    ,[DatapointDate]
    ,dc.[DataPointValue] 
  FROM [tblTSS_DataCollection] dc
  Where DatapointID = 1093
  </code></pre>
  
  <p>This here would return a very simple table:</p>
  
  <pre><code>DataPointID DatapointDate            DataPointValue
  1093        2012-07-29 00:00:01.000  0.01869818
  1093        2012-07-29 00:01:01.000  0.01882841
  1093        2012-07-29 00:02:01.000  0.01895865
  1093        2012-07-29 00:03:01.000  0.01908888
  1093        2012-07-29 00:04:01.000  0.01921912
  </code></pre>
  
  <p>Now I have another table called tblTSS_ScaleSettings which looks like this:</p>
  
  <pre><code>SELECT [ID]
        ,[DatapointID]
        ,[EffectiveDate]
        ,[ScaleType]
        ,[ScaleValue]
  FROM [tblTSS_ScaleSettings]
  </code></pre>
  
  <p>Which will return a result something like this:</p>
  
  <pre><code>ID  DatapointID EffectiveDate            ScaleType   ScaleValue
  1   1093        2012-07-29 00:03:01.000  *           10.0000
  </code></pre>
  
  <p>Now what I need to be able to do is something like this:</p>
  
  <pre><code>SELECT TOP 5 
         dc.[DataPointID]
        ,[DatapointDate]
        ,dc.[DataPointValue] AS [DVOld]
        ,CASE sc.ScaleType
              WHEN '*' THEN dc.[DataPointValue] * sc.ScaleValue
              WHEN '/' THEN dc.[DataPointValue] / sc.ScaleValue
              WHEN '+' THEN dc.[DataPointValue] + sc.ScaleValue
              WHEN '-' THEN dc.[DataPointValue] - sc.ScaleValue
              ELSE dc.[DataPointValue]
          END
          AS [DatapointValue]
   FROM [tblTSS_DataCollection] dc
   JOIN [tblTSS_ScaleSettings] sc
   on sc.DatapointID = dc.DatapointID
   Where dc.DatapointID = 1093
  </code></pre>
  
  <p>Which would return: </p>
  
  <pre><code>DataPointID DatapointDate            DVOld        DatapointValue
  1093        2012-07-29 00:00:01.000  0.01869818   0.1869818
  1093        2012-07-29 00:01:01.000  0.01882841   0.1882841
  1093        2012-07-29 00:02:01.000  0.01895865   0.1895865
  1093        2012-07-29 00:03:01.000  0.01908888   0.1908888
  1093        2012-07-29 00:04:01.000  0.01921912   0.1921912
  </code></pre>
  
  <p>However, what is wrong with this is because the scaling EffectiveDate in the table doesn't start until 00:03:01 scaling should start then not on all the records. Scaling should be that scale until the next effectivedate. Sometimes we will have multiple Scales that happen and it changes at different times throughout the year. So I need the Select Query to plan for that.... This is where it gets tricky. </p>
  
  <p>Which would look like this:</p>
  
  <pre><code>DataPointID DatapointDate            DVOld        DatapointValue
  1093        2012-07-29 00:00:01.000  0.01869818   0.01869818
  1093        2012-07-29 00:01:01.000  0.01882841   0.01882841
  1093        2012-07-29 00:02:01.000  0.01895865   0.01895865
  1093        2012-07-29 00:03:01.000  0.01908888   0.1908888
  1093        2012-07-29 00:04:01.000  0.01921912   0.1921912
  </code></pre>
  
  <p>Can someone please help?</p>