Subquery to find consecutive dates

[Tomas C]

Howdy Folks,

I have a table that contains a series of asbestos sampling dates at
several different locations. A simplified version of the structure is as
shown below:

[Location]
[Sampler] - Company that performed the sampling
[Sample Date]
[Result] - Numerical Result
[Detection Flag] - Yes or No

When the detection flag is "Y" for a given day, I want to know if the
next day also has a detection. If not, I want to ignore that detection.
If yes, I want the first day to get flagged with a "1" and the
subsequent day to be flagged with a "2". This gets trickier when I have
more than two days in a row that have a detection. In fact I have no
idea how to approach such a case from a query perspective.

Ultimately, I want to look at date pairs. I want to know if a detection
on one day makes it more likely that a detection will occur the next
day. Let's consider the data below. We'll assume that all are detections:

Date
1/1/2008
1/2/2008
1/3/2008
1/4/2008
1/10/2008
1/15/2008
1/16/2008
1/25/2008

I think what I would like to see as my end product is this:

Date Detection Day
1/1/2008 1
1/2/2008 2
1/2/2008 1
1/3/2008 2
1/3/2008 1
1/4/2008 2
1/15/2008 1
1/16/2008 2

Note that a date can appear twice if it occurs in a block of detections
where n > 2. Does anyone have any advice on how to proceed? I would
greatly appreciate it.

Thanks in advance.

 

[Tomas C]

I think I am pretty close to figuring this out. If I get what I need, I
will post the answer here.

 

[Michel Walsh]

You can rank the dates, If dates are consecutive, their ranks will also be,
so the difference is the same:

date rank date-rank
d k d-k
d+1 k+1 d-k
d+2 k+2 d-k
d+5 k+3 d-k+2
d+6 k+4 d-k+2
d+7 k+5 d-k+2
d+11 k+6 d-k+5
d+12 k+7 d-k+5



and so, it is a matter to

SELECT MIN(date), MAX(date)
....
GROUP BY date-rank



Now, the problem is to find the rank. You can use the procedure you want,
personally, I use join:

--------------
SELECT a.date, LAST(a.otherField), MIN(b.date), MAX(b.date)
FROM myTable AS a INNER JOIN myTable AS b
ON a.date >= b.date
GROUP BY a.date-COUNT(*)
--------------




Hoping it may help,
Vanderghast, Access MVP

 

[Michel Walsh]

I tried to do many things at one. First, the ranking technique using joins
works only if there is no dup.


So, better to take an example.

Using Northwind, as example, I want to get the sequence of orderDate, per
employee, in other words, find, per employee, how many consecutive days they
succeed in placing at least one order.


First, I built a query returning no dup:

-------------------
SELECT DISTINCT Orders.EmployeeID, Orders.OrderDate
FROM Orders;
--------------------


which I call DistinctEmployeeOrderDate.



Next, a query to do the ranking it self:

-----------------------
SELECT a.EmployeeID, a.OrderDate, COUNT(*) AS rank
FROM DistinctEmployeeOrderDate AS a
INNER JOIN DistinctEmployeeOrderDate AS b
ON (a.EmployeeID=b.EmployeeID) AND (a.OrderDate>=b.OrderDate)
GROUP BY a.EmployeeID, a.OrderDate
ORDER BY a.EmployeeID, a.OrderDate;
-------------------------


and finally, use the group on date-rank, to get the consecutive dates:


---------------------------------------
SELECT TOP 5
x.EmployeeID,
Min(x.OrderDate) AS MinOfOrderDate,
Max(x.OrderDate) AS MaxOfOrderDate
FROM EmployeeDateRank AS x
GROUP BY x.EmployeeID, x.orderDate-x.rank
ORDER BY Max(x.orderDate)-Min(x.orderDate) DESC
---------------------------------------



returning


Employee MinOfOrderDate MaxOfOrderDate
Peacock, Margaret 1998.03.02 1998.03.06
Fuller, Andrew 1998.04.06 1998.04.10
Leverling, Janet 1997.02.10 1997.02.13
Fuller, Andrew 1998.03.30 1998.04.02
Callahan, Laura 1997.10.27 1997.10.30
Buchanan, Steven 1998.02.03 1998.02.06




where we can see that Andrew succeed to place (at least) one order for 5
consecutive dates (1998.04.06 to 1998.04.10)


If you tried to use directly the GROUP BY a.date-COUNT(*), you will
definitively get the error that you cannot use an aggregate in the group
clause. You have to use a sub-query (or cascading the queries, as here).



Vanderghast, Access MVP