# Sum amount within a date range

• ### Question

• I have 2 tables where I Need to sum up sales amount within a booking date range.

Below an example to help illustrate this task.

```Table1:
CustomerID	BookingDate (YYYY-MM-DD)
1			2014-04-29
1			2014-07-30
2			2014-03-31
2			2014-06-30

Table2:
CustomerID	SalesDate (YYYY-MM-DD)	Amount
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

Result:
CustomerID	BookingDate (YYYY-MM-DD)	Sum(Amount From Table2)
1			2014-04-29					50							-- SalesDate between 2014-01-01 and 2014-04-29
1			2014-07-30					300							-- SalesDate between 2014-04-30 and 2014-07-30
2			2014-03-31					50							-- SalesDate between 2014-01-01 and 2014-03-31
2			2014-06-30					0							-- SalesDate between 2014-04-01 and 2014-06-30```

• Edited by Tuesday, November 4, 2014 3:56 PM Code format
Tuesday, November 4, 2014 3:56 PM

• 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 Wednesday, November 5, 2014 11:34 AM
• Marked as answer by Friday, November 28, 2014 10:48 AM
Tuesday, November 4, 2014 4:49 PM

```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;
```

• Proposed as answer by Wednesday, November 5, 2014 11:34 AM
• Marked as answer by Friday, November 28, 2014 10:49 AM
Tuesday, November 4, 2014 5:29 PM

### All replies

• 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 Wednesday, November 5, 2014 11:34 AM
• Marked as answer by Friday, November 28, 2014 10:48 AM
Tuesday, November 4, 2014 4:49 PM

```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;
```

• Proposed as answer by Wednesday, November 5, 2014 11:34 AM
• Marked as answer by Friday, November 28, 2014 10:49 AM
Tuesday, November 4, 2014 5:29 PM
• ```;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
```