    AND c.vocabulary_id = 'RxNorm Extension'
   JOIN concept c2 ON DRUG_CONCEPT_ID = c2.CONCEPT_ID;
 
---need to think of amount
-/*select c.concept_name,c2.concept_name, regexp_substr(regexp_substr(c.concept_name,'box\sof\s.*'),'\d+')
- from concept c 
-join concept_relationship cr on concept_id=concept_id_1 and relationship_id='Contains' and c.vocabulary_id='RxNorm Extension'
-join concept c2 on c2.concept_id=concept_id_2
- where c.concept_class_id like '%Pack%' and c.concept_code not in (select pack_concept_code from pc_stage)
- and c.invalid_reason is null;*/ 
 --fix 2 equal components
 DELETE
 FROM PC_STAGE

DELETE ds_stage
WHERE ((AMOUNT_UNIT = '%' AND amount_value > 100) OR (NUMERATOR_UNIT = '%' AND NUMERATOR_VALUE > 100));

-- XXX Check this
--delete deprecated ingredients  I deprecate those ingredients that had died before 2 Feb 2017 (and so they dont exist in drug_concept_stage)
DELETE ds_stage
WHERE INGREDIENT_CONCEPT_CODE IN (SELECT DISTINCT INGREDIENT_CONCEPT_CODE
                                  FROM ds_stage s

WHEN MATCHED THEN UPDATE
  SET DENOMINATOR_VALUE = cx WHERE d.DRUG_CONCEPT_CODE = ds.DRUG_CONCEPT_CODE;


/*
-- XXX ?? ????
--rounding
UPDATE ds_stage
   SET AMOUNT_VALUE = ROUND(AMOUNT_VALUE,3 - FLOOR(LOG(10,AMOUNT_VALUE)) -1),
       NUMERATOR_VALUE = ROUND(NUMERATOR_VALUE,3 - FLOOR(LOG(10,NUMERATOR_VALUE)) -1),
       DENOMINATOR_VALUE = ROUND(DENOMINATOR_VALUE,3 - FLOOR(LOG(10,DENOMINATOR_VALUE)) -1);
*/
COMMIT;

--fix solid forms with denominator
