CODEWITHSUNDEEP
sql-serverselect
blankon91
blankon91

Reputation: 515

how to select the 3 most recent months?

I want to get the result for the 3 most recent months. and this is what I've create so far:

SELECT 
    year([date]) as tahun, 
    month([date]) as bulan, 
    [type] as tipe,
    SUM([net qty]) total_karton, 
    CAST(SUM([cm1 (rp)]) as decimal) as total_uang
FROM 
    tbl_weeklyflash_ID
GROUP BY 
    year([date]), 
    month([date]),
    [type]
ORDER BY 
    month([date]), [type]

but that query shows all months, how to get only the 3 most recent months?

Upvotes: 2

Views: 1985

Answers (2)

stb
stb

Reputation: 3513

try this:

WHERE month([date]) between month(getdate()) -3 and month(getdate())

Upvotes: 1

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239764

Just add a WHERE clause, something like:

WHERE
    DATEDIFF(month,[date],CURRENT_TIMESTAMP) between 0 and 2 --May have to adjust the end value on this

2 will give you the current month and the previous 2. If you want 3 whole months of data, you might need to adjust the end value.

DATEDIFF always gives the number of transitions that have occurred, for the date part specified. So in the above, it's counting the number of times that the month has changed between date (not a great column name, BTW) and the current date.

Upvotes: 3

Related Questions