CODEWITHSUNDEEP
sql
Michael W
Michael W

Reputation: 67

SQL Query for a Total Amount between Date Range

Problem: I want to return the Total Amount of Invoices created in the 1st 15 days from when the generated their 1st Invoice

Invoice Table:

ClientID  InvoiceDate  InvoiceAmt
--------  -----------  ----------
150       1/15/2012    150
150       1/25/2012    1200
150       2/10/2012    800
250       1/12/2012    950
250       1/26/2012    1050
250       2/05/2012    1250
400       1/10/2012    500
400       1/26/2012    700


So the 1st Invoice created for each is the following:

150 = 1/15/2012
250 = 1/12/2012
400 = 1/10/2012


Now I want to calculate 15 Days out from the Initial Invoice Date. So the calculation should return:

150 = 1/30/2012
250 = 1/27/2012
400 = 1/25/2012


Now I would like the query to Sum the InvoiceAmt for the appropriate date range.

150 = 1350
250 = 2000
400 = 500


Any ideas ?

Upvotes: 0

Views: 2384

Answers (1)

Taryn
Taryn

Reputation: 247860

You can use the following (See SQL Fiddle with Demo):

select i1.clientid
    , sum(invoiceamt) as Total
from invoice i1
inner join
(
    select clientid, min(invoicedate) mindate
    from invoice i
    group by clientid 
) i2
    on i1.clientid = i2.clientid
where i1.invoicedate between i2.mindate and dateadd(d, 15, mindate)
group by i1.clientid

Upvotes: 2

Related Questions