                 ) where cnt_gr > 1
             ) rxe,
             (
-                select distinct ingredient_concept_code,
-                case when amount_value is not null then
-                    amount_value
-                else
+                select distinct ingredient_concept_code, round(dosage, 3-floor(log(10, dosage))-1) as dosage, 'good' as flag from 
+                ( 
+                    select ingredient_concept_code,
                     case when amount_value is null and denominator_value is null then 
                         numerator_value
                     else 
                         numerator_value/denominator_value
-                    end 
-                end as dosage,
-                'rx' as flag 
-                from drug_strength_stage  where vocabulary_id_1='RxNorm'
+                    end as dosage                
+                    from drug_strength_stage  where vocabulary_id_1='RxNorm' and amount_value is null
+                )
             ) rx
             where rxe.ingredient_concept_code=rx.ingredient_concept_code(+)
             and rxe.dosage=rx.dosage(+)
         )
     ) merged_rxe, drug_strength_stage dss 
-    where merged_rxe.flag='rxe' and merged_rxe.cnt_flags=2
+    where (
+        merged_rxe.flag='bad' and merged_rxe.cnt_flags=2 or
+        merged_rxe.flag='bad' and merged_rxe.cnt_flags=1 and dosage<>min_dosage
+    )
     and dss.ingredient_concept_code=merged_rxe.ingredient_concept_code
     and dss.amount_value=merged_rxe.dosage
     and dss.vocabulary_id_1='RxNorm Extension'


--14
--deprecate the drugs that have inaccurate dosage due to difference in ingredients subvarieties
update concept_stage c set invalid_reason='D',
valid_end_date=(SELECT latest_update-1 FROM vocabulary WHERE vocabulary_id = 'RxNorm Extension') 
where (concept_code, vocabulary_id) in (
    select dss.drug_concept_code, dss.vocabulary_id_1 from (
        select ingredient_concept_code, dosage, flag, count(distinct flag) over (partition by ingredient_concept_code, dosage_group) cnt_flags from (
            select rxe.ingredient_concept_code, rxe.dosage, rxe.dosage_group, nvl(rx.flag,rxe.flag) as flag from (
                select distinct ingredient_concept_code, dosage, dosage_group, 'rxe' as flag 
                from (
                    select ingredient_concept_code, dosage, dosage_group, count(*) over(partition by ingredient_concept_code, dosage_group) as cnt_gr
                    from (

                                    select distinct ingredient_concept_code, round(dosage, 3-floor(log(10, dosage))-1) as dosage   
                                    from ( 
                                        select ingredient_concept_code,
                                        case when amount_value is null and denominator_value is null then 
                                            numerator_value
                                        else 
                                            numerator_value/denominator_value
                                        end as dosage
                                        from drug_strength_stage  where vocabulary_id_1='RxNorm Extension' and amount_value is null
                                    )           
                                )
                            ) 
                        )
