Create tables in DB to represent the various formularies #1

I want to create a table for each formulary, which contains the list of trials and matched details.

USP DC

proposed table

usp_dc_matching
    nct_id
    ,cateogory
    ,class
    ,status (approved, rejected, difficult, null)
    ,timestamp

Drafted insert and select

INSERT INTO uspdc_matching 
VALUES
(
    %(nct_id)s
    ,%(category)s
    ,%(class)s
    ,%(status)s
    ,NOW()
)
;



WITH most_recent_status as (
    SELECT 
        *,
        ROW_NUMBER() OVER(PARTITION BY nct_id order by timestamp desc) as rn
    FROM usp_dc_matching
) -- maybe as view
select nct_id
from most_recent_status
where 
    rn = 1
    AND
    status = %(status)s
;

# USP DC proposed table ```sql usp_dc_matching nct_id ,cateogory ,class ,status (approved, rejected, difficult, null) ,timestamp ``` Drafted insert and select ```sql INSERT INTO uspdc_matching VALUES ( %(nct_id)s ,%(category)s ,%(class)s ,%(status)s ,NOW() ) ; WITH most_recent_status as ( SELECT *, ROW_NUMBER() OVER(PARTITION BY nct_id order by timestamp desc) as rn FROM usp_dc_matching ) -- maybe as view select nct_id from most_recent_status where rn = 1 AND status = %(status)s ; ```

Completed for USP DC and USP MMG. I don't think I have time for the VA formulary yet.

One improvement I would make would be to add tiers to which something can be matched along with a specific plan of how to match.

A couple of issues I ran across while working on this.

Some drugs are being tested for off label use
Some drugs do not have records in USP DC or MMG datasets.

An improved approach to matching would be nice.

One improvement I would make would be to add tiers to which something can be matched along with a specific plan of how to match. A couple of issues I ran across while working on this. - Some drugs are being tested for off label use - Some drugs do not have records in USP DC or MMG datasets. An improved approach to matching would be nice.

Labels Milestones

Create tables in DB to represent the various formularies #1

USP DC