How to find maximum of date if group ids (particular group) present, else latest date where all group ids present

Question

0.00/5 (No votes)

See more:

Hello All,

processID   date
75          26-06-2023
18          27-06-2023
75          27-06-2023
89          27-06-2023
98          27-06-2023
18          28-06-2023
18          29-06-2023
75          29-06-2023
89          29-06-2023
98          29-06-2023

in above table, I need maximum date when group of process id available(here group- process id=18,75,89,98) where all 4 id's are present in a group. if one less id is present then it should take max means latest date where all four id's present. means if I run query on 27th June then I should get max date as 27th June(as here all 4 process id's present), if I run query on 28th June, still I should get date as 27th June(as on 28th June we dont have all 4 process id's present, we have only process id=18 present.)how can I achieve this?

I tried: select max(date) from table where processID IN (18,75,89,98) but here it gives maximum of process id not considering all 4 process id's present or not. What to do?

What I have tried:

select max(date) from table where processID IN (18,75,89,98) but here it gives maximum of process id not considering all 4 process id's present or not. What to do?

Posted 7-Jul-23 2:53am

Prads12

Updated 7-Jul-23 4:45am

OriginalGriff

v2

Add a Solution

Comments

Member 15627495 7-Jul-23 9:22am

SELECT distinct(processID), MAX(date) FROM 'table' order by processID ASC

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CHill60 · Answer 1 · 2023-07-07T04:45:00

Solution 2

You can do this with a CTE (or a temporary table or a table variable)

SQL

;;;;with cte as 
(
	select [date]
	from @tab
	group by [date]
	having COUNT(*) = 4
)
select max([date]) from cte
where [date] <= @rundate;

The first part is ensuring that I only look at dates where all four processes are present.
The final part looks for the maximum date before the run date selected. I used

SQL

declare @rundate date = '2023-06-28';

Just for completeness - I set up your sample data as follows

SQL

declare @tab table (processID int, [date] date);
insert into @tab (processID, [date]) values
 (75,'2023-06-26')
,(18,'2023-06-27')
,(75,'2023-06-27')
,(89,'2023-06-27')
,(98,'2023-06-27')
,(18,'2023-06-28')
,(18,'2023-06-29')
,(75,'2023-06-29')
,(89,'2023-06-29')
,(98,'2023-06-29');

Posted 7-Jul-23 4:45am

CHill60

Updated 7-Jul-23 4:47am

v2

Comments

Prads12 8-Jul-23 11:49am

Hello @CHill60: thanks for solution but it is not working as expected as I have different process ID as well apart from 18,75,89,98, so I applied where clause to filter out along with your solution but it is not giving right results. I have done like this: but it didnt work.
with cte as
(
select [date]
from @tab where processID in(18,75,89,98)
group by [date]
having COUNT(*) = 4
)
select max([date]) from cte
where [date] <= @rundate;

CHill60 10-Jul-23 4:28am

I have just run your code and it worked perfectly - for a run date of 28/06 it returned 27/06 as expected

Prads12 26-Jul-23 8:22am

I have updated my question here, this is actual requirement as above query is not giving result what is expected for me.

Can you please have a look here, thanks in advance
https://www.codeproject.com/Questions/5365486/How-to-write-SQL-for-below-scenario

OriginalGriff · Answer 2 · 2023-07-07T03:12:00

Solution 1

Use a GROUP BY:

SQL

SELECT ProcessID, MAX(DATE) FROM MyTable GROUP BY ProcessID

SQL GROUP BY Statement[^] and it's probably worth havign a look here as well: SQL GROUP By and the "Column 'name' is invalid in the select list because..." error[^]

Posted 7-Jul-23 3:12am

OriginalGriff