I have a table in my SQL Server DB that is sturctured as
- Entity_ID varchar(255)
- Production_Date date
- ProductValue float
The table is indexed by
Production_Date. The dates are one month apart and there are no gaps in the dates once the data starts for each
Entity_ID. But each entity will start a different date.
There can be hundreds of rows for a single
Entity_ID. For the first sixty rows for an
Entity_ID (sorted ascending by the date) I need to know the ordinal position of the maximum value for each series of dates, starting at the first date and going to sixth date.
By this for the tenth date in the series for a particular entity, I am looking for the position of the maximum values for the first through tenth dates.
I can see how I can do this within a C# program, but I was wondering if a SQL script might be faster. I have a couple of million entities and I can see grinding through the database could take time. I am wondering if straight SQL might be faster than dragging all the data from the database into a C# program.
As far as I am thinking about structuring a C# program, I would read the first 60 values for each entity. Then start walking through the stack of values. For each step along the way, I would keep track of the position of the maximum value as I go along. For each step from 1 to 60 I could then write back to the database the position of the maximum value up to that step.
I know how to do this in C#, but am struggling to see if this logic (or some other) can be applied in a SQL script.