can I do this in Crystal or do I need views? 1

[psimon1]

Hello

We run SQL Server 2005 and I know for a fact that, with a series of views, I can easily do this. I don't know if
a. I can do this in CRXI
b. this report will take excessively long.

In short, I'm looking for a systematic way of pulling the employee's second most recent status date. An employee can have 100 statuses or 1. I can do this with three views but has anyone figured out a reasonable way to do this within CR?

rank empno status date
1 100001 A1 1/1/2006
2 100001 T1 4/1/2007
3 100001 T2 4/15/2007

In this case, I would want to pull T1, 4/1/07.

Thanks!

 

[synapsevampire]

You can use a Command objetc in Crystal and paste in SQL.

Command objects are listed below your data source as Add Command.

The difference in time should be negligible.

Since we can't see your table layouts or have the SQL for the Views we can't address the efficacies or the 3 View method or doing so using a Command Object.

You can also cheat it by pulling all dates, grouping by the empno, then the date, and in the report header for the date place:

if next({table.empno}) = {table.empno} then
next({table.date})

Now the date group header will work as your detail display, suppress the details and the group footer.

-k

 

[psimon1]

Thank you. I was able to write a report for "max" date but not the second. I think that your "next" one might work. Very interesting...

Here's the code for the three views that I had in mind:

CREATE VIEW vwEMPLOYEE1
AS
select row_number() over (partition by EMPLOYEE ORDER BY DATE_STAMP) AS [RANK],
EMPLOYEE, FLD_NBR, A_VALUE AS "EMP-STATUS", TIME_STAMP,DATE_STAMP, BEG_DATE
from LAWAPP.HRHISTORY
WHERE FLD_NBR=20

GO
CREATE VIEW vwEMPLOYEE2
AS
SELECT MAX(RANK) AS MAXDATE, EMPLOYEE, EMP-STATUS, BEG_DATE
from vwEMPLOYEE1
GROUP BY EMPLOYEE, EMP-STATUS, BEG_DATE

go

CREATE VIEW vwEMPLOYEE3
AS
SELECT RANK-1 AS 2NDRANK, EMPLOYEE
FROM vwEMPLOYEE1
GROUP BY EMPLOYEE

***

My Crystal Report would join the 1st and 3rd views as an inner join to pull EMP-STATUS and BEG_DATE.