Return to Question

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, ID, 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

Date, Total IDs Last Day

9/8/16,3

9/9/16,8

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

Date, Total IDs Last Day

9/8/16,2

9/9/16,4

9/10/16,6

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,   ID, 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          

Date, Total IDs Last Day
9/8/16,3
9/9/16,8

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

Date, Total IDs Last Day
9/8/16,2
9/9/16,4
9/10/16,6

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, but then reappear on Day T+4, and disappear again on Day T+6.

So what I am trying to know is the following:

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

Then AS OF TODAY (latest date in the database) 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.

Date, StockNo, blah, blahblah, blahblahblah

Date, Count of StockNo Last Day

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.

Date, ID, blah, blahblah, blahblahblah

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

The query I want to run is for every Date, count all the IDs but only if this is the last date the ID shows up.

So if the ID 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 ID 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.

Date, ID, blah, blahblah, blahblahblah

Date, Total IDs Last Day

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 ID(number 6) disappeared but then re-appeared.

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 (bad data, returns, etc...) on Day T+1, but then reappear on Day T+4, and disappear again on Day T+6.

So what I am trying to know is the following:

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

Then AS OF TODAY (latest date in the database) 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, Total IDs 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.

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, but then reappear on Day T+4, and disappear again on Day T+6.

So what I am trying to know is the following:

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

Then AS OF TODAY (latest date in the database) 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, Total IDs 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.