I have a table(DrData) that stores Dr data. One Dr can have multiple rows on this table. One row for each location they provide services for within a healthcare system. On this table there is a column named DrStartDt and DrEndDt. I am writing an extract that only wants one row in the output per Dr. In my output I have a field called ActiveIndicator. If the Dr is currently providing services to at least 1 location the ActiveIndicator will be set to 1. If not, it should be set to 0.

I tried querying the DrData table using a distinct and this will give me one row per Dr. However, it won’t let me properly set the ActiveIndicator field based on the DrStartDt and DrEndDt fields.