Compare previous & suppress unwanted records - is this report possible 2

[urbanhim]

I need to create a report which shows all the clients that have amended their address for currentdate-1.

The table I am using, AUDIT_ACCOUNT_ADDRESS, has multiple records, because every time the client amends their address a new AUDIT_ACCOUNT_ADDRESS record is created.

So for each client, we could have 30 address records. However, its only the most recent two that I need to see. Based on MODIFIED_DATE descending and CREATED_DATE descending.

So when I sort using that method, it works out that:

The 1st record is their current address.
The 2nd record is their previous address.

However, not all addresses are actual changes as if the client simply clicks in their address but doesnt make a change, a new address record will still be created, but it will be exact, ie, the 1st and 2nd record in my sort will be exact. I therefore need to only show the current, and previous address where there is a change.

Here is an example of my data:

Account_ID Address_1 Town County PCode Modified Created
Y0602 Tiger Hse London London SE1 22:03:01 22:04:01
Y0602 Tiger Hse City London SE1 22:03:01 22:02:01
Y0602 Tiger Hse City London SE1 22:03:01 22:01:45
Z0302 LA Reds LA LA LA 17:03:01 17:45:45
Z0302 LA Reds LA LA LA 17:03:01 16:07:45


So I have the sort order correct, the top line being the correct address, the 2nd line being the previous address.

I have also used a count and group expert as recommened elsewhere on this forum, which suppresses where there is only 1 Account_ID (new accounts which we dont want on report) and where there > 2 (which in the above example would remove the third line) - This leaves me with just 2 lines per client, current and previous address.

Now, as mentioned though, not all addresses are actual changes, so I need to ignore those that are no different. For instance, in the above example, Z0302 should not appear as its not an actual change.

I have been using previous command, which is checking each address line with its previous entry, i thought this was unique to Account_id, but soon realised that the previous just shows the previous record printed in report, which might be the address for another client!

Having done SHOW SQL, this is what I have:

SELECT "AUDIT_ACCOUNT_ADDRESS"."ACTION",
"AUDIT_ACCOUNT_ADDRESS"."ADDRESS_ID",
"AUDIT_ACCOUNT_ADDRESS"."ADDRESS_1",
"AUDIT_ACCOUNT_ADDRESS"."ADDRESS_2",
"AUDIT_ACCOUNT_ADDRESS"."TOWN",
"AUDIT_ACCOUNT_ADDRESS"."POSTCODE",
"AUDIT_ACCOUNT_ADDRESS"."COUNTRY",
"AUDIT_ACCOUNT_ADDRESS"."CREATED_DATE",
"AUDIT_ACCOUNT_ADDRESS"."MODIFIED_DATE",
"AUDIT_ACCOUNT_ADDRESS"."ACCOUNT_ID"
FROM "CONTENT"."AUDIT_ACCOUNT_ADDRESS"
"AUDIT_ACCOUNT_ADDRESS" WHERE ("AUDIT_ACCOUNT_ADDRESS"."MODIFIED_DATE">=TO_DATE
('19-03-2008 00:00:00', 'DD-MM-YYYY HH24:MI:SS') AND "AUDIT_ACCOUNT_ADDRESS"."MODIFIED_DATE"<TO_DATE
('20-03-2008 00:00:00', 'DD-MM-YYYY HH24:MI:SS'))
ORDER BY "AUDIT_ACCOUNT_ADDRESS"."ACCOUNT_ID",
"AUDIT_ACCOUNT_ADDRESS"."CREATED_DATE" DESC, "AUDIT_ACCOUNT_ADDRESS"."MODIFIED_DATE" DESC


Grateful for any advice on this report.

Thanks you





UrbanHim
Crystal XI Report Writer
London

[small]What's the best cure for a hangover?... Heavy drinking the night before!![/small]

 

[lbass]

Create a formula {@concat}:

{table.Account_ID}+" "+{table.Address_1}+" "+{table.Town}+" "+{table.County}+" "+{table.PCode}

Then change the group selection to:

distinctcount({@concat},{table.AcctID}) > 1

Also go to the section expert and add a formula like this:

{@concat} = previous({@concat}) or
{#cntwingrp} > 2

...where {#cntwingrp} is a running total that uses a distinctcount of {@concat}, evaluate for each record, reset on change of group AcctID.

-LB

 

[urbanhim]

Sorry for not responding to this ealier, I have been away. I just want to say your recommendation LB is absolutely brilliant! Thank you so much!



UrbanHim
Crystal XI Report Writer
London

[small]What's the best cure for a hangover?... Heavy drinking the night before!![/small]