CODEWITHSUNDEEP
c#mysql
carol1287
carol1287

Reputation: 395

Getting the correct price per activity using MySql

We are developing a card check-in system in C# with MySql. Depending on the time the user checks-in, a different amount is charged to the user. We have a price table that specifies the price during the entire day per hour. When the user checks-in, I need to be able to know how much that check-in cost. Below are the tables I have:

ACTIVITY TABLE:

CARD_ID      CLOCK

1            5/26/2013 10:40:47 AM
2            5/26/2013 10:48:50 AM
3            5/26/2013 12:00:00 AM
4            5/26/2013 14:40:20 AM

PRICE TABLE

TIME         PRICE
00:00        4
01:00        4
02:00        4
03:00        4
04:00        4
05:00        4
06:00        4
07:00        4
08:00        6
09:00        6
10:00        6
11:00        6
12:00        6
13:00        7
14:00        7
15:00        7
16:00        7
17:00        7
18:00        8
19:00        8
20:00        4
21:00        4
22:00        4
23:00        4

I am trying to figure out the best approach here to get this. Is there a way I can get this with a query? Once I know the price, I can be able to insert this into a transaction table. Thanks a lot.

Upvotes: 0

Views: 61

Answers (2)

Aron
Aron

Reputation: 15772

select 
    price,
    CARD_ID,
    clock
from activity a
join price p on  p.time <= TIME(a.clock)
  AND 
TIME(a.clock) < ADDTIME(p.time, '01:00:00')

This might not work correctly when your time is close to the hour mark since I have no idea how MySQL time compare works then and to be honest I find MySQL pretty sketchy in general.

Upvotes: 1

sarwar026
sarwar026

Reputation: 3821

Try this:

SELECT a.card_id, a.clock, p.price
FROM activity a
INNER JOIN price p
ON HOUR(a.clock) = HOUR(p.time)

Upvotes: 2

Related Questions