CODEWITHSUNDEEP
mysqlcase
Faisal Qayyum
Faisal Qayyum

Reputation: 132

Mysql carrying out the rate according to the range given

I have a below table in mysql.

CREATE TABLE `mdc_tariff_slabs` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Primary Key',
`type` varchar(50) DEFAULT NULL,
`slab_name` varchar(50) NOT NULL COMMENT 'Name of the Slab',
`slab_start` int(50) NOT NULL COMMENT 'Start of the slab',
`slab_end` int(50) DEFAULT NULL COMMENT 'End of the slab',
`rate` varchar(50) NOT NULL COMMENT 'Rate of slab',
`t_id` int(11) NOT NULL COMMENT 'Tariff ID, Foriegn Key',
 PRIMARY KEY (`id`),
 KEY `T_ID` (`t_id`),
 CONSTRAINT `T_ID` FOREIGN KEY (`t_id`) REFERENCES `mdc_tariff` (`id`) ON DELETE CASCADE
 ) ENGINE=InnoDB AUTO_INCREMENT=61 DEFAULT CHARSET=latin1;

/*Data for the table `mdc_tariff_slabs` */

insert  into `mdc_tariff_slabs`(`id`,`type`,`slab_name`,`slab_start`,`slab_end`,`rate`,`t_id`) values (1,NULL,'S-1',1,100,'10',1),(2,NULL,'S-2',101,150,'12',1),(3,NULL,'S-3',151,NULL,'14',1),(4,NULL,'S-1',1,50,'5',2),(5,NULL,'S-2',51,100,'7',2),(6,NULL,'S-3',101,NULL,'8',2),(52,'WAPDA','S-1',1,100,'12',12),(53,'WAPDA','S-2',101,150,'14',12),(54,'WAPDA','S-3',151,NULL,'16',12),(58,'Generator','S-1',1,100,'17',12),(59,'Generator','S-2',101,150,'20',12),(60,'Generator','S-3',151,NULL,'22',12);

The same is in my SQL Fiddle

I have slabs range. Within that range of a particular type I want to check the rate. Assuming the value to be checked is 40 and the type is Generator. So if the value is in the slab range 1-100 the rate would be 17 and vice versa.

Expected Output

 type     |value|rate|
======================
 Generator| 40  |17  |
....

How can I achieve this?

Any help would be highly appreciated.

Upvotes: 0

Views: 28

Answers (1)

Steven Moseley
Steven Moseley

Reputation: 16345

It looks like slabs can overlap, so you should go for the MAX of slab value to ensure you're handling bad data. This is tricky, because you have rate defined as a varchar. That's why I'm casting it to an int value before summing it. You may still have issues with this. I suggest instead going with an int value for that column.

It also looks like the slab_end can be NULL, representing no end, so a simple BETWEEN condition won't work for your case. I handled it with an OR condition.

Here's how I would do it:

SET @value = 40;
SET @type = 'Generator';

SELECT @type AS type,
    @value AS value,
    MAX(CAST(rate AS UNSIGNED)) AS rate
FROM mdc_tariff_slabs
WHERE type = @type
    AND @value >= slab_start
    AND (@value <= slab_end OR slab_end IS NULL);

Upvotes: 1

Related Questions