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 Entity_ID and 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.

  • Is the Entity_ID and Production_Date index clustered? If not, I would alter the index and add INCLUDE (ProductValue) so that this can be done as fast as possible in SQL Server. Also, the section discussing the selection criteria is unclear - you need all ten of the dates for the first ten, but only six for the first 60? An example with sample data would be helpful here. – Laughing Vergil Dec 17 '18 at 20:54
  • I would suggest that you post some data here and then explain your needs via data to help us to help you better, i.e. here is the source data and here is the result I want from the source data – jyao Dec 17 '18 at 21:02
  • Please have a look at this question on how to generate a Minimal, Complete, and Verifiable Example – McNets Dec 17 '18 at 21:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.