[AccessD] Calculate Pool Times

Scott,

Thanks for the example.  I'll put it against the 'Cartesian join' approach 
with a list of times...and see if I can find some benifits in one or the 
other.

Thanks,

Mark A. Matte


>From: "Scott Marcus" <marcus at tsstech.com>
>Reply-To: Access Developers discussion and problem 
>solving<accessd at databaseadvisors.com>
>To: "Access Developers discussion and problem 
>solving"<accessd at databaseadvisors.com>
>Subject: Re: [AccessD] Calculate Pool Times
>Date: Fri, 14 Apr 2006 08:29:20 -0400
>
>I have a solution that uses only SQL statements...
>
>Tables needed:
>
>Discount
>========
>  DiscountID           AutoNumber
>  Description          Text
>  StartDateTime        Date/time
>  EndDateTime          Date/Time
>  RatePerHour          Currency   (amount to discount off standard rate)
>
>TableTime
>=========
>  TableTimeID          Autonumber
>  Person               Text
>  StartDateTime        Date/Time
>  EndDateTime          Date/Time
>
>Queries:
>
>StandardRate
>============
>
>SELECT TableTimeID,
>        Person,
>        "Standard Time" AS Description,
>        StartDateTime,
>        EndDateTime,
>        3 AS RatePerHour,
>        DateDiff("n",StartDateTime,EndDateTime)/60 AS Hours
>        RatePerHour*Hours AS Amount
>FROM TableTime
>
>PlayerDiscounts
>===============
>
>SELECT T.TableTimeID,
>        T.Person,
>        D.Description,
>        IIF(D.StartDateTime<T.StartDateTime,
>            T.StartDateTime,D.StartDateTime) AS Start,
>        IIF(D.EndDateTime<T.EndDateTime,
>            D.EndDateTime,T.EndDateTime) AS End,
>        D.RatePerHour,
>        DateDiff("n", IIf(D.StartDateTime<T.StartDateTime,
>                          T.StartDateTime,
>                          D.StartDateTime),
>                      IIf(D.EndDateTime<T.EndDateTime,
>                          D.[EndDateTime],
>                          T.EndDateTime))/60 AS Hours,
>        RatePerHour*Hours AS Amount
>FROM TableTime T, Discount D
>WHERE (D.StartDateTime<=T.EndDateTime) AND
>       (D.EndDateTime>=T.StartDateTime)
>ORDER BY T.TableTimeID
>
>PlayerCharges
>=============
>
>SELECT * FROM StandardRate
>UNION
>SELECT * FROM PlayerDiscounts
>
>TotalCharges
>============
>
>SELECT TableTimeID,
>        Person,
>        Sum(Amount) AS Total
>FROM PlayerCharges
>GROUP BY TableTimeID, Person
>
>Scott Marcus
>Computer Programmer
>TSS Technologies Inc.
>www.tss.com
>513-772-7000 x1113
>
>-----Original Message-----
>From: Mark A Matte [mailto:markamatte at hotmail.com]
>Sent: Thursday, April 13, 2006 1:46 PM
>To: accessd at databaseadvisors.com
>Subject: [AccessD] Calculate Pool Times
>
>Hello All,
>
>I need some help with my logic here.  The app will track how long some
>one
>is on a pool table and charge accordingly.  I want to have the specials
>calculate automatically.  For example:  Someone shoots from 16:00 to
>23:00.
>
>The normal rate is 3dph.  From 17:00 to 19:00 is free and from 20:00 to
>22:00 is 1.5dph.  These are only the specials today...there might only
>be
>one tomorrow.
>
>I started with a specials table that would have the day,start,end, and
>rate.
>
>   Each special is its own record.  In the end it would charge:
>
>16:00-16:59 3$
>17:00-18:59 0$
>19:00-19:59 3$
>20:00-21:59 1.5$
>22:00-23:00 3$
>
>I guess I could loop through minute by minute and compare the time to
>the
>each special... and sum that way...but that didn't seem right?
>
>Any ideas/suggestions on an approach would be greatly appreciated.
>
>Thanks,
>
>Mark A. Matte
>
>
>--
>AccessD mailing list
>AccessD at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/accessd
>Website: http://www.databaseadvisors.com
>--
>AccessD mailing list
>AccessD at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/accessd
>Website: http://www.databaseadvisors.com
>
>
>
>
>
>
>NOTICE:  This electronic mail transmission is for the use of the named 
>individual or entity to which it is directed and may contain information 
>that is privileged or confidential.  If you are not the intended recipient, 
>any disclosure, copying, distribution or use of the contents of any 
>information contained herein is prohibited.  If you have received this 
>electronic mail transmission in error, delete it from your system without 
>copying or forwarding it, and notify the sender of the error by replying 
>via email or calling TSS Technologies at (513) 772-7000, so that our 
>address record can be corrected.  Any information included in this email is 
>provided on an “as is” and “where as” basis, and TSS Technologies makes no 
>representations or warranties of any kind with respect to the completeness 
>or accuracy of the information contained in this email.


>--
>AccessD mailing list
>AccessD at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/accessd
>Website: http://www.databaseadvisors.com