February 15, 2010 at 8:15 am
Hi
I have a SQL view that selects data from my tables. What I need to do is to add some code that returns the sales data for the last 24 months on a rolling basis.
SELECT dbo.SalesHistory.ACCOUNT, dbo.SalesHistory.PART, dbo.Stock.DESCRIPTION, dbo.Stock.FAMILY, dbo.SalesHistory.QTY, dbo.SalesHistory.PRICE,
dbo.SalesHistory.DISC1, dbo.CUSTOMER.POST, dbo.SalesHistory.DATE, dbo.Family.FAMILY_NAME
FROM dbo.SalesHistory INNER JOIN
dbo.Stock ON dbo.SalesHistory.PART = dbo.Stock.PART INNER JOIN
dbo.Family ON dbo.Stock.FAMILY = dbo.Family.FAMILY LEFT OUTER JOIN
dbo.CUSTOMER ON dbo.SalesHistory.ACCOUNT = dbo.CUSTOMER.ACCOUNT
WHERE (dbo.Stock.FAMILY IN ('800', '600', '1400', '2200', '2000', '5600', '200', '3000', '3600', '5200')) AND (dbo.SalesHistory.DATE >= CONVERT(DATETIME,
'2008-01-01 00:00:00', 102))
Could someone please help? I am pretty new to SQL and as such am finding this tricky.
Thanks in advance.
February 15, 2010 at 8:19 am
Check out "DateAdd" in Books Online. You can add -2 years to get what you're looking for.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 15, 2010 at 8:26 am
Had already attempted to use DATEADD but unsuccessfully!
I can add some code into my SELECT like
SELECT DATEADD(MONTH, -24, GETDATE())
But I am not sure how I then edit my WHERE statement so that only the last 24 months are returned against my dbo.SalesHistory.DATE field.
February 15, 2010 at 8:37 am
OK
So I have tried this :
SELECT DATEADD(YEAR, - 2, GETDATE()) AS Expr1, DATE
FROM dbo.SalesHistory
WHERE (DATE >= CONVERT(DATETIME, 'Expr1'))
But I get a date conversion error. Does anyone have any ideas where I'm going wrong?
February 15, 2010 at 8:43 am
Change your code like this
WHERE DATE >= DATEADD (YEAR, - 2, GETDATE())
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
February 15, 2010 at 8:54 am
Thanks Bru - thats sorted it.:-)
February 15, 2010 at 10:04 am
You are Welcome !
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply