Extracting MAXIMUM Insert Date detail

[shipcd]

We have a scenario where we have user data sorted by MONTHID and INSERTDATE which illustrates supervisor hierarchy as of that change date.

Example:

MONTHID INSERTDATE USERID NAME SUPERVISOR
200401 1/1/2004 X123 SMITH JILL JONES
200401 1/15/2004 X123 SMITH BETTY SMITH

Above sample shows supervisor change occuring mid-month.

We need to extract the maximum insert date PER MONTH PER USER and use that as a baseline to then group for further reporting.

The supervisor structure is 5 levels, and although the original sort must be for individual user to gain the maximum, the ultimate grouping must be top down. (Region, VP, Manager, Supervisor, User).

We have no funding for database enhancements, so a boolean flag on MAX date is out. We need to find a strictly Crystal Reporting solution to our dilemna.

Any ideas?

 

[dgillz]

Right click on the insert date, select insert summary, use a maximum() summary, and goup by user. Do this again and group by month.

Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports

http://www.trianglepartners.com

 

[lyanch]

Use a subselect in your where clause:

t1.INSERTDATE = (select max(t2.INSERTDATE ) from TABLEITSIN t2 where t1.USERID = t2.USERID
and datepart(month, t1.INSERTDATE) = datepart(month, t2.INSERTDATE)
and datepart(year, t1.INSERTDATE) = datepart(year, t2.INSERTDATE) )

Then just select the rest of your data the way you want it..

Lisa

 

[shipcd]

Thanks for the ideas - we've tried both and still run into the error message that the MAXIMUM cannot be applied. Error message is as follows: "This function cannot be used because it must be evaluated later." We've tried to apply WHILEPRINTINGRECORDS - still doesn't work. Further ideas?

 

[dgillz]

Isn't the INSERTDATE a simple database field?

The approach I asked you to take does not require any formulas, so why would you be getting a formula error?

Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports

http://www.trianglepartners.com

 

[lbass]

lyanch's solution is the way to go in this situation, I think. You would not get the error message you mentioned if you had implemented this correctly. You should go to database->show SQL query and add the clauses she mentions at the end of the where clause. Let's say that your table containing the date is called "Employee". Then the clauses would look something like:

where
Employee.`INSERTDATE` = (select max(t2.`INSERTDATE`) from Employee t2 where Employee.`USERID` = t2.`USERID`
and datepart(month, Employee.`INSERTDATE`) = datepart(month, t2.`INSERTDATE`)
and datepart(year, Employee.`INSERTDATE`) = datepart(year, t2.`INSERTDATE`) )

You should be able to edit the SQL query in 8.0 or 8.5. In 9.0, you might have to approach this differently.

-LB