I’m having a hard time with this query, thanks in advance for the help!
Given a set of items with opened and close date I need to calculate the count of opened items for each months (all items not yet closed before the end of the month)
I’m attaching an example below with some dates, the desired outcome would look something like this
+————+
| Month_Year | Open_Stock_Count
+————+
| Sep 2012 | 0
| Oct 2012 | 4
| Nov 2012 | 4
| Dec 2012 | 2
| Jan 2013 | 3
| Feb 2013 | 0
| Mar 2013 | 0
+————+—
I have created a fiddle with this example
https://www.db-fiddle.com/f/kBFviS9ZjMWdXR8aeZ3bDk/1
I’m using
Mysql 5.7.44-log
ONLY_FULL_GROUP_BY disbled
CREATE TABLE TestDate (
Objectid int,
OpenDate date NULL,
CloseDate date NULL
);
SET sql_mode = '';
INSERT INTO TestDate(Objectid, OpenDate, CloseDate)
VALUES(1, '2013-02-18 00:00:00', '2013-02-19 00:00:00');
INSERT INTO TestDate(Objectid, OpenDate, CloseDate)
VALUES(2, '2013-01-22 00:00:00', '2013-01-28 00:00:00');
INSERT INTO TestDate(Objectid, OpenDate, CloseDate)
VALUES(3, '2012-10-17 00:00:00', '2013-02-01 00:00:00');
INSERT INTO TestDate(Objectid, OpenDate, CloseDate)
VALUES(4, '2012-10-28 00:00:00', '2012-12-14 00:00:00');
INSERT INTO TestDate(Objectid, OpenDate, CloseDate)
VALUES(5, '2013-01-16 00:00:00', '2013-02-02 00:00:00');
INSERT INTO TestDate(Objectid, OpenDate, CloseDate)
VALUES(6, '2013-01-20 00:00:00', '2013-01-25 00:00:00');
INSERT INTO TestDate(Objectid, OpenDate, CloseDate)
VALUES(7, '2012-11-25 00:00:00', '2012-12-04 00:00:00');
INSERT INTO TestDate(Objectid, OpenDate, CloseDate)
VALUES(8, '2012-10-20 00:00:00', '2013-02-04 00:00:00');
INSERT INTO TestDate(Objectid, OpenDate, CloseDate)
VALUES(9, '2012-10-31 00:00:00', '2012-11-15 00:00:00');
I have tried many aproaches with different ideas from arround stackoverflow but none of them seem to create the desired outcome
user3099631 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.