MySQL, Count LIKE statments and oldest date problems

Question

Fairly new to MySQL and I'm getting a little lost in how to build the following query.

The table below shows what I want my intended output to be like.

+---------+------------+-----------------+-------+----------+------------+
| Account |  Inv Date  |   Description   | Value | Inv Item |  Eff From  |
+---------+------------+-----------------+-------+----------+------------+
|      12 | 2018-08-14 | Unlimited (SV4) | 5.99  |  3056746 | 2018-08-02 |
|      12 | 2018-08-14 | Unlimited (SV4) | 5.99  |  3056748 | 2018-08-02 |
|      15 | 2018-09-14 | Unlimited (GB2) | 7.99  |  3187748 | 2018-09-07 |
|      15 | 2018-09-14 | Unlimited (GB1) | 7.26  |  3187749 | 2018-09-07 |
+---------+------------+-----------------+-------+----------+------------+

I want to see accounts that have more than one description like "Unlimited", the "Inv Date" needs to show only the oldest date for each account. And the "Eff from" needs to be from when the "status" was first "active" on the account.

Here is my code as it currently stands, i've also included the tables i'm trying to join.

SELECT
    AA.Account,
    MIN(II.InvDate),
    ST.Description,
    ST.Value,
    ST.InvItem,
    MIN(AA.EffFrom)
FROM db.tblAccountStatus AA
    INNER JOIN db.tblInvoiceID II ON II.Account = AA.Account
    INNER JOIN db.tblInvoiceStatus ST ON ST.InvID = II.InvID
WHERE AA.Status = 'Active'
    AND ST.Description LIKE '%Unlimited%'
        GROUP BY ST.InvItem HAVING COUNT(II.InvDate) >1

.

db.tblInvoiceStatus
+--------+------------------+-------+----------+
| Inv ID |   Description    | Value | Inv Item |
+--------+------------------+-------+----------+
|   1030 | Unlimited (SV4)  | 5.99  |  3056746 |
|   1030 | Unlimited (SV4)  | 5.99  |  3056748 |
|   1030 | Extras (PB1)     | 0.99  |  3056751 |
|   1045 | Unlimited (SLV2) | 5.99  |  3166769 |
|   1045 | Extras (PA1)     | 1.99  |  3166770 |
|   1078 | Unlimited (GB2)  | 7.99  |  3187748 |
|   1078 | Unlimited (GB1)  | 7.26  |  3187749 |
|   1091 | Unlimited (SV1)  | 5.99  |  3186788 |
|   1098 | Unlimited (BA2)  | 5.49  |  3209899 |
+--------+------------------+-------+----------+

.

db.tblInvoiceID
+--------+---------+------------+
| Inv ID | Account |  Inv Date  |
+--------+---------+------------+
|   1030 |      12 | 2018-08-14 |
|   1045 |      12 | 2018-09-14 |
|   1078 |      15 | 2018-09-14 |
|   1091 |      17 | 2018-09-15 |
|   1098 |      17 | 2018-10-15 |
|   1099 |      19 | 2018-10-20 |
+--------+---------+------------+

.

db.tblAccountStatus
+---------+--------+------------+------------+
| Account | Status |  Eff From  |   Eff To   |
+---------+--------+------------+------------+
|      12 | Active | 2018-08-02 | 2018-09-16 |
|      12 | Active | 2018-09-17 | 2018-09-28 |
|      12 | Active | 2018-09-29 | NULL       |
|      15 | Active | 2018-09-07 | 2018-09-16 |
|      15 | Closed | 2018-09-17 | NULL       |
|      17 | Active | 2016-04-28 | NULL       |
|      19 | Active | 2015-05-05 | NULL       |
+---------+--------+------------+------------+

user_9984576 · Answer

It seems good to inner join db.tblInvoiceID that has oldest date records as below sql.

SELECT
    AA.Account,
    II.InvDate,
    ST.Description,
    ST.Value,
    ST.InvItem,
    AA.EffFrom
FROM db.tblAccountStatus AA
    INNER JOIN (
        SELECT InvID, Account, MIN(InvDate) AS InvDate FROM db.tblInvoiceID
        GROUP BY Account
    ) II ON II.Account = AA.Account
    INNER JOIN db.tblInvoiceStatus ST ON ST.InvID = II.InvID
WHERE AA.Status = 'Active'
    AND ST.Description LIKE '%Unlimited%'
        GROUP BY ST.InvItem

The following is my output.
Although your intended output doesn't include Account = 17, I couldn't understand a condition from your question. Please teach me if you have additional conditions.

+---------+------------+------------------+-------+---------+------------+
| Account | InvDate    | Description      | Value | InvItem | EffFrom    |
+---------+------------+------------------+-------+---------+------------+
|      12 | 2018-08-14 | Unlimited (SV4)  |  5.99 | 3056746 | 2018-08-02 |
|      12 | 2018-08-14 | Unlimited (SV4)  |  5.99 | 3056748 | 2018-08-02 |
|      17 | 2018-09-15 | Unlimited (SV1)  |  5.99 | 3186788 | 2016-04-28 |
|      15 | 2018-09-14 | Unlimited (GB2)  |  7.99 | 3187748 | 2018-09-07 |
|      15 | 2018-09-14 | Unlimited (GB1)  |  7.26 | 3187749 | 2018-09-07 |
+---------+------------+------------------+-------+---------+------------+
5 rows in set (0.00 sec)

MySQL, Count LIKE statments and oldest date problems

1 Answers