CODEWITHSUNDEEP
sql
user3577606
user3577606

Reputation: 137

get max date when sum of a field equals a value

I have a problem with writing a query.
Row data is as follow :

DATE        CUSTOMER_ID AMOUNT
20170101    1           150
20170201    1           50
20170203    1           200
20170204    1           250
20170101    2           300
20170201    2           70

I want to know when(which date) the sum of amount for each customer_id becomes more than 350, How can I write this query to have such a result ?

CUSTOMER_ID   MAX_DATE
1             20170203
2             20170201

Thanks,

Upvotes: 2

Views: 1224

Answers (5)

Vincent Vancalbergh
Vincent Vancalbergh

Reputation: 3327

SELECT CUSTOMER_ID, MIN(DATE) AS GOALDATE
FROM ( SELECT cd1.*, (SELECT SUM(AMOUNT)
                 FROM CustData cd2
                 WHERE cd2.CUSTOMER_ID = cd1.CUSTOMER_ID
                   AND cd2.DATE <= cd1.DATE) AS RUNNINGTOTAL
  FROM CustData cd1) AS custdata2
WHERE RUNNINGTOTAL >= 350
GROUP BY CUSTOMER_ID

DB Fiddle

Upvotes: -2

Waayd
Waayd

Reputation: 375

I think it is not an easy calculation and you have to calculate something. I know It could be seen a little mixed but i want to calculate step by step. As fist step if we can get success for your scenario, I believe it can be made better about performance. If anybody can make better my query please edit my post;

Unfortunately the solution that i cannot try on computer is below, I guess it will give you expected result;

-- Get the start date of customers
SELECT MIN(DATE) AS DATE
    ,CUSTOMER_ID
INTO #table
FROM TABLE t1

-- Calculate all possible date and where is sum of amount greater than 350
SELECT t1.CUSTOMER_ID
    ,SUM(SELECT Amount FROM TABLE t3 WHERE t3.DATE BETWEEN t1.DATE
            AND t2.DATE) AS total
    ,t2.DATE AS DATE
INTO #tableCalculated
FROM #table t1
INNER JOIN TABLE t2 ON t.ID = t2.ID
    AND t1.DATE != t2.DATE
WHERE total > 350

-- SELECT Min amount and date for per Customer_ID
SELECT CUSTOMER_ID, MIN(DATE) AS DATE
FROM #tableCalculated
GROUP BY ID

Upvotes: 0

2SRTVF
2SRTVF

Reputation: 198

ANSI SQL

Used for the test: TSQL and MS SQL Server 2012

select 
  "CUSTOMER_ID",
  min("DATE")
FROM
(
 select 
  "CUSTOMER_ID",
  "DATE",
  (
  SELECT 
    sum(T02."AMOUNT") AMOUNT
  FROM "TABLE01" T02
  WHERE
     T01."CUSTOMER_ID" = T02."CUSTOMER_ID"
     AND T02."DATE" <= T01."DATE"
  ) "AMOUNT"
 from "TABLE01" T01
) T03
where
  T03."AMOUNT" > 350
group by
  "CUSTOMER_ID"
GO

CUSTOMER_ID | (No column name)   
----------: | :------------------
          1 | 03/02/2017 00:00:00
          2 | 01/02/2017 00:00:00

db<>fiddle here

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269803

Simply use ANSI/ISO standard window functions to calculate the running sum:

select t.*
from (select t.*,
             sum(t.amount) over (partition by t.customer_id order by t.date) as running_amount
      from t
     ) t
where running_amount - amount < 350 and
      running_amount >= 350;

If for some reason, your database doesn't support this functionality, you can use a correlated subquery:

select t.*
from (select t.*,
             (select sum(t2.amount)
              from t t2
              where t2.customer_id =  t.customer_id and
                    t2.date <= t.date
             ) as running_amount
      from t
     ) t
where running_amount - amount < 350 and
      running_amount >= 350;

Upvotes: 2

SirPilan
SirPilan

Reputation: 4847

DB-Fiddle

SELECT
    tmp.`CUSTOMER_ID`,
    MIN(tmp.`DATE`) as MAX_DATE
FROM
    (
        SELECT
            `DATE`,
            `CUSTOMER_ID`,
            `AMOUNT`,
            (
                SELECT SUM(`AMOUNT`) FROM tbl t2 WHERE t2.`DATE` <= t1.`DATE` AND `CUSTOMER_ID` = t1.`CUSTOMER_ID`
            ) AS SUM_UP
        FROM
            `tbl` t1
        ORDER BY
            `DATE` ASC
   ) tmp
WHERE
    tmp.`SUM_UP` > 350
GROUP BY
    tmp.`CUSTOMER_ID`

Explaination:

First I select all rows and subselect all rows with SUM and ID where the current row DATE is smaller or same as all rows for the customer. From this tabe i select the MIN date, which has a current sum of >350

Upvotes: 0

Related Questions