Showing All Dates if There Is No Data

In this article, we will explore how to modify a SQL query to show all dates in the date range if there is no data for that specific date. This can be achieved by modifying the WHERE clause of the query.

Understanding the Query

The provided SQL query retrieves data from two tables: trans_lhpdthp and ms_partcategory. The query filters the data based on a date range and groups the results by PartID and IdMesin.

SELECT 
    a.Tanggal,
    a.IdMesin,
    a.PartID,
    b.PartName,
    SUM(a.QtyPlanning) AS Plan,
    SUM(IF (a.HasilProduksi <> 'Good', a.QtyProduksi, 0)) AS NG,
    SUM(IF (a.HasilProduksi = 'Good', a.QtyProduksi, 0)) AS OK
FROM 
    trans_lhpdthp a
INNER JOIN ms_partcategory b on b.PartID = a.PartID
WHERE a.Tanggal BETWEEN '2018-01-09' AND '2018-01-12'
GROUP BY a.PartID, a.IdMesin
ORDER BY b.PartName;

The query filters the data based on the date range specified in the WHERE clause. The results are grouped by PartID and IdMesin, and the SUM function is used to calculate the total quantity for each group.

Modifying the Query

To show all dates in the date range if there is no data for that specific date, we can modify the query as follows:

SELECT 
    a.Tanggal,
    a.IdMesin,
    a.PartID,
    b.PartName,
    SUM(a.QtyPlanning) AS Plan,
    SUM(IF (a.HasilProduksi <> 'Good', a.QtyProduksi, 0)) AS NG,
    SUM(IF (a.HasilProduksi = 'Good', a.QtyProduksi, 0)) AS OK
FROM 
    trans_lhpdthp a
INNER JOIN ms_partcategory b on b.PartID = a.PartID
WHERE 
    a.Tanggal BETWEEN '2018-01-09' AND '2018-01-12'
    OR (
        a.Tanggal NOT IN (
            SELECT Tanggal FROM trans_lhpdthp WHERE Tanggal BETWEEN '2018-01-09' AND '2018-01-12'
        )
    )
GROUP BY a.PartID, a.IdMesin
ORDER BY b.PartName;

In this modified query, we have added an additional condition to the WHERE clause. The OR operator is used to combine two conditions:

• The first condition checks if the date falls within the specified range using the BETWEEN operator.
• The second condition checks if the date does not exist in the trans_lhpdthp table for the specified date range.

The subquery inside the OR operator selects all dates that exist in the trans_lhpdthp table for the specified date range. If a date does not exist, it is included in the result set.

Using Natural Join

Another approach to achieve this result is by using natural join instead of INNER JOIN.

SELECT 
    a.Tanggal,
    a.IdMesin,
    a.PartID,
    b.PartName,
    SUM(a.QtyPlanning) AS Plan,
    SUM(IF (a.HasilProduksi <> 'Good', a.QtyProduksi, 0)) AS NG,
    SUM(IF (a.HasilProduksi = 'Good', a.QtyProduksi, 0)) AS OK
FROM 
    trans_lhpdthp a
NATURAL JOIN ms_partcategory b
WHERE 
    a.Tanggal BETWEEN '2018-01-09' AND '2018-01-12'
GROUP BY a.PartID, a.IdMesin
ORDER BY b.PartName;

In this query, the NATURAL JOIN is used instead of INNER JOIN. The NATURAL JOIN combines two tables based on their common columns without specifying any join conditions.

Using Cross Join

Another approach to achieve this result is by using cross join instead of INNER JOIN or natural join.

SELECT 
    a.Tanggal,
    a.IdMesin,
    a.PartID,
    b.PartName,
    SUM(a.QtyPlanning) AS Plan,
    SUM(IF (a.HasilProduksi <> 'Good', a.QtyProduksi, 0)) AS NG,
    SUM(IF (a.HasilProduksi = 'Good', a.QtyProduksi, 0)) AS OK
FROM 
    trans_lhpdthp a
CROSS JOIN ms_partcategory b
WHERE 
    a.Tanggal BETWEEN '2018-01-09' AND '2018-01-12'
GROUP BY a.PartID, a.IdMesin
ORDER BY b.PartName;

In this query, the CROSS JOIN is used instead of INNER JOIN or NATURAL JOIN. The CROSS JOIN combines two tables based on their rows without joining them on any common columns.

Conclusion

In this article, we explored three different approaches to show all dates in a date range if there is no data for that specific date. We modified the query by adding an additional condition to the WHERE clause using OR operator, used natural join instead of INNER JOIN, and used cross join instead of INNER JOIN or NATURAL JOIN.

Each approach has its own advantages and disadvantages, and the choice of which one to use depends on the specific requirements of the problem.

Last modified on 2023-11-03