Getting values for a varying date range in Postgresql

Question

I am trying to get a maximum value from one table using the date range from another table. I am using SQL on a postgresql database. The goal is to get the maximum value for the date range added to the table that has the start and end date (by year and area). I see this as two steps outlined below.

Step One: I am looking to use two columns that are dates in Table1 to create a range. The table has these columns:

ID (integer(11))
Date1 (varchar(10))
Date2 (varchar(10))
Year (integer)
Area (varchar)

Here is some sample data from Table1:

ID   Date1      Date2        Year    Area
101  8/21/2000  11/20/2000   2000    5
102  7/31/2000  10/30/2000   2000    5
103  7/10/2000  10/9/2000    2000    6
104  7/10/2000  10/9/2000    2000    6
105  7/4/2000   10/3/2000    2000    6
106  7/10/2000  10/9/2000    2000    6
107  7/31/2000  10/30/2000   2000    7
108  7/31/2000  10/30/2000   2000    7

Step Two: Pull the maximum value from Table2 based on the varying date range from Date1 to Date2 in Table1. Table2 has these values:

Date (varchar(12))
Area (varchar(11))
Value (varchar(6))

Here is some (very limited) sample data from Table2:

Date      Area  Value
8/2/2000    5   72.1
8/25/2000   5   68.4
9/14/2000   5   53.3
7/5/2000    6   47.9
8/1/2000    6   10.2
9/30/2000   6   11.6
8/5/2000    7   35.2
9/1/2000    7   45.4

So in the end I would like a modified Table1 that adds the Max_Value for the date range (pulled from Table2) and looks like this:

ID   Date1      Date2       Year    Area    Max_Value
101  8/21/2000  11/20/2000  2000    5       68.4
102  7/31/2000  10/30/2000  2000    5       72.1
103  7/10/2000  10/9/2000   2000    6       11.6
104  7/10/2000  10/9/2000   2000    6       11.6
105  7/4/2000   10/3/2000   2000    6       47.9
106  7/10/2000  10/9/2000   2000    6       11.6
107  7/31/2000  10/30/2000  2000    7       45.4
108  7/31/2000  10/30/2000  2000    7       45.4

Thanks for any help in advance.

Gordon Linoff · Accepted Answer · 2015-05-13 18:35:24Z

1

You can do this in several ways. One method would use join with an explicit aggregation. However, because you only want one column, I think a correlated subquery is simpler to code:

select t1.*,
       (select max(t2.value)
        from table2 t2
        where t2.date between t1.date1 and t1.date2
       ) as maxvalue
from table1 t1;

answered May 13, 2015 at 18:35

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Sasha Over a year ago

@GordanLinoff That is very close. The other part I am having an issue with is the maximum value for all Year and Area combinations (for that varying date range).

Tim3880 Over a year ago

Then add "and t2.area=t1.area" in the sub query.

Collectives™ on Stack Overflow

Getting values for a varying date range in Postgresql

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related