Hi, I am creating a Crystal report and I need to select information between two tables. The second table tableB is actually audit trial information which I need to compare against the first tableA and from that work out the differences for Quater1, Quarter 2 etc information.
At moment I have a problem after I use an inner join I have more than one row of data from the same row item from tableA. This is working correctly as it is showing is showing all the different times and data each item in tableA was updated. So if one item was updated 5 times in a year it will show 5 rows of data with timestamps for each time. But I need to only show one unique row depending on which date I need.
So for one of my query’s I need to know only the latest updated item. And then I need to know what was the last item update up to a certain date for Quater1, Quater2 etc.
There is something else I should mention. To get the information I require from tableB i have to use 2 PK and FK to join the tables. So I join with both ReviewID and then a RecordID from tableA and link those to table 2 (AUDIT) ReviewID and RecordID.
One table holds all the data items (TableA) that I want to report on. I then need to retrieve Audit history from another table (AUDIT).
I would like to create a select statement that would choose the latest time stamped item, so that only one row show for each of the tableA items. The problem is that the TableA may be null or it could have 1 to many values linking to the TableA reviewID and recoredID row.
Please see attached example data:
There are three tabs, TableA, Audit table, and a Inner join of the two tables. On the third tab, you will see 2 of the table values are coloured in blue were the rows of information are the same however the timestamp and also the scores of the data are different. This is only a small sample of the data, so the problem only shows once. However I want to be able to report only the latest instance of that data up to a certain date. For example here I would like to compare the Current scoreA with last score which would be OLDSCOREA. This is current score of 12 and old score of 3. But trying to get the data to only show once is important. How do I restrict the other duplicate Audit table items appearing from other date periods?