I have a table which looks something like this:

create table drugs_list (\
rule                varchar,\
category            varchar,\
code                varchar,\
code_type           varchar(5),\
code_length         int NULL,\
look_back_period    int DEFAULT 6\
look_back_interval  varchar DEFAULT 'months'\
);

The table structure looks something like:

And so on...

There's another history table I'm working with which looks like:

(Note rows 1, 3, and 4 are the same person. Rows 1 and 3 are the same rule on different days, but rows 3 and 4 are different rules on the same day.. The pattern continues as such with varied combinations for all people)

I dont know the structure of the history table since I didn't create it, but the query I'm trying to run is this:

SELECT \
sub_id \
suffix_id \
rule \
max(date) \
FROM history_table ht \
INNER JOIN drugs_list dl on ht.rule = dl.rule \
WHERE category = (rule category) and date >= current_date - INTERVAL dl.look_back_period || ' ' || dl.look_back_interval 

The last where condition is supposed to look something like 'and current_date >= INTERVAL '6 months' for the first row in drugs_list table and 'and current_date >= INTERVAL '1 year' for the second row in the drugs_list table

The final table is supposed to look something like this:

The idea is to find information from the last 6 months or last one year, or any other kind of time interval for each
(sub_id || suffix_id) combo by the rule in the rule column (which matches the rule in the drugs_list column) and the MAX value in the date column from history table.. The

When I try to run the code I get the error:

"[42703] ERROR: column "interval" does not exist"

How should I fix this query to get it to work? Could really use some help on this!

(FYI, i originally had the look back period column and look back interval as 1 column VARCHAR DEFAULT '6 months' , but when I tried to run the query that way a parameter window kept popping up)

CodePudding user response：

for column based interval, here's how you should write it.

    SELECT 
        sub_id 
        suffix_id 
        rule 
    max(date) 
    FROM history_table ht 
    INNER JOIN drugs_list dl on ht.rule = dl.rule 
    WHERE category = (rule category) and 
      date >= current_date -  (look_back_period||' '||look_back_interval)::interval

try this dbfiddle