Mysql – Query – Want the count of ID based on the LAST DATE it appears

MySQL

This will be in MySQL. Table consists of following fields:

Date, StockNo, blah, blahblah, blahblahblah

I believe the primary key needs to be Date & StockNo.

This is Inventory Data for widgets.
The assumption I am making is that when the StockNo no longer appears in the data, it was sold as of the date it last appeared.

The thing is this…sometimes a StockNo will disappear on Day T+1 (and thus we'd assume it was sold), but then reappear on Day T+4 (due to data error, or a return, etc…), and disappear again on Day T+6 and NEVER reappear. So Day T+6 is when we'd consider it SOLD.

So what I am trying to know is the following:

1) How many unique StockNo disappear on Day T+1 vs. Day T? (for all Days)

2) Then AS OF THIS MOMENT how many unique StockNo had their LAST day in the database on Day T?

So the output would show those two counts for all Dates in the database.

In addition to the dummy data below, I have sample data in CSV (40,000 entries per day) which i can send.

Thank you!!!

Additional info:

So if the StockNo shows up in the database on 9/9/16 but NOT on 9/10/16 then we would say that it disappeared and therefore add it to the total count for date 9/9/16.

If that same StockNo later reappears on 9/11/16, when we run the query again that ID will no longer be added to the count as of 9/9/16. If on 9/12/16 that ID disappears again then it would be added to the count as of 9/11/16.

here's some sample data. I did not put any data in for the "blahs" since it is not relevant for the query (i believe)

Date, StockNo, blah, blahblah, blahblahblah

9/8/2016, 1
9/8/2016, 2
9/8/2016, 3
9/8/2016, 4
9/8/2016, 5
9/8/2016, 6
9/8/2016, 7
9/8/2016, 8
9/9/2016, 1
9/9/2016, 2
9/9/2016, 3
9/9/2016, 4
9/9/2016, 5
9/9/2016, 9
9/9/2016, 10
9/9/2016, 11
9/10/2016, 2
9/10/2016, 4
9/10/2016, 6
9/10/2016, 11
9/10/2016, 12
9/10/2016, 13

The expected output run on 9/9/16 would be:

Date, Count of StockNo Last Day

9/8/16,3

9/9/16,8

The expected output run on 9/10/16 would be:

Date, Count of ID Last Day

9/8/16,2

9/9/16,4

9/10/16,6

Note that when the query is run on 9/10/16, the total for 9/8/16 is different from when it was run on 9/9/16 because an StockNo(number 6) disappeared but then re-appeared.

Best Answer

Think about an intermediate table:

SELECT StockNo, MAX(`Date`) AS last_date
    FROM my_table GROUP BY StockNo;

Now flip that over:

SELECT last_date, COUNT(*) AS ct
    FROM ( SELECT StockNo, MAX(`Date`) AS last_date
               FROM my_table GROUP BY StockNo ) AS x
    GROUP BY last_date
    ORDER BY last_date;
Related Question