Multiple Monthly Statements within Monthly billing cycle

Question

Each department is supposed to have only one statement per monthly billing cycle.

 My goal is to figure out how many departments have been hit with more than one billing statement within the same billing cycle.

CONTEXT:

My current query is set to check only departments that are supposed to be billed monthly.

Department table has a one-to-many relationship with Statement table (a department is expected to have one statement at the end of each billing cycle)

Each billing statement has its own closed_date

a.payment_period=1 represents departments that are billed monthly

I also tried searching between specific expire dates on a per account basis.

  The problem with my query is that it outputs the total number of billing closed_dates, instead of only checking for multiple occurrences within a billing cycle.  

How can this query be modified to only output departments with more than one instance of a monthly bill occurring within each department’s billing cycle?

QUERY:

    SELECT s.department, s.statement, COUNT(s.closed_date) AS sd, from_unixtime(s.closed_date)
    FROM statement s
    INNER JOIN department d
    ON s.department=d.department
    WHERE from_unixtime(s.closed_date) BETWEEN 
    DATE_SUB(from_unixtime(d.exp_date), INTERVAL 1 MONTH) AND 
    from_unixtime(d.exp_date)  
    AND d.period=1
    GROUP BY s.statement DESC
    HAVING sd>1

SAMPLE OUTPUT:

department  statement        sd     closed_date
1719712        9351464        3     2018-09-24 
1719709        9351463        2     2018-09-24 
1719708        9351462        2     2018-09-24 
1719665        9351457        3     2018-09-24

Please provide sample input data as well. – Madhur Bhaiya Nov 07 '18 at 20:10 — Madhur Bhaiya, Nov 07 '18 at 20:10

O. Jones · Answer 1 · 2018-11-07T22:10:56.487

It looks like you are trying to group by months.

In MySQL it's a job for the LAST_DAY() function. The pattern you need for that is:

 SELECT a, b, LAST_DAY(datestamp) month_ending, SUM(c) c, MAX(d) maxd
   FROM table
  GROUP BY a, b, LAST_DAY(datestamp)

So, for your situation, you first want to find the situations with more than one invoice in any given month.

I have to guess because I don't understand the meaning of exp_date. This should do what you want.

      SELECT s.department, LAST_DAY(FROM_UNIXTIME(s.closed_date)) month_ending,
             COUNT (*) statement_count
        FROM statement s
        JOIN department d ON s.department = d.department
       WHERE d.period = 1
         AND s.closed_date >=  (FROM_UNIXTIME(d.exp)) - INTERVAL 1 MONTH
         AND s.closed_date <   (FROM_UNIXTIME(d.exp)) 
       GROUP BY s.department, LAST_DAY(FROM_UNIXTIME(s.closed_date))
       HAVING COUNT(*) > 1

This gives a result set with one row per department per month, filtered so it only shows departments with multiple invoices.

Multiple Monthly Statements within Monthly billing cycle

1 Answers1