1

I've seen some posts here regarding the SUM OVER function to calculate the cumulative sum. But the issue which I have is slightly different. I need to keep a running total of distinct inactive clients by year-month. So far I've created the query to give the result as:

date       InactiveClients  TotalInactiveClients
2016-04    91954             91954
2016-03    90065             182019 <-- sums 2016-04 and 2016-03
2016-02    84517             266536 <-- but in past 3 months i had around 80k of inactivity
2016-01    101561            368097
2015-12    113953            482050
2015-11    85425             567475
2015-10    95681             663156
2015-09    80503             743659
2015-08    82327             825986
2015-07    96672             922658
2015-06    84375             1007033
2015-05    83740             1090773
2015-04    96933             1187706
2015-03    68572             1256278

The query is which gives me the result is here:

SELECT
      Date_FORMAT(pq.date, "%Y-%m") date,
      pq.InactiveClients as 'InactiveClients',
      @runBal := @runBal + pq.InactiveClients as TotalInactiveClients
  FROM ( SELECT 
           distinct ss1.time date, 
           COUNT(DISTINCT c1.id) as InactiveClients
         FROM `contacts` c1, `statengineSent` ss1
           LEFT JOIN `statengineViews` AS sv1 
             ON (sv1.email = ss1.email 
             AND sv1.dispatchID = ss1.dispatchID 
             AND sv1.email is NULL)
         WHERE ss1.email = c1.email 
           AND ss1.time > DATE_SUB(now(), interval 13 MONTH)
         GROUP BY YEAR(date), MONTH(date)
         ORDER BY date desc ) pq,
      ( select @runBal := 0 ) sqlvars

PS: in case you are wondering with the following LEFT JOIN I am excluding all the active contacts.

LEFT JOIN `statengineViews` AS sv1 
  ON (sv1.email = ss1.email 
  AND sv1.dispatchID = ss1.dispatchID 
  AND sv1.email is NULL)
WHERE ss1.email = c1.email 
  AND ss1.time > DATE_SUB(now(), interval 13 MONTH)

I need help with the following: Could anyone help me to make the result of TotalInactiveClients equal the sum of previous months unique contact IDs? (For example 2015-03 TotalInactiveClients = uniqe count of contactIDs inbetween 2015-03 upto 2016-04. So for 2015-10 - i need all the contactID's which occurred from 2016-04 to 2015-10 (including 2015-10)).

  • Please edit your question to show what you would like the output to be. – Michael Green Apr 19 '16 at 10:23
0

OK since no-one seemed to reply (except for Michael Green - Thanks) i did the MySQL statement the long way. If anyone has an idea how to make it smaller or how to combine them, then please let me know:

Last months inactivity:

Select b.date , count(b.contactID) FROM
(SELECT distinct Date_FORMAT(ss1.time, "%Y-%m") as date, c1.id as ContactID
           FROM `contacts` c1, `statengineSent` ss1
           LEFT JOIN `statengineViews` AS sv1 
           ON (sv1.email = ss1.email  AND sv1.dispatchID = ss1.dispatchID AND sv1.email is NULL)
           WHERE ss1.email = c1.email 
           AND YEAR(ss1.time) = YEAR(CURRENT_DATE - INTERVAL 0 MONTH)
                   AND MONTH(ss1.time) = MONTH(CURRENT_DATE - INTERVAL 0 MONTH)
                   ORDER BY date desc)a,
(SELECT distinct Date_FORMAT(ss1.time, "%Y-%m") as date, c1.id as ContactID
           FROM `contacts` c1, `statengineSent` ss1
           LEFT JOIN `statengineViews` AS sv1 
           ON (sv1.email = ss1.email  AND sv1.dispatchID = ss1.dispatchID AND sv1.email is NULL)
           WHERE ss1.email = c1.email 
           AND YEAR(ss1.time) = YEAR(CURRENT_DATE - INTERVAL 1 MONTH)
                   AND MONTH(ss1.time) = MONTH(CURRENT_DATE - INTERVAL 1 MONTH)
                   ORDER BY date desc)b
WHERE a.ContactID = b.ContactID

The result was:

date    inactivity
2016-03 83108

2 Months ago inactivity:

Select c.date , count(c.contactID) FROM
(SELECT distinct Date_FORMAT(ss1.time, "%Y-%m") as date, c1.id as ContactID
           FROM `contacts` c1, `statengineSent` ss1
           LEFT JOIN `statengineViews` AS sv1 
           ON (sv1.email = ss1.email  AND sv1.dispatchID = ss1.dispatchID AND sv1.email is NULL)
           WHERE ss1.email = c1.email 
           AND YEAR(ss1.time) = YEAR(CURRENT_DATE - INTERVAL 0 MONTH)
                   AND MONTH(ss1.time) = MONTH(CURRENT_DATE - INTERVAL 0 MONTH)
                   ORDER BY date desc)a,
(SELECT distinct Date_FORMAT(ss1.time, "%Y-%m") as date, c1.id as ContactID
           FROM `contacts` c1, `statengineSent` ss1
           LEFT JOIN `statengineViews` AS sv1 
           ON (sv1.email = ss1.email  AND sv1.dispatchID = ss1.dispatchID AND sv1.email is NULL)
           WHERE ss1.email = c1.email 
           AND YEAR(ss1.time) = YEAR(CURRENT_DATE - INTERVAL 1 MONTH)
                   AND MONTH(ss1.time) = MONTH(CURRENT_DATE - INTERVAL 1 MONTH)
                   ORDER BY date desc)b,
(SELECT distinct Date_FORMAT(ss1.time, "%Y-%m") as date, c1.id as ContactID
           FROM `contacts` c1, `statengineSent` ss1
           LEFT JOIN `statengineViews` AS sv1 
           ON (sv1.email = ss1.email  AND sv1.dispatchID = ss1.dispatchID AND sv1.email is NULL)
           WHERE ss1.email = c1.email 
           AND YEAR(ss1.time) = YEAR(CURRENT_DATE - INTERVAL 2 MONTH)
                   AND MONTH(ss1.time) = MONTH(CURRENT_DATE - INTERVAL 2 MONTH)
                   ORDER BY date desc)c
WHERE a.ContactID = b.ContactID AND b.ContactID = c.ContactID

The result was:

date    inactivity
2016-02 82355

AS you can see right now i'm using 2 statements which already are very big and sloppy and also i need to run 12 different queries to get the whole year inactivity. Can anyone help me combine these or make it work as an interval for a whole year? It does the trick but as i said, it's very sloppy :(

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.