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)
============================
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 |
+----+-------+------------+------+------+------+------+
+----+-------+------------+------+------+------+------+
|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]