Jump to content

Booking sytem query for debit


joacef

Recommended Posts

I'm trying to do a bookingsystem.

If I book the time 06:30:00-19:00:00 will it return.

06:30:00-07:00:00

07:00-00-16:00:00

16:00:00-19:00:00

It will also return the hours between the times and debit code. Does anyone have an example?

 

CREATE TABLE IF NOT EXISTS `times` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `starttime` time NOT NULL,

  `stoptime` time NOT NULL,

  `debit` varchar(4) COLLATE utf8_unicode_ci NOT NULL,

  PRIMARY KEY (`id`)

) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=11 ;

 

INSERT INTO `times` (`id`, `starttime`, `stoptime`, `debit`) VALUES

(6, '00:00:00', '06:00:00', 'ot2'),

(7, '06:00:00', '07:00:00', 'ot1'),

(8, '07:00:00', '16:00:00', 'ord'),

(9, '16:00:00', '20:00:00', 'ot1'),

(10, '20:00:00', '24:00:00', 'ot2');

Link to comment
Share on other sites

I will try to explain better.

 

If I make an booking for the time 06:30:00-19:00:00 I will run a query on the table times.

 

It will find that between 06:30:00-07:00:00 there will be an debit for 30 minutes with the debitcode ot1

(7, '06:00:00', '07:00:00', 'ot1')

 

It will find that between 07:00:00-16:00 there will be an debit for 9 hours with the debitcode ord

(8, '07:00:00', '16:00:00', 'ord')

 

It will find that between 16:00:00-19:00:00 there will be an debit for 3 hours with the debitcode ot1

(9, '16:00:00', '20:00:00', 'ot1')

 

 

Link to comment
Share on other sites

I have solved the problem.

 

DROP TABLE IF EXISTS tmp_result;

 

CREATE temporary TABLE IF NOT EXISTS tmp_result (

  `starttime` time ,

  `stoptime` time ,

  `debit` varchar(4),

  `start` time ,

  `stop` time COLLATE utf8_unicode_ci

 

  );

 

INSERT INTO tmp_result

SELECT starttime, stoptime, debit,

    IF ('04:30:00' < starttime, starttime, TIME('04:30:00')) AS start,

    IF ('19:00:00' > stoptime, stoptime, TIME('19:00:00')) AS stop

FROM times

 

    HAVING

        (starttime <= start AND stoptime >= stop AND start < stop)

    OR

        (starttime <= stop AND stoptime >= stop AND start < stop)

    OR 

        (starttime >= start AND stoptime <= stop AND start < stop)

;

 

SELECT *, ROUND(TIME_TO_SEC(TIMEDIFF(stop,start))/3600,2) AS debitcnt FROM tmp_result;

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.