I am looking for a way to count a row only if it is unique. I have done this in the past when I copy the data out and place it in Excel I look at the row above and if it is the same then I give the row a 0 otherwise it is unique and it gets a 1
this is what I have in Excel sorry the formatting is not that great but this is the basic idea of what I need for the output. I will place the above formula in B3. The first row shows that the patient has two rows for the same VISIT NUMBER. The ACCOUNT NUMBER is the Patient Number so when that is the same it is the same patient. So for this count we have a patient with one visit. further down we will see we have 1 patient with 2 separate visits so I would want a 1 in each row.
A B C D
2 VISIT NUMBER VISIT COUNT ACCOUNT NUMBER PATIENT COUNT
3 1587608488 0 505298582 0
4 1587608488 1 505298582 1
5 1080198294 1 505365381 1
6 1071559875 1 505382923 0
7 1072592173 1 505382923 1
8 3080034303 1 505429161 1
I thought I might do a subquery but that just gave me a 1 all the way down and I see why. I was telling it to give me the count for each visit. Well a patient will only have 1 Visit per visit so that is right but not what I was looking for, Even if I got the count right I would only want the total on the last row or even just on one row for that account. In the above example I have 4 patients and 5 visits. Does this make sense?