Results 1 to 4 of 4
  1. #1
    Star Lounger
    Join Date
    Jun 2009
    Posts
    59
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Counting Records

    Hello,
    I'm trying to calculate the number of days per month by property. Easy enough, if I didn't have to worry about stays that overlap months (for example, the first record below):





    Property, RateSchedule, ArrivalDate, StayLength (the number of days for the stay)
    1, TBD, 7/30/2011, 7
    2, TBD, 7/15/2011, 4
    1, TBD, 7/14/2011, 1


    Ideally, I'd like to see the below results:


    Property 1
    July: 3
    Aug: 5
    Property 2
    July: 4


    Because the first records "Arrival Date" is equal to 7/30/2011, only 2 days should be counted in July, while the other 5 days should be accounted for in August. From a SQL perspective, is there an easy way to accomplish the above?

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    Attached find a zipped rentals.mdb file. This nut took me a while to crack but I have a workable solution. The solution took a custom function and 2 queries, one to calculate the days in each month and one to total them by property.

    The custom function will handle rentals within a single month, across 2 months and even across multiple months. It will NOT handle rentals that span a year, i.e. Dec 11 through Jan 12. This could be included if needed.

    In the qryCntDaysInMonth I only included July-Sept for demo purposes but you need only add a column for each month of the year by copying one of the existing months and changing the name and month value.
    Then add the new fields to the qryTotalDaysByMonth query.

    You can write a report using the qryTotalDaysByMonth query as a source to get the exact output you want.

    Please post back if you have further questions.
    Attached Files Attached Files
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    Star Lounger
    Join Date
    Jun 2009
    Posts
    59
    Thanks
    0
    Thanked 0 Times in 0 Posts
    This rocks! I would be curious to see it span years if possible.

  4. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts

    Improved Version

    Sorry, I just can't get my mind working to get it to wrap years.
    However, attached is an improved version which has the queries showing all 12 months and fixed a problem that showed up in the user defined function when I did the expansion.
    Attached Files Attached Files
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •