-- MedicationCodeDim

-- Differences between DEID and DEID_UF
SELECT TOP 100 *
FROM deid.MedicationCodeDim

SELECT TOP 100 *
FROM deid_uf.MedicationCodeDim


-- What are the Type classifications used?
SELECT Type,
       count(*) AS n
FROM deid_uf.MedicationCodeDim
GROUP BY Type


-- Overview of RxNorm
SELECT TOP 1000 *
FROM deid_uf.MedicationCodeDim
WHERE Type = 'RxNorm'


-- How many ways is guaifenesin represented
SELECT *
FROM deid_uf.MedicationCodeDim
WHERE MedicationGenericName = 'guaiFENesin 100 mg/5 mL oral syrup'

SELECT DISTINCT MedicationGenericName,
                Code
FROM deid_uf.MedicationCodeDim
WHERE MedicationGenericName = 'guaiFENesin 100 mg/5 mL oral syrup'


-- Oxycodone (RXCUI 7804)
SELECT count(*)
FROM deid_uf.MedicationCodeDim
WHERE Type = 'RxNorm'
  AND code = '7804'

-- Medications with oxycodone in name
SELECT count(*)
FROM deid_uf.MedicationCodeDim
WHERE MedicationGenericName LIKE '%oxycodone%'

-- Forms
SELECT MedicationForm,
       count(*) as N
FROM deid_uf.MedicationCodeDim
WHERE MedicationGenericName LIKE '%oxycodone%'
GROUP BY MedicationForm
ORDER BY N DESC


-- Metoprolol
SELECT count(*)
FROM deid_uf.MedicationCodeDim
WHERE Type = 'RxNorm'
  AND CODE = '6918'

-- MedicationSetDim
SELECT TOP 100 *
FROM deid_uf.MedicationSetDim

SELECT Type,
       count(*)
FROM deid_uf.MedicationSetDim
GROUP BY Type


-- Epic Medication Groupers
SELECT TOP 100 *
FROM deid_uf.MedicationSetDim
WHERE Type = 'Epic Medication Grouper'

SELECT DISTINCT Name
FROM deid_uf.MedicationSetDim
WHERE Type = 'Epic Medication Grouper'

-- ERX Narcotics grouper...lots missing!
SELECT DISTINCT MedicationGenericName
FROM deid_uf.MedicationSetDim
WHERE name = 'ERX NARCOTICS'


-- Therapeutic Class
SELECT DISTINCT MedicationTherapeuticClass
FROM deid_uf.MedicationSetDim
WHERE Type = 'Therapeutic Class'
ORDER BY MedicationTherapeuticClass

-- Pharmaceutical Class
SELECT DISTINCT MedicationPharmaceuticalClass
FROM deid_uf.MedicationSetDim
WHERE Type = 'Pharmaceutical Class'
ORDER BY MedicationPharmaceuticalClass

SELECT DISTINCT MedicationPharmaceuticalClass,
                MedicationPharmaceuticalSubclass
FROM deid_uf.MedicationSetDim
WHERE MedicationTherapeuticClass = 'ANTIASTHMATICS'
ORDER BY MedicationPharmaceuticalClass, MedicationPharmaceuticalSubclass


-- MedicationDim
SELECT TOP 1000 *
FROM deid_uf.MedicationDim

SELECT TOP 1000 *
FROM deid_uf.MedicationDim
WHERE NAME <> 'Unknown - STOR Data Warehouse'

SELECT TOP 1000 *
FROM deid_uf.MedicationDim
WHERE GenericName <> '*Unspecified'

SELECT TOP 100 *
FROM MedicationDim
WHERE SimpleGenericName LIKE '%ipratropium%'

SELECT *
FROM MedicationDim
WHERE PharmaceuticalClass = 'ANTICHOLINERGICS, ORALLY INHALED SHORT ACTING'
   OR PharmaceuticalClass = 'BETA-ADRENERGIC AND ANTICHOLINERGIC COMBO, INHALED'
   OR PharmaceuticalClass = 'BETA-ADRENERGIC AGENTS'



SELECT *
FROM deid_uf.MedicationSetDim
WHERE MedicationGenericName LIKE '%leuprolide%'


SELECT count(*)
FROM deid_uf.MedicationSetDim
WHERE MedicationPharmaceuticalClass = 'ANTINEOPLASTIC LHRH(GNRH) AGONIST,PITUITARY SUPPR.'

-- MedicationOrderFact
SELECT TOP 10 *
FROM deid_uf.MedicationOrderFact mof
	     LEFT JOIN deid_uf.MedicationSetDim msd ON msd.MedicationKey = mof.MedicationKey
WHERE msd.MedicationPharmaceuticalClass = 'ANTINEOPLASTIC LHRH(GNRH) AGONIST,PITUITARY SUPPR.'

SELECT count(*)
FROM deid_uf.MedicationOrderFact mof
WHERE mof.MedicationPharmaceuticalClass = 'ANTINEOPLASTIC LHRH(GNRH) AGONIST,PITUITARY SUPPR.'

SELECT TOP 10 *
FROM deid_uf.MedicationOrderFact mof
	     LEFT JOIN deid_uf.PatDurableDim p ON p.PatientDurableKey = mof.PatientDurableKey
WHERE mof.MedicationPharmaceuticalClass = 'ANTINEOPLASTIC LHRH(GNRH) AGONIST,PITUITARY SUPPR.'


SELECT count(DISTINCT mof.PatientDurableKey)
FROM deid_uf.MedicationOrderFact mof
	     LEFT JOIN deid_uf.PatDurableDim p ON p.PatientDurableKey = mof.PatientDurableKey
WHERE mof.MedicationPharmaceuticalClass = 'ANTINEOPLASTIC LHRH(GNRH) AGONIST,PITUITARY SUPPR.'

SELECT DISTINCT mof.PatientDurableKey,
                UCSFDerivedRaceEthnicity_X,
                Sex
FROM deid_uf.MedicationOrderFact mof
	     LEFT JOIN deid_uf.MedicationSetDim msd ON msd.MedicationKey = mof.MedicationKey
	     LEFT JOIN deid_uf.PatDurableDim p ON p.PatientDurableKey = mof.PatientDurableKey
WHERE msd.MedicationPharmaceuticalClass = 'ANTINEOPLASTIC LHRH(GNRH) AGONIST,PITUITARY SUPPR.'


SELECT count(DISTINCT mof.PatientDurableKey),
                Sex
FROM deid_uf.MedicationOrderFact mof
	     LEFT JOIN deid_uf.PatDurableDim p ON p.PatientDurableKey = mof.PatientDurableKey
WHERE mof.MedicationPharmaceuticalClass = 'ANTINEOPLASTIC LHRH(GNRH) AGONIST,PITUITARY SUPPR.'
GROUP BY Sex


SELECT mof.PatientDurableKey,
       min(mof.StartDateKeyValue) AS ADMIN_DATE
FROM deid_uf.MedicationOrderFact mof
	     LEFT JOIN deid_uf.PatDurableDim p ON p.PatientDurableKey = mof.PatientDurableKey
WHERE mof.MedicationPharmaceuticalClass = 'ANTINEOPLASTIC LHRH(GNRH) AGONIST,PITUITARY SUPPR.'
GROUP BY mof.PatientDurableKey

SELECT *
FROM deid_uf.MedicationOrderFact
WHERE PatientDurableKey = 'D8F125D64E6F74'


SELECT *
FROM deid_uf.MedicationOrderFact
WHERE PatientDurableKey = 'DFE52CDC8D497F'
  AND MedicationPharmaceuticalClass = 'ANTINEOPLASTIC LHRH(GNRH) AGONIST,PITUITARY SUPPR.'
ORDER BY StartDateKeyValue


SELECT *
FROM deid_uf.MedicationOrderFact
WHERE PatientDurableKey = 'DFE52CDC8D497F'
  AND MedicationPharmaceuticalClass = 'ANTINEOPLASTIC LHRH(GNRH) AGONIST,PITUITARY SUPPR.'
  AND Frequency <> '*Unspecified'
ORDER BY StartDateKeyValue

SELECT mof.PatientDurableKey,
       min(mof.StartDateKeyValue) AS FIRST_START_DATE,
       mof.MedicationGenericName
FROM deid_uf.MedicationOrderFact mof
	     LEFT JOIN deid_uf.MedicationSetDim msd ON msd.MedicationKey = mof.MedicationKey
	     LEFT JOIN deid_uf.PatDurableDim p ON p.PatientDurableKey = mof.PatientDurableKey
WHERE msd.MedicationPharmaceuticalClass = 'ANTINEOPLASTIC LHRH(GNRH) AGONIST,PITUITARY SUPPR.'
GROUP BY mof.PatientDurableKey, mof.StartDateKeyValue, mof.MedicationGenericName
ORDER BY mof.PatientDurableKey


SELECT mof.PatientDurableKey,
       min(mof.StartDateKeyValue)   AS FIRST_START_DATE,
       min(mof.OrderedDateKeyValue) AS FIRST_ORDER_DATE,
       mof.MedicationGenericName
FROM deid_uf.MedicationOrderFact mof
	     LEFT JOIN deid_uf.PatDurableDim p ON p.PatientDurableKey = mof.PatientDurableKey
WHERE mof.MedicationPharmaceuticalClass = 'ANTINEOPLASTIC LHRH(GNRH) AGONIST,PITUITARY SUPPR.'
GROUP BY mof.PatientDurableKey, mof.StartDateKeyValue, mof.OrderedDateKeyValue, mof.MedicationGenericName
ORDER BY mof.PatientDurableKey



SELECT mof.PatientDurableKey,
       mof.mode,
       min(mof.StartDateKeyValue)   AS FIRST_START_DATE,
       min(mof.OrderedDateKeyValue) AS FIRST_ORDER_DATE,
       mof.MedicationGenericName
FROM deid_uf.MedicationOrderFact mof
	     LEFT JOIN deid_uf.MedicationSetDim msd ON msd.MedicationKey = mof.MedicationKey
	     LEFT JOIN deid_uf.PatDurableDim p ON p.PatientDurableKey = mof.PatientDurableKey
WHERE msd.MedicationPharmaceuticalClass = 'ANTINEOPLASTIC LHRH(GNRH) AGONIST,PITUITARY SUPPR.'
GROUP BY mof.PatientDurableKey, mof.StartDateKeyValue, mof.OrderedDateKeyValue, mof.MedicationGenericName, mode
ORDER BY mof.PatientDurableKey

SELECT mof.PatientDurableKey,
       p.BirthDate,
       min(mof.StartDateKeyValue)   AS FIRST_START_DATE,
       min(mof.OrderedDateKeyValue) AS FIRST_ORDER_DATE
FROM deid_uf.MedicationOrderFact mof
	     LEFT JOIN deid_uf.MedicationSetDim msd ON msd.MedicationKey = mof.MedicationKey
	     LEFT JOIN deid_uf.PatDurableDim p ON p.PatientDurableKey = mof.PatientDurableKey
WHERE msd.MedicationPharmaceuticalClass = 'ANTINEOPLASTIC LHRH(GNRH) AGONIST,PITUITARY SUPPR.'
GROUP BY mof.PatientDurableKey, p.BirthDate
ORDER BY p.BirthDate DESC

SELECT mof.PatientDurableKey,
       p.BirthDate,
       min(mof.StartDateKeyValue)   AS FIRST_START_DATE,
       min(mof.OrderedDateKeyValue) AS FIRST_ORDER_DATE,
       CASE
	       WHEN min(mof.StartDateKeyValue) IS NOT NULL THEN min(mof.StartDateKeyValue)
	       ELSE min(mof.OrderedDateKeyValue)
	       END                      AS DateToUse
FROM deid_uf.MedicationOrderFact mof
	     LEFT JOIN deid_uf.PatDurableDim p ON p.PatientDurableKey = mof.PatientDurableKey
WHERE mof.MedicationPharmaceuticalClass = 'ANTINEOPLASTIC LHRH(GNRH) AGONIST,PITUITARY SUPPR.'
GROUP BY mof.PatientDurableKey, p.BirthDate
ORDER BY p.BirthDate

-- Inserting into temporary table
SELECT mof.PatientDurableKey,
       p.BirthDate,
       min(mof.StartDateKeyValue)   AS FIRST_START_DATE,
       min(mof.OrderedDateKeyValue) AS FIRST_ORDER_DATE,
       CASE
	       WHEN min(mof.StartDateKeyValue) IS NOT NULL THEN min(mof.StartDateKeyValue)
	       ELSE min(mof.OrderedDateKeyValue)
	       END                      AS DateToUse
	INTO ##Lupron
FROM deid_uf.MedicationOrderFact mof
	     LEFT JOIN deid_uf.MedicationSetDim msd ON msd.MedicationKey = mof.MedicationKey
	     LEFT JOIN deid_uf.PatDurableDim p ON p.PatientDurableKey = mof.PatientDurableKey
WHERE msd.MedicationPharmaceuticalClass = 'ANTINEOPLASTIC LHRH(GNRH) AGONIST,PITUITARY SUPPR.'
GROUP BY mof.PatientDurableKey, p.BirthDate
ORDER BY p.BirthDate

-- Retrieving results from temporary table
select * from #Lupron