0

I am generating a report which displays how long the plant was running for the day.

Since the operator shuts down the system at lunchtime, I have 2 records for the plant operation hours for each day. I want to display only one record that contains the production Start Time (6:00 am) and production End Time (around 4:00 pm). I have got a table Runtime_Combined that has an auto-incrementing index.

I want to select start date (e.g. 9/1/2021 6:04 AM, which has runtime_combined_ndx = 1) and end date (e.g. 9/1/21 4:23 PM, which has runtime_combined_ndx = 2).

SELECT ProductionStartDate, ProductionEndDate  
FROM Runtime_Combined     
WHERE month(ProductionStartDate) = month (ProductionStartDate)     
And day(ProductionStartDate) = day( ProductionStartDate)

Sample data

yurmix
  • 852
  • 2
  • 8
  • 21
Pran
  • 1
  • 1
  • 1
    Please, edit your question and provide sample data, desired output, your current attempt and what is wrong with it. Also check this: https://stackoverflow.com/tags/sql/info, https://stackoverflow.com/help/how-to-ask – astentx Sep 08 '21 at 22:34
  • `min(start_date)` and `max(end_date)` – astentx Sep 08 '21 at 22:35

2 Answers2

0

You can use aggregation:

SELECT date(ProductionStartDate), sum(runtime_combined_ndx)  
FROM Runtime_Combined     
GROUP BY date(ProductionStartDate);
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
0

Use grouping. Something like:

SELECT MIN(ProductionStartDate) AS Start, MAX(ProductionEndDate) As End
FROM Runtime_Combined
WHERE <....>
GROUP BY DATE(ProductionStartDate)
yurmix
  • 852
  • 2
  • 8
  • 21