Hi,
we have an system for trouble-tickets and I need some help to evaluate/analyze the timestamp and determine if it´s during business hours or not - for billing purposes.
Our business hours is from 8:30 AM to 06:00 PM, all other times should be considered as an 'Extra-service' and then use another price/rates.
I´ll try to explain a little bit our scenario.
TBL_Customer
############
id
Name
...
TBL_Tickets
###########
id
TimeStamp_Creation
TimeStamp_Finish
...
TBL_Tickets_Interactions
########################
id
Ticket_IDTimeStamp_Start
TimeStamp_Finish
Method (Local/Remote)
Description
Charge (y/n)
Status
TBL_Interval_Prices
###################
id
Interval_Start
Interval_End
Business_Day (y/)
Method (Local/Remote)
Minute_Price
TBL_Tickets_Billing
###################
id
Ticket_ID
Interval_Start
Interval_End
BusinessHours (y/n)
Amount_Time
Minute_Price
Final_Price
----------------------------------
Let´s say that we have only one customer and he have a ticket starting '01/11/2013 05:00 PM' and ending '01/11/2013 07:00 PM', for that case during billing phase we should input two entries on 'TBL_Tickets_Billing', one regarding ticket 'starting '01/11/2013 05:00 PM' and ending '01/11/2013 06:00 PM' with price during business days/hours/method and another entry regarding the same ticket but 'starting '01/11/2013 06:00 PM' and ending '01/11/2013 07:00 PM' with price during non business hours/method.
----------------------------------
>>>>>>>>> There is a way to accomplish that using only SQL query or should I use another code (ASP/ASP.NET) to get basis information, evaluate and then add to billing table?
No comments:
Post a Comment