Handling SQL join via timezone-CodePudding

I need to join two tables. The join has to be handled using parameter @timezone. But there is a catch - @timezone's value can be null, '', ' ' or anything else. User can enter everything.

If @timezone's value is null the outcome of the join will be basically nothing. So that, if it happens, there has to be at least basic value to join (for example, 'Pacific Standard Time')

DECLARE @StartDate DATE = '2022-01-20', @EndDate DATE = '2022-01-30', @t1 INT = 12345, @timezone VARCHAR(max) = NULL, @OnlyActivated INT = 0
SELECT r.ChargeType, r.CustomerName, r.PurchaseDate, r.accountID
FROM table_report as r
LEFT JOIN [sys].[time_zone_info] tzi ON tzi.[name] = @timezone (???)

I tried IIF(@timezone IN (NULL,'',' '),'Pacific Standard Time',@timezone) and it worked with '' and ' ' scenarios but then I realized that null can't be compared.

Maybe there is a pretty obvious solution but I don't see it.

Hope you can help with that.

P.S. CURRENT_TIMEZONE_ID() is not supported.

CodePudding user response：

You need to left-join twice to do this. The second join needs to have a condition that it only executes if the first join does not succeed.

DECLARE
    @StartDate DATE = '2022-01-20',
    @EndDate DATE = '2022-01-30',
    @t1 INT = 12345,
    @timezone VARCHAR(max) = NULL,
    @OnlyActivated INT = 0;

SELECT
  r.ChargeType, r.CustomerName, r.PurchaseDate, r.accountID,
  ISNULL(tzi1.SomeValue, tzi2.SomeValue)
FROM table_report as r
LEFT JOIN [sys].[time_zone_info] tzi1 ON tzi1.[name] = @timezone
LEFT JOIN [sys].[time_zone_info] tzi2 ON tzi2.[name] = 'Pacific Standard Time'
    AND tzi1.[name] IS NOT NULL;

An alternative, if you are only worrying about NULL is to just ISNULL the variable

DECLARE
    @StartDate DATE = '2022-01-20',
    @EndDate DATE = '2022-01-30',
    @t1 INT = 12345,
    @timezone VARCHAR(max) = NULL,
    @OnlyActivated INT = 0;

SELECT
  r.ChargeType, r.CustomerName, r.PurchaseDate, r.accountID,
  tzi.SomeValue
FROM table_report as r
LEFT JOIN [sys].[time_zone_info] tz1 ON tzi.[name] = ISNULL(@timezone, 'Pacific Standard Time');