How to Prefilter dates before grouping

user_16584506 0
Viewed 34

I've grouped some MySQL data to perform some concatenation of fields. However I have dates which I need to find the earliest date before grouping, then display the earliest startdate and latest finishdate in the result.

SELECT *,
GROUP_CONCAT(RTRIM(inventoryID),' x ',qty SEPARATOR' + ') AS jobs
FROM Jobs WHERE ordernbr = '150002' GROUP BY grouping

My Table data is in the format of:

OrderNbr, Qty, InventoryID, StartDate, FinishDate, RequestedDate, OrderDate

After grouping, I need the result to show:

InventoryID, Qty, EarliestStartDate, LatestFinishDate, OrderDate, RequestedDate, jobs

But I'm not sure how to find and display dates before and after grouping.

I'm thinking something like this

SELECT MIN(startdate) AS EarlistStartDate,
GROUP_CONCAT(RTRIM(inventoryID),' x ',qty SEPARATOR' + ') AS jobs
FROM Jobs WHERE ordernbr = '150002' GROUP BY grouping
group-by mysql
1 Answers

If you want to find the earliest start date and latest finish date inside the grouping, then this should suffice:

SELECT MIN(startdate) AS EarliestStartDate, MAX(finishDate) AS LatestFinishDate
GROUP_CONCAT(RTRIM(inventoryID),' x ',qty SEPARATOR' + ') AS jobs
FROM Jobs WHERE ordernbr = '150002' GROUP BY grouping

However, if you need to find the latest finish date before the grouping and the earliest start date after the grouping, then you would need to do something like this:

SELECT COALESCE(latestbefore.finishDate, jbgroup.EarliestStartDate),
       COALESCE(earliestafter.startDate, jbgroup.LatestFinishDate),
       jbgroup.jobs
FROM
(
SELECT MIN(startdate) AS EarliestStartDate,
GROUP_CONCAT(RTRIM(inventoryID),' x ',qty SEPARATOR' + ') AS jobs
FROM Jobs WHERE ordernbr = '150002' GROUP BY grouping
) jbgroup
LEFT JOIN jobs latestbefore
ON latestbefore.finishDate <= jbgroup.EarliestStartDate
LEFT JOIN jobs afterlatestbefore
ON afterlatestbefore.finishDate > latestbefore.finishDate AND
   afterlatestbefore.finishdate <= jbgroup.EarliestStartDate
LEFT JOIN jobs earliestafter
ON earliestafter.startDate >= jbgroup.LatestFinishDate
LEFT JOIN jobs beforeearliestafter
ON beforeearliestafter.startDate < earliestafter.startDate AND
   beforeearliestafter.startDate >= jbgroup.LatestFinishDate
WHERE afterlatestbefore.finishDate IS NULL AND
      beforeearliestafter.finishDate IS NULL;

Explanation:

  • we use our initial query as a subselect, getting the groups
  • latestbefore is the latest date before the group
  • we guarantee that latestbefore is the latest date before the group via ensuring that no such afterlatestbefore exists that is before the group but after latestbefore
  • earliestafter is the earliest date after the group
  • we guarantee that earliestafter is the earliest date after the group via ensuring that no such beforeearliestafter exists that is after the group but before earliestafter
user_4365600
Related