MIN and MAX with values with a twist


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: alt text

Here is what I ultimately want:

alt text

And here is what I am getting with my currenty SQL query:

 SELECT daily_volume, MIN(volume_date) AS min_date,
         MAX(volume_date) AS max_date, ins_num
 FROM daily_volume
 WHERE    ins_num = 3854439
 GROUP BY daily_volume, ins_num

Result from my SQL Query:

  202    11/30/2015    11/30/2015    3854439
  900    11/28/2015    11/28/2015    3854439
 1100    11/24/2015    12/3/2015    3854439
 1200    11/27/2015    11/27/2015    3854439
 1300    11/26/2015    11/26/2015    3854439

SQL Code to replicate table:

 -- Drop Table if it exists
 IF OBJECT_ID('TempDB..#mytable','U') IS NOT NULL
         DROP TABLE #mytable
 -- Create Table
 CREATE TABLE #mytable (
     [ins_num] [int] NULL,
     [param_seq_num] [tinyint] NULL,
     [volume_date] [datetime] NULL,
     [daily_volume] [float] NULL,
     [monthly_priced_volume] [float] NULL
 -- Insert Data into Table
 -- == Insert Data into Table
 INSERT INTO #mytable
         (ins_num, param_seq_num, volume_date, daily_volume, monthly_priced_volume)
  '3854439','1','Nov 24 2015 12:00AM','1100','0', 
  '3854439','1','Nov 25 2015 12:00AM','1100','0',  
  '3854439','1','Nov 26 2015 12:00AM','1300','0', 
  '3854439','1','Nov 27 2015 12:00AM','1200','0', 
  '3854439','1','Nov 28 2015 12:00AM','900','0', 
  '3854439','1','Nov 29 2015 12:00AM','1100','0', 
  '3854439','1','Nov 30 2015 12:00AM','202','0', 
  '3854439','1','Dec  1 2015 12:00AM','1100','0', 
  '3854439','1','Dec  2 2015 12:00AM','1100','0', 
  '3854439','1','Dec  3 2015 12:00AM','1100','0'

rawdata.jpg (73.0 kB)
endresult.jpg (36.5 kB)
more ▼

asked Nov 23, 2015 at 04:21 PM in Default

avatar image

40 2

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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.

     min(daily_volume) as qty,
     min(volume_date) as [from],
     max(volume_date) as [to]
        - (row_number()over(partition by daily_volume order by volume_date ) 
        - row_number()over(order by volume_date )) as GroupID
 from #mytable
 ) grouping_subquery
 group by groupID
 order by 
more ▼

answered Nov 24, 2015 at 11:37 AM

avatar image

Kev Riley ♦♦
66.8k 48 65 81


You are a genius! This worked flawlessly. I can't even come close to understanding what you did above but I'm going to start reading up on Gaps and Islands problems to make some sense.


Nov 24, 2015 at 06:33 PM bimini07

Itzik Ben-Gan is my go-to resource for this - you can get his chapter from MVP Deep Dives, on this for free from https://www.manning.com/books/sql-server-mvp-deep-dives

Nov 24, 2015 at 08:04 PM Kev Riley ♦♦

And to understand better how it works, break out those columns from the subquery and look at the values

Nov 24, 2015 at 08:05 PM Kev Riley ♦♦
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Nov 23, 2015 at 04:21 PM

Seen: 119 times

Last Updated: Nov 24, 2015 at 08:05 PM

Copyright 2018 Redgate Software. Privacy Policy