Join Query based on Date

Question

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.

Let me give you an example of the tblTSS_DataCollection:

SELECT TOP 5
   [DataPointID]
  ,[DatapointDate]
  ,dc.[DataPointValue] 
FROM [tblTSS_DataCollection] dc
Where DatapointID = 1093

This here would return a very simple table:

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

Now I have another table called tblTSS_ScaleSettings which looks like this:

SELECT [ID]
      ,[DatapointID]
      ,[EffectiveDate]
      ,[ScaleType]
      ,[ScaleValue]
FROM [tblTSS_ScaleSettings]

Which will return a result something like this:

ID  DatapointID EffectiveDate            ScaleType   ScaleValue
1   1093        2012-07-29 00:03:01.000  *           10.0000

Now what I need to be able to do is something like this:

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

Which would return:

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

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.

Which would look like this:

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

Can someone please help?

Answer 1

Something like this might work for you:

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
 LEFT JOIN tblTSS_ScaleSettings sc
 ON sc.DatapointID = dc.DatapointID
 AND sc.EffectiveDate = (
    SELECT MAX(EffectiveDate)
    FROM tblTSS_ScaleSettings
    WHERE DatapointID = dc.DatapointID
        AND EffectiveDate <= dc.DatapointDate
 )
 WHERE dc.DatapointID = 1093

Answer 2

would a from clause like this work?

 FROM [tblTSS_DataCollection] dc
 JOIN sc  on sc.DatapointID = dc.DatapointID inner join
(
    select datapointid, max(effectivedate) as max_dt
    from
        [tblTSS_ScaleSettings] 
    where
        effectiveDate <= getdate()
    group by datapointID
) mx on
sc.datapointid = mx.datapointid and
sc.effectivedate = mx.max_dt

what you entered should be equivalent to:

SELECT TOP 10
       dc.[DataPointID]
      ,[DatapointDate]
      ,dc.[DataPointValue] AS [DVOld]
      ,EffectiveDate
    ,ScaleType
    ,ScaleValue
    ,CASE ScaleType 
            WHEN '*' THEN dc.[DataPointValue] * ScaleValue 
            WHEN '/' THEN dc.[DataPointValue] / ScaleValue 
            WHEN '+' THEN dc.[DataPointValue] + ScaleValue 
            WHEN '-' THEN dc.[DataPointValue] - ScaleValue 
            ELSE dc.[DataPointValue]
        END
        AS [DatapointValue]

  FROM [tblTSS_DataCollection] dc inner join
(select DatapointID, max(EffectiveDate) as max_effective,
from tblTSS_ScaleSettings ts
    where ts.EffectiveDate <= dc.DatapointDate
group by DatapointID) mx
on dc.datapointid = mx.datapointid and
EffectiveDate = max_effective 
  Where dc.DatapointID = 1093