Sum amount within a date range

All replies

• 

  

  0

  Sign in to vote

  Using window functions (available from 2012 on) you can do it like this:

  DECLARE @Table1 TABLE (CustomerID INT, BookingDate DATE)
  INSERT INTO @Table1 (CustomerID, BookingDate)
  VALUES (1, '2014-04-29'), (1, '2014-07-30'), (2, '2014-03-31'), (2, '2014-06-30')
  
  DECLARE @Table2 TABLE (CustomerID INT, SalesDate DATE, Amount INT)
  INSERT INTO @Table2 (CustomerID, SalesDate, Amount)
  VALUES (1, '2014-01-30', 20), (1, '2014-02-25', 30), (1, '2014-05-20', 100), (1, '2014-07-30', 200), (1, '2014-09-30', 80), (2, '2014-03-20', 50)
  
  SELECT t1.customerID, t1.prevBookingDate, t1.prevBookingDate, SUM(COALESCE(t2.amount,0)) AS amount
    FROM (
          SELECT *, COALESCE(DATEADD(DAY,1,LAG(BookingDate) OVER (PARTITION BY t1.CustomerID ORDER BY BookingDate)),'2014-01-01') AS prevBookingDate
            FROM @Table1 t1
  	   ) t1
      LEFT outER JOIN @Table2 t2
  	  ON t1.CustomerID = t2.CustomerID
  	  AND t2.SalesDate BETWEEN prevBookingDate AND BookingDate 
   GROUP BY t1.customerID, t1.prevBookingDate, t1.prevBookingDate
   ORDER BY customerID, t1.prevBookingDate
  

  
  

  • Proposed as answer by Wendy Fu Wednesday, November 5, 2014 11:34 AM
  • Marked as answer by Katherine Xiong Friday, November 28, 2014 10:48 AM

  Tuesday, November 4, 2014 4:49 PM
• 

  

  0

  Sign in to vote

  Please try this code:

  declare @Table1 table 
  (CustomerID int,BookingDate date );
  insert @Table1
  values 
  ( 1, '2014-04-29' ), 
  (1, '2014-07-30' ), 
  (2, '2014-03-31' ), 
  (2, '2014-06-30') ;
  
  declare @Table2 table 
  (CustomerID int, SalesDate date, Amount int);
  insert @Table2
  values 
  (1,'2014-01-30',20) ,
  (1,'2014-02-25',30) ,
  (1,'2014-05-20',100) ,
  (1,'2014-07-30',200) ,
  (1,'2014-09-30',80) ,
  (2,'2014-03-20',50) ;
  
  with cte as 
  (
  select 
  	CustomerID, 
  	BookingDate , 
  	row_number() over (partition by CustomerID order by BookingDate ) as rn
  from @Table1 )
  , cte2 as 
  (
  select 
  	T2.CustomerID , 
  	isnull(T1.BookingDate, '2014-01-01') as FromDate, 
  	T2.BookingDate as ToDate 
  from cte as T1
  right join cte as T2 
  on T1.rn = T2.rn - 1 
  and T1.CustomerID = T2.CustomerID
  )
  select 
  	b.CustomerID , 
  	b.ToDate as BookingDate, 
  	isnull(sum(a.Amount), 0) as [Sum(Amount From Table2)]
  from @Table2 as a
  right join cte2 as b 
  on a.CustomerID = b.CustomerID 
  and a.SalesDate > b.FromDate 
  and a.SalesDate <= b.ToDate
  group by 
  	b.CustomerID , 
  	b.ToDate;
  

  
  

  ---

  T-SQL Articles
  T-SQL e-book by TechNet Wiki Community
  T-SQL blog

  • Proposed as answer by Wendy Fu Wednesday, November 5, 2014 11:34 AM
  • Marked as answer by Katherine Xiong Friday, November 28, 2014 10:49 AM

  Tuesday, November 4, 2014 5:29 PM
• 

  

  0

  Sign in to vote

  ;With CTE 
  AS
  (
  SELECT ROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY BookingDate) AS Seq,
  CustomerID,	BookingDate
  FROM Table1
  )
  
  SELECT c1.CustomerID, c1.BookingDate, COALESCE(t.Total,0) AS SumTable2
  FROM CTE c1
  LEFT JOIN CTE c2
  ON c2.CustomerID = c1.CustomerID
  AND c2.Seq = c1.Seq - 1
  OUTER APPLY (SELECT SUM(Amount) AS Total
  FROM Table2
  WHERE CustomerID = c1.CustomerID
  AND SalesDate BETWEEN COALESCE(c2.BookingDate,0) AND c1.BookingDate
  )t
  

  
  

  ---

  Please Mark This As Answer if it solved your issue
  Please Mark This As Helpful if it helps to solve your issue
  Visakh
  ----------------------------
  My MSDN Page
  My Personal Blog
  My Facebook Page

  Tuesday, November 4, 2014 6:23 PM