I need to get the MIN and MAX dates for volume but I need to group it based on volume and not all the volume of same amount.... Basically, I have daily volume and dates for those daily volume. I need to be able to get the MIN Date as "to" and MAX date as "from" for a set of volume. Note that the volume can traverse dates and then break and then have a new set of dates for the same volume. Hopefully the screenshots below do a better job explaining than I can. I know how to do this via code.. but was wondering if the same was possible with SQL. Please note that the SQL will be called from within an application and I can't insert into a temp table to get my end result data set...
Here is the raw data from the table that I am querying:
Here is what I ultimately want:
And here is what I am getting with my currenty SQL query:
Result from my SQL Query:
SQL Code to replicate table:
Variation of the 'Gaps and Islands problem'.
Once you've worked out a way of grouping the consecutive volumes, then it's easy :)
I chose to use a combination of a row_number() over the volume_dates, partitioned by the volumes, and a row_number() over the entire set of volume_dates.
answered Nov 24, 2015 at 11:37 AM
Kev Riley ♦♦