I have this table (table_2), which basically provides patient info and the department they visited on specific dates –
| Patient_ID | department | service_dte | birth_dte | count |
|---|---|---|---|---|
| 12345 | cardiac | 1/2/21 | 6/18/78 | 5 |
| 12345 | cardiac | 8/20/21 | 6/18/78 | 5 |
| 12345 | cardiac | 10/28/21 | 6/18/78 | 5 |
| 12345 | ortho | 1/14/21 | 6/18/78 | 2 |
| 12345 | ortho | 7/7/21 | 6/18/78 | 2 |
| 12345 | cardiac | 8/20/21 | 6/18/78 | 5 |
| 12345 | cardiac | 4/19/21 | 6/18/78 | 5 |
| 12345 | obgyn | 4/1/21 | 6/18/78 | 1 |
| 78645 | neuro | 5/1/21 | 7/18/87 | 2 |
| 78645 | neuro | 7/7/21 | 7/18/87 | 2 |
| 78645 | ent | 7/7/21 | 7/18/87 | 1 |
| 32423 | gastro | 11/7/21 | 3/12/57 | 1 |
I would like my desired output to produce an additional column (max) that provides highest repeated “department” per “Patient_ID”, while keeping all the columns as they are, similar to below table –
| Patient_ID | department | service_dte | birth_dte | count | max |
|---|---|---|---|---|---|
| 12345 | cardiac | 1/2/21 | 6/18/78 | 5 | cardiac |
| 12345 | cardiac | 8/20/21 | 6/18/78 | 5 | cardiac |
| 12345 | cardiac | 10/28/21 | 6/18/78 | 5 | cardiac |
| 12345 | ortho | 1/14/21 | 6/18/78 | 2 | cardiac |
| 12345 | ortho | 7/7/21 | 6/18/78 | 2 | cardiac |
| 12345 | cardiac | 8/20/21 | 6/18/78 | 5 | cardiac |
| 12345 | cardiac | 4/19/21 | 6/18/78 | 5 | cardiac |
| 12345 | obgyn | 4/1/21 | 6/18/78 | 1 | cardiac |
| 78645 | neuro | 5/1/21 | 7/18/87 | 2 | neuro |
| 78645 | neuro | 7/7/21 | 7/18/87 | 2 | neuro |
| 78645 | ent | 7/7/21 | 7/18/87 | 1 | neuro |
| 32423 | gastro | 11/7/21 | 3/12/57 | 1 | gastro |
I tried below code; that provided me the count of department a patient visited in the above table –
select *
, count(department) OVER (PARTITION BY patient_id, department) AS count
FROM table_1
however, it is not giving me desired value for patient 12345’s max column, which should be cardiac. Instead of cardiac, I am getting ortho. Please kindly assist. This is the code I am trying –
select *
, max(department) OVER (PARTITION BY patient_id) AS max
FROM table_2
Please kindly assist with an optimal way I can get the maximum column and advise on what I am or might be doing wrong.
Thank you.
