Help for Date Gaps

[Winnie]

Please help me with some idea for my difficulty of
finding the date gaps between unknown of number
of rows in the two columns (by days) for each
concatenation of "LastName + FirstName + DOB" as a unique
identifier. For each "LName" + "FName" + "DOB", there are
one or two or many plans (unknown number of start and end
dates for each person), I need to use the following row
of StateDate minus EndDate from the previous row to find
out how many gaps each person has and post the result to
another column called "Gap#" and the date difference by
days into another column "GapDays", If "Gap#" >= 2
for "LName" + "FName" + "DOB", delete; if "Gap#" <= 1
while "GapDays">45, also delete; then do the same thing
for next person and so on. Please advise. The data sheet
looks like the following

LName FName DOB PType SDate EDate Gap# GapDays
A a 1/1/2000 A 1/1/2000 3/31/2000
A a 1/1/2000 B 4/5/2000 5/31/2000
A a 1/1/2000 C 1/1/2002

B b 2/2/1950 B 1/15/1999 12/20/2002



Thank you for kind help.

Winnie Zhou

 

[Dale Fye]

Winnie,

Is there any chance that the end date of one row will be greater than
the start date of another row?

I think the following should do what you are looking for.

SELECT T.LName, T.FName,
T.DOB, T.SDate, T.EDate,
(SELECT COUNT(*)
FROM yourTable T2
WHERE T2.LNAME = T.LNAME
AND T2.FNAME = T.FNAME
AND T2.DOB = T.DOB
AND T2.SDate <= T.SDate) as GapNum,
DateDiff('d', T.EDate,
(SELECT Min([StartDate])
FROM yourTable T1
WHERE T1.LNAME = T.LNAME
AND T1.FNAME = T.FNAME
AND T1.DOB = T.DOB
AND T1.SDate = > T.EDate)) as GapDays
FROM yourTable T

There are two nested sub-queries here so this could take a while to
run.

--
HTH

Dale Fye


Please help me with some idea for my difficulty of
finding the date gaps between unknown of number
of rows in the two columns (by days) for each
concatenation of "LastName + FirstName + DOB" as a unique
identifier. For each "LName" + "FName" + "DOB", there are
one or two or many plans (unknown number of start and end
dates for each person), I need to use the following row
of StateDate minus EndDate from the previous row to find
out how many gaps each person has and post the result to
another column called "Gap#" and the date difference by
days into another column "GapDays", If "Gap#" >= 2
for "LName" + "FName" + "DOB", delete; if "Gap#" <= 1
while "GapDays">45, also delete; then do the same thing
for next person and so on. Please advise. The data sheet
looks like the following

LName FName DOB PType SDate EDate Gap# GapDays
A a 1/1/2000 A 1/1/2000 3/31/2000
A a 1/1/2000 B 4/5/2000 5/31/2000
A a 1/1/2000 C 1/1/2002

B b 2/2/1950 B 1/15/1999 12/20/2002



Thank you for kind help.

Winnie Zhou

 

[Winnie]

Hi Dale,

Thank you so much for helping me on MS Access message
board. To answer your question, each EndDate on the
previous row should <= StartDate on next row. In this
case your "AND T2.SDate <= T.SDate as "GapNum" needs to
be AND T2.SDate >= T.EDate as GapNum" Are T1 and T2 new
tables for each nested subquery? For people on the
datasheet have many plans, the number we don't know yet.
There will be an unknown number of nested subqueries. How
do I display this situation?

Another silly question (forgive me, I just started to use
Access). How do you display the concatenation of T.LName
+ T.FName + T.DOB where only keep the first two digits of
T.LName, first two digits of T.FName, and T.DOB format in
01012000 from 01/01/2000 as a new column called PID?



Please help and many thanks.

 

[Dale Fye]

Winnie, T1 and T2 aliases that make it easier to refer to the table
name. When you do a sub-query, it is beneficial to use aliases,
especially in this case where you are trying to count the number of
times a combination of things occurs within the database, for a
particular value.

The following subquery computes the number of records in the table
that have the same LName, FName, and DOB that of the current record,
but which have a start date <= to the current record. The way it is
coded should be correct.

(SELECT COUNT(*)
FROM yourTable T2
WHERE T2.LNAME = T.LNAME
AND T2.FNAME = T.FNAME
AND T2.DOB = T.DOB
AND T2.SDate <= T.SDate) as GapNum,

If for the purposes of the query, you want to create a PID field that
contains just the left 2 characters of the lname, fname + dob then you
need to do something like:

SELECT LEFT(T.LNAME & ' ', 2) & LEFT(T.RNAME & ' ', 2) &
FORMAT(T.DOB, 'yyyymmdd') as PID

LEFT will return an error if the length argument is greater than the
actual length of the string or the string is NULL, so I added 2 spaces
to each field prior to using LEFT. You can change the date format to
whatever you want.

--
HTH

Dale Fye


Hi Dale,

Thank you so much for helping me on MS Access message
board. To answer your question, each EndDate on the
previous row should <= StartDate on next row. In this
case your "AND T2.SDate <= T.SDate as "GapNum" needs to
be AND T2.SDate >= T.EDate as GapNum" Are T1 and T2 new
tables for each nested subquery? For people on the
datasheet have many plans, the number we don't know yet.
There will be an unknown number of nested subqueries. How
do I display this situation?

Another silly question (forgive me, I just started to use
Access). How do you display the concatenation of T.LName
+ T.FName + T.DOB where only keep the first two digits of
T.LName, first two digits of T.FName, and T.DOB format in
01012000 from 01/01/2000 as a new column called PID?



Please help and many thanks.

-----Original Message-----
Winnie,

Is there any chance that the end date of one row will be greater than
the start date of another row?

I think the following should do what you are looking for.

SELECT T.LName, T.FName,
T.DOB, T.SDate, T.EDate,
(SELECT COUNT(*)
FROM yourTable T2
WHERE T2.LNAME = T.LNAME
AND T2.FNAME = T.FNAME
AND T2.DOB = T.DOB
AND T2.SDate <= T.SDate) as GapNum,
DateDiff('d', T.EDate,
(SELECT Min([StartDate])
FROM yourTable T1
WHERE T1.LNAME = T.LNAME
AND T1.FNAME = T.FNAME
AND T1.DOB = T.DOB
AND T1.SDate = > T.EDate)) as GapDays
FROM yourTable T

There are two nested sub-queries here so this could take a while to
run.

--
HTH

Dale Fye


Please help me with some idea for my difficulty of
finding the date gaps between unknown of number
of rows in the two columns (by days) for each
concatenation of "LastName + FirstName + DOB" as a unique
identifier. For each "LName" + "FName" + "DOB", there are
one or two or many plans (unknown number of start and end
dates for each person), I need to use the following row
of StateDate minus EndDate from the previous row to find
out how many gaps each person has and post the result to
another column called "Gap#" and the date difference by
days into another column "GapDays", If "Gap#" >= 2
for "LName" + "FName" + "DOB", delete; if "Gap#" <= 1
while "GapDays">45, also delete; then do the same thing
for next person and so on. Please advise. The data sheet
looks like the following

LName FName DOB PType SDate EDate Gap# GapDays
A a 1/1/2000 A 1/1/2000 3/31/2000
A a 1/1/2000 B 4/5/2000 5/31/2000
A a 1/1/2000 C 1/1/2002

B b 2/2/1950 B 1/15/1999 12/20/2002



Thank you for kind help.

Winnie Zhou


.