Giving data from the last 6 months

Question

Post reply

Giving data from the last 6 months

jvElecenter

SSCarpal Tunnel

Points: 4502
More actions
August 18, 2004 at 3:35 am

#164712

Hi
Needed a query giving the data from the last siX months.
Did now something like where month(dateorder) in (month(getdate()-1,month(getdate()-2, etc...

Is there an easier way ?
thx in advance

JV

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Dølle Døck Mr or Mrs. 500 Points: 577 More actions · Answer 1

Try this:

WHERE DateDiff(month, dateorder, GetDate()) <= 6

Regards,

Anders Dæmroen
epsilon.no

Douglas Bentley SSC-Addicted Points: 435 More actions · Answer 2

Grasshopper you beat me to it...

However do want to include that the year should be defined as well.

Add datediff(yy,dateorder,getdate())=0

Had a report that gave too many results and remembered that I had not defined the year.

Doug

jvElecenter SSCarpal Tunnel Points: 4502 More actions · Answer 3

hi,

sorry but i still get different results when i check it with my query.

SELECT DISTINCT

TOP 100 PERCENT YEAR(DTFACT) AS Jaar, CODART AS Artikelcode, LBART1 AS artikelomschrijving, COUNT(CODNAT) AS [Aantal OL], SUM(QUANTI)

AS [Aantal Stuks], SUM(MTTHT) AS Omzet

FROM dbo.VLIGVEN

WHERE

my piece : --(MONTH(DTFACT) IN (MONTH(GETDATE()) - 1, MONTH(GETDATE()) - 2, MONTH(GETDATE()) - 3, MONTH(GETDATE()) - 4, MONTH(GETDATE()) - 5, MONTH(GETDATE()) - 6)) and year(dtfact) = year(getdate())

your piece :

datediff(yy,dtfact,getdate())=0 and DateDiff(month, dtfact, GetDate()) < 6

GROUP BY YEAR(DTFACT), CODART, LBART1

ORDER BY YEAR(DTFACT) DESC, CODART

ANy idea ???

Thx

JV

mlipham Mr or Mrs. 500 Points: 516 More actions · Answer 4

try doing datediff for number of days instead.

Beer is the answer to all the world's woes.

SQLBill SSC Guru Points: 51440 More actions · Answer 5

You are missing an = in the second WHERE.

Your WHERE asks for 1, 2, 3, 4, 5 AND 6 months ago.

The other WHERE asks for months LESS than 6 months ago.

Try this (note the =)

datediff(yy,dtfact,getdate())=0 and DateDiff(month, dtfact, GetDate()) <= 6

-SQLBill

Douglas Bentley SSC-Addicted Points: 435 More actions · Answer 6

Mr. Bill,

Ooooops.... Ya got me.....

Should have used that or <7

Thanks

Doug

Jeff Moden SSC Guru Points: 1004412 More actions · Answer 7

Sometimes, you have to add to subtract...

WHERE DateOrder >= DATEADD(mm,-6,GETDATE())

Notice that we added a NEGATIVE six months to the current date. Will also handle jumps across years, centuries, leap years, etc.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)