Display most recent records from group data 1

[robbclaxton]

I have a table of data collected at regular intervals from a series of customers. I have managed to display the data in groups by customer, then by unique entity (tank). Each Tank record now has 5 lines of data (one for every date collected). I want to be able to only view the most recent record and not the early values.. Any ideas?

My SQL is

SELECT "CUSTOMER_SITES"."SITE_CUST_ID", "CUSTOMER_SITES"."SITE_STATUS", "CUSTOMER_SITES"."SITE_NAME", "CUSTOMER_SITES"."SITE_ID", "CUSTOMER_SITES"."SITE_OTHER_ID", "TANKS_LINES"."TANK_NUM", "TANKS_LINES"."TANK_HIGH_LEVEL_LIMIT", "TANKS_LINES"."TANK_OVERFILL_LIMIT", "TANKS_LINES"."TANK_MAX_VOLUME", "TANKS_LINES"."TANK_ACCU_HIGH_LEVEL_LIMIT", "TANKS_LINES"."TANK_ACCU_OVERFILL_LIMIT", "TANKS_LINES"."TANK_ACCU_FULL_VOLUME", "TANKS_LINES"."TANK_ACCU_MAX_VOLUME", "BMS_ACCUCHART_STATUS"."BAS_USER_ENABLE", "BMS_ACCUCHART_STATUS"."BAS_DATE_TIME"
FROM ("VRSC"."BMS_ACCUCHART_STATUS" "BMS_ACCUCHART_STATUS" INNER JOIN "VRSC"."TANKS_LINES" "TANKS_LINES" ON ("BMS_ACCUCHART_STATUS"."BAS_SITE_ID"="TANKS_LINES"."TANK_SITE_ID") AND ("BMS_ACCUCHART_STATUS"."BAS_TANK_NUM"="TANKS_LINES"."TANK_NUM")) INNER JOIN "VRSC"."CUSTOMER_SITES" "CUSTOMER_SITES" ON "TANKS_LINES"."TANK_SITE_ID"="CUSTOMER_SITES"."SITE_ID"
WHERE "CUSTOMER_SITES"."SITE_STATUS"='Y' AND "CUSTOMER_SITES"."SITE_CUST_ID"='S700684'
ORDER BY "CUSTOMER_SITES"."SITE_NAME", "TANKS_LINES"."TANK_NUM", "BMS_ACCUCHART_STATUS"."BAS_DATE_TIME

 

[lbass]

What field tells you which record is the most recent? Is it the bas_date_time field? If so, go to report->selection formula->record and enter:

{BMS_ACCUCHART_STATUS.BAS_DATE_TIME} = maximum({BMS_ACCUCHART_STATUS.BAS_DATE_TIME},{TANKS_LINES.TANK_NUM})

If you need to calculate across groups, be sure to use running totals, since non-group selected records are still IN the report, though not displayed, and thus would contribute to the more ususal summaries. Running totals will automatically ignore non-group selected records.

-LB