Results from next full month

General

Results from next full month

Hi There,

 

I currently have a dataset where the range of results span between the dates: 15-01-2017 to 30-04-2018

So therefore, my min(date) would be 15-01-2017

However, I want to return results whereby if my min(date) is not a complete month, it should give me the results from next full month onwards (i.e.: 01-02-2017 to 30-04-2018).

 

Hoping to get some direction

 

Thank you

Tags (3)

Accepted Solutions
Junior Contributor

Re: Results from next full month

Same as on StackOverflow :-)

 

This will return the start of the next month unless it's the first of the month:

trunc(add_months(min(datecol)-1,1), 'mon') 

Add one month to the day before the min date (i.e. only the 1st will result in the previous month), then get the 1st of that month.

Depending on your actual query you might filter using

qualify datecol >= trunc(add_months(min(datecol) over (partition by ??)-1,1), 'mon') 
1 ACCEPTED SOLUTION
2 REPLIES
Junior Contributor

Re: Results from next full month

Same as on StackOverflow :-)

 

This will return the start of the next month unless it's the first of the month:

trunc(add_months(min(datecol)-1,1), 'mon') 

Add one month to the day before the min date (i.e. only the 1st will result in the previous month), then get the 1st of that month.

Depending on your actual query you might filter using

qualify datecol >= trunc(add_months(min(datecol) over (partition by ??)-1,1), 'mon') 

Re: Results from next full month

Thank you very much, dnoeth

Saved me a heap of headache