Click here to Skip to main content
15,922,427 members
Please Sign up or sign in to vote.
1.00/5 (2 votes)
See more:
I have two tables.

first table:intrans table- column:Date_intrans,Curves coming from intrans table(which is updated daily basis, so date_intrans has daily data second table in infradate where we have monthly data for each intrans date.

I need latest or max(Date_intrans) where we have all four curve id (17,20,54,58) present for each month till 2025-12-01 in below screenshot: max Date_intrans should be 2023-01-01(should not be 2023-01-02 as it doesnt has curve id 58 present for 2023-05-01, so it should check all values till 2025-12-01, if one month also missing for curve then it should not be max(Date_intrans)) how to write sql for it?


Date intrans(daily data in Year-Month-date format),	infradate(each month data from next month, monthly data in year-month-date format),	Curveid	
2023-01-01	2023-02-01	17	
	        2023-02-01	20	
	        2023-02-01	54	
	        2023-02-01	58	
	        2023-02-01	4	
	        2023-02-01	6	
	        2023-03-01	17	
	        2023-03-01	20	
	        2023-03-01	54	
	        2023-03-01	58	
	        2023-03-01	4	
	        2023-03-01	6	
	        2023-04-01	17	
	        2023-04-01	20	
	        2023-04-01	54	
	        2023-04-01	58	
	        2023-04-01	4	
	        2023-04-01	6	
	        2023-05-01	17	
	        2023-05-01	20	
	        2023-05-01	54	
	        2023-05-01	58	
	        2023-05-01	4	
	        2023-05-01	6	
	      data till 2025-12-01		
 2023-01-02	2023-02-01	17	
	        2023-02-01	20	
	        2023-02-01	54	
	        2023-02-01	58	
	        2023-02-01	4	
	        2023-02-01	6	
	        2023-03-01	17	
	        2023-03-01	20	
	        2023-03-01	54	
	        2023-03-01	58	
	        2023-03-01	4	
	        2023-03-01	6	
	        2023-04-01	17	
	        2023-04-01	20	
	        2023-04-01	54	
	        2023-04-01	58	
	        2023-04-01	4	
	        2023-04-01	6	
	        2023-05-01	17	
	        2023-05-01	20	
	        2023-05-01	54	
	        2023-05-01	4	
	        2023-05-01	6	
	data till 2025-12-01

curve id 58 is not present for 2023-05-1, so this is not latest Dateintrans

What I have tried:

I tried

select a.Date_intrans,b. infradate, a.curveid from intrans a left join infradate b

on a.timid=b.timid where a.curveid in (17,20,54,58)
Posted
Updated 27-Jul-23 9:44am
v2
Comments
Maciej Los 27-Jul-23 16:05pm    
Follow this link: SQL Server 2022 | db<>fiddle[^]

This is the same question as How to find maximum of date if group ids (particular group) present, else latest date where all group ids present[^]

I have already provide a solution to that post giving exactly the results you said you wanted.

You have subsequently said
Quote:
I have updated my question here, this is actual requirement as above query is not giving result what is expected for me.
and yet the results from my solution were identical to the results you said you wanted in your question and it even takes into account the rundate. So in what way does that solution not fit? What are YOU not telling us?

To get the results you require go to your original question, use the code from my solution, changing my tablename @Tab to your table name intrans and use
SQL
declare @rundate date = '2025-12-01';
instead of the value I provided.
 
Share this answer
 
Comments
Prads12 26-Jul-23 9:03am    
Thank you for your answer @chilli,with your answer above, it will give max date where we have all 4 id's present. But it is not considering do we have data for each month(infradate), as if we dont have in between month still it gives result. hope it is clear for you(I have to make sure that everymonth should also present from infradate table.
CHill60 27-Jul-23 3:39am    
Not making yourself clear at all, nor are you taking care with your dates (there is no '2023-01-02' in your sample data).
You said "I need latest or max(Date_intrans) where we have all four curve id (17,20,54,58) present for each month", are you now saying that once any curve id is absent then do not consider any date beyond that point?
E.g.
Date 1 - has all 4 ids
Date 2 - has only 3 ids
Date 3 - has all 4 ids
So required data is "Date 1" not "Date 3"
Prads12 27-Jul-23 5:31am    
sorry may b I am bad at explaining or asking correct question apologies. your logic will work correctly, but it is not considering infradate into consideration, it is checking if all curve present which is half correct logic. but I need infradate(all months, from next month till 2025-12-01 for infradate) in your solution, it will give correct result but if their is any infradate is missing then still it will give maximum Date_intrans. if one infradate also missing then maximum Date_intrans should not consider date, it should consider Date_intrans where we have all infradate.

so here it should consider:
all 4 ids
also all months of infradate then that max(Date intrans) should return.

if all 4 ids present but if any infradate from another table is missing then it should return max previus Date intrans where we have all infraDate.

Hope this is clear to you and apologies if I am not good at explaining scenario

Maciej Los 26-Jul-23 13:34pm    
5ed!
Try using GROUP BY[^] with the HAVING clause and the COUNT aggregate function.
 
Share this answer
 
Comments
Prads12 26-Jul-23 8:06am    
how to see if till 2025 data is available for particular date?

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