Joining two tables but pulling in values only if multiple columns match

I have the query below that assigns a station volume based on the volume processed that week. Station sizes are based by looking at 4 data points throughout the year. This is because station fluctuate in volume throughout the year for example, they may have small volume in one quarter, but large in another. The goal is to assign a size to a station that will best suite it the entire year. The sql below gets together the four data points that will be needed to evaluate what size a station_size will be applied. There are multiple combinations of volume possibilities that can take place over the year which is why they are stored in a separate table. I have a separate table called "station_sizes" that provides the logic for what the station size will be based on the volume taken from the four point in time.

For example if a station processes small volume in wk 14 and small volume in wk27 and standard volume in wk 40 and small volume in wk 47 then they would be a ds small station size. There are 1000s of scenarios in the station_size table.

I want the final output to be the excerpt from the query output + station_size. The station size will be pulled from the station_size table.

I need to know how to have the query below reference the station size table so that it can identify for each year group is wk_16= xyz.. and wk_26 =xyz.. and wk_39 = xyz.. and wk_43 =xyz then it will pull in the size logic form the station size table.

For the ar section in the query. I want the logic to be if a station has "Yes" for AR at a minimum it needs to be a standard station size. I am not sure if I did that peice right. I am fine if its bigger than that but no smaller.

SELECT station,
del_date,
sum(volume)/7 as volume_ref,DATE_PART("week",del_date) as week_num,DATE_PART("year",del_date) as year,
CASE
when sum(volume)/7 between 0 and 20000 then 'ds x-small'
when sum(volume)/7 between 20000 and 36000 and max(ar) <> 'yes' then 'ds small'
when sum(volume)/7 between 36000 and 42000 then 'ds standard'
when sum(volume)/7 between 42000 and 72000 then 'ds large'
when sum(volume)/7 > 72000 then 'ds x-large'
when max(ar) = 'YES' then 'ds standard'
else 'ds small'
end as station_ref
FROM prophecy_na.na_topology_lrp
Right JOIN wbr_global.raw_station_extended_attribute
ON prophecy_na.na_topology_lrp.station = wbr_global.raw_station_extended_attribute.ds
where week_num in(16,26,39,43)
GROUP BY station, del_date;






Continue reading...