In case you missed the announcement: Alteryx One is here, and so is the Spring Release! Learn more about these new and exciting releases here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Automatically adjust (Teradata SQL) Where clause to previous month in YYYY-MM format

fruehling
6 - Meteoroid

So the first step on our flow is something we manually update monthly

 

This Teradata SQL query:

select DB.TABLE.field1,
DB.TABLE.Year_month
from DB.TABLE
where DB.TABLE.Year_month = '2023-01'

 

How do I adjust this WHERE clause to always be the previous month?

 

Any help is greatly appreciated

 

Edit:  As I'm still struggling with this, I have found that maybe I need something like this:

DateTimeFormat([DateTime_Out],"%Y-%m") 

or 

DateTimeAdd(DateTimeFirstOfMonth(), 1, "months")

But I can't exactly put together the syntax required

 

9 REPLIES 9
Qiu
21 - Polaris
21 - Polaris

@fruehling 
This is not exactly Alteryx question.

I found this and hope it helps.

https://www.encodedna.com/sqlserver/tips/how-to-get-previous-months-records-in-sql-server.htm

HAVING MONTH(SalesDate) < (SELECT DATEPART(M, DATEADD(M, 0, GETDATE())))

 

fruehling
6 - Meteoroid

Thank you!  You're right I guess I should have posted this on a sql board.  Really appreciate your reply!

fruehling
6 - Meteoroid

Edited main post

DataNath
17 - Castor
17 - Castor

Hey @fruehling, could you try something like this?

 

SELECT
DB.TABLE.field1,
DB.TABLE.Year_month
FROM DB.TABLE
WHERE PARSE("Year_month" AS DATE) = DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE())-1, 0)
fruehling
6 - Meteoroid

Thanks for that.  It did not like this.  It told me Year_month does not match a Defined Type name.  Is this because it is a Teradata source?

DataNath
17 - Castor
17 - Castor

Ah my apologies @fruehling - I was testing this on Microsoft SQL, overlooked that it was Teradata! I've not worked with TD before but from a look over some documentation it should look something like this:

 

SELECT DB.TABLE.field1,
DB.TABLE.Year_month
FROM DB.TABLE
WHERE TO_DATE(DB.TABLE.Year_month, 'YYYY-MM') = ADD_MONTHS(CURRENT_DATE - EXTRACT(DAY FROM CURRENT_DATE) + 1, -1)

 

fruehling
6 - Meteoroid

That seems to have worked as the test query button did not throw an error!  I need to make this change in several inputs, but I think you've got it.  I am guessing the -1 is going to previous month?

DataNath
17 - Castor
17 - Castor

Great to hear @fruehling! Yes the -1 is just part of the ADD_MONTHS function so it’s just adding -1 months to the first day of the current month to get the first day of the previous.

fruehling
6 - Meteoroid

DM sent.  Please let me know if you did not receive

Labels
Top Solution Authors