CREATE VIEW VW_ISREFERENCED_LOC
SELECT
LEVELOFCAREID,
SHORTDESCRIPTION,
CODE,
CAST(
CASE WHEN EXISTS( SELECT 'X' FROM CHILDACTIVITY WHERE CHILDACTIVITY.LevelOfCareID IS NOT NULL AND CHILDACTIVITY.LevelOfCareID = LEVELOFCARE.LEVELOFCAREID) THEN 1
WHEN EXISTS( SELECT 'X' FROM CHILDACTIVITYHISTORY WHERE CHILDACTIVITYHISTORY.LevelOfCareID IS NOT NULL AND CHILDACTIVITYHISTORY.LevelOfCareID = LEVELOFCARE.LEVELOFCAREID) THEN 1
WHEN EXISTS( SELECT 'X' FROM SHELTER WHERE SHELTER.LevelOfCareID IS NOT NULL AND SHELTER.LevelOfCareID = LEVELOFCARE.LEVELOFCAREID) THEN 1
WHEN EXISTS( SELECT 'X' FROM SHELTERINQUIRY WHERE SHELTERINQUIRY.LevelOfCareID IS NOT NULL AND SHELTERINQUIRY.LevelOfCareID = LEVELOFCARE.LEVELOFCAREID) THEN 1
WHEN EXISTS( SELECT 'X' FROM RATE WHERE RATE.LevelOfCareID IS NOT NULL AND RATE.LevelOfCareID = LEVELOFCARE.LEVELOFCAREID) THEN 1
WHEN EXISTS( SELECT 'X' FROM RATEHISTORY WHERE RATEHISTORY.LevelOfCareID IS NOT NULL AND RATEHISTORY.LevelOfCareID = LEVELOFCARE.LEVELOFCAREID) THEN 1
WHEN EXISTS( SELECT 'X' FROM SHELTERHISTORY WHERE SHELTERHISTORY.LevelOfCareID IS NOT NULL AND SHELTERHISTORY.LevelOfCareID = LEVELOFCARE.LEVELOFCAREID) THEN 1
WHEN EXISTS( SELECT 'X' FROM FOSTERCHILD WHERE FOSTERCHILD.LevelOfCareID IS NOT NULL AND FOSTERCHILD.LevelOfCareID = LEVELOFCARE.LEVELOFCAREID) THEN 1
WHEN EXISTS( SELECT 'X' FROM FOSTERCHILDHISTORY WHERE FOSTERCHILDHISTORY.LevelOfCareID IS NOT NULL AND FOSTERCHILDHISTORY.LevelOfCareID = LEVELOFCARE.LEVELOFCAREID) THEN 1
WHEN EXISTS( SELECT 'X' FROM FINANCIALPOSTINGDETAIL WHERE FINANCIALPOSTINGDETAIL.LevelOfCareID IS NOT NULL AND FINANCIALPOSTINGDETAIL.LevelOfCareID = LEVELOFCARE.LEVELOFCAREID) THEN 1
WHEN EXISTS( SELECT 'X' FROM MENTALSERVICEREQUEST WHERE MENTALSERVICEREQUEST.LevelOfCareID IS NOT NULL AND MENTALSERVICEREQUEST.LevelOfCareID = LEVELOFCARE.LEVELOFCAREID) THEN 1
ELSE 0 END AS INT) AS IsBeingReferenced
FROM LEVELOFCARE
