Find availability of storage racks in stores between dates range

Question

CREATE TABLE `storage_availability` (
`store_id` int(11) NOT NULL,
`availability_before` int(11) NOT NULL COMMENT 'how many rack places are available until
date_move',
`date_move` date NOT NULL,
`availability_after` int(11) NOT NULL COMMENT 'how many rack places are available after
date_move',
PRIMARY KEY (`store_id`),
KEY `date_move` (`date_move`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='store the avaibility of rack space
for
each warehouse';
INSERT INTO `storage_availability` VALUES (1,2500,'2014-06-01',3500),(2,100,'2014-06-
30',10000),(3,8500,'2014-07-15',1000),(4,3000,'2013-11-01',2000),(5,500,'2014-09-01',20000),
(6,5000,'2014-04-15',100),(7,200,'2015-02-01',300),(8,9000,'2014-02-16',1000);

My Sql Query

SELECT * FROM `storage_availability` 
WHERE

  (`availability_before` >='2000' 
    and `date_move` >= '2013-09-01' 
    and `store_id` not  in (
           select `store_id` from `storage_availability` 
           where `date_move` <= '2014-04-30') 
    ) 
    or 
    ( `availability_after` >= '2000' 
      and `date_move` <= '2014-04-30'  
      and `store_id`  not in (
            select `store_id` from `storage_availability` 
            where `date_move` >= '2013-09-01'
    ))

Start Date : 2013-09-01 and End Date : 2014-04-30 and required racks : 2000

Required Result : Stored ID 1,3 & 4

store_id 1 has date out of start date and end date, Are you sure the required racks condtion is AND? — thebignoob, May 04 '14 at 11:23
On store_id 1= 2500 racks will available till 2014-06-01 and after this date 3500 racks will be available Similarly for store_id 2 = 100 racks will available till 2014-06-30 and after this date 10 racks will be available — user3601145, May 04 '14 at 13:18
Okay, I think I get what you're trying for: Given a date range [`2013-09-01`, `2014-05-01`), find all records where there is sufficient racks (`2000`) before and after any moves. Your set of data isn't connected though (ie, no sequential entries where `availability_before` matches `availability_after`), which is strange (and a little worrying). Are you sure this is what your problem comprises? — Clockwork-Muse, May 04 '14 at 23:10

score 0 · Accepted Answer · edited May 23 '17 at 10:26

First off, it's useful to be able to determine any reductions. In particular, your subqueries can be removed:

AND date_move >= '2013-09-01' 
AND store_id NOT IN(SELECT store_id 
                    FROM  storage_availability 
                    WHERE date_move <= '2014-04-30')

actually reduces to:

AND date_move >= '2014-05-01'

(well, the intermediate step would be AND date_move > '2014-04-30', but you want to avoid that in the case that something is a timestamp or similar)

The needed query is then:

SELECT store_id, availability_before, date_move, availability_after
FROM storage_availability
WHERE (availability_before >= 2000 AND date_move >= '2014-05-01') 
      OR (availability_after >= 2000 AND date_move < '2013-09-01')
      OR (availability_after >= 2000 AND availability_before >= 2000
          AND date_move >= '2013-09-01' AND date_move < '2014-05-01')

which yields the required:

STORE_ID    AVAILABILITY_BEFORE     DATE_MOVE       AVAILABILITY_AFTER
1           2500                    '2014-06-01'    3500
3           8500                    '2014-07-15'    1000
4           3000                    '2013-11-01'    2000

(and working fiddle)

Interestingly, given the answer to this question, I suspect that there may be some logical reduction available, but I'm not seeing it at the moment. The ones I am seeing aren't really any good...

Find availability of storage racks in stores between dates range

1 Answers1