I have a situation where I have to do a count based on certain set of conditions as stated below and I want to use LODs (if possible) for this.
Every time a reservation changes,reservation key is constant but the change Id gets updated and is greater than the previous value.Example: For the reservation key of 1000, there are entries for 01/09, 01/08,01/07,01/06,01/05 but I have to count only the one's for 01/09 since they are the latest ( change ID is max).
Second, I also need to count all the reservations on/before 01/09 that did not change.Example: Reservation key: 4688713(Change ID: 10), 1321388 (Change ID: 13),2460336(Change ID: 6),218459(Change ID: 4),179858((Change ID: 2).
I need to sum up both the counts above to figure out the the number of reservations for that date: 01/09 here. I have a feeling that I can use LODs here but haven't been able to nail the logic yet. If there are other better ways please suggest. Thank you for your time. Work book attached for your review.