Hi Sharon,

unless you post the CREATE TABLE statements + INSERT INTO with sample data, it is just groping in the dark....

However, your PARTITION seems to be incorrect if I understand the example correctly.

IMHO it should be something like :

ROW_NUMBER() OVER (PARTITION BY S.MRN ORDER BY S.SESSIONID) AS VISIT_ID

You want to split your result into parts where each part contains all visits of a certain patient... so PARTITION BY should contain identification of the patient... and then you want to number each patient's visits from 1, so you need to order by either sessionID or ChartStartTime (maybe.. not sure how the data look in reality).

Did it help a bit? If not, please, take the time to post table structure and sample data - then someone will be able to help you.