gistfile1.sql

SELECT OwnerTypeName AS Folder,
       EncounterNo, EncntrStartDate, 
       EnrolleeName,
       DocTypeName, d.DocId, ModifyDateTime
FROM DocTypes AS dt
  INNER JOIN documents AS d ON dt.DocType = d.DocType
  INNER JOIN docsowners AS do ON d.DocId = do.DOcId
  INNER JOIN Owners AS o ON do.OwnerId = o.OwnerId
  INNER JOIN OwnerTypes AS ot ON o.OwnerType = ot.OwnerType
  INNER JOIN Encounters AS e ON do.OwnerId = e.EncntrOwnerId
  INNER JOIN MedicalRecords AS mr ON e.MedRecOwnerId = mr.MedRecOwnerId
  INNER JOIN Enrollees AS en ON mr.EnrolleeOwnerId = en.EnrolleeOwnerId
WHERE DocTypeName = 'CL0120'
AND EncntrStartDate >= '1/17/2012 12:00 AM' AND EncntrStartDate <= '2/1/2012 5:00 AM'

UNION

SELECT OwnerTypeName AS Folder,
       EncounterNo, EncntrStartDate, 
       EnrolleeName,
       DocTypeName, d.DocId, ModifyDateTime
FROM DocTypes AS dt
  INNER JOIN documents AS d ON dt.DocType = d.DocType
  INNER JOIN docsowners AS do ON d.DocId = do.DOcId
  INNER JOIN Owners AS o ON do.OwnerId = o.OwnerId
  INNER JOIN OwnerTypes AS ot ON o.OwnerType = ot.OwnerType
  INNER JOIN MedicalRecords AS mr ON do.OwnerId = mr.MedRecOwnerId
  INNER JOIN Enrollees AS en ON mr.EnrolleeOwnerId = en.EnrolleeOwnerId
  Inner Join Encounters as e on mr.MedRecOwnerId = e.MedRecOwnerId
WHERE DocTypeName = 'CL0120'
AND EncntrStartDate >= '1/17/2012 12:00 AM' AND EncntrStartDate <= '2/1/2012 5:00 AM'