All Forums Database
arjun2189 1 post Joined 07/15
08 Jul 2015
Sum of previous rows based on time intervals

Hi,
I am unable to get the intended result table.
I have the following table:
ORDERSERIALNO     USERID       PRODSKU         PRODUCTTABLE       NETREVENUE     ORDERPLACEDTIME          
123-345                   123            PHOTO01           CARDS                            44                  31OCT2010                  
123-454                   123            PHOTO5x7         CARDS                            30                  23MAR2011
123-333                  123            PHOTO8x10        CARDS                            50                  12OCT2011
123-897                  123            IPHONE6            PAPERGIFT                       40                 6DEC2012
 
I want the result table as
ORDERSERIALNO           PRODSKU         PRODUCTTABLE       NETREVENUE     ORDERPLACEDTIME      REVENUE_BEFORE1YEAR    
123-345                        PHOTO01           CARDS                            44                  31OCT2010                      0
123-454                        PHOTO5x7         CARDS                            30                  23MAR2011                     44
123-333                        PHOTO8x10        CARDS                            50                  12OCT2011                    74(44+30)
123-897                        IPHONE6            PAPERGIFT                       40                 6DEC2012                        0
 
I want the result table to have a column of revenue generated 1 YEAR ago for all the orders before that particular product was purchased. This should be based on product table.
For example the 1st record has that field as 0 since no product was ordered before that. The 2nd record has the revenue of 1st record, since 1st record was purchased in the timespan of 1 year before the 2nd record. Similarly the 3rd records contains the revenue (summation of 1st and 2nd record) as they fall in the timespan of 1 year before the 3rd record.
But the 4th record has 0 in it because no PAPERGIFT product was ordered before the 4th record. The 1st,2nd and 3rd records have PRODUCTTABLE as CARDS, so the revenue was summed.
Hence i want this to be based on the product table and the time span.
I have written the following query but it does not produce the correct output :

select orderserialno, prodsku, netrevenue, orderplacedtime, 
sum(netrevenue) OVER (PARTITION BY producttable, orderplacedtime - interval '365' day ROWS UNBOUNDED PRECEDING) AS REVENUE_BEFORE1YEAR
from scratch.net_revenue where userid ='001042874727'

Any corrections in the above query to get the correct output will be appreciated. 
Thank you.

 
             

manib0907 61 posts Joined 04/15
09 Jul 2015

Sum(netrevenue)over(partition by producttable order by orderplacedtime rows unbounded preceding)
try this

Cheers,
Mani

You must sign in to leave a comment.