I reviewed the other posts with titles that seem similar to what I am looking to develop, and the solutions either did not match the desired outcome or did not work with the dataset.
I am trying to create a summary table based on an instrument data table. The instrument data table has the following columns: part number of the instrument, serial number, activation status, activation date, and deactivation date if there is one. I have been unable to create this table.
Below is an overly simplified version of the incoming data.
Part Number | Serial Number | Status | Activation Date | Deactivation Date |
PN001 | 10001 | Deactivated | 8/9/2022 | 11/26/2022 |
PN001 | 10002 | Deactivated | 9/25/2022 | 1/10/2023 |
PN001 | 10003 | Active | 11/15/2022 | |
PN002 | 20001 | Deactivated | 9/21/2022 | 12/14/2022 |
PN002 | 20002 | Deactivated | 10/3/2022 | 1/3/2023 |
PN002 | 20003 | Active | 12/9/2022 | |
PN002 | 20004 | Active | 1/16/2023 | |
PN003 | 30001 | Deactivated | 8/12/2022 | 9/20/2022 |
PN003 | 30002 | Deactivated | 9/24/2022 | 11/30/2022 |
PN003 | 30003 | Deactivated | 11/7/2022 | 12/29/2022 |
PN003 | 30004 | Active | 11/14/2022 | |
PN003 | 30005 | Active | 1/28/2023 | |
The goal is to create a table that lists active instruments each month by counting the number of devices by part number indicated as active for that given month.
Month of Activity | Part Number | Count of Active
on the 1st of the month |
2022-09 | PN001 | 1 |
2022-09 | PN002 | 0 |
2022-09 | PN003 | 1 |
2022-10 | PN001 | 2 |
2022-10 | PN002 | 1 |
2022-10 | PN003 | 1 |
2022-11 | PN001 | 1 |
2022-11 | PN002 | 2 |
2022-11 | PN003 | 1 |
2022-12 | PN001 | 3 |
2022-12 | PN002 | 2 |
2022-12 | PN003 | 2 |
2023-01 | PN001 | 2 |
2023-01 | PN002 | 2 |
2023-01 | PN003 | 1 |
2023-02 | PN001 | 1 |
2023-02 | PN002 | 2 |
2023-02 | PN003 | 2 |
Thank you in advance for any guidance and assistance.
