Return items not modified in "X" days?

Question

Return items not modified in "X" days?

mellestad

SSC Rookie

Points: 46
More actions
April 19, 2012 at 11:51 am

#255047

So, we have re-usable drums and totes with serial numbers and I'm trying to make a report that lists out serial numbers that have not been used in x months.
My dataset is a list of manufacturing orders that lists serial number and document date.
So each serial number might be entered once, or a thousand times with various dates.
Really simplified, how do I take this:
A1, JAN 1990
A1, FEB 2012
B1, JAN 1990
B1, FEB 2011
ETC. ETC.
And ask it, "Show me serial numbers that have not been used in at least 12 months" and get this:
B, FEB 2011
Without getting this:
A1, JAN 1990
B1, JAN 1990
B1, FEB 2011

Viewing 9 posts - 1 through 9 (of 9 total)

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

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 1

12 months back, from today will include months in 2011 also. So you really cant base 12 months on the date column (which contains only years) with the data you provided. Are we missing something here?

Can you explain what really is "12 months" ??

mellestad SSC Rookie Points: 46 More actions · Answer 2

mellestad

SSC Rookie

Points: 46

April 19, 2012 at 12:09 pm

#1476186

Edited OP for clarity

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 3

Need more clarification

SELECT DATEADD(MM,-12,GETDATE())

give you 2011-04-19 11:12:01.653

Now if you had a row with April 2011, should that be neglected or should that be included?

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

Please read the first article I reference below in my signature block, it will walk you through what you need to post and how to do it in order to get the best possible answer.

mellestad SSC Rookie Points: 46 More actions · Answer 5

ColdCoffee (4/19/2012)
Need more clarification
SELECT DATEADD(MM,-12,GETDATE())
give you 2011-04-19 11:12:01.653
Now if you had a row with April 2011, should that be neglected or should that be included?

OK, I should have used better dates, lol. How can I re-phrase. Hmm.

If I just do a standard query, like:

SELECT serialnumber FROM a WHERE DATE < (GETDATE()-360)

I get a list of rows with a date < 360 days, but what I'm trying to pull out is a list of discreet serial numbers that don't have anything listed for the past 360 days.

Does that make more sense?

Ron McCullough SSC Guru Points: 63877 More actions · Answer 6

Oops did not refresh to see your last post before I posted this.

In general something like this might just be useful, if you have NOT solved your problem.

CREATE TABLE #T(Id CHAR(2),Usedlast DATETIME,X INT IDENTITY(1,1))

INSERT INTO #T

SELECT 'A1', '1990' UNION ALL

SELECT 'A1', '2012' UNION ALL

SELECT 'B1', '1990' UNION ALL

SELECT 'B1','2011'

DECLARE @MinMonths INT

DECLARE @MaxMonths INT

SET @MinMonths = 12

SET @MaxMonths = 23

SELECT Id,UsedLast,X,DATEDIFF(mm,UsedLast,GETDATE()) AS 'Months not used' FROM #T

WHERE DATEDIFF(mm,UsedLast,GETDATE()) BETWEEN @MinMonths AND @MaxMonths

Result:

id .... Used last ...... ...... ... X ..... Months not used

B12011-01-01 00:00:00.000 4 ..... 15

Or have I simplified your problem excessively ?

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

drew.allen SSC Guru Points: 77012 More actions · Answer 7

You're looking for the HAVING clause.

SELECT SerialNum, MAX(UsedDate) AS LastUsedDate

FROM YourTable

GROUP BY SerialNum

HAVING MAX(UsedDate) < DATEADD(YEAR, -1, GETDATE())

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

mellestad SSC Rookie Points: 46 More actions · Answer 8

drew.allen (4/19/2012)
You're looking for the HAVING clause.
SELECT SerialNum, MAX(UsedDate) AS LastUsedDate
FROM YourTable
GROUP BY SerialNum
HAVING MAX(UsedDate) < DATEADD(YEAR, -1, GETDATE())
Drew

Yay!! Awesome, thank you very much. I didn't even know that clause existed, its perfect.

A big thanks for everyone else who tried to decipher my rambling above

Return items not modified in "X" days?

Cookies on SQLServerCentral