Identifying Missing Date Ranges

[androo235]

I have a table that is for utility bills. Simplified, each line has an identifier for the property the bill refers to, has a from date and a to date defining the period covered by the bill and a bill amount. Sometimes the bill amount is null. My problem is that there are some periods which are not covered by a line with a bill amount. That is if I group the table by property number and date ranges ascending by from date using only lines which have a non-null bill amount then I get a nice table with no duplicated periods, however there are (a few) gaps.




Now, sometimes the gap is covered exactly or partially by a from to date row in the original data that has a null bill amount. These are legitimate rows and the data is structured in such a way that where this is the case then an adjacent row with a bill amount will include the charge for the period that has a null bill amount. So, I need a table that accounts for all days that are covered in the data without duplication. However, there will sometimes be a real gap in the data, that is a period that is not covered at all and I need to identify those real gaps too.


Here's what seems to me a possible approach.


Step One: First create a table using a grouping query which consolidates all lines with a bill amount by the from to date. I have this.




SELECT Bill_Summary.[Property No], Bill_Summary.[Bill Account No], Bill_Summary.[Bill Date], Bill_Summary.[Property Address], Bill_Summary.[Bill From Date], Bill_Summary.[Bill To Date], [Bill_Summary]![Bill To Date]-[Bill_Summary]![Bill From Date] AS [Days USe], Bill_Summary.Consumption, Bill_Summary.[Total Milk Charge], Bill_Summary.[Total Honey Charge], Bill_Summary.[Total Milk Standing Charge], Bill_Summary.[Total Honey Standing Charge], Bill_Summary.[Bill Amount]
FROM Bill_Summary
GROUP BY Bill_Summary.[Property No], Bill_Summary.[Bill Account No], Bill_Summary.[Bill Date], Bill_Summary.[Property Address], Bill_Summary.[Bill From Date], Bill_Summary.[Bill To Date], [Bill_Summary]![Bill To Date]-[Bill_Summary]![Bill From Date], Bill_Summary.Consumption, Bill_Summary.[Total Milk Charge], Bill_Summary.[Total Honey Charge], Bill_Summary.[Total Milk Standing Charge], Bill_Summary.[Total Honey Standing Charge], Bill_Summary.[Bill Amount]
HAVING (((Bill_Summary.[Bill Amount]) Is Not Null))
ORDER BY Bill_Summary.[Property No], Bill_Summary.[Bill From Date];




Step Two: Then identify from the created table for each property where there are gaps (So result would be a list, Property No, Start date of Gap, End Date of Gap). How do I do that?


Step Three: Then go back to the original data and look for lines (or possibly more than a single line) that exactly fills that gap (or partially fill its, trickier, but never overfills - there can be no overlaps). If I knew how to do Step Two I think I have some clue how to do this step - at least for exact matches for the gaps.


Step Four: Union query the results of steps one and three to create the table that accounts for all days that are covered in the data without duplication.


Step Five: Like step two, identify the real gaps in the final table. Get s list of them by Property No and From Date - To date.


That's it.


Thanks in advance for any help.

[rpeare]

if you are still working on this if you can post a sample database with some garbage data and say exactly how you want your result data to look I can have a go at it.