How to write SQL for below scenario

Question

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 26-Jul-23 0:25am

Prads12

Updated 27-Jul-23 9:44am

Dave Kreskowiak

v2

Add a Solution

Comments

Maciej Los 27-Jul-23 16:05pm

Follow this link: SQL Server 2022 | db<>fiddle[^]

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-26T02:48:00

Solution 3

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.

Posted 26-Jul-23 2:48am

CHill60

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!

OriginalGriff · Answer 2 · 2023-07-26T01:07:00

Solution 1

Try using GROUP BY[^] with the HAVING clause and the COUNT aggregate function.

Posted 26-Jul-23 1:07am

OriginalGriff

Comments

Prads12 26-Jul-23 8:06am

how to see if till 2025 data is available for particular date?