CODEWITHSUNDEEP
mysqlsql
James T
James T

Reputation: 109

SQL query - selecting first and last record of each day in a month

Mysql 5.7.30: I have two tables:

projects: projectID, timeNeeded

registrations: registrationID, projectID, startDateTime

What I want to achieve: For the current month (based on startDateTime) I want to know "timeNeeded" by selecting the first and last record for each day in the month. If there's only 1 record for a day, it should still count it twice.

E.g if there's 4 registrations on one day, I only want to include the first and last of those 4.

I'm a little unsure how to get started here, i guess there's multiple ways to achieve this. Speed isn't important, as long as it's better than my first idea; using multiple queries and PHP to process it.

Sample data and wanted result:

Project table:      
project1    50  
project2    20  
project3    30  
        
Registation table:      (hour:minute hidden)
reg1    project1    2020-07-01
reg2    project1    2020-07-01
reg3    project3    2020-07-02
reg4    project3    2020-07-02
reg5    project2    2020-07-02
reg6    project2    2020-07-02
reg7    project3    2020-07-03
reg8    project1    2020-07-04
reg9    project3    2020-07-05
reg10   project2    2020-07-05
        
        
Result (projects.timeNeeded for first and last of each day):        
reg1    50  
reg2    50  
reg3    30  
reg6    20  
reg7    30  
reg7    30  
reg8    50  
reg8    50  
reg9    30  
reg10   20

Upvotes: 0

Views: 1312

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1271231

I would approach this by using union all, once for the first record on each day and once for the last:

select r.*, p.timeneeded
from Registration r join
     Project p
     on r.projectid = p.projectid
where extract(year_month from r.startDateTime) = extract(year_month from now()) and
      r.registrationID = (select r2.registrationID
                          from Registration r2
                          where date(r2.startDateTime) = date(r.startDatetime)
                          order by r2.registrationID 
                          limit 1
                         )
union all
select r.*, p.timeneeded
from Registration r join
     Project p
     on r.projectid = p.projectid
where extract(year_month from r.startDateTime) = extract(year_month from now()) and
      r.registrationID = (select r2.registrationID
                          from Registration r2
                          where date(r2.startDateTime) = date(r.startDatetime)
                          order by r2.registrationID desc
                          limit 1
                         )
order by registrationID;

Note: Your dates are all the same. The name of the column suggests that there might be a time component, but your question doesn't have it. So this uses the registration id to determine the first and last on each day.

Here is a db<>fiddle.

Upvotes: 1

forpas
forpas

Reputation: 164224

The tricky part of this requirement is the double rows for the dates that have only 1 registration, this is why I use UNION ALL.
Aggregation is needed to get the first and last startDateTime of each day and finally joins:

select r.registrationID, p.timeNeeded
from (
  select registrationID, projectID, startDateTime 
  from Registration
  union all
  select max(registrationID), max(projectID), max(startDateTime) 
  from Registration
  group by date(startDateTime)
  having count(*) = 1
) r
inner join ( 
  select date(startDateTime) date, 
         min(startDateTime) min_date,
         max(startDateTime) max_date
  from Registration
  where date_format(startDateTime, "%Y-%m") = date_format(current_date, "%Y-%m")
  group by date
) t on r.startDateTime in (t.min_date, t.max_date) 
inner join Project p on p.projectID = r.projectID
order by r.startDateTime

See the demo.
Results:

| registrationID | timeNeeded |
| -------------- | ---------- |
| reg1           | 50         |
| reg2           | 50         |
| reg3           | 30         |
| reg6           | 20         |
| reg7           | 30         |
| reg7           | 30         |
| reg8           | 50         |
| reg8           | 50         |
| reg9           | 30         |
| reg10          | 20         |

Upvotes: 1

NRB
NRB

Reputation: 105

Since your table is missing the headers, am writing a bit abstract.

select * from registration sort by timestamp_col desc limit 1;

and

select * from registration sort by timestamp_col limit 1;

These two queries should give you the first and last registrations. In case you have only one registration, then both queries will return you the same record. This will meet your needs.

I have ignored the join with the Project table, assuming you know how to join two tables.

Upvotes: 0

Related Questions