Partition By Logic

I have a dataset that has roughly 1 million rows. Without hard coding any claims, what would be the way to get the resulting output? From my research I determined something like DENSE_RANK or ROW_NUMBER() with a partition expression should do the trick.Is there a way to use DENSE_RANK to say “go down the list of PATNO and if the PATNO is the same, then keep going, but if it changes group the above PATNO and make them one claim”.

The dates do not matter in this case. Basically I just want to find a way to tell SQL to automically recognize sets of claims based on Patno. Sometimes there are 50 lines with the same Patno which makes 1 claim, and sometimes there could be only 1-2 lines with the same Patno that make up a claim.

Answer

If you want the sum of charges for a patno, then you want a group by, I think:

select patno, sum(charges)
from t
group by patno;

I think you are overcomplicating the problem.