How to write query to get dates

Question

1.00/5 (2 votes)

See more:

I have result of two table as below column I need to have , custm date where we have all date(starting from next month till end of dec 2024), if one month also missing then it should not be in custm date column.

In example below: custm date='8/1/2023' then it should check for Cetstart date. is data available for sep 2023 till dec 2024 in Cetstart date, as in we dont have data for oct( we have data for november) so that custm date should not be picked up date.In cetstart date if any month is missing between next month of custm date till dec 2024, then it should not be right custm date.
so in short custm date should check if Cetstart date is avaiable for next month till dec 2024(all months should be available, if anyone month also missing then it should not be in custm date column)

is it possible to get like this?

custm date	CetStart				
8/1/2023	10/1/2023				
8/1/2023	11/1/2023				
8/1/2023	12/1/2023				
8/1/2023	1/1/2024				
8/1/2023	2/1/2024				
8/1/2023	3/1/2024				
8/1/2023	4/1/2024				
8/1/2023	5/1/2024				
8/1/2023	6/1/2024				
8/1/2023	7/1/2024				
8/1/2023	8/1/2024				
8/1/2023	9/1/2024				
8/1/2023	10/1/2024				
8/1/2023	11/1/2024				
8/1/2023	12/1/2024				
7/31/2023	8/1/2023				
7/31/2023	9/1/2023				
7/31/2023	10/1/2023				
7/31/2023	11/1/2023				
7/31/2023	12/1/2023				
7/31/2023	1/1/2024				
7/31/2023	2/1/2024				
7/31/2023	3/1/2024				
7/31/2023	4/1/2024				
7/31/2023	5/1/2024				
7/31/2023	6/1/2024				
7/31/2023	7/1/2024				
7/31/2023	8/1/2024				
7/31/2023	9/1/2024				
7/31/2023	10/1/2024				
7/31/2023	11/1/2024				
7/31/2023	12/1/2024				
7/28/2023	8/1/2023				
7/28/2023	9/1/2023				
7/28/2023	10/1/2023				
7/28/2023	11/1/2023				
7/28/2023	12/1/2023				
7/28/2023	1/1/2024				
7/28/2023	2/1/2024
7/28/2023	3/1/2024
7/28/2023	4/1/2024
7/28/2023	5/1/2024
7/28/2023	6/1/2024
7/28/2023	7/1/2024
7/28/2023	8/1/2024
7/28/2023	9/1/2024
7/28/2023	10/1/2024
7/28/2023	11/1/2024
7/28/2023	12/1/2024

-------------------------

output I needed:

Output:
custm date
7/31/2023
7/28/2023

What I have tried:

SSMS

select custm date, CetStart from table
where DATENAME(month,mtuCetStart)=DATENAME(month,DATEADD(Month,+1,tradeDate)) and DATENAME(Year,mtuCetStart)=DATENAME(Year,tradeDate)

but here it is not checking for whole mtucetstart till 2024 dec, it is only checking for one next month only.

Posted 3-Aug-23 2:30am

Prads12

Updated 3-Aug-23 4:26am

Andre Oosthuizen

v2

Add a Solution

1 solution

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)

Andre Oosthuizen · Accepted Answer · 2023-08-03T04:26:00

Not sure what you call your data table, I named it 'your_table'. You can use a query as in my code -

SQL

SELECT custm_date
FROM your_table t1
WHERE NOT EXISTS (
    SELECT 1
    FROM your_table t2
    WHERE t2.custm_date = t1.custm_date
    AND NOT EXISTS (
        SELECT 1
        FROM your_table t3
        WHERE t3.custm_date = t1.custm_date
        AND t3.CetStart = DATEADD(MONTH, n.n, DATEADD(DAY, 1, EOMONTH(t1.custm_date, 1)))
    )
)

The subquery above with the alias 'n' generates a sequence of numbers from 1 to the number of months between the next month of the 'custm_date' and December 2024. It then checks for each month in the sequence whether there exists a corresponding 'CetStart' date in your table for the given 'custm_date.' If any month is missing, the 'NOT EXISTS' condition will be true, and that 'custm_date' will not be included in your output result.

To test this, I have done the following -

SQL

CREATE TABLE your_table (
    custm_date DATE,
    CetStart DATE
);

INSERT INTO your_table (custm_date, CetStart) VALUES
('8/1/2023', '10/1/2023'),
('8/1/2023', '11/1/2023'),
('8/1/2023', '12/1/2023'),
('8/1/2023', '1/1/2024'),
('8/1/2023', '2/1/2024'),
('8/1/2023', '3/1/2024'),
('8/1/2023', '4/1/2024'),
('8/1/2023', '5/1/2024'),
('8/1/2023', '6/1/2024'),
('8/1/2023', '7/1/2024'),
('8/1/2023', '8/1/2024'),
('8/1/2023', '9/1/2024'),
('8/1/2023', '10/1/2024'),
('8/1/2023', '11/1/2024'),
('8/1/2023', '12/1/2024'),
('7/31/2023', '8/1/2023'),
('7/31/2023', '9/1/2023'),
('7/31/2023', '10/1/2023'),
('7/31/2023', '11/1/2023'),
('7/31/2023', '12/1/2023'),
('7/31/2023', '1/1/2024'),
('7/31/2023', '2/1/2024'),
('7/31/2023', '3/1/2024'),
('7/31/2023', '4/1/2024'),
('7/31/2023', '5/1/2024'),
('7/31/2023', '6/1/2024'),
('7/31/2023', '7/1/2024'),
('7/31/2023', '8/1/2024'),
('7/31/2023', '9/1/2024'),
('7/31/2023', '10/1/2024'),
('7/31/2023', '11/1/2024'),
('7/31/2023', '12/1/2024'),
('7/28/2023', '8/1/2023'),
('7/28/2023', '9/1/2023'),
('7/28/2023', '10/1/2023'),
('7/28/2023', '11/1/2023'),
('7/28/2023', '12/1/2023'),
('7/28/2023', '1/1/2024'),
('7/28/2023', '2/1/2024'),
('7/28/2023', '3/1/2024'),
('7/28/2023', '4/1/2024'),
('7/28/2023', '5/1/2024'),
('7/28/2023', '6/1/2024'),
('7/28/2023', '7/1/2024'),
('7/28/2023', '8/1/2024'),
('7/28/2023', '9/1/2024'),
('7/28/2023', '10/1/2024'),
('7/28/2023', '11/1/2024'),
('7/28/2023', '12/1/2024');

If you run the sample code I gave you, the output result will be -

Output

custm_date
----------
2023-07-31
2023-07-28

These are the 'custm_date' values for which all the months from August 2023 to December 2024 have corresponding 'CetStart' dates in your table. Any other 'custm_date' with missing months would not be included in your output result set.