CODEWITHSUNDEEP
sqlamazon-athenaamazon-quicksight
BINewb25
BINewb25

Reputation: 1

Find out last month's data when rolling into a new year

Currently, I am using this code to look at the previous month's data for quicksight in Amazon's Athena (this first part works*):

SELECT month, count(1)
FROM table1
WHERE CAST(EXTRACT(month from now()) - 1 as VARCHAR(2)) = month
GROUP BY month

The challenge is how to ensure that this code will work once we roll over into a new year? I currently have

SELECT month, count(1)
FROM table1
WHERE CASE WHEN( month = '1' THEN month = '13'
ELSE month
END)
CAST(EXTRACT(month from now()) - 1 as VARCHAR(2)) = month
GROUP BY month

To clarify, month was input as a string, hence the CAST as VARCHAR(2) to get "01" through "12". My thought process behind this was that if month = '01', then it reads it as '13', then extracts '1', equaling '12'. But not sure if that will work

Upvotes: 0

Views: 655

Answers (2)

Theo
Theo

Reputation: 132972

You can use the date_add function to subtract one month from today:

SELECT DATE_ADD('month', -1, NOW())

Alternatively you can subtract an interval of one month to achieve the same results:

SELECT NOW() - INTERVAL '1' MONTH

In both cases you can then use MONTH(…) or EXTRACT(MONTH FROM …) to get the month number.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271003

You seem to want:

where month = extract(month from now()) - 1 or
      (extract(month from now()) = 1 and month = 12)

Upvotes: 0

Related Questions