[AccessD] Calculate Pool Times

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.