Show change in data

[mdj3366]

Hi there,

Using CRXI, SQL database. This is the data as it is pulling now:
Name Pay Code payment_date wages
Person1 160 03/18/2011 996.00
Person1 160 03/25/2011 996.00
Person1 160 04/01/2011 996.00
Person1 160 04/08/2011 996.00
Person1 160 04/15/2011 996.00
Person1 160 04/22/2011 996.00
Person1 160 04/29/2011 996.00
Person1 160 05/06/2011 996.00
Person1 162 05/13/2011 996.00

What i want is to only show payment dates 5/13 and 5/06 because of the change in the pay code. How can i accomplish this? Any help is greatly appreciated.

 

[lbass]

Try a section suppression formula like this:

not onlastrecord and
{table.person} = next({table.person}) and
{table.code} = next({table.code})

-LB

 

[mdj3366]

Thanks for the reply, LB. I am also getting records for those who did not have a pay code that changed. The pay code is a formula:

if {payment.Sub}in["Y",""] then "160" else
if {payment.Sub}="N" then "162"

The selection criteria is this:
{org.ORG_NAME} = "Plan" and
{payment.payment_date} <= {?Payment Date} and
{payment.status_lkup_id} in [1974.00, 2037.00]

 

[lbass]

Please show a sample of original data and then show the results you are getting with my formula.

-LB

 

[mdj3366]

Original data:

Name Pay Code Payment Date Gross Amount
person 1 160 04/08/2011 700.00
person 1 160 04/15/2011 700.00
person 1 160 04/22/2011 700.00
person 1 160 04/29/2011 700.00
person 1 160 05/06/2011 700.00
person 1 160 05/13/2011 700.00
person 1 160 05/20/2011 700.00
Person 2 160 04/08/2011 703.46
Person 2 160 04/15/2011 703.46
Person 2 160 04/22/2011 703.46
Person 2 160 04/29/2011 703.46
Person 2 160 05/06/2011 703.46
Person 2 160 05/13/2011 703.46
Person 2 160 05/20/2011 703.46

With your formula in the section expert:

Name Pay Code Payment Date Gross Amount
Person 1 160 05/20/2011 700.00
Person 2 160 05/20/2011 703.46
Person 3 160 05/13/2011 700.00

 

[mdj3366]

I forgot to include this in the section with your formula:
Person 4 160 04/29/2011 996.00
Person 4 162 05/13/2011 996.00

So the formula is somewhat working.

 

[lbass]

Insert a group on {table.person} which you can suppress if you want and then change the suppression formula to:

distinctcount({table.code},{table.person}) = 1 or
(
not onlastrecord and
{table.person} = next({table.person}) and
{table.code} = next({table.code})
)

-LB

 

[mdj3366]

Actually, what i posted last is incorrect. That should have read:
Person 4 160 05/06/2011 996.00
Person 4 162 05/13/2011 996.00

I do not need to look back any further than the most recent payment code changes.

Thanks.

 

[mdj3366]

Almost there. This is what i am getting now using the formula last suggested:

Name Pay Code Payment Date Gross Amount
Person 1 160 04/29/2011 996.00
Person 1 162 05/13/2011 996.00
Person 2 160 04/29/2011 884.40
Person 2 162 05/13/2011 884.40

Although both have payment dates of 5/6 and they both have 160, they are being skipped.

 

[lbass]

Is it sorted by payment date ascending?

-LB

 

[mdj3366]

No, it is grouoped on employee id, then sorted by name ascending.

 

[lbass]

Add a second sort on date ascending.

-LB

 

[mdj3366]

Perfect! Thank you very much, LB! You are truly awesome!!