Dear Heri:
I reproduce your query here, reformated as I prefer to see it for study:
SELECT TOP 100 PERCENT EmployeeNo, StartDate AS StartDate,
Amount AS oldsalary, EndDate AS EndDate, BenefitCode,
(SELECT TOP 1 MAX([amount])
FROM EMPBENEFITS T2
WHERE T2.employeeno = T.employeeno
AND startdate >= '2005-06-01'
AND startdate <= '2006-06-30') AS newsalary
FROM dbo.empBenefits T
WHERE (StartDate IN (SELECT TOP 1([startdate])
FROM EMPBENEFITS T1
WHERE T1.employeeno = T.employeeno
AND Benefitcode <> 'HON'
AND startdate >= '2005-06-01'
AND startdate <= '2006-06-30'))
GROUP BY EmployeeNo, Amount, BenefitCode, StartDate, EndDate, Amount
ORDER BY EmployeeNo, StartDate
You have made considerable changes to what I suggested, some of which make
little sense to me. My comments about this follow:
1. You have used TOP 100 PERCENT lin line 1 above. This is fairly
meaningless in this context. For someone studying this code, it is a
distraction to no purpose. Remember, it may be you coming back to read and
maintain this code in a year or two. Why have such distractions?
2. You Alias StartDate as StartDate. This does nothing. Same for EndDate.
Any excess code that does nothing just slows the process of reading and
digesting code. I recommend you avoid that.
3. The TOP 1 of an aggregate MAX is meaningless. MAX can return only one
value.
4. You have dropped the inner subquery from the subquery returning
newsalary. This makes a presumption, that salaries always increase. If
anyone ever takes a pay cut, this will malfunction!
5. You are limiting the results to the period June 1, 2005 through June 30,
2006 (a 13 month period). At least, it looks like you wish to do this. May
I recommend this? Use date literals. The date literals would be
represented as #06/01/2005# and #06/30/2006#. You must use MM/DD/YYYY for
date literals. However, what will happen when you need to use this query a
year from now? Wouldn't you want the dates to advance with time? Wouldn't
it be better to do this under the user's control, or even automatically? Or
do you intend to have someone change the query every year? And would it
ever be the case that someone would need to switch back and forth between
"this year" and "last year" in viewing these results?
6. In line 9 you use IN instead of =. The query returns only one row of
one column. IN and = are equivalent in this situation, but = is a simpler
read. IN is something I would reserve for situations where there could be
more than one row, just for the purpose of readability.
7. In line 12 you filter Benefitcode <> "HON". I expect his filter should
be applied to the earlier subquery as well if you want to eliminate rows
from consideration.
8. You have many columns in the GROUP BY. In part, this is my fault, as I
omitted something.
In addition to the above, there are challenges getting the nested subqueries
working in Access. So, I'm making this suggestion. Send me a copy of your
database and I'll work out these things. First, make a copy of the database
and rename the extension from MDB to XXX. Zip up the file. Then attach it
to an email to me (e-mail address removed)
I also want to know what results you expect to see, especially for an
employee who has 3 rows in the table.
Tom Ellison
Hi Tom,
Got your query right thank you so much I included it in my Select
Statement. However, I have this problem with my query. This query
gets the MIN and MAX date and get the OLDSALARY and NEWSALARY.
However, how can I check if there is a record inbetween my specified
date range ? Like for Example my date range is startdate >=
'2005-06-01' AND startdate <= '2006-06-30' and there is a record with
stardate of '2006-01-01' with the same employeeno it is in between my
specified date my old salary should be the amount from 2006-01-01 and
not the amount from 2005-06-01 becuase it is the employee salary before
the MAX date my NEWSALARY will be having no problem because I can get
the MAX stardate but my OLDSALARY is wrong because there is still a
record in between which should be my OLDSALARY value.
This query Get the Min and Max of Salary inbetween dates I want to get
the OldSalary Between Max and Min in case there is inbetween Record
how can I check it in addition to this query ? Date format is
smalldatetime
As you can see I inluded your query and it helps me a lot i can ow
display my record in a single row.
Hope you can help me again in my problem ... Kindly modify my query
below to get my desired output.
SELECT TOP 100 PERCENT EmployeeNo, StartDate AS StartDate, Amount AS
oldsalary, EndDate AS EndDate, BenefitCode,
(SELECT TOP 1 MAX([amount])
FROM EMPBENEFITS T2
WHERE T2.employeeno = T.employeeno AND startdate >= '2005-06-01' AND
startdate <= '2006-06-30') AS newsalary
FROM dbo.empBenefits T
WHERE (StartDate IN
(SELECT TOP 1([startdate])
FROM EMPBENEFITS T1
WHERE T1.employeeno = T.employeeno AND Benefitcode <> 'HON' AND
startdate >= '2005-06-01' AND startdate <= '2006-06-30'))
GROUP BY EmployeeNo, Amount, BenefitCode, StartDate, EndDate, Amount
ORDER BY EmployeeNo, StartDate
Again Thank you