dmd oddities

dm+d oddities

VMPs with multiple DT categories

In almost all cases, all VMPPs in the same VMP family belong to the same DT category (or don't have a DT category).

In a handful of cases, a VMP has some VMPPs belong to one DT category, and others belonging to another one.

SELECT
     dmd2_vmp.vpid,
     dmd2_vmp.nm,
     dmd2_dtpaymentcategory.descr,
     COUNT(*)
FROM dmd2_vmp
INNER JOIN (
    SELECT dmd2_vmp.vpid
    FROM dmd2_vmp
    INNER JOIN dmd2_vmpp
        ON dmd2_vmp.vpid = dmd2_vmpp.vpid
    INNER JOIN dmd2_dtinfo 
        ON dmd2_vmpp.vppid = dmd2_dtinfo.vppid
    GROUP BY dmd2_vmp.vpid
    HAVING COUNT(DISTINCT pay_catcd) > 1
) s
    ON dmd2_vmp.vpid = s.vpid
INNER JOIN dmd2_vmpp
    ON dmd2_vmp.vpid = dmd2_vmpp.vpid
INNER JOIN dmd2_dtinfo
    ON dmd2_vmpp.vppid = dmd2_dtinfo.vppid
INNER JOIN dmd2_dtpaymentcategory
    ON dmd2_dtinfo.pay_catcd = dmd2_dtpaymentcategory.cd
GROUP BY
    dmd2_vmp.vpid,
    dmd2_vmp.nm,
    dmd2_dtpaymentcategory.descr
ORDER BY dmd2_vmp.nm;

vpid	nm	descr	count
7526911000001105	Iodine aqueous oral solution sugar free	Part VIIIA Category C	1
7526911000001105	Iodine aqueous oral solution sugar free	Part VIIIA Category A	1
3655311000001100	Isopropyl myristate 15% / Liquid paraffin 15% gel	Part VIIIA Category C	2
3655311000001100	Isopropyl myristate 15% / Liquid paraffin 15% gel	Part IXa	2
10901211000001104	Menthol 2% in Aqueous cream	Part VIIIA Category C	1
10901211000001104	Menthol 2% in Aqueous cream	Part IXa	1
7293511000001108	Ostomy adhesives	Part IXc	4
7293511000001108	Ostomy adhesives	Parts IXb and IXc	2
7294111000001102	Ostomy deodorants	Part IXc	26
7294111000001102	Ostomy deodorants	Part IXa	1
3376311000001102	White soft paraffin 15% / Liquid paraffin light 6% cream	Part VIIIA Category C	3
3376311000001102	White soft paraffin 15% / Liquid paraffin light 6% cream	Part IXa	1