SQLTeam.com | Weblogs | Forums

Get previous 5 days of revenue for each group


#1

Here is my table

CREATE TABLE financials ( 
id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY, 
CountryID VARCHAR(30) NOT NULL, 
ProductID VARCHAR(30) NOT NULL,  
Revenue VARCHAR(50), 
cost VARCHAR(50), 
reg_date TIMESTAMP 
); 

CREATE TABLE financials_sql ( 
id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY, 
CountryID VARCHAR(30) NOT NULL, 
ProductID VARCHAR(30) NOT NULL,  
Revenue VARCHAR(50), 
cost VARCHAR(50), 
reg_date TIMESTAMP 
); 

INSERT INTO `financials` (`id`, `CountryID`, `ProductID`, `Revenue`, `cost`, `reg_date`) VALUES ('1', 'Canada', 'Doe', '20', '5', '2010-01-31 12:01:01'); 
INSERT INTO `financials` (`id`, `CountryID`, `ProductID`, `Revenue`, `cost`, `reg_date`) VALUES ('2', 'USA', 'Tyson', '40', '15', '2010-02-14 12:01:01'); 
INSERT INTO `financials` (`id`, `CountryID`, `ProductID`, `Revenue`, `cost`, `reg_date`) VALUES ('3', 'France', 'Keaton', '80', '25', '2010-03-25 12:01:01'); 
INSERT INTO `financials` (`id`, `CountryID`, `ProductID`, `Revenue`, `cost`, `reg_date`) VALUES ('4', 'France', 'Keaton', '180', '45', '2010-04-24 12:01:01'); 
INSERT INTO `financials` (`id`, `CountryID`, `ProductID`, `Revenue`, `cost`, `reg_date`) VALUES ('5', 'France', 'Keaton', '30', '6', '2010-04-25 12:01:01'); 
INSERT INTO `financials` (`id`, `CountryID`, `ProductID`, `Revenue`, `cost`, `reg_date`) VALUES ('6', 'France', 'Emma', '15', '2', '2010-01-24 12:01:01'); 
INSERT INTO `financials` (`id`, `CountryID`, `ProductID`, `Revenue`, `cost`, `reg_date`) VALUES ('7', 'France', 'Emma', '60', '36', '2010-01-25 12:01:01'); 
INSERT INTO `financials` (`id`, `CountryID`, `ProductID`, `Revenue`, `cost`, `reg_date`) VALUES ('8', 'France', 'Lammy', '130', '26', '2010-04-25 12:01:01'); 
INSERT INTO `financials` (`id`, `CountryID`, `ProductID`, `Revenue`, `cost`, `reg_date`) VALUES ('9', 'France', 'Louis', '350', '12', '2010-04-25 12:01:01'); 
INSERT INTO `financials` (`id`, `CountryID`, `ProductID`, `Revenue`, `cost`, `reg_date`) VALUES ('10', 'France', 'Dennis', '100', '200', '2010-04-25 12:01:01'); 
INSERT INTO `financials` (`id`, `CountryID`, `ProductID`, `Revenue`, `cost`, `reg_date`) VALUES ('11', 'USA', 'Zooey', '70', '16', '2010-04-25 12:01:01'); 
INSERT INTO `financials` (`id`, `CountryID`, `ProductID`, `Revenue`, `cost`, `reg_date`) VALUES ('12', 'France', 'Alex', '2', '16', '2010-04-25 12:01:01'); 

For each product and date combination, I need to get the revenue for previous 5 days. For instance, for Product ‘Keaton’, it would return sum of revenue as 210 while for "Emma", it would return 75

SELECT ProductID, sum(revenue), reg_date
  FROM financials f
Where reg_date in (
    SELECT reg_date
    FROM financials as t2
    WHERE t2.ProductID = f.productID
    ORDER BY reg_date
    LIMIT 5)

Unfortunately, i can't find a website that supports 'LIMIT & IN/ALL/ANY/SOME subquery'. Would my query work or not?


#2

Limit is a mysql thing. On mssql we use top


#3

or OFFSET/FETCH


#4

The code you provides looks like MySQL - so this may not (probably won't) work - but for SQL Server here are 2 solutions:

Declare @financials Table (   
        id int Identity(1,1) Primary Key
      , CountryID varchar(30) Not Null
      , ProductID varchar(30) Not Null
      , Revenue numeric(5,2)
      , cost numeric(5,2)
      , reg_date datetime
        ); 

 Insert Into @financials (
        CountryID
      , ProductID
      , Revenue
      , cost
      , reg_date
        )
 Values ('Canada', 'Doe', '20', '5', '2010-01-31 12:01:01')
      , ('USA', 'Tyson', '40', '15', '2010-02-14 12:01:01')
      , ('France', 'Keaton', '80', '25', '2010-03-25 12:01:01') 
      , ('France', 'Keaton', '180', '45', '2010-04-24 12:01:01') 
      , ('France', 'Keaton', '30', '6', '2010-04-25 12:01:01')
      , ('France', 'Emma', '15', '2', '2010-01-24 12:01:01')
      , ('France', 'Emma', '60', '36', '2010-01-25 12:01:01') 
      , ('France', 'Lammy', '130', '26', '2010-04-25 12:01:01') 
      , ('France', 'Louis', '350', '12', '2010-04-25 12:01:01') 
      , ('France', 'Dennis', '100', '200', '2010-04-25 12:01:01') 
      , ('USA', 'Zooey', '70', '16', '2010-04-25 12:01:01')
      , ('France', 'Alex', '2', '16', '2010-04-25 12:01:01');

The above creates the sample table...

The following is the first solution - using a windowing function...but it has a problem based on the totals you stated.

 Select *
      , Total5Preceding = sum(Revenue) over(Partition By ProductID 
                                                Order By reg_date 
                                                    Rows Between 5 Preceding And Current Row) 
   From @financials
  Order By
        ProductID
      , reg_date;

This solution provides the totals you are looking for - based on the idea that what you really want are the 5 previous contiguous rows (that is no gaps in dates):

   With dates (t_date)
     As (
 Select dateadd(day, t.Number, (Select min(reg_date) From @financials))
   From dbo.Tally t
  Where t.Number <= datediff(day, (Select min(reg_date) From @financials), (Select max(reg_date) From @financials))
        )
 Select *
   From @financials f
  Cross Apply (Select sum(t.Revenue) As TotalRevenue
                 From dates             d
                 Left Join @financials  t On t.reg_date = d.t_date
                                         And t.ProductID = f.ProductID
                Where d.t_date Between dateadd(day, -5, f.reg_date) And f.reg_date
              ) As f1 
  Order By
        f.ProductID
      , f.reg_date;

I modified your table - changing the timestamp to a date and defining the numeric columns so we can actually sum them up.

As I stated before - not sure this will work with MySQL as they seem to have issues supporting common table expressions, cross apply and the windowing functions.