Unique Values for each month that is different from previous months

I have table called DataFile. This keeps all my data records that get stored in a daily bases.

Data in this table looks like the below:

Id ConfigAccountId Shelf FileIdentifier Created 5356341 23 BSAS020006 C200094 28/01/2013 5356342 23 BSAS020006 C200095 28/01/2013 5356343 23 BSAS020006 C200096 28/01/2013 5356344 23 BSAS020006 C200097 28/01/2013 5356345 23 BSAS020006 C200098 28/01/2013 5356346 23 BSAS020006 C200099 28/01/2013 5356347 23 BSAS020006 C200100 28/01/2013 5356348 23 BSAS020006 C200101 28/01/2013 5356349 23 BSAS020006 C200102 28/02/2013 5356350 23 BSAS020006 C200103 28/02/2013 5356351 23 BSAS020006 C200104 28/02/2013 5356352 23 BSAS020006 C200105 28/02/2013 5356353 23 BSAS020006 C200106 28/02/2013 5356354 23 BSAS020006 C200107 28/02/2013 5356355 23 BSAS020007 C200108 28/02/2013 5356356 23 BSAS020007 C200109 28/02/2013

If you look at the data, the shelf column will change number only when it is full, however I need to know how many unique shelf codes I have for the month. The problem is that shelf BSAS020006 runs over two month period so if I run a distinct for February it will count shelf BSAS020006 again(I hope I'm making sense). I need a unique Shelf count every month. So if a shelf number has already been reported on in Jan, and it runs over to Feb it must only show the count for Jan.

If it is counted in Jan, then count of feb must include only the ones that were not present in jan. And similarly when we count for march, the shelf should be unique to march and if they were included in jan or feb , they should not be counted.

Is there a solution in Python?

https://stackoverflow.com/questions/67326935/unique-values-for-each-month-that-is-different-from-previous-months April 30, 2021 at 09:07AM