Sql-server – Returning correct timezone offset from join

sql servertimezone

Meeting table

|MeetingID | Start               | Timezone             | LocalStartTime
|13        | 2018-01-31 19:00:00 | (UTC+10:00)Melbourne |
|23        | 2018-01-31 19:00:00 | (UTC+10:00)Melbourne |
|3         | 2018-01-31 19:00:00 | (UTC+10:00)Hobart    |

Timezone Offset

|Timezone ID| StartDateTime    | EndDateTime      | Timezone              | OffsetMin
|1          | 2017-09-30 16:00 | 2018-03-31 16:00 | (UTC+10:00) Melbourne | 660
|2          | 2018-03-31 16:00 | 2018-10-06 16:00 | (UTC+10:00) Melbourne | 600

Hi, I have joined the above two tables in order to retrieve offsetmin, however the only foreign key i have in the meeting table is Timezone. Joining on this returns two rows for each meetingID. This is because each timezone is listed twice inside the timezone offset table, with two seperate offsetmin values based on whether it is day light savings or not. 

select m.meetingid, m.start, m.timezone, m.localstarttime, t.offsetmin
from meetingtable as m
join timezone as t

Meeting Table With Offset

|MeetingID | Start               | Timezone             | LocalStartTime| OffsetMin    
|13        | 2018-01-31 19:00:00 | (UTC+10:00)Melbourne |               |660
|13        | 2018-01-31 19:00:00 | (UTC+10:00)Melbourne |               |600

I am trying to return only the correct timezone offset based off its date, whether or not it falls into daylight savings or not (Timezone ID 1 or 2)

I am using MSSQL

Best Answer

If you are not able to modify or generate a new Timezones table without daylights, you could use more than one solution.

Using CROSS APPLY

select m.meetingid, 
       m.start, 
       m.timezone, 
       m.localstarttime, 
       t.offsetmin
from   meetingtable as m
cross apply (select top 1 offsetmin
             from timezone
             where timezone = m.timezone) t;

Using sub-query

select m.meetingid, 
       m.start, 
       m.timezone, 
       m.localstarttime, 
       (select top 1 offsetmin
        from timezone
        where timezone = m.timezone) offsetmin
from   meetingtable as m;

Using GROUP BY

select    m.meetingid, 
          m.start, 
          m.timezone, 
          m.localstarttime, 
          t.offsetmin
from      meetingtable as m
left join (select timezone,  max(offsetmin) offsetmin
           from   timezone
           group by timezone) t
on         t.timezone = m.timezone;
Related Question