What is the most straightforward way to pad empty dates in sql results (on either mysql or perl end)?

I have a sales table with columns:

CREATE TABLE SALES (
    SALESID INT NOT NULL AUTO_INCREMENT,
    NAME VARCHAR(255) NOT NULL,
    QUANTITY INT,
    SALESDATE DATE NOT NULL DEFAULT (CURRENT_DATE),
    PRIMARY KEY (SALESID)
);

In this table we entry data for those dates when there are sales records. The dates for which there is no sales no data will be there. Sample entry data:

INSERT INTO SALES (NAME, QUANTITY, SALESDATE) VALUES ('XEPRAMYCIN',3, '2024-10-23');
INSERT INTO SALES (NAME, QUANTITY, SALESDATE) VALUES ('FEVOUT',2, '2024-10-23');
                                   
INSERT INTO SALES (NAME, QUANTITY, SALESDATE) VALUES ('XEPRAMYCIN',7, '2024-10-21');
INSERT INTO SALES (NAME, QUANTITY, SALESDATE) VALUES ('FEVOUT',5, '2024-10-21');
                                   
INSERT INTO SALES (NAME, QUANTITY, SALESDATE) VALUES ('XEPRAMYCIN',4, '2024-10-20');
INSERT INTO SALES (NAME, QUANTITY, SALESDATE) VALUES ('FEVOUT',4, '2024-10-20');

Now if a user wants to check the sales data for the medicine ‘XEPRAMYCIN‘ from ‘2024-10-20‘ to ‘2024-10-23‘, there will be no rows of data for the date 2024-10-22.

But I need to show all the dates that occurs between the selected date by the user and if there are no records in those dates it should show 0. How can I do that?

SELECT `NAME`, `QUANTITY`, `SALESDATE` FROM `SALES` WHERE DATE(`SALESDATE`) BETWEEN '2024-10-20' AND '2024-10-23';

Desired output::

NAME            QUANTITY    SALESDATE
XEPRAMYCIN         4        2024-10-20
XEPRAMYCIN         7        2024-10-21
XEPRAMYCIN         0        2024-10-22
XEPRAMYCIN         3        2024-10-23

How to solve this problem? I am using MYSQL DB. Thank You!!!

You need to sign in to view this answers

Related Post