[ davidrs @ 12.02.2009. 08:55 ] @
Pozdrav svima, pokusavam da napravim jednu test bazu o rezervacijama i stanju smestajnih kapaciteta. OS - Ubuntu8.04, WinXP MySQL -Ver 14.12 Distrib 5.0.51a, for debian-linux-gnu (i486) using readline 5.2 phpMyAdmin 2.11.3 Baza ima 2 tabele i to: Code: ============================ mysql> show tables; +-----------------+ | Tables_in_baza1 | +-----------------+ | tbl_1 | | tbl_2 | +-----------------+ 2 rows in set (0.00 sec) mysql> describe tbl_1; +---------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +---------+--------------+------+-----+---------+----------------+ | ID | int(11) | NO | PRI | NULL | auto_increment | | naziv | varchar(100) | NO | | NULL | | | dolazak | date | NO | | NULL | | | odlazak | date | NO | | NULL | | | ob_1 | int(11) | NO | | NULL | | | ob_2 | int(11) | NO | | NULL | | | ob_3 | int(11) | NO | | NULL | | | ob_4 | int(11) | NO | | NULL | | +---------+--------------+------+-----+---------+----------------+ 8 rows in set (0.00 sec) mysql> describe tbl_2; +-----------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-----------+-------------+------+-----+---------+----------------+ | ID | smallint(6) | NO | PRI | NULL | auto_increment | | objekat | varchar(60) | NO | | NULL | | | kapacitet | int(11) | NO | | NULL | | +-----------+-------------+------+-----+---------+----------------+ 3 rows in set (0.00 sec) sa sledecim podacima: ======================== mysql> select * from tbl_1; +----+-------+------------+------------+------+------+------+------+ | ID | naziv | dolazak | odlazak | ob_1 | ob_2 | ob_3 | ob_4 | +----+-------+------------+------------+------+------+------+------+ | 1 | skup1 | 2009-02-01 | 2009-02-05 | 10 | 5 | 0 | 0 | | 2 | skup2 | 2009-02-03 | 2009-02-10 | 5 | 10 | 20 | 0 | | 3 | skup3 | 2009-03-10 | 2009-03-14 | 0 | 20 | 0 | 0 | | 4 | skup4 | 2009-02-15 | 2009-02-20 | 30 | 0 | 0 | 40 | +----+-------+------------+------------+------+------+------+------+ 4 rows in set (0.00 sec) mysql> select * from tbl_2; +----+---------+-----------+ | ID | objekat | kapacitet | +----+---------+-----------+ | 1 | ob_1 | 50 | | 2 | ob_2 | 30 | | 3 | ob_3 | 70 | | 4 | ob_4 | 80 | +----+---------+-----------+ 4 rows in set (0.00 sec) Potreban mi je rezultat koji bi prikazivao za svaki dan u godini stanje kapaciteta po objektima, trebao bi da izgleda ovako: Code: +----+-------+------------+------+------+------+------+ |GOD | MESEC | DATUM | ob_1|ob_2|ob_3|ob_4| +----+-------+------------+---- --+----- -+------+------+ |2009| Januar | 2009-01-01 | 0 | 0 | 0 | 0 | |2009| Januar | 2009-01-02| 0 | 0 | 0 | 0 | |2009| Januar | 2009-01-03 | 0 | 0 | 0 | 0 | |2009| Januar | 2009-01-04 | 0 | 0 | 0 | 0 | +----+-------+------------+------+------+------+------+ Za svaki dan (kolona DATUM) bi trebalo da se izracuna da li ispunjava uslov: IF (DATUM > ili = od 'dolazak' AND < od 'odlazak') onda vrednost od (ob_1 oduzmi od kapaciteta ob_1),(ob_2 oduzmi do kapaciteta ob_2), ... itd. Ukoliko ko ima bilo kakve instrukcije kako da resim ovaj problem ili u kom smeru da idem bio bih zahvalan. [Ovu poruku je menjao misk0 dana 13.02.2009. u 22:23 GMT+1] |