0

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

  • Could you generate a new Timezone table without duplicates for daylight? – McNets Feb 9 '18 at 7:22
1

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;
  • This solution assumes that the max(offsetmin) will always be the correct timezone, however the solution will need to check if it is between the two date times won't it, instead of bringing in the top record? – Vine Feb 20 '18 at 0:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.