Jump to content

Booking sytem query for debit


joacef
 Share

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.

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.

 Share

×
×
  • 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.