SQL Statement - Check the earliest date as per group of records - If true , return custom text in a custom column

Luthius

Active Member
Joined
Apr 5, 2011
Messages
318
Guys I have the following problem.
I need to identify if the date of the record is the earliest date of the group.
The idea is NOT return an aggregation table, the idea is return all the records but in a custom column I want to identify if the date of the record is the earliest of the group. If true, then the record of the column will receive a text like "Most Updated record", otherwise, "Discontinued".

Below a simplification of the table where GroupID means foreign key

MyTable
  • Id
  • GroupID
  • RecordDate
  • Observations
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,980
Office Version
  1. 365
Platform
  1. Windows
You can either do this with a set of two queries, or with a subquery within a query.
I will describe how to do two query one, as you can use the Query Builder for that and do not need to write your own SQL code.

First, create an aggregate query that has two fields: GroupID and RecordDate.
Group by the GroupID field, and choose the "Min" option for the RecordDate, to return the earliest date for each query.

Now, create a new query, doing a left-join from your original table to your aggregate query, joining on the GroupID field.
You can return whatever fields from your original table that you like, and then add a calculated field in this format:
IIF(Table.RecordDate = Query.MinRecordDate,"Most Updated","Discontinued")

Obviously, you will need to change Table.RecordDate with the reference to your RecordDate field from the table, and Query.MinRecordDate with the reference of your minimum record date from the aggregate query. If you are sure what they are supposed to look like, just select the appropriate fields from the Expression Builder, and Access will fill in the details.
 

Luthius

Active Member
Joined
Apr 5, 2011
Messages
318
Thank you for taking your time to help me.

I understood but is there a way to that with a single SQL statement instead to store an aggregate query into my database? As you mentioned, something using subqueries?

Thank you
 

Luthius

Active Member
Joined
Apr 5, 2011
Messages
318
I'm trying to merge both statements but it is NOT working. It is driving me crazy... 🥴

My Idea is a Sub-query....


SQL:
SELECT MyTable.GroupId,
       MyTable.RecordDate,
       MyTable.Remarks,
       Switch([RecordDate]=[MinOfRecordDate], "Most Updated", [RecordDate]<>[MinOfRecordDate], "Discontinued") AS CustomColumn
FROM MyTable
LEFT JOIN
  (SELECT MyTable.GroupId,
          Min(MyTable.RecordDate) AS MinOfRecordDate
   FROM MyTable
   GROUP BY MyTable.GroupId) MyTable ON MyQuery.GroupId = MyTable.GroupId;
 

jackd

Well-known Member
Joined
Oct 19, 2006
Messages
1,318
Office Version
  1. 365

ADVERTISEMENT

Here is a mock up that may be helpful.

Table TLuth

TLuth TLuth

idgroupdidRecordDateobservations
1​
1​
02-Mar-21​
xxx
2​
1​
13-Feb-98​
rdds
3​
2​
27-Oct-17​
abc
4​
3​
27-Oct-17​
4rtgtg
5​
2​
19-Mar-06​
qwe
6​
1​
12-Aug-19​
tgt
7​
2​
28-Dec-19​
ref
8​
1​
31-Oct-98​
makd
9​
3​
07-Mar-21​
wefrf
VBA Code:
SELECT TLuth.groupdid
, Min(TLuth.RecordDate) AS MinOfRecordDate
FROM TLuth
GROUP BY TLuth.groupdid;

Result:

Query2 Query2

groupdidMinOfRecordDate
1​
13-Feb-98​
2​
19-Mar-06​
3​
27-Oct-17​
 

Luthius

Active Member
Joined
Apr 5, 2011
Messages
318
Thank you but my request is ONE singe query.


"is there a way to do that with a single SQL statement instead to store an aggregate query into my database? As you mentioned, something using subqueries?"

My Idea is a Sub-query....

SQL:
SELECT MyTable.GroupId,
       MyTable.RecordDate,
       MyTable.Remarks,
       Switch([RecordDate]=[MinOfRecordDate], "Most Updated", [RecordDate]<>[MinOfRecordDate], "Discontinued") AS CustomColumn
FROM MyTable
LEFT JOIN
  (SELECT MyTable.GroupId,
          Min(MyTable.RecordDate) AS MinOfRecordDate
   FROM MyTable
   GROUP BY MyTable.GroupId) MyTable ON MyQuery.GroupId = MyTable.GroupId;
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,980
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Here is how you can do it all in one table:
SQL:
SELECT MyTable.GroupID, MyTable.RecordDate, MyTable.Remarks, IIf([MyTable].[RecordDate]=[x].[MinOfRecordDate],"Most Updated","Discontinued") AS CustomColumn
FROM MyTable LEFT JOIN
(SELECT MyTable.GroupID, Min(MyTable.RecordDate) AS MinOfRecordDate
FROM MyTable
GROUP BY MyTable.GroupID) AS x
ON MyTable.GroupID = x.GroupID;
 
Solution

Luthius

Active Member
Joined
Apr 5, 2011
Messages
318
SQL:
SELECT
MyDataSource.*,
IIf([MyDataSource].[Planned Date] = [x].[MinOfRecordDate], "To schedule", "Highlight to Cancel") AS CustomColumn
FROM MyDataSource
LEFT JOIN (SELECT
MyDataSource.[Maintenance item],
MIN(MyDataSource.[Planned Date]) AS MinOfRecordDate
FROM MyDataSource
GROUP BY MyDataSource.[Maintenance item]) AS x
 ON MyDataSource.[Maintenance item] = x.[Maintenance item];
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,980
Office Version
  1. 365
Platform
  1. Windows
OK, so did my query work?
It looks like you didn't originally give us the real table and field names, and just substituted them into the SQL code I gave you.
Is that correct?
 

Luthius

Active Member
Joined
Apr 5, 2011
Messages
318
Your solution is perfect. Exactly what I was looking for. I just paste the final result of my query based on your solution.

Mr jackd thank you as well for your support and precious time.

This is the best forum!!!!
 

Watch MrExcel Video

Forum statistics

Threads
1,129,927
Messages
5,639,035
Members
417,065
Latest member
ALONSO_1150

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top