Date filter problem

Transact-SQL
1

Hi, I have a sql 2008 view and I 'm trying to get the last 4 months of information considering the current year and month ,this means Jan,Dec,Nov and Oct, when the current month will be feb the result must be feb,jan,dec,nov etc how can I get this?

This is the code I have but this dont shows me Jan 2016

SELECT Año, Mes, DNr, DName, Cost, ST, T, Qty, RNr, RName
FROM dbo.ViewVK_SalesByRep01
WHERE (Mes >= Month(DATEADD(MM, - 3, GETDATE())))

Thanks in advance

2

WHERE (Mes >= dateadd(month, -3, dateadd(month, datediff(month, 0, getdate()), 0))De nada

3

Hi Stephen,

I applied your recomendations but it don't works, dont shows anything, what can it be?.

SELECT Año, Mes, DNr, DName, Cost, ST, T, Qty, RNr, RName
FROM dbo.ViewVK_SalesByRep01
WHERE (Mes >= DATEADD(month, - 3, DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0)))

4

Aha! The date manipulations ultimately returns a date, currently 2015-10-01 based on today's date, but the comparison is against a month (Mes). You would need to combine Ano and Mes to form an actual date and compare against that. Ultimately, it would be better to only have a Date column but for now...WHERE cast(cast(ano as varchar(10)) + cast(mes as varchar(10)) + '01' as date) >= DATEADD(month, - 3, DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0))I am assuming that Ano and Mes are integers. If they are already some type of strings their CAST is superfluous and only the outer CAST to date is necessary.

5

For performance reasons, it's best not to use any function on the data in the table columns if it can be avoided. Therefore, I recommend the code below. It may seem lengthy, but assuming the tables are indexed correctly, it will perform vastly better than any column conversions.

SELECT Año, Mes, DNr, DName, Cost, ST, T, Qty, RNr, RName
FROM dbo.ViewVK_SalesByRep01
CROSS APPLY (
    SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 3, 0) AS date_three_months_ago
) AS assign_alias_names
WHERE Año >= YEAR(date_three_months_ago)
 AND ((Mes >= MONTH(date_three_months_ago) AND Año < YEAR(date_three_months_ago)) OR
      (Mes <= MONTH(GETDATE())) AND Año = YEAR(date_three_months_ago))
6

Good point! This reinforces the desire to have a Date column in the table instead of separate Ano and Mes.

7

I followed your suggestions but it shows me just january 2015 I dont know what it's wrong

SELECT Año, Mes, DNr, DName, Cost, ST, T, Qty, RNr, RName
FROM dbo.ViewVK_SalesByRep01
CROSS APPLY (
SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 3, 0) AS date_three_months_ago
) AS TMP
WHERE Año >= YEAR(date_three_months_ago)
AND ((Mes >= MONTH(date_three_months_ago) AND Año < YEAR(date_three_months_ago)) OR
(Mes <= MONTH(GETDATE())) AND Año = YEAR(date_three_months_ago))

8

D'OH! Sorry, I need to correct the WHERE:

WHERE Año >= YEAR(date_three_months_ago)
AND ((Mes >= MONTH(date_three_months_ago) AND Año = YEAR(date_three_months_ago)) OR
(Mes <= MONTH(GETDATE())) AND Año > YEAR(date_three_months_ago))

9

Thanks Scoott and Stephen, it works.

kind regards...

10

Mi gusto...