Forum Discussion

HansHamm's avatar
HansHamm
Qrew Assistant Captain
6 years ago
Evan,
The 2nd example is closer to what is needed.
If I was going to lay it out it would be something like this...

Show me a count of nights for associates in hotel rooms from 10/21 thru 10/26
Would give me 6 nights for both Joe and Sally
  14-Oct 15-Oct 16-Oct 17-Oct 18-Oct 19-Oct 20-Oct 21-Oct 22-Oct 23-Oct 24-Oct 25-Oct 26-Oct
Joe X X X X X X X X X X X X X
Sally               X X X X X X
Show me a count of nights for associates in hotel rooms from 10/14 thru 10/26
Would give me 13 nights for Joe 6 nights for Sally
  14-Oct 15-Oct 16-Oct 17-Oct 18-Oct 19-Oct 20-Oct 21-Oct 22-Oct 23-Oct 24-Oct 25-Oct 26-Oct
Joe X X X X X X X X X X X X X
Sally               X X X X X X


------------------------------
Hans Hamm
------------------------------
EvanWestbrook's avatar
EvanWestbrook
Qrew Cadet
6 years ago
Hans,

Mark has some good ideas below. I'm responding in a different branch of this thread just to keep our suggestions straight for you.

To accomplish what is mentioned in Example 2, you could create a library table called something like "Weeks" (or whatever your consistent time unit is.) Here's how you might set it up:
  •  Fields: [Week Description], [Week Start Date], and [Week End Date].
  • Relate "Weeks" to "Hotel Stays" table one to many (one week can have many hotel stays.)
  • Set up an automation to automatically link a [Hotel Check-In Date]  [Hotel Check-Out Date] pair on "Hotel Stays" to the parent Weeks table. (See Brian's solution on response 3 for an example)
  • Set up report on "Hotel Stays" table to filter based on "Week" for the hotel stay instead of the start and end date.
    • i.e. "Show me the # of nights in Week Ending 10/20" instead of "Show me the # of nights between "10/14" and 10/20"
If a truly dynamic feature is what you're after, ​​​Mark's solution would be good.

------------------------------
Evan Westbrook
PRIME Developer
Harder Mechanical Contractors Inc.
Portland OR
------------------------------