By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,491 Members | 1,868 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,491 IT Pros & Developers. It's quick & easy.

Query w/ date range and grouping

P: 78
I need to create a report showing bed utilization. I have the following fields and tables (main fields; additional info contained in tables):
apartment.id (primary key)
apartmentbed.apartmentid (join)
apartmentbed.id (primary key)
customerApartmentBedAssignment.apartmentbedid(join )
customerApartmentBedAssignment.startdate
customerApartmentBedAssignment.enddate

Sample data from customerApartmentBedAssignment Table:
id apartmentBedID startDate endDate
1......22......4/24/2006......8/12/2006
2......192......2/6/2006......4/6/2006
3......2......2/6/2006......5/6/2006

I want to know if a bed is being used, and show in the following format:

End result desired (weekly):
.......................................1/1/08 1/2/08 1/3/08 1/4/08 1/5/08 1/6/08 1/7/08
Totals Beds Avail (for each apartmentid)
Beds Used
Beds Unused


Example for apartmentid 154 for Jan 1-7/2008:
...................................1/1/08 1/2/08 1/3/08 1/4/08 1/5/08 1/6/08 1/7/08
Totals Beds Avail .......... 5........5........5........5........5........5.... ....5
Beds Used .................... 5........5........4........4........4........5.... ....5
Beds Unused ................ 0........0........1........1........1........0.... ....0

I tried to be as specific as possible. Please let me know if anything is unclear. Thanks for the help.
Feb 6 '08 #1
Share this Question
Share on Google+
1 Reply


P: 78
Bump .
Feb 11 '08 #2

Post your reply

Sign in to post your reply or Sign up for a free account.