complicated date issue

  • Thread starter Thread starter pat67
  • Start date Start date
P

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
 
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
 
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 -
Click to expand...

ok let me check it out. thanks
 
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 -
Click to expand...

I am going to have to wait until next week when i have 3 dates. with
only 2 right now, the qry returns blanks.
 
ok let me check it out. thanks- Hide quoted text -

- Show quoted text -
Click to expand...

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
 
Back
Top