cancel
Showing results for 
Search instead for 
Did you mean: 

Can I select values based on the earliest Date?

Former Member
0 Kudos

I need to be able to Select a value based on the earlisst date that an employee worked in a specific State. For example:

Our employess travel from state to state. I need to create a report that says for year 2013 show me the first state the the employee worked in. The employee may have worked in 5 state but I only want to see the first one for all employees in 2013.

Can this be done? I am faqirly new to Crystal, so I am not great at formulas yet.

Thanks,

Rick

Accepted Solutions (1)

Accepted Solutions (1)

abhilash_kumar
Active Contributor
0 Kudos

Hi Richard,

You'll be spoilt for choice when it comes to this requirement! You have many ways to do this:

Using Groups and Group Selection formula:

1) Create a Group on the Employee ID field

2) Go to Report > Selection Formula > Group and use this code:

Minimum({Date_field}, {Employee ID}) = {Date_field}

3) Suppress the details section and move everything to the Group Header or Group Footer section

4) Add a Record Selection Formula to bring back records only for year 2013.

Using SQL Expressions:

1) Under Field Explorer, look for an option called 'SQL Expressions'. Right-click and select New > Give it a name (First Date) and use this code:

(

     Select Min("Date_field") from Table

     where "Employee ID" = "Table.Employee ID"

)

2) Drag and drop it on the details section

3) Go to Report > Selection Formula > Record and use this code:

{Date_field} = {%First Date}

AND

year({Date_field}) = 2013

Hope this helps.

-Abhilash

abhilash_kumar
Active Contributor
0 Kudos

And of course the second one is a far better performing option than the first one because it only returns the first state the employee worked for in that year.

-Abhilash

abhilash_kumar
Active Contributor
0 Kudos

OR, you can create a SQL Query that brings back the results you need and use that to create the report using the 'Add Command' option.

-Abhilash

Former Member
0 Kudos

Thank you. That worked well.

Rick

Former Member
0 Kudos

Abhilash,This Sql option worked, But there is one problem.I need to add another table Dept , linking with dept_no.After adding this table I am getting duplicates rows. How to remove these.

Please help

Answers (1)

Answers (1)

Former Member
0 Kudos

Hi,

May you try this.

The firstly, you create a new store procedure.

CREATE PROCEDURE [dbo].[FirstStateForEmployee]

          @Year INT

AS

BEGIN

          SET NOCOUNT ON;

          SELECT T2.empID, T2.firstName + ' ' + T2.lastName AS EmployeeName, T2.startDate, T2.workState

          FROM dbo.OCST T1 WITH(NOLOCK)

          INNER JOIN

          (

                    SELECT TOP 1 T2.empID, T2.workState, T2.firstName, T2.lastName, T2.startDate

                    FROM dbo.OHEM T2 WITH(NOLOCK)

                    WHERE YEAR(ISNULL(T2.startDate, 0)) = @Year

                    ORDER BY T2.StartDate ASC

          )T2 ON T2.workState = T1.Code

          --WHERE T2.empID = @EmployeeCode

END

GO

GO

The secondly, you create a new crystal report and set datasouce by the store procedure.

The thirdly, you create a new group section, set empID value in this group and then drop and drap values to show in detail section.

I hope to help you.