Medicare Health Assessment Report

-- Valid NPI or Prov# - Start
SELECT (last_name + ', ' + first_name) AS Provider
	,LTRIM(RTRIM(NPI_nbr)) AS NPI#
	,LTRIM(RTRIM(amisys_prov_nbr)) AS Provider#
	,(
		SELECT COUNT(*) TotalCount
		FROM MedicareHealthAssessment
		WHERE providerid = LTRIM(RTRIM(vp.amisys_prov_nbr))
		GROUP BY providerid
		) AS TotalCountByProvNum
	,(
		SELECT COUNT(*) TotalCount
		FROM MedicareHealthAssessment
		WHERE prov_NPI = LTRIM(RTRIM(vp.NPI_nbr))
		GROUP BY prov_NPI
		) AS TotalCountByNPI
FROM vistar_provider vp
WHERE (
		SELECT COUNT(*) TotalCount
		FROM MedicareHealthAssessment
		WHERE providerid = vp.amisys_prov_nbr
		GROUP BY providerid
		) > 0
	OR (
		SELECT COUNT(*) TotalCount
		FROM MedicareHealthAssessment
		WHERE prov_NPI = LTRIM(RTRIM(vp.NPI_nbr))
		GROUP BY prov_NPI
		) > 0
ORDER BY (
		SELECT COUNT(*) TotalCount
		FROM MedicareHealthAssessment
		WHERE providerid = vp.amisys_prov_nbr
		GROUP BY providerid
		) DESC;

-- Valid NPI or Prov# - Stop
-- Total Count NPI - Start
SELECT prov_NPI
	,CASE
		WHEN (
				SELECT DISTINCT NPI_nbr
				FROM vistar_provider
				WHERE NPI_nbr = prov_NPI
				) <> ''
			THEN 'True'
		ELSE 'False'
		END AS Valid
	,COUNT(*) TotalCount
FROM MedicareHealthAssessment
GROUP BY prov_NPI
HAVING COUNT(*) > 1
ORDER BY Valid
	,COUNT(*) DESC;

-- Total Count NPI - Stop
-- Total Count Prov# - Start
SELECT providerid
	,Valid = CASE
		WHEN (
				SELECT amisys_prov_nbr
				FROM vistar_provider
				WHERE amisys_prov_nbr = providerid
				) <> ''
			THEN 'True'
		ELSE 'False'
		END
	,COUNT(*) TotalCount
FROM MedicareHealthAssessment
GROUP BY providerid
HAVING COUNT(*) > 1
ORDER BY Valid
	,COUNT(*) DESC;

-- Total Count Prov# - Stop
-- Report Bad NPI/ProvNum - Start
SELECT assessmentid
	,prov_fname
	,prov_lname
	,prov_cred
	,providerid
	,prov_NPI
	,prov_phone
	,username
FROM EktronWwwProd.dbo.MedicareHealthAssessment
WHERE providerid IN (
		SELECT providerid
		FROM MedicareHealthAssessment
		WHERE CASE
				WHEN (
						SELECT amisys_prov_nbr
						FROM vistar_provider
						WHERE amisys_prov_nbr = providerid
						) <> ''
					THEN 'True'
				ELSE 'False'
				END = 'False'
		GROUP BY providerid
		)
	OR prov_NPI IN (
		SELECT prov_NPI
		FROM MedicareHealthAssessment
		WHERE CASE
				WHEN (
						SELECT DISTINCT NPI_nbr
						FROM vistar_provider
						WHERE NPI_nbr = prov_NPI
						) <> ''
					THEN 'True'
				ELSE 'False'
				END = 'False'
		GROUP BY prov_NPI
		)
ORDER BY username;

-- Report Bad NPI/ProvNum - Stop
SELECT assessmentid AS ID
	,groupname AS GroupName
	,memberid AS MemberNo
	,CASE
		WHEN au.MemberNo IS NOT NULL
			THEN 'Valid'
		END AS MemberValidity
	,LTRIM(RTRIM(providerid))
	,CASE
		WHEN vp.amisys_prov_nbr IS NOT NULL
			THEN 'Valid'
		END AS ProviderValidity
	,prov_NPI
	,CASE
		WHEN vp1.NPI_nbr IS NOT NULL
			THEN 'Valid'
		END AS NPIValidity
	,assessment_status
	,dbo.fnFormatDate(assessment_open_date, 'MM/DD/YYYY') AS assessment_open_date
	,dbo.fnFormatDate(assessment_close_date, 'MM/DD/YYYY') AS assessment_close_date
	,reviewer_1
	,reviewer_2
	,diagnosis_code
	,dbo.fnFormatDate(date_of_service, 'MM/DD/YYYY') AS date_of_service
	,provider_type
	,dbo.fnFormatDate(diagnosis_code_entry_date, 'MM/DD/YYYY') AS diagnosis_code_entry_date
	,diagnosis_code_status
	,PCN
	,SOURCE_REFERENCE
	,PROVIDER_REFERENCE_NUMBER
	,FORM_REFERENCE_NUMBER
	,CLAIM_REFERENCE_NUMBER
	,mem_fname
	,mem_lname
	,mem_minit
	,dbo.fnFormatDate(mem_dob, 'MM/DD/YYYY') AS mem_dob
	,mem_bmi
	,prov_fname
	,prov_lname
	,prov_cred
	,dbo.FormatUSPhone(prov_phone) AS prov_phone
	,attestation
	,username
	,exported
FROM EktronWwwProd.dbo.MedicareHealthAssessment mha
LEFT OUTER JOIN vistar_provider vp ON vp.amisys_prov_nbr = LTRIM(RTRIM(mha.providerid))
LEFT OUTER JOIN vistar_provider vp1 ON vp1.NPI_nbr = LTRIM(RTRIM(mha.prov_NPI))
LEFT OUTER JOIN Amisys.dbo.AmisysUser au ON au.MemberNo COLLATE Latin1_General_CI_AS = mha.memberid COLLATE Latin1_General_CI_AS