none
SQL 2008R2 - Help to evaluate timestamp

    Question

  • 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?


    Friday, November 01, 2013 12:19 AM

All replies