Click here to Skip to main content
15,845,681 members
Please Sign up or sign in to vote.
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
Updated 7-Jul-23 5:45am
v2
Comments
Member 15627495 7-Jul-23 9:22am    
SELECT distinct(processID), MAX(date) FROM 'table' order by processID ASC


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');
 
Share this answer
 
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
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[^]
 
Share this answer
 

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900