Unable to print data for last 6 months in SQL with Subquery-CodePudding

I'm trying to get result for the last 6 months from a set of data, however when I run my query it shows nothing. The question I'm working on is:

Q. Using a subquery, print name, cost and the maximum distance of the transport if the transport has been allocated (use the start date of the allocation here) within the last 6 months calculated from today (Today here implies the date the query is run. Must not hardcode the date)

This is my query:

SELECT TransportCost, TransportName, TransportMaxDist
FROM Service
WHERE FromDate IN
(SELECT FromDate
FROM Service
WHERE MONTH(FromDate) = 6);

When I run this query it shows the correct column names, but there is no data in those columns:

 --------------- --------------- ------------------ 
| TransportCost | TransportName | TransportMaxDist |
 --------------- --------------- ------------------ 
|               |               |                  |
 --------------- --------------- ------------------

Below are the CREATE TABLE and sample data syntaxes:

CREATE TABLE Allocation (
  TruckVINNum CHAR (4),
  TransportID CHAR (2),
  FromDate CHAR (16),
  ToDate CHAR (16),
  TruckAllocation CHAR (8),
  PRIMARY KEY (TruckVINNum, TransportID)
);

INSERT INTO Allocation VALUES
    ('VO23','T1','2020-12-05','2020-12-08','3 Days'),
    ('VO24','T2','2020-12-03','2020-12-07','4 Days'),
    ('VO25','T3','2020-12-07','2020-12-13','6 Days'),
    ('VO26','T4','2021-09-01','2021-09-10','9 Days'),
    ('VO27','T5','2021-07-16','2021-07-21','5 Days');

CREATE TABLE Service(
    TransportID CHAR(2) PRIMARY KEY,
    TransportName VARCHAR(11),
    TransportCost DECIMAL(6,2),
    TransportMaxDist DECIMAL(6,2),
    FromDate CHAR(16)
);

INSERT INTO Service VALUES
    ('T1','Removalist',5000.50, 1000.56,'2020-12-05'),
    ('T2','Carpenter',1500.00, 550.18, '2020-12-03'),
    ('T3','Electrician',1750.50, 988.00,'2020-12-07'),
    ('T4','Scaffolder',2400.70, 340.76,'2021-09-10'),
    ('T5','Plumber',2100.00, 668.90,'2021-07-16');

CodePudding user response：

Firstly you need to know what is your query doing, particularly the subquery. Let's inspect your query here:

SELECT TransportCost, TransportName, TransportMaxDist
FROM Service
WHERE FromDate IN
/*the query inside the parentheses is called as subquery*/
(SELECT FromDate
FROM Service
WHERE MONTH(FromDate) = 6);

When you run that subquery alone, you're doing WHERE MONTH(FromDate) = 6 which means that you'll only want a data where the month value in FromDate column is 6 or June; regardless of year and day. That's not really fulfilling the condition of "... within the last 6 months calculated from today...".

For "within 6 month" condition, you need to use MySQL date manipulation functions like DATE INTERVAL and CURDATE() or CURRENT_DATE() functions. Therefore, a query like this:

SELECT TransportCost, TransportName, TransportMaxDist, FromDate
FROM Service
WHERE FromDate >= DATE_SUB(CURDATE(),INTERVAL 6 MONTH);

As you can see, the operation DATE_SUB(CURDATE(),INTERVAL 6 MONTH) takes CURDATE() which is today, subtract it by 6 month interval using DATE_SUB(). This can also be written like:

SELECT TransportCost, TransportName, TransportMaxDist, FromDate
FROM Service
WHERE FromDate >= (CURDATE() - INTERVAL 6 MONTH);

I think you'll understand more what that function do when you run SELECT CURDATE() AS 'Today', DATE_SUB(CURDATE(),INTERVAL 6 MONTH) AS '6 Months Ago from Today'.

One particular detail mentioned by @KenWhite in the comment is about the FromDate(in both tables) and ToDate column datatype. Which in your example is CHAR instead of DATE. The possible problem with this is that you might get incorrect/inconsistent results when using date manipulation functions. There is a way to convert that data into a recognizable date format but it'll make the query longer than necessary. So, if it's suppose to store date, then use the proper DATE datatype for the columns.

Here's a demo fiddle.