maya.more <meena.mk@gmail.com> wrote:
> I have a table with Date and unit column. . I want to find sum of unit column
> considering 3 days each
>
> User will specify start and enddate
>
> Eg
>
> Date Unit
> 10/1/2011 1
> 10/2/2011 2
> 10/3/2011 3
> 10/4/2011 4
> 10/5/2011 4
> 10/6/2011 1
> 10/7/2011 2
> 10/8/2011 3
> 10/9/2011 1
> 10/10/2011 1
> 10/11/2011 1
> suppose if user selects date 10/1/2011 to 10/6/2011
>
> then output should be
>
> start date enddate unit
> 10/1/2011 10/3/2011 6
> 10/4/2011 10/6/2011 9
>
>
> pls let me know if anybody has any idea.
Maybe something like that:
test=*# select * from d; d | i
------------+----2011-10-17 | 12011-10-18 | 22011-10-19 | 32011-10-20 | 42011-10-21 | 52011-10-22 | 62011-10-23 |
72011-10-24 | 82011-10-25 | 92011-10-26 | 102011-10-27 | 112011-10-28 | 122011-10-29 | 132011-10-30 | 142011-10-31 |
152011-11-01| 162011-11-02 | 172011-11-03 | 182011-11-04 | 192011-11-05 | 20
(20 rows)
Time: 0,276 ms
test=*# select '2011-01-01'::date + (extract(doy from d) / 3)::int *
'3day'::interval - '3day'::interval as "from", '2011-01-01'::date +
(extract(doy from d) / 3)::int * '3day'::interval as "to", sum(i) from d
group by 1,2 order by 1; from | to | sum
---------------------+---------------------+-----2011-10-16 00:00:00 | 2011-10-19 00:00:00 | 62011-10-19 00:00:00 |
2011-10-2200:00:00 | 152011-10-22 00:00:00 | 2011-10-25 00:00:00 | 242011-10-25 00:00:00 | 2011-10-28 00:00:00 |
332011-10-2800:00:00 | 2011-10-31 00:00:00 | 422011-10-31 00:00:00 | 2011-11-03 00:00:00 | 512011-11-03 00:00:00 |
2011-11-0600:00:00 | 39
(7 rows)
it fails for more than 1 year, but i hope, you can modify my sql for
your own.
Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly." (unknown)
Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°