complicated date issue

[pat67]

Hi, here is my issue. I have a table with dates and part numbers on
it. It is a table I append each week with what is on the report I am
uploading. What I am looking for is to see how long parts have been on
the list. The problem is a part may be on the list this week, off the
list next week and then on the list for 4 weeks after. I can't do a
simple min for the date because that would give me this week. What I
am looking for is how many current consecutive weeks the part has been
on and when the first week was of the current weeks list. Any ideas?

Thanks

 

[John Spencer]

This may get you started on identifying where the breaks occur. It is
untested, but it may give you an idea.

SELECT A.PartNumber, A.ActiveDate
, Max(B.ActiveDate) as PriorDate
FROM TheList As A LEFT JOIN TheList As B
ON A.PartNumber = B.PartNumber
AND A.ActiveDate > B.ActiveDate
GROUP BY A.PartNumber, A.ActiveDate
HAVING A.ActiveDate -7 < Max(B.ActiveDate)


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[pat67]

This may get you started on identifying where the breaks occur.  It is
untested, but it may give you an idea.

SELECT A.PartNumber, A.ActiveDate
, Max(B.ActiveDate) as PriorDate
FROM TheList As A LEFT JOIN TheList As B
ON A.PartNumber = B.PartNumber
AND A.ActiveDate > B.ActiveDate
GROUP BY A.PartNumber, A.ActiveDate
HAVING A.ActiveDate -7 < Max(B.ActiveDate)

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County




- Show quoted text -

ok let me check it out. thanks

 

[pat67]

This may get you started on identifying where the breaks occur.  It is
untested, but it may give you an idea.

SELECT A.PartNumber, A.ActiveDate
, Max(B.ActiveDate) as PriorDate
FROM TheList As A LEFT JOIN TheList As B
ON A.PartNumber = B.PartNumber
AND A.ActiveDate > B.ActiveDate
GROUP BY A.PartNumber, A.ActiveDate
HAVING A.ActiveDate -7 < Max(B.ActiveDate)

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County




- Show quoted text -

I am going to have to wait until next week when i have 3 dates. with
only 2 right now, the qry returns blanks.

 

[pat67]

ok let me check it out. thanks- Hide quoted text -

- Show quoted text -

Ok. I added data to make 3 sets of dates. I ran the query and what I
see is where a part is on the first week, then not the second, but
then again on the third, it shows twice like this

Part Date Prior Date
123 2/14/11 3/1/11
123 3/1/11 3/1/11


What I am looking for is for it to show like this

Part Earliest Date
123 3/1/11


meaning even though this part was on the 2/14 report, it wasn't on
2/22 report and thus is new for 3/1. So if it were on 3/8, 3/15 and
3/22, i would see 3/1 as the earliest date. If in fact it came off and
was not back on until the 3/22 report, the the date i would see is
3/22

Am i explaining it clearly? I know what I mean I am trying to convey
it properly.

Thanks