Assist with join code please

[QFanatic]

Hello

I have a transaction table (that is currently missing one day's data for March - the 18th) and a Calendar table.

Code looks like this...

Dates_t:
select  req_date
   from
Date_Dim_Table where req_date between 20200301 and 20200331 (this returns 31 rows - correct)

Then I want to join to Trans table

Volumes:
LEFT join (Dates_t) Load *;

SELECT
  log_date as req_date
  ,count(1) as hour_volume
FROM
Trans_Table
   where log_date between 20200301 and 20200331
group by 1

I want the result to have Zero for hour_volume for the 18th - since there is no data, it shouldnt just be omitted from the Table.

I figured using a LEFTJoin would work, bit it doesnt .

Any assistance please. Thank you

[MayilVahanan · Accepted solution]

Hi

Try like below

Dates_t:
select  req_date
   from
Date_Dim_Table where req_date between 20200301 and 20200331 (this returns 31 rows - correct)

Then I want to join to Trans table

Volumes:
LEFT join (Dates_t) Load *;

SELECT
  log_date as req_date
  ,count(1) as hour_volume
FROM
Trans_Table
   where log_date between 20200301 and 20200331
group by 1;

NoConcatenate

FinalTable:
Load req_date, Alt(hour_volume, 0) as B Resident Dates_t;

DROP Table Dates_t;

[QFanatic]

Thank you - forgot about ALT