Tak, na szybkości.......

SELECT customers.id, customers.lastname, customers.name, customers.address, customers.city, customers.zip, tariffs.value, tariffs.period, assignments.dateto, assignments.at
        FROM customers
        LEFT JOIN assignments ON customers.id = customerid
        LEFT JOIN tariffs ON assignments.tariffid = tariffs.id
        WHERE deleted = 0
            AND (customers.status = 3)
              AND (assignments.invoice = 1)
              AND (assignments.dateto >= (select now()::abstime::int4) OR assignments.dateto = 0)
              AND (  (assignments.period=3 and tariffs.value BETWEEN 1 AND 4)  and (assignments.period in (4,5) and tariffs.value >= 4 )  )
        GROUP BY customers.id, customers.lastname, customers.name, customers.address, customers.city, customers.zip,  tariffs.value, tariffs.period, assignments.dateto, assignments.at;


Pozdrawiam Miłosz

2011/11/10 Paweł <zappa@bass.pl>
Witam,

Napisałem sobie dwa zapytania na potrzeby raportu który chcę zrobić, nie wiem tylko jak na poziomie sql połączyć te zapytania aby wynik pojawiał się w momencie gdy customer.id jest wynikiem RÓWNOCZEŚNIE obydwu zapytań.
Zapytanie ma wyświetlić wszystkich podłączonych klientów którzy mają równocześnie występujące:
- zobowiązanie miesięczne na kwotę >= 4zł
oraz
- zobowiązania kwartalne, roczne na kwotę od 1zł do 4zł

SELECT customers.id, customers.lastname, customers.name, customers.address, customers.city, customers.zip, tariffs.value, tariffs.period, assignments.dateto, assignments.at
       FROM customers
       LEFT JOIN assignments ON customers.id = customerid
       LEFT JOIN tariffs ON assignments.tariffid = tariffs.id
       WHERE deleted = 0
               AND (customers.status = 3)
               AND (assignments.invoice = 1)
               AND (assignments.dateto >= (select now()::abstime::int4) OR assignments.dateto = 0)
               AND (assignments.at != 1)
               AND (tariffs.value BETWEEN 1 AND 4)
       GROUP BY customers.id, customers.lastname, customers.name, customers.address, customers.city, customers.zip,  tariffs.value, tariffs.period, assignments.dateto, assignments.at;

SELECT customers.id, customers.lastname, customers.name, customers.address, customers.city, customers.zip, tariffs.value, tariffs.period, assignments.dateto, assignments.at
       FROM customers
       LEFT JOIN assignments ON customers.id = customerid
       LEFT JOIN tariffs ON assignments.tariffid = tariffs.id
       WHERE deleted = 0
               AND (customers.status = 3)
               AND (assignments.invoice = 1)
               AND (assignments.dateto >= (select now()::abstime::int4) OR assignments.dateto = 0)
               AND (assignments.at = 1)
               AND (tariffs.value >= 4)
       GROUP BY customers.id, customers.lastname, customers.name, customers.address, customers.city, customers.zip,  tariffs.value, tariffs.period, assignments.dateto, assignments.at;

Będę wdzięczny za pomoc.

--
Pozdrawiam
Paweł Mendela
to green | systemy
_______________________________________________
lms mailing list
lms@lists.lms.org.pl
http://lists.lms.org.pl/mailman/listinfo/lms