Re: loop and recordset
- From: Frank <PremierSoftware@xxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Wed, 3 Jan 2007 04:06:01 -0800
This is an interesting problem. I'm not sure that I yet fully understand
exactly what is required. Does Lan wish to know the date span and/or
duration of each missing period or is he interested only in knowing that
there IS a missing period?
I ask because I like your SQL solution and can see how it might be modified
to identify Original_Recip_Ids where there is a one (or more) missing
period(s) without returning the date span and/or duration of each such
period. Indeed, I can see how it could be modified to deal with a recordset
which contained more than 2 of a Original_Recip_Id.
Could your SQL approach be modified to deal with such a recordset?
"onedaywhen" wrote:
.
Lan wrote:
below is my recordset. They are enrollment records. I want to identify
those members whose membership lapsed. In this case, ID 222, his membership
elaped three months from May to July. ID 333's membership elapsed 1 month.
I am trying to write the code that will generate me that information
(member ID)
Original_Recip_Id First_Dt_Of_Service Last_Dt_Of_Service
111 11/9/2006 11/30/2006
111 12/1/2006 12/31/2006
222 4/1/2006 4/30/2006
222 8/1/2006 8/26/2006
333 4/1/2006 4/30/2006
333 6/1/2006 6/10/2006
Forget looping through recordsets: that's a process. Instead, focus on
the end product. Do you want the set of missing periods? SQL is a
declarative language and requires a set-based mindset; looping through
recordsets uses a procedural mindset.
Short answer: Use the SQL language to return only the set of required
rows e.g. try this query:
SELECT E1.Original_Recip_Id,
DATEADD('D', 1, E1.Last_Dt_Of_Service) AS missing_period_start_date,
DATEADD('D', -1, E2.First_Dt_Of_Service) AS missing_period_end_date
FROM enrollment_2 AS E1, enrollment_2 AS E2
WHERE E1.Original_Recip_Id = E2.Original_Recip_Id
AND E1.First_Dt_Of_Service < E2.First_Dt_Of_Service
AND NOT EXISTS (
SELECT *
FROM enrollment_2 AS E3
WHERE E1.Original_Recip_Id = E3.Original_Recip_Id
AND DATEADD('D', 1, E1.Last_Dt_Of_Service) = E3.First_Dt_Of_Service
)
;
Long answer: Your SQL DDL (table design) shapes your SQL DML (queries).
You would appear you have a valid-time state ('history') table, which
provide some challenges in SQL DDL.
Your basic table:
CREATE TABLE enrollment_2 (
Original_Recip_Id INTEGER NOT NULL,
First_Dt_Of_Service DATETIME NOT NULL,
Last_Dt_Of_Service DATETIME NOT NULL
);
This table obviously needs some constraints.
It would seem you have defined a contiguous period as being where the
start date of the later period is one day greater than the end date of
the earlier period, where both have no time element (alternative views:
both are at midnight; both have a time value equal to the first time
granule). This is known in the SQL literature as closed-closed
representation.
Therefore, we need constraints on both datetime columns to ensure they
contain no time elements. I'll use CHECK constraints (easier to post
<g>) but you can use column-level Validation Rules:
ALTER TABLE enrollment_2 ADD
CONSTRAINT enrollment_2__First_Dt_Of_Service__first_time_granule
CHECK (First_Dt_Of_Service = DATEVALUE(First_Dt_Of_Service)
)
;
ALTER TABLE enrollment_2 ADD
CONSTRAINT enrollment_2__Last_Dt_Of_Service__first_time_granule
CHECK (Last_Dt_Of_Service = DATEVALUE(Last_Dt_Of_Service)
)
;
Personally, for the closed-closed representation I prefer to use the
*last* time granule for end dates e.g. the month of January would be
modelled using the values (#2007-01-01 00:00#, #2007-01-31 23:59:59#).
I find having no gaps in the data makes queries easier to write.
Snodgrass (see below) prefers the closed-open representation, where the
start date of the later period equals the end date of the earlier
period but I find this prevents the use of BETWEEN predicates.
The table needs a key. First the candidate keys:
ALTER TABLE enrollment_2 ADD
CONSTRAINT enrollment_2__candidate_key_1
UNIQUE (Original_Recip_Id, First_Dt_Of_Service)
;
ALTER TABLE enrollment_2 ADD
CONSTRAINT enrollment_2__candidate_key_2
UNIQUE (Original_Recip_Id, Last_Dt_Of_Service)
;
Note, however, that none of the above constraints prevents duplicates.
In short, preventing overlapping periods will ensure you have a
sequenced key. The only option here is to use a table-level CHECK
constraint:
ALTER TABLE enrollment_2 ADD
CONSTRAINT enrollment_2__no_overlapping_periods
CHECK (0 = (
SELECT COUNT(*)
FROM enrollment_2 AS E1, enrollment_2 AS E2
WHERE E1.Original_Recip_Id = E2.Original_Recip_Id
AND E1.First_Dt_Of_Service < E2.First_Dt_Of_Service
AND
(
E2.First_Dt_Of_Service < E1.Last_Dt_Of_Service
OR E2.Last_Dt_Of_Service < E1.Last_Dt_Of_Service
)
)
);
An alternative approach would be to *explicitly* model the periods
where the state is 'not enrolled', possibly using the maximum datetime
value (#2999-12-31 23:59:59#) as the 'infinite' end date, then you
could disallow non-contiguous periods using a constraint (hint: it
would look a lot like the above query). As you have found, allowing
non-contiguous periods and looking for missing periods after the fact
can be harder work.
Further reading:
Thinking in SQL
by Joe Celko
http://www.dbazine.com/ofinterest/oi-articles/celko5
Keeping Time
by Joe Celko
http://www.dbazine.com/ofinterest/oi-articles/celko10
Developing Time-Oriented Database Applications in SQL
by Richard T. Snodgrass
http://www.cs.arizona.edu/people/rts/publications.html
Here's some VBA code to reproduce the above table with constraints and
data, plus the proposed solution to the problem of finding missing
periods:
' ---<VBA code>---
Sub enrol_2()
'Kill "C:\DropMe.mdb"
Dim cat
Set cat = CreateObject("ADOX.Catalog")
With cat
.Create _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\DropMe.mdb"
With .ActiveConnection
' Create table
.Execute _
"CREATE TABLE enrollment_2 (" & vbCr & "Original_Recip_Id" & _
" INTEGER NOT NULL, " & vbCr & "First_Dt_Of_Service" & _
" DATETIME NOT NULL, " & vbCr & "Last_Dt_Of_Service" & _
" DATETIME NOT NULL" & vbCr & ")" & vbCr & ";"
' Create constraints
.Execute _
"ALTER TABLE enrollment_2 ADD" & vbCr & "CONSTRAINT" & _
" enrollment_2__First_Dt_Of_Service__first_time_granule" & vbCr &
"CHECK" & _
" (First_Dt_Of_Service = DATEVALUE(First_Dt_Of_Service)" & vbCr &
");"
.Execute _
"ALTER TABLE enrollment_2 ADD" & vbCr & "CONSTRAINT" & _
" enrollment_2__Last_Dt_Of_Service__first_time_granule" & vbCr &
"CHECK" & _
" (Last_Dt_Of_Service = DATEVALUE(Last_Dt_Of_Service)" & vbCr &
");"
.Execute _
"ALTER TABLE enrollment_2 ADD " & vbCr & "CONSTRAINT" & _
" enrollment_2__no_overlapping_periods " & vbCr & "CHECK" & _
" (0 = ( " & vbCr & "SELECT COUNT(*) " & vbCr & "FROM enrollment_2"
& _
" AS E1, enrollment_2 AS E2 " & vbCr & "WHERE E1.Original_Recip_Id"
& _
" = E2.Original_Recip_Id " & vbCr & "AND E1.First_Dt_Of_Service" &
_
" < E2.First_Dt_Of_Service " & vbCr & "AND " & vbCr & "( " & vbCr &
"E2.First_Dt_Of_Service" & _
" < E1.Last_Dt_Of_Service " & vbCr & "OR E2.Last_Dt_Of_Service" & _
" < E1.Last_Dt_Of_Service" & vbCr & ")));"
' Create data
.Execute _
"INSERT INTO enrollment_2 (Original_Recip_Id," & _
" First_Dt_Of_Service, Last_Dt_Of_Service)" & _
" " & vbCr & "VALUES (111, #2006-11-09 00:00:00#, #2006-11-30" & _
" 00:00:00#)" & vbCr & ";"
.Execute _
"INSERT INTO enrollment_2 (Original_Recip_Id," & _
" First_Dt_Of_Service, Last_Dt_Of_Service)" & _
" " & vbCr & "SELECT DT1.Original_Recip_Id,
DT1.First_Dt_Of_Service," & _
" DT1.Last_Dt_Of_Service " & vbCr & "FROM (" & vbCr & "SELECT 111"
& _
" AS Original_Recip_Id, #2006-12-01 00:00:00#" & _
" AS First_Dt_Of_Service, #2006-12-31 00:00:00#" & _
" AS Last_Dt_Of_Service " & vbCr & "FROM enrollment_2" & vbCr &
"UNION" & _
" ALL" & vbCr & "SELECT 222, #2006-04-01 00:00:00#," & _
" #2006-04-30 00:00:00#" & vbCr & "FROM enrollment_2" & vbCr &
"UNION" & _
" ALL" & vbCr & "SELECT 222, #2006-08-01 00:00:00#," & _
" #2006-08-26 00:00:00#" & vbCr & "FROM enrollment_2" & vbCr &
"UNION" & _
" ALL" & vbCr & "SELECT 333, #2006-04-01 00:00:00#," & _
" #2006-04-30 00:00:00#" & vbCr & "FROM enrollment_2" & vbCr &
"UNION" & _
" ALL" & vbCr & "SELECT 333, #2006-06-01 00:00:00#," & _
" #2006-06-10 00:00:00#" & vbCr & "FROM enrollment_2" & vbCr & ")"
& _
" AS DT1;"
' Proposed solution
Dim rs
Set rs = .Execute( _
"SELECT E1.Original_Recip_Id, " & vbCr & "DATEADD('D'," & _
" 1, E1.Last_Dt_Of_Service) AS missing_period_start_date," & _
" DATEADD('D', -1, E2.First_Dt_Of_Service)" & _
" AS missing_period_end_date" & vbCr & "FROM enrollment_2" & _
" AS E1, enrollment_2 AS E2" & vbCr & "WHERE E1.Original_Recip_Id"
& _
" = E2.Original_Recip_Id" & vbCr & "AND E1.First_Dt_Of_Service" & _
" < E2.First_Dt_Of_Service" & vbCr & "AND NOT EXISTS" & _
" (" & vbCr & "SELECT *" & vbCr & "FROM enrollment_2 AS E3" & vbCr
& "WHERE" & _
" E1.Original_Recip_Id = E3.Original_Recip_Id" & vbCr & "AND" & _
" DATEADD('D', 1, E1.Last_Dt_Of_Service)" & _
" = E3.First_Dt_Of_Service" & vbCr & ");")
MsgBox rs.GetString
End With
Set .ActiveConnection = Nothing
End With
End Sub
' ---</VBA code>---
Jamie.
--
- Follow-Ups:
- Re: loop and recordset
- From: Lan
- Re: loop and recordset
- From: onedaywhen
- Re: loop and recordset
- References:
- loop and recordset
- From: Lan
- Re: loop and recordset
- From: onedaywhen
- loop and recordset
- Prev by Date: Re: Horizontal Scroll bar on List box
- Next by Date: Re: Using a Module as an Event Procedure
- Previous by thread: Re: loop and recordset
- Next by thread: Re: loop and recordset
- Index(es):
Relevant Pages
|
Loading
