I have a SQL server table that looks like this

The monthid is a column in the format of yyyymm with integer datatype.

I need to extract the last 12 months of data.

Can someone please suggest how I can do it

CodePudding user response：

Covert your data to a Pandas DataFrame. You can check how to do here.

# Convert date column to datetime
df['A header'] = pd.to_datetime(df['A header'], format='%Y%m')

# Sort and select last 12 months
df.sort_values(by='A header',ascending=True).set_index("A header").last("12M").reset_index()

| A header            | Another header   |
|:--------------------|:-----------------|
| 2020-12-01 00:00:00 | C                |
| 2021-01-01 00:00:00 | D                |
| 2021-02-01 00:00:00 | E                |
| 2021-03-01 00:00:00 | F                |
| 2021-04-01 00:00:00 | G                |
| 2021-05-01 00:00:00 | H                |
| 2021-06-01 00:00:00 | I                |
| 2021-07-01 00:00:00 | J                |
| 2021-08-01 00:00:00 | K                |
| 2021-09-01 00:00:00 | L                |
| 2021-10-01 00:00:00 | M                |
| 2021-11-01 00:00:00 | N                |

CodePudding user response：

You can use the following query:

SELECT *
FROM YourTable
WHERE [A header] >= YEAR(DATEADD(month, -12, GETDATE())) * 100   MONTH(DATEADD(month, -12, GETDATE()))

db<>fiddle

This is made worse by the fact you are storing dates as integers. Instead you should store the last day of the month as a date type, then you can do

WHERE [A header] >= DATEADD(month, -12, GETDATE()))