MySQL Forums
Forum List  »  General

Re: Problem with a complex SQL query - select total user count, invoice count for each month from specified period
Posted by: Radoslav Yankov
Date: May 31, 2013 11:00AM

CREATE DATABASE TEST_DB;

CREATE TABLE USER
(
ID VARCHAR(80) NOT NULL,
REGISTRATION_DATE DATE NOT NULL,
PRIMARY KEY(ID)
);

CREATE TABLE INVOICES
(
USER_ID VARCHAR(80) NOT NULL,
INVOICE_NUMBER VARCHAR(50) NOT NULL,
CREATION_DATE DATE NOT NULL,
PRIMARY KEY(USER_ID, INVOICE_NUMBER),
FOREIGN KEY(USER_ID) REFERENCES USER(ID)
);

CREATE TABLE REPORTCAL
(
YR SMALLINT UNSIGNED,
MO TINYINT UNSIGNED,
PRIMARY KEY(YR, MO)
);

INSERT INTO USER(ID, REGISTRATION_DATE) VALUES ('A', '2012-01-10');
INSERT INTO USER(ID, REGISTRATION_DATE) VALUES ('B', '2012-01-12');
INSERT INTO USER(ID, REGISTRATION_DATE) VALUES ('C', '2012-01-20');
INSERT INTO USER(ID, REGISTRATION_DATE) VALUES ('D', '2012-01-26');
INSERT INTO USER(ID, REGISTRATION_DATE) VALUES ('E', '2012-02-01');
INSERT INTO USER(ID, REGISTRATION_DATE) VALUES ('F', '2012-03-02');
INSERT INTO USER(ID, REGISTRATION_DATE) VALUES ('G', '2012-03-11');
INSERT INTO USER(ID, REGISTRATION_DATE) VALUES ('H', '2012-03-12');
INSERT INTO USER(ID, REGISTRATION_DATE) VALUES ('I', '2012-03-22');
INSERT INTO USER(ID, REGISTRATION_DATE) VALUES ('J', '2012-03-24');
INSERT INTO USER(ID, REGISTRATION_DATE) VALUES ('K', '2012-09-09');
INSERT INTO USER(ID, REGISTRATION_DATE) VALUES ('L', '2012-09-23');
INSERT INTO USER(ID, REGISTRATION_DATE) VALUES ('M', '2012-12-13');
INSERT INTO USER(ID, REGISTRATION_DATE) VALUES ('N', '2012-12-16');
INSERT INTO USER(ID, REGISTRATION_DATE) VALUES ('O', '2013-04-13');
INSERT INTO USER(ID, REGISTRATION_DATE) VALUES ('P', '2013-04-28');
INSERT INTO USER(ID, REGISTRATION_DATE) VALUES ('Q', '2013-05-28');
INSERT INTO USER(ID, REGISTRATION_DATE) VALUES ('R', '2013-05-29');

INSERT INTO INVOICES(USER_ID, INVOICE_NUMBER, CREATION_DATE) VALUES ('A', '1', '2012-01-10');
INSERT INTO INVOICES(USER_ID, INVOICE_NUMBER, CREATION_DATE) VALUES ('A', '2', '2012-01-20');
INSERT INTO INVOICES(USER_ID, INVOICE_NUMBER, CREATION_DATE) VALUES ('A', '3', '2012-03-12');
INSERT INTO INVOICES(USER_ID, INVOICE_NUMBER, CREATION_DATE) VALUES ('A', '4', '2012-12-16');
INSERT INTO INVOICES(USER_ID, INVOICE_NUMBER, CREATION_DATE) VALUES ('A', '5', '2013-02-18');
INSERT INTO INVOICES(USER_ID, INVOICE_NUMBER, CREATION_DATE) VALUES ('A', '6', '2013-04-08');
INSERT INTO INVOICES(USER_ID, INVOICE_NUMBER, CREATION_DATE) VALUES ('A', '7', '2013-04-08');
INSERT INTO INVOICES(USER_ID, INVOICE_NUMBER, CREATION_DATE) VALUES ('D', '1', '2012-02-08');
INSERT INTO INVOICES(USER_ID, INVOICE_NUMBER, CREATION_DATE) VALUES ('D', '2', '2012-04-28');
INSERT INTO INVOICES(USER_ID, INVOICE_NUMBER, CREATION_DATE) VALUES ('D', '3', '2012-06-25');
INSERT INTO INVOICES(USER_ID, INVOICE_NUMBER, CREATION_DATE) VALUES ('D', '4', '2012-07-09');
INSERT INTO INVOICES(USER_ID, INVOICE_NUMBER, CREATION_DATE) VALUES ('D', '5', '2013-05-28');
INSERT INTO INVOICES(USER_ID, INVOICE_NUMBER, CREATION_DATE) VALUES ('M', '1', '2012-01-21');
INSERT INTO INVOICES(USER_ID, INVOICE_NUMBER, CREATION_DATE) VALUES ('M', '2', '2012-02-23');
INSERT INTO INVOICES(USER_ID, INVOICE_NUMBER, CREATION_DATE) VALUES ('M', '3', '2012-05-26');
INSERT INTO INVOICES(USER_ID, INVOICE_NUMBER, CREATION_DATE) VALUES ('M', '4', '2013-02-23');
INSERT INTO INVOICES(USER_ID, INVOICE_NUMBER, CREATION_DATE) VALUES ('M', '5', '2013-03-13');
INSERT INTO INVOICES(USER_ID, INVOICE_NUMBER, CREATION_DATE) VALUES ('M', '6', '2013-03-23');

INSERT INTO REPORTCAL(YR, MO) VALUES(2012, 1);
INSERT INTO REPORTCAL(YR, MO) VALUES(2012, 2);
INSERT INTO REPORTCAL(YR, MO) VALUES(2012, 3);
INSERT INTO REPORTCAL(YR, MO) VALUES(2012, 4);
INSERT INTO REPORTCAL(YR, MO) VALUES(2012, 5);
INSERT INTO REPORTCAL(YR, MO) VALUES(2012, 6);
INSERT INTO REPORTCAL(YR, MO) VALUES(2012, 7);
INSERT INTO REPORTCAL(YR, MO) VALUES(2012, 8);
INSERT INTO REPORTCAL(YR, MO) VALUES(2012, 9);
INSERT INTO REPORTCAL(YR, MO) VALUES(2012, 10);
INSERT INTO REPORTCAL(YR, MO) VALUES(2012, 11);
INSERT INTO REPORTCAL(YR, MO) VALUES(2012, 12);
INSERT INTO REPORTCAL(YR, MO) VALUES(2013, 1);
INSERT INTO REPORTCAL(YR, MO) VALUES(2013, 2);
INSERT INTO REPORTCAL(YR, MO) VALUES(2013, 3);
INSERT INTO REPORTCAL(YR, MO) VALUES(2013, 4);
INSERT INTO REPORTCAL(YR, MO) VALUES(2013, 5);
INSERT INTO REPORTCAL(YR, MO) VALUES(2013, 6);
_______________________________________________________________________

Period: 01.01.2012-31.05.2013 or Janury 2012-May 2013
Expected results:

Year | Month | Total count of users | Count of users who created at least one invoice in this month | Count of invoices in this month |

2012| Jan | 4 | 2 | 3 |
2012| Feb | 5 | 2 | 2 |
2012| March | 10 | 1 | 1 |
2012| April | 10 | 1 | 1 |
2012| May | 10 | 1 | 1 |
2012| June | 10 | 1 | 1 |
2012| July | 10 | 1 | 1 |
2012|August | 10 | 0 | 0 |
2012| Sept | 12 | 0 | 0 |
2012| Oct | 12 | 0 | 0 |
2012| Nov | 12 | 0 | 0 |
2012| Dec | 14 | 1 | 1 |
2013| Jan | 14 | 0 | 0 |
2013| Feb | 14 | 2 | 2 |
2013| March | 14 | 1 | 2 |
2013| April | 16 | 1 | 2 |
2013| May | 18 | 1 | 1 |

I need information for each month from the specified period, so from January 2012 to May 2013. Total count of user means total count of users up to
(and including) the month, e.g. in January we have 4 users, in February we have one new user, so the total count of users in February is 4 + 1 = 5.

In January user with id A created 2 invoices and user with id M - one invoice, so Count of users who created at least one invoice in this month is 2
(2 different users created invoices) and Count of invoices in this month is 3 (total count of invoices in January).

I hope, you understand the logic and what I exactly want. It's to difficult for me to write such query, so I need help.

Thanks a lot in advance! :-)

Options: ReplyQuote


Subject
Written By
Posted
Re: Problem with a complex SQL query - select total user count, invoice count for each month from specified period
May 31, 2013 11:00AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.