well, whenever you use any of the aggregate functions(MIN,MAX,COUNT)you have to have one or more GROUP BY column(s).

IF you group by just EMPID, you can only end up getting the MIN/MAX PrimaryRater column.

if you group by both EMPID and PrimaryRater, you don't get the "end Date" you were looking for according to your sample data, so im not sure if your expected results is incorrect, or there's more to the question

Lowell (12/10/2013)if you group by both EMPID and PrimaryRater, you don't get the "end Date" you were looking for according to your sample data, so im not sure if your expected results is incorrect, or there's more to the question

Why not? It seems correct to me.

Luis C.Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

Lowell (12/10/2013)if you group by both EMPID and PrimaryRater, you don't get the "end Date" you were looking for according to your sample data, so im not sure if your expected results is incorrect, or there's more to the question

Why not? It seems correct to me.

Lius, it was his expected results that made me wonder: he said his expected results for one of the rows was this data:

12345 01/01/1966 B12345 07/17/1966

, but since i grouped by PrimaryRater, and there's only one row, the min/max dates are teh same value (01/01/1966)

that's why i thought either his expected results might be incorrect, or there's some biz rule not yet described, like 'if there's no [?date], use a different row?

Lowell--There is no spoon, and there's no default ORDER BY in sql server either.Actually, Common Sense is so rare, it should be considered a Superpower. --my son

SELECT EMPID,CONVERT(VARCHAR,Min(CONVERT(DATETIME,EffectiveDate)),107) AS StartDate, PrimaryRater,CONVERT(VARCHAR,MAX(CONVERT(DATETIME,EffectiveDate)),107) AS EndDate FROM ReportingTable GROUP BY EMPID, PrimaryRater

1. The EffectiveDate is the sysdate of the update. It does not distinguish between the EffectiveDate of change for either the Primary Rater/Reviewer.2. The only way to determine who was actually changed is to compare the IDs of the PrimaryRater and PrimaryReviewer at the time of the change with the previous PrimaryRater or PrimaryReviewer.

So, in this example the EffectiveDate of 01/01/1966 is the effective date of both Rater and Reviewer because there was no record prior. EffectiveDate 07/17/1966 is the EffectiveDate of a new set of Rater/Reviewers. Thus the Start and End dates of the previous are:

B67890 is the most Current Reviewer based on MAX date 07/12/2013A12345 is the most Current Rater based on Start Date of 07/17/1966B12345 & E67890 are the most Previous set based on EffectiveDate 01/01/1966 with no Previous sets.

The example you provided is excellent for gaining the date sets for Raters but not for adding in the most current Reviewer.

What modification would add? Thanks in advance for your considerations and help on this matter.