Help w/SQL

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am using MIcrosoft's code to begin with and tweaked it to fit my needs,
but
the code only works when the number is incrementing, I need help tweaking the
code to take into account that the number may decrease. Here is my sql:

SELECT Inactive1.RosterDate, Inactive1.Inactive, (Select Max(Inactive) from
tblClinicalLiasonAnalysis Where Inactive < Inactive1.[Inactive] ) AS
PrevInactive, [Inactive]-[PrevInactive] AS [Number], [Number]/[PrevInactive]
AS [Percent], Inactive1.Active, (Select Max(Active) from
tblClinicalLiasonAnalysis Where Active < Inactive1.[Active]) AS PrevActive,
[Active]-[PrevActive] AS NumberA, [NumberA]/[PrevActive] AS PercentA
FROM tblClinicalLiasonAnalysis AS Inactive1;


Like I said, everything works fine as long as Inactive and Active are
increasing from one record to the next, my dilema is how to take into account
a decrease from one record to the next.

Thanks
 
Dear Richard:

I have reproduced your query here, with some modifications to help me read
and unserstand it:

SELECT RosterDate, Inactive,
(SELECT Max(Inactive)
FROM tblClinicalLiasonAnalysis
WHERE Inactive < Inactive1.Inactive)
AS PrevInactive,
Inactive - PrevInactive AS Number,
Number / PrevInactive AS Percent,
Active,
(SELECT MAX(Active)
FROM tblClinicalLiasonAnalysis
WHERE Active < Inactive1.Active) AS PrevActive,
Active - PrevActive AS NumberA,
NumberA / PrevActive AS PercentA
FROM tblClinicalLiasonAnalysis AS Inactive1;

Making some assumptions about the data on which this operates, it seems you
have a table which is keyed by the RosterDate. However, the subqueries are
not correlated on the successive RosterDates, but on the Active/Inactive
values. Almost certainly you need to correlate on RosterDate.

Perhaps this will do what you wish:

SELECT RosterDate, Inactive,
(SELECT Inactive
FROM tblClinicalLiasonAnalysis T1
WHERE T1.RosterDate =
(SELECT MAX(RosterDate)
FROM tblClinicalLiasonAnalysis T2
WHERE T2.RosterDate < T.RosterDate))
AS PrevInactive,
Active,
(SELECT Active
FROM tblClinicalLiasonAnalysis T1
WHERE T1.RosterDate =
(SELECT MAX(RosterDate)
FROM tblClinicalLiasonAnalysis T2
WHERE T2.RosterDate < T.RosterDate))
AS PrevActive
FROM tblClinicalLiasonAnalysis AS Inactive1;

I have removed 4 columns you had used. You cannot refer to an aliased
column (PrevInactive or PrevActive) within a query where it is introduced.
You could write a new query referencing the above and do this, or you could
repeat the subquery that returns these values.

A way around this might be:

SELECT T.RosterDate, T.Inactive, T1.Inactive AS PrevInactive,
T.Inactive - T1.Inactive AS Number,
(T.Inactive - T1.Inactive) / T1.Inactive AS Percent,
T.Active, T1.Active as PrevActive,
T.Active - T1.Active AS NumberA,
(T.Active - T1.Active) / T1.Active AS PercentA
FROM tblClincalLiasonAnalysis T,
tblClinicalLiasonAnalysis T1
WHERE T1.RosterDate =
(SELECT MAX(T2.RosterDate)
FROM tblClinicalLiasonAnalysis T2
WHERE T2.RosterDate < T.RosterDate)

Please let me know how this works for you. There are some pitfalls doing
this in Jet that are difficult to predict.

Tom Ellison


Richard said:
I am using MIcrosoft's code to begin with and tweaked it to fit my needs,
but
the code only works when the number is incrementing, I need help tweaking the
code to take into account that the number may decrease. Here is my sql:

SELECT Inactive1.RosterDate, Inactive1.Inactive, (Select Max(Inactive) from
tblClinicalLiasonAnalysis Where Inactive < Inactive1.[Inactive] ) AS
PrevInactive, [Inactive]-[PrevInactive] AS [Number], [Number]/[PrevInactive]
AS [Percent], Inactive1.Active, (Select Max(Active) from
tblClinicalLiasonAnalysis Where Active < Inactive1.[Active]) AS PrevActive,
[Active]-[PrevActive] AS NumberA, [NumberA]/[PrevActive] AS PercentA
FROM tblClinicalLiasonAnalysis AS Inactive1;


Like I said, everything works fine as long as Inactive and Active are
increasing from one record to the next, my dilema is how to take into account
a decrease from one record to the next.

Thanks
Click to expand...
 
I have removed 4 columns you had used. You cannot refer to an aliased
column (PrevInactive or PrevActive) within a query where it is introduced.
Click to expand...
<snip>

PMFBI

I think you might be thinking of how SQL Server
handles aliased columns which is different than Jet.

In Jet, you can refer to an alias in an expression
of the SELECT clause that occurs "after" (to the
right of) the alias is defined. But that means there
cannot be any ambiguity in the columns of the
SELECT clause (field names/aliases).

SQL Server only considers aliases at the end
of the process. You can get all "loosy-goosy"
to the point it may make your head hurt, i.e.,
give one field an alias of another field in the
SELECT, and that other field can be aliased
as the first field;

but, at the cost that you cannot refer to those aliases
in an expression on down the line within the SELECT
clause.

Apologies again for butting in.

gary
 
Dear Gary:

You are probably right. Richard did not specify whether his query is for
Jet or for MSDE, both of which are included with Access.

I would only add that using constructions that presume the use of a certain
database engine over nearly all others will greatly reduce the portability
of your code. So, I would suggest this is a good "best practice" to not use
Jet specific constructions.

To the maximum extent possible, I try to code queries in my own projects and
for the newsgroups to be functional in both Jet and MSDE, and to be somewhat
portable to other query engines.

Tom Ellison
 
Hi Tom,

I don't know....

I always use Jet and cannot believe anyone
would ever want to use MSDE. 8-)

You are of course right about "best practice."

I wasn't going to say anything, but what you
say carries a lot of weight I believe on this
newsgroup. You are always thorough in your
analysis and explanations. If someone was learning
Access, they would do well to follow your
responses.

But they might not realize how Jet can differ
from MSDE.

I'm going to try hard to filter out such nitpicking
from now on. But if I do, will the filter be applied
before (MSDE) or after the join (Jet)? 8-)

take care,

gary
 
Dear Gary,

Well, what you say carries a lot of weight with me, for SURE!

Jet differs from MSDE, as do many other database engines. I'm not familiar
with such a large number, but I do try to strike some kind of "medium" that
promotes some degree of portability. SQL was supposed to be a standard, and
therefore somewhat portable. It has not lived up to that promise nearly to
the degree I'd like to see.

Gary, you have more than sufficient good will with me! I mean to show
absolutely no animosity here. Your comments are very welcome, and may
indeed be valuable to the OP, which must remain an overriding consideration.
Please chip in whenever and whatever you wish. While I can, and have,
defended my perspective, it is not the only one of value. What you
contributed is valuable as well.

If I seemed at all short with you, I guarantee it was only that I was in a
hurry.

My kindest regards, and great respect for your knowledge,
Tom Ellison
 
Back
Top