Postgres Sql Query Sum Between generate_series

Question

Scratching my head over this one. I am trying to Sum all t1.sales and group into a generated time series of 48 periods in the next 3 days. However my attempt below is returning incorrect results.

SELECT 
seq.date,
hh.period,
sum(t1.sales)
From
(select date(date)from generate_series(current_date,current_date + '3 days'::interval ,'1 day'::interval) date)as seq 
cross join (select period from generate_series (1,48) period) hh
Left join tbl_look t1
ON (
seq.date between t1.from_date and t1.to_date 
and hh.period between t1.from_period and t1.to_period)

Example of the tbl_look, see that period 33 has two rows. I need the sum to capture the 'to_period' when present. In the example below to_period 33 should sum to 13.5, and to_period 34 should = 17.8.

from_date     to_date      ;from_period; to_period; sales
"2016-12-19"  "2016-12-19" ;33         ; 48       ; 5.000
"2016-12-19"  "2016-12-19" ;33         ;          ; 8.500
"2016-12-19"  "2016-12-19" ;34         ;          ; 12.800
"2016-12-19"  "2016-12-19" ;35         ;          ; 16.000
"2016-12-19"  "2016-12-19" ;36         ; 38       ; 17.450
"2016-12-19"  "2016-12-19" ;37         ;          ; 17.850
"2016-12-19"  "2016-12-19" ;38         ;          ; 17.400

Expected results

seq.results   hh.period  sales
"2016-12-19"  33         13.5
"2016-12-19"  34         17.8
"2016-12-19"  35         21
"2016-12-19"  36         22.45
"2016-12-19"  37         40.3
"2016-12-19"  38         39.85
"2016-12-19"  39         5
"2016-12-19"  40         5
 ......

yes, first row shows: to_period 48 so that's 5 in all periods to 48. So period 34 = 12.8+5 = 17.8. — user636322
– user636322, Commented Dec 20, 2016 at 9:44
Could you please add to_date column to your tbl_look sample + requested results? — David דודו Markovitz
– David דודו Markovitz, Commented Dec 20, 2016 at 10:29

David דודו Markovitz · Accepted Answer · 2016-12-20 11:15:52Z

1

select      date (dt.date)      as date
           ,pr.period           as period
           ,sum (t.sales)       as sales

from                    generate_series (current_date,current_date+3,'1 day'::interval) as dt (date)

            cross join  generate_series (1,48) as pr (period)

            left join   tbl_look  as t

            on          dt.date   between t.from_date   and t.to_date 
                    and pr.period between t.from_period and coalesce (t.to_period,t.from_period)

group by    dt.date
           ,pr.period

order by  dt.date
         ,pr.period

+------------+--------+--------+
| date       | period | sales  |
+------------+--------+--------+
| 2016-12-19 | 1      | (null) |
+------------+--------+--------+
| 2016-12-19 | 2      | (null) |
+------------+--------+--------+
.
.
.
+------------+--------+--------+
| 2016-12-19 | 32     | (null) |
+------------+--------+--------+
| 2016-12-19 | 33     | 13.500 |
+------------+--------+--------+
| 2016-12-19 | 34     | 17.800 |
+------------+--------+--------+
| 2016-12-19 | 35     | 21.000 |
+------------+--------+--------+
| 2016-12-19 | 36     | 22.450 |
+------------+--------+--------+
| 2016-12-19 | 37     | 40.300 |
+------------+--------+--------+
| 2016-12-19 | 38     | 39.850 |
+------------+--------+--------+
| 2016-12-19 | 39     | 5.000  |
+------------+--------+--------+
| 2016-12-19 | 40     | 5.000  |
+------------+--------+--------+
| 2016-12-19 | 41     | 5.000  |
+------------+--------+--------+
| 2016-12-19 | 42     | 5.000  |
+------------+--------+--------+
| 2016-12-19 | 43     | 5.000  |
+------------+--------+--------+
| 2016-12-19 | 44     | 5.000  |
+------------+--------+--------+
| 2016-12-19 | 45     | 5.000  |
+------------+--------+--------+
| 2016-12-19 | 46     | 5.000  |
+------------+--------+--------+
| 2016-12-19 | 47     | 5.000  |
+------------+--------+--------+
| 2016-12-19 | 48     | 5.000  |
+------------+--------+--------+

edited Dec 20, 2016 at 11:15

answered Dec 20, 2016 at 11:06

David דודו Markovitz

45.2k7 gold badges75 silver badges97 bronze badges

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

2 Comments

David דודו Markovitz Over a year ago

Please check now. This is the final version (If this was indeed the requested format for the results...)

user636322 Over a year ago

Your right my example was not rigorous enough. but thanks for your solution its super!

Clodoaldo Neto · Accepted Answer · 2016-12-20 13:59:25Z

This one does not need a cross join:

select
    gs.d::date,
    generate_series(
        from_period, coalesce(to_period, from_period)
    ) as period,
    sum(sales) as sales
from
    look
    right join
    generate_series(
        current_date, current_date + 3, '1 day'
    ) gs (d) on d between from_date and to_date
group by 1, 2
order by 1, 2
;
     d      | period | sales  
------------+--------+--------
 2016-12-20 |     33 | 13.500
 2016-12-20 |     34 | 17.800
 2016-12-20 |     35 | 21.000
 2016-12-20 |     36 | 22.450
 2016-12-20 |     37 | 40.300
 2016-12-20 |     38 | 39.850
 2016-12-20 |     39 |  5.000
 2016-12-20 |     40 |  5.000
 2016-12-20 |     41 |  5.000
 2016-12-20 |     42 |  5.000
 2016-12-20 |     43 |  5.000
 2016-12-20 |     44 |  5.000
 2016-12-20 |     45 |  5.000
 2016-12-20 |     46 |  5.000
 2016-12-20 |     47 |  5.000
 2016-12-20 |     48 |  5.000

Collectives™ on Stack Overflow

Postgres Sql Query Sum Between generate_series

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related