title: SQL, Getting start and end date from measuring date in many to many relation
body: <p>Just to make the situation more clear. I have a table with devices and another with device readings. The table device has devices for each customer. A customer can have had more than 1 device, but only 1 active device. The latest device has EndDate “null” and that is active one. So from table 1 to table 2 is a many to many relation.</p>
  
  <p>Below an example of the table "device":</p>
  
  <pre><code>Table1: device
  
  DeviceID      customernr  StartDate   EndDate
  1             123         06-05-2002  19-06-2008
  2             123         19-06-2008  null
  
  </code></pre>
  
  <p>I also have another table called device readings. For some reason the only connecting thing is their customer number. Each device can have more than one value.</p>
  
  <p>Below an example of the table "devicereadings":</p>
  
  <pre><code>Table2: Devicereadings
  
  customernr   ReadingDate   Value
  123          12-05-2002    5
  123          20-06-2008    6
  123          25-06-2008    7
  
  </code></pre>
  
  <p>Now I would like to know what the deviceID is for each reading. And to make it more readable for me I would like the start and end date for each reading as well.</p>
  
  <p>I want the following output:</p>
  
  <pre><code>Table2: Devicereadings + output i want from table 1
  
  customernr   ReadingDate   Value   DeviceID   StartDate   EndDate
  123          12-05-2002    5       1          06-05-2002  19-06-2008
  123          20-06-2008    6       2          19-06-2008  null
  123          25-06-2008    7       2          19-06-2008  null
  </code></pre>
  
  <p>Below is the query that I used but a lot of the times it returns start and end dates for the wrong reading date. For example reading date in 2019 but the start/end date is like 2002-2003. </p>
  
  <pre><code>Select
  
  a.customernr
  a.ReadingDate
  a.Value
  b.DeviceID
  b.StartDate
  b.EndDate
  
  From Devicereadings a
  
     LEFT JOIN Device b
     on b.customernr = a.customernr
        WHERE 
           ReadingDate &gt; StartDate and
           ReadingDate &lt; EndDate or
           b.EndDate is null
  
  </code></pre>
  
  <p>I tried a lot more and different combinations in the where but I honestly don’t know what I’m doing and if I’m looking in the right direction. Would love to hear if someone can give me a clue as to where I should go. If I need to make something more clear please ask away!</p>
  
  <p>If you see any typos in my example code, it’s just an example to give a clear view of what the actual query is.</p>
  
  <p>Below is my actual query:</p>
  
  <pre><code>select  
  
          a.MeteringPoint,
          b.Meternummer,
          a.Measurement,
          a.[Time],
          a.[Value],
          a.[Status],
          b.DatumTijdMutatie,
          b.DatumTijdMutatieEnd
  
  from ge.METERREADINGS a 
  
      left JOIN ul.Meters b
      on b.AansluitingID = a.MeteringPoint
          WHERE
  
          REPLACE((CONVERT(VARCHAR(50),LEFT([Time],10),105)),'.','-') &gt; CONVERT(VARCHAR(50),b.DatumTijdMutatie,110) and
          REPLACE((CONVERT(VARCHAR(50),LEFT([Time],10),105)),'.','-') &lt; CONVERT(VARCHAR(50),b.DatumTijdMutatieEnd,110)or
          b.DatumTijdMutatieEnd is null or
  
          REPLACE((CONVERT(VARCHAR(50),LEFT([Time],10),105)),'.','-') &gt; CONVERT(VARCHAR(50),b.DatumTijdMutatie,110) and
          REPLACE((CONVERT(VARCHAR(50),LEFT([Time],10),105)),'.','-') &lt; CONVERT(VARCHAR(50),b.DatumTijdMutatieEnd,110) and 
          b.DatumTijdMutatieEnd is not null or
  
          REPLACE((CONVERT(VARCHAR(50),LEFT([Time],10),105)),'.','-') &gt; CONVERT(VARCHAR(50),b.DatumTijdMutatie,110) and 
          b.DatumTijdMutatieEnd is null
  
  </code></pre>
  
  <p>Below some translations from my basic query to the actual query above:</p>
  
  <ul>
  <li>Time =  Meater reading</li>
  <li>DatumTijdMutatie = StartDate</li>
  <li>DatumTijdMutatieEnd = EndDate</li>
  <li>Meternummer = DeviceID</li>
  <li>MeteringPoint = customernr</li>
  </ul>
  
  <p>I also don't know if my query itself is good because of the conversions for dates. The reason for those is because dates get saved as text (which I sadly can’t change). Time, datumtijdmutatie and datumtijdmutatie do however look the same like DD-MM-YYY after the conversion. Before it looks like <img src="https://i.stack.imgur.com/XDQ83.png" alt="date text"></p>
  
  <p>If you have any idea please let me know !</p>