BPMonk
asked on
MaxDate and Second to max date
Hi all,
Ok Ill provide 2 records to start the ball rolling
Worksrollnumber Surname Latest Pay Date Amount Last Pay Date Amount
123456 Bloggs 21/10//08 890 21/09/08 890
789101 Doe 21/010/08 1000 21/02/08 650
Ok, the 2 records above are what I want to see, and a comparison will be done to highlight differences in monthly pension payments. What I need to do is find the Max Date of all employees i.e their Latest pay Date and then the Second to max date, which means when a last entry was made, so it is 21/09/08 for Bloggs and 21/02/08 for Doe. I have written a query to get the Max Date for each row. Now I need to find the second to max date. I have tried multiple nested queries and also created a MaxDate view and queried that but I only got 'too many values' What I am trying to do is get a maxdate of the set of dates which arent equal the current max date. Sounds simple but it has been a ball ache :/
Thanks
Ok Ill provide 2 records to start the ball rolling
Worksrollnumber Surname Latest Pay Date Amount Last Pay Date Amount
123456 Bloggs 21/10//08 890 21/09/08 890
789101 Doe 21/010/08 1000 21/02/08 650
Ok, the 2 records above are what I want to see, and a comparison will be done to highlight differences in monthly pension payments. What I need to do is find the Max Date of all employees i.e their Latest pay Date and then the Second to max date, which means when a last entry was made, so it is 21/09/08 for Bloggs and 21/02/08 for Doe. I have written a query to get the Max Date for each row. Now I need to find the second to max date. I have tried multiple nested queries and also created a MaxDate view and queried that but I only got 'too many values' What I am trying to do is get a maxdate of the set of dates which arent equal the current max date. Sounds simple but it has been a ball ache :/
Thanks
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.