Hi, softcom-ga:
The subject line implies that MS SQL Server 2000 is the platform here,
as it was in some previous questions.
Although your description of the tables omits any discussion of their
primary keys, it is apparently essential to your purpose to be able to
"join" the tables by using some field(s) on the Permits records to
identify corresponding records in the Inspections table. Let's
"define" the tables before we continue this discussion.
For the sake of clarity I will describe two simplified tables with
some DDL (data definition language) in SQL:
CREATE TABLE Inspections
( InspectionDescription varchar(10),
InspectionMonths int
)
GO
CREATE TABLE Permits
( Permit int,
PermitDate datetime,
Comments varchar(255),
Inspection1 varchar(10) Null,
Inspection2 varchar(10) Null,
Inspection3 varchar(10) Null,
Inspection4 varchar(10) Null,
Inspection1Date datetime Null,
Inspection2Date datetime Null,
Inspection3Date datetime Null,
Inspection4Date datetime Null
)
GO
From the limited description given, the best choice for "key" to
identifying records in the Inspections table seems to be the
InspectionDescription field. The sample Permits record shown in your
question appears to give us a matching value (if any) in the
Inspection1 field (resp. Inspection2, Inspection3, and Inspection4
fields).
Therefore as a start we could construct a "join" query matching
Permits and Inspections records as follows regarding the first
"inspection":
SELECT * from Permits, Inspections
WHERE Permits.Inspection1 = Inspections.InspectionDescription
Here the "*" means "give me all the fields" (on both of the matching
pair of records from Permits and Inspections). Of the fields you want
from the output query, this would give us [Permit] and [PermitDate]
but not the other three fields (which require a bit of "computation").
It also omits the logic of the expiration dates which restricts
selection of records, and it only performs a match on the first of the
"inspection" fields.
The first of the "computations" concerns the expiration date, which is
also tied to the logic and the input parameters (month and year) which
determine the selection of records. Your description is to "return
all the Permits that will expire in a given month and year."
For this purpose we wish to add (to PermitDate) the smallest number of
months of any of the four possible "inspections" noted on a Permits
record. Such a quantity can be expressed by a "dependent query" that
returns a single value, in our case:
SELECT min(I.InspectionMonths)
FROM Inspections I
WHERE (Inspection1Date is Null
AND I.InspectionDescription = Inspection1)
OR (Inspection2Date is Null
AND I.InspectionDescription = Inspection2)
OR (Inspection3Date is Null
AND I.InspectionDescription = Inspection3)
OR (Inspection4Date is Null
AND I.InspectionDescription = Inspection4)
That is my interpretation of your requirement, that a permit expires
when the earliest of any unfulfilled inspections specified therein
will expire.
We assemble the various computations and logical conditions into the
following SQL query, where the input parameters @Month and @Year are
to be replaced by actual values of month and year:
SELECT Permit, PermitDate,
DateAdd(month,
( SELECT min(I.InspectionMonths)
FROM Inspections I
WHERE (Inspection1Date is Null
AND I.InspectionDescription = Inspection1)
OR (Inspection2Date is Null
AND I.InspectionDescription = Inspection2)
OR (Inspection3Date is Null
AND I.InspectionDescription = Inspection3)
OR (Inspection4Date is Null
AND I.InspectionDescription = Inspection4)
), PermitDate) as ExpiryDate,
LTRIM(RTRIM(
Coalesce(Inspection1,'') + ' ' +
Coalesce(Inspection2,'') + ' ' +
Coalesce(Inspection3,'') + ' ' +
Coalesce(Inspection4,'')
)) as Inspection,
CASE WHEN CHARINDEX('Excavation',Comments) > 0
THEN 'Y'
ELSE 'N'
END as Letter
FROM Permits
WHERE DatePart(month, DateAdd(month,
( SELECT min(I.InspectionMonths)
FROM Inspections I
WHERE (Inspection1Date is Null
AND I.InspectionDescription = Inspection1)
OR (Inspection2Date is Null
AND I.InspectionDescription = Inspection2)
OR (Inspection3Date is Null
AND I.InspectionDescription = Inspection3)
OR (Inspection4Date is Null
AND I.InspectionDescription = Inspection4)
), PermitDate)) = @Month
AND DatePart(year, DateAdd(month,
( SELECT min(I.InspectionMonths)
FROM Inspections I
WHERE (Inspection1Date is Null
AND I.InspectionDescription = Inspection1)
OR (Inspection2Date is Null
AND I.InspectionDescription = Inspection2)
OR (Inspection3Date is Null
AND I.InspectionDescription = Inspection3)
OR (Inspection4Date is Null
AND I.InspectionDescription = Inspection4)
), PermitDate)) = @Year
I tested this query under SQL Server 2000 with your "sample data"
cases above, and it appears to work as you would expect it to from the
"sample output".
regards, mathtalk-ga |
Request for Answer Clarification by
softcom-ga
on
03 Dec 2002 08:12 PST
Hi Mathtalk-ga
I haven't gotten it to work yet, but it looks very promising. The
'Excavation' needs to be any of the InspectionDescriptions that are in
the Inspections table. Would I just replace 'Excavation' with the
field name?
Will the statement find 'Excavation' in the Comments field if there is
other text there? I'm looking through my SQL book, but it does have
the CHARINDEX, I'm assuming that's like an instr command in vb.
The month are just numbers right (January=1)?
Thanks
|
Clarification of Answer by
mathtalk-ga
on
03 Dec 2002 08:35 PST
Hi,
The "calculation" I implemented for [Letter] is this requirement:
"I need to check the comments field and see if the word 'Excavation'
is anywhere there and if so, in a field called 'Letter' put 'Y', or if
the word is not found, then it would say 'N'"
You are correct that CHARINDEX is like function INSTR in VB. It
returns the starting position of the search expression within the
searched string if found, and otherwise returns 0. Therefore if you
wished to search for more than one word, e.g. 'Framing' as well as
'Excavation', you could replace:
WHEN CHARINDEX('Excavation',Comments) > 0
by the more complicated expression:
WHEN CHARINDEX('Excavation',Comments) > 0
OR CHARINDEX('Framing',Comments) > 0
or if you wish:
WHEN CHARINDEX('Excavation',Comments)
+ CHARINDEX('Framing',Comments) > 0
Your suggestion for putting the field name InspectionDescription (from
the Inspections table) would require some rewriting. As it stands the
reference to that field from within the CASE statement for the
[Letter] field would not work. Please explain the logic more fully;
what I implemented was narrowly drawn to search only for 'Excavation'
as originally asked, and I'm not sure what the more general search
should be.
The month and year parameters are indeed numbers, like 1 for January
and 2003 for next year. I used the @Month and @Year notation because
this would be the syntax if you were to write a stored procedure with
this query (to allow for passing the parameters in).
regards, mathtalk-ga
|
Request for Answer Clarification by
softcom-ga
on
03 Dec 2002 08:57 PST
Hi again
I was able to get it to work with 'excavation'.
Whatever Inspection it returns needs to be the one it looks for in
Comments.
So if it returns Framing, then it needs to look for framing in the
comments field.
Sorry about that, I was just using Excavation as an example. There
are about 15 records in the Inpsection table and if any of them are
pass due, then it needs to check the Comments field.
The reason for this is so that we know if a letter has been sent.
Once I have the selected data, I'll have a button on my VB app that
will allow the user to create a delimited text file with which they
can use as a data source for mail merge in MS Word or whatever. Once
they hit the button, I'm also going to update the Comments field with
the Inspection and current date, indicating that a letter has been
sent to the client regarding there overdue inspection, so that next
time the [Letter] value will be 'Y'.
Hope that helps
|
Clarification of Answer by
mathtalk-ga
on
03 Dec 2002 14:07 PST
Let's see if I can restate your requirement:
The field [Letter] should be 'Y' if and only if there exists an
Inspections record
- whose InspectionDescription is in one of the Permit.InspectionN
fields (N = 1,2,3,4), for which the corresponding
Permit.InspectionNDate field is Null,
- whose InspectionMonths value agrees with the minimum value
determined elsewhere (so that the expiration date for that type of
inspection falls into the given month and year),
- and such that the InspectionDescription appears in the Comments
field.
Otherwise the field [Letter] should be 'N'.
regards, mathtalk-ga
|
Request for Answer Clarification by
softcom-ga
on
03 Dec 2002 15:13 PST
Hi mathtalk
It sounds like you have the right idea.
Just to clarify with the original example:
If the Month and year were May 2003 and the data was as follows
18567-Nov 1, 2002-Property undergoing renovation-Framing-null or
""-null or ""-null or ""-null-null-null-null
then the result would be
[Pernit][PermitDate][ExpiryDate][Inspection][Letter]
18567-Nov 1, 2002-May 1, 2003-Framing-N
The [Letter] field would be Y only if Framing was in the Comments
field.
So whatever value is returned as [Inspection] will be looked for in
the Comments field and if found, then [Letter] will be 'Y', if not
then 'N'
I hope that clarifies things
|
Clarification of Answer by
mathtalk-ga
on
03 Dec 2002 20:52 PST
It clarifies things enough for me to begin making the change. However
I think there are potentially multiple Inspections records that are
"returned". Perhaps you were alluding to this when you initially
wrote:
"The one last thing is that the all the Inspection fields need to be
combined, so that there is one field for Inspection."
While I took this to mean combine all the values in Inspection1,
Inspection2, Inspection3, and Inspection4, it now seems likely that
you meant only to combine those which have not been completed or even
just those which are going to expire in the given month and year.
If it is possible that several Inspections records have the same
[InspectionMonths] value, then it will also be possible for more than
one kind of permitted inspection to expire in the same month. It is
even possible that two different [InspectionMonths] values might give
rise to expiration dates that fall into the same month. For example,
suppose we have:
Framing 6
Runoff 5
in the Inspections table, and [PermitDate] is Sep. 30, 2002. Then
both types lead to an expiration date in Septembe of 2002 (because
Friday the 30th of February does not exist.
Is it possible you want multiple rows return in the query recordset?
regards, mathtalk
|
Request for Answer Clarification by
softcom-ga
on
04 Dec 2002 09:35 PST
As far as I know there shouldn't be any inspections with the same
number of months.
ie framing 2
electrical 2
But I hadn't thought about your point regarding two coming up in the
same month as because of February.
Is it difficult to return mutliple rows?
I can live with it if it returns the first one that meets the
criteria, that being the lower number of months. So for your last
scenario, returning runoff.
I really appreciate your thoroughness. Thanks
|
Clarification of Answer by
mathtalk-ga
on
04 Dec 2002 09:38 PST
Hi, softcom-ga:
I have revised the section of the SQL statement which defines the
output field [Letter] so that all four possible inspection fields
(1,2,3,4) are checked in the Comments (if the corresponding
inspection's permit expires in the given month).
I also include at the outset some SQL statements which, when executed
at the same time as the query itself, will declare and assign values
to the parameters @Month and @Year which we have used previously.
This allows one to test the query more easily by changing the assigned
values through these initial statements (rather than by editing the
entire statement).
DECLARE @Month int, @Year int
SELECT @Month = 1
SELECT @Year = 2003
SELECT Permit, PermitDate,
DateAdd(month,
( SELECT min(I.InspectionMonths)
FROM Inspections I
WHERE (Inspection1Date is Null
AND I.InspectionDescription = Inspection1)
OR (Inspection2Date is Null
AND I.InspectionDescription = Inspection2)
OR (Inspection3Date is Null
AND I.InspectionDescription = Inspection3)
OR (Inspection4Date is Null
AND I.InspectionDescription = Inspection4)
), PermitDate) as ExpiryDate,
LTRIM(RTRIM(
Coalesce(Inspection1,'') + ' ' +
Coalesce(Inspection2,'') + ' ' +
Coalesce(Inspection3,'') + ' ' +
Coalesce(Inspection4,'')
)) as Inspection,
CASE
WHEN CHARINDEX(Inspection1,Comments) > 0
AND Inspection1Date is Null
AND EXISTS (SELECT * from Inspections I
WHERE I.InspectionDescription = Inspection1
AND DatePart(month, DateAdd(month,
I.InspectionMonths,
PermitDate)) = @Month
AND DatePart(year, DateAdd(month,
I.InspectionMonths,
PermitDate)) = @Year
)
THEN 'Y'
WHEN CHARINDEX(Inspection2,Comments) > 0
AND Inspection1Date is Null
AND EXISTS (SELECT * from Inspections I
WHERE I.InspectionDescription = Inspection2
AND DatePart(month, DateAdd(month,
I.InspectionMonths,
PermitDate)) = @Month
AND DatePart(year, DateAdd(month,
I.InspectionMonths,
PermitDate)) = @Year
)
THEN 'Y'
WHEN CHARINDEX(Inspection3,Comments) > 0
AND Inspection1Date is Null
AND EXISTS (SELECT * from Inspections I
WHERE I.InspectionDescription = Inspection3
AND DatePart(month, DateAdd(month,
I.InspectionMonths,
PermitDate)) = @Month
AND DatePart(year, DateAdd(month,
I.InspectionMonths,
PermitDate)) = @Year
)
THEN 'Y'
WHEN CHARINDEX(Inspection4,Comments) > 0
AND Inspection1Date is Null
AND EXISTS (SELECT * from Inspections I
WHERE I.InspectionDescription = Inspection4
AND DatePart(month, DateAdd(month,
I.InspectionMonths,
PermitDate)) = @Month
AND DatePart(year, DateAdd(month,
I.InspectionMonths,
PermitDate)) = @Year
)
THEN 'Y'
ELSE 'N'
END as Letter
FROM Permits
WHERE DatePart(month, DateAdd(month,
( SELECT min(I.InspectionMonths)
FROM Inspections I
WHERE (Inspection1Date is Null
AND I.InspectionDescription = Inspection1)
OR (Inspection2Date is Null
AND I.InspectionDescription = Inspection2)
OR (Inspection3Date is Null
AND I.InspectionDescription = Inspection3)
OR (Inspection4Date is Null
AND I.InspectionDescription = Inspection4)
), PermitDate)) = @Month
AND DatePart(year, DateAdd(month,
( SELECT min(I.InspectionMonths)
FROM Inspections I
WHERE (Inspection1Date is Null
AND I.InspectionDescription = Inspection1)
OR (Inspection2Date is Null
AND I.InspectionDescription = Inspection2)
OR (Inspection3Date is Null
AND I.InspectionDescription = Inspection3)
OR (Inspection4Date is Null
AND I.InspectionDescription = Inspection4)
), PermitDate)) = @Year
Please continue to review my questions about the possibilities of
multiple "expiring" inspections within the same month so that we can
be clear about the requirements for these situations. In coding the
changes above I have opted to set [Letter] to 'Y' if any of the
"descriptions" appear in the Comments. However your business rules
are not clear to me, and it seems at least plausible that it should be
'Y' only if _all_ such expiring inspections appear in the Comments.
In other words, if there are supposed to be separate letters for each
inspection type, perhaps the omission of any one of them should set
[Letter] to 'N'.
regards, mathtalk-ga
|
Clarification of Answer by
mathtalk-ga
on
04 Dec 2002 09:48 PST
One more point I feel should be raised about the situation with
multiple inspections. In your original post you say that:
"The one last thing is that the all the Inspection fields need to be
combined, so that there is one field for Inspection."
In my implementation I simply combined all the inspection fields into
one field, as you specified. It occurs to me, however, that you may
intend for only the expiring inspections to be combined in this field,
or only those which have not been completed. Please clarify what you
want to combined in the output field [Inspection].
regards, mathtalk-ga
|
Request for Answer Clarification by
softcom-ga
on
04 Dec 2002 10:13 PST
Hi mathtalk-ga
That's correct only the expired inspections.
What I meant was that I didn't want the query to output
[Inspection1],[Inspection2],[Inspection3], and [Inspection4] fields,
instead I wanted a single field [Inspections] which I thought would
only contain one inspectiondescription. But as you've pointed out the
result could be more than one. I need only one in the [inspection]
field because the [Letter] field indicates whether or not a letter has
been sent regarding the particular Inspection.
Therefore there either needs to be multiple records returned when 2 or
more inspection expire in a month or just return the one that expires
first (being the one with the lower number of months)
I hope that helps.
|
Clarification of Answer by
mathtalk-ga
on
04 Dec 2002 11:16 PST
Thanks for the clarification. Given that context, I think I will feel
more comfortable with my answer if I rework the entire query so that
it returns the earliest expiring inspection, but with multiple rows if
more than one expires on the same date.
In the case of the Sep. '02 to Mar. '03 expirations devised earlier,
this would mean any inspections expiring on Mar. 1st would appear
individually in the results, but an inspection expiring later in the
month (e.g. on Mar. 30 in the example) would not appear at all.
regards, mathtalk-ga
|
Request for Answer Clarification by
softcom-ga
on
04 Dec 2002 15:22 PST
Is there anything else that you need from me?
|
Clarification of Answer by
mathtalk-ga
on
04 Dec 2002 20:21 PST
Hi, softcom-ga:
Here is the revised query:
SELECT Permit, PermitDate,
DateAdd(month, InspectionMonths, PermitDate) as ExpiryDate,
InspectionDescription as Inspection,
CASE WHEN CHARINDEX(InspectionDescription,Comments) > 0
THEN 'Y'
ELSE 'N'
END as Letter
FROM Permits, Inspections
WHERE ((Inspection1Date is Null
AND InspectionDescription = Inspection1)
OR (Inspection2Date is Null
AND InspectionDescription = Inspection2)
OR (Inspection3Date is Null
AND InspectionDescription = Inspection3)
OR (Inspection4Date is Null
AND InspectionDescription = Inspection4)
)
AND InspectionMonths =
( SELECT min(I.InspectionMonths)
FROM Inspections I
WHERE (Inspection1Date is Null
AND I.InspectionDescription = Inspection1)
OR (Inspection2Date is Null
AND I.InspectionDescription = Inspection2)
OR (Inspection3Date is Null
AND I.InspectionDescription = Inspection3)
OR (Inspection4Date is Null
AND I.InspectionDescription = Inspection4)
)
AND @Month =
DatePart(month,
DateAdd(month, InspectionMonths, PermitDate)
)
AND @Year =
DatePart(year,
DateAdd(month, InspectionMonths, PermitDate)
)
As far as whether I need something more from you, I call your
attention to the following point. I wrote in my initial answer:
"That is my interpretation of your requirement, that a permit expires
when the earliest of any unfulfilled inspections specified therein
will expire."
Without feedback from you, I have no way of knowing if this is the
correct logic from the standpoint of "business rules" for which you
are the expert. I can point out an implication of this, however:
The query as now revised will _not_ return records for Permits which
have unfulfilled inspections that expired in _earlier_ months than the
one given by parameters @Month and @Year.
Example: Suppose that in addition to having two inspections that
expire on Mar. 1, 2003 and one on Mar. 30, 2003, a Permits record also
has an inspection that expires on Apr. 1, 2003. If the parameters are
set for March of 2003, then two records will be returned (the two
which expire on Mar. 1). If the parameters are set for April of 2003,
then no records will be returned.
regards, mathtalk-ga
|
Request for Answer Clarification by
softcom-ga
on
05 Dec 2002 10:01 PST
Hi mathtalk-ga
As I mentioned before I thought that there would only be a maximum of
one expiry inititally until you pointed out the february scenario.
Anyway, I think that you have it right. I'm away until Monday, so I
can't test it out until then, but I'm sure it's correct.
Thanks
|
Clarification of Answer by
mathtalk-ga
on
05 Dec 2002 10:23 PST
Hey, softcom-ga:
That's music to my ears! I would point out that there are two ways
that a Permits record could have two expiring inspections in the same
month. Besides the "February scenario" (which would only occur
roughly once in a hundred times, given a uniform distribution of
permit dates), there is also the case of two inspections having the
same number of "inspection months" (which might not be possible, given
your actual Inspections recordset).
best wishes, mathtalk-ga
|
,
0 Comments
)