Query to Calculate Duplicate Counts within number of day limits

[c0redumpt]

Heya, getting back into utilising Access, and have searched this forum finding kind-of related answers but no real definitive solution...

I have a table with the following fields:

[Org] [UN] [ADMDATE] [SEPDATE]
23 001 28/03/04 29/03/04
23 001 01/02/04 03/02/04
23 001 20/02/04 22/02/04
23 001 30/03/04 31/03/04
24 001 xxx
24 001 xxx
23 001 xxx
23 002 xxx

Where there are multiple ORG and UN unique combinations...

I need a query that will output the number of duplicates for each unique [Org, UN] that fall within a 28day period (ie where the corresponding admdate from previous sepdate is within 28 days) ..

So for above example it would give the following output:

[org] [ur] [count]
23 001 2

as for Org 23 and UR 001 there are 2 records:
[org] [ur] [admdate] [sepdate]
23 001 01/02/04 03/02/04
23 001 20/02/04 22/02/04

23 001 28/03/04 29/03/04
23 001 30/03/04 31/03/04

that have their admdate fall within 28 days of previous sepdate ... Can anyone help??

 

[PHV]

Something like this ?
SELECT A.Org, A.UN, Count(*) As [Count]
FROM yourTable A INNER JOIN yourTable B
ON A.Org=B.Org And A.UN=B.UN And A.admdate>=B.sepdate And (A.admdate-B.sepdate)<=28
GROUP BY A.Org, A.UN

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[c0redumpt]

Thanks for that is a good start!!! Kinda there... the below is an some test data:
org UN admdate sepdate
5 666666 2/02/2005 4/02/2005
5 666666 5/02/2005 7/02/2005
5 666666 8/03/2005 9/03/2005
5 666666 10/03/2005 12/03/2005
6 666666 22/03/2005 23/03/2005
6 666666 3/02/2005 4/02/2005
6 666666 5/02/2005 7/02/2005
6 666666 20/03/2005 21/03/2005
6 666666 1/02/2005 2/02/2005

with your query i get following results:

org UN count
5 666666 2
6 666666 4 <- should be 3

it overcounts for org 6 UN 666666 by 1 as the query looks at all cases of <=28 days when it needs to only look at consecutive ascending order cases

6 666666 1/02/2005 2/02/2005
6 666666 3/02/2005 4/02/2005 (<=28 to prev)
6 666666 5/02/2005 7/02/2005 (<=28 to prev)
6 666666 20/03/2005 21/03/2005
6 666666 22/03/2005 23/03/2005 (<=28 to prev)

Is that a bit clearer??

 

[PHV]

Perhaps this ?
SELECT A.Org, A.UN, Count(*) As [Count]
FROM yourTable A INNER JOIN yourTable B
ON A.Org=B.Org And A.UN=B.UN And A.admdate>=B.sepdate And (A.admdate-B.sepdate)<=28
WHERE B.sepdate=(SELECT Max(C.sepdate) FROM yourTable C WHERE C.Org=A.Org And C.UN=A.UN And C.sepdate>=A.admdate)
GROUP BY A.Org, A.UN

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[c0redumpt]

Thanks again for the prompt reply, unfortunately with this i get no results at all with test data

Basically this is a query that needs to output number of times a person[UN] is readmitted into an organisation[Org] with 28 days for each visit...

Your initial code was getting there but I think needs to have some sort of modification to look at each visit in an ascending order kind of way?

hmmm

 

[PHV]

Sorry for the typo:
SELECT A.Org, A.UN, Count(*) As [Count]
FROM yourTable A INNER JOIN yourTable B
ON A.Org=B.Org And A.UN=B.UN And A.admdate>=B.sepdate And (A.admdate-B.sepdate)<=28
WHERE B.sepdate=(SELECT Max(C.sepdate) FROM yourTable C WHERE C.Org=A.Org And C.UN=A.UN And C.sepdate[red]<[/red]=A.admdate)
GROUP BY A.Org, A.UN

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[c0redumpt]

Thanks for the prompt reply again... Still couldnt get it to go in Access, so went down different path with SAS and managed to get the outputs required

cheers for the tips/code examples