SELECT x.article "Артикул", REPLACE (s.NAME, CHR (9), ' ') "Наименование"
FROM ((SELECT z.article
FROM supermag.smcard z
WHERE z.accepted = 1 AND z.datatype = '0'
MINUS
SELECT c.article
FROM supermag.smdocuments a,
supermag.smcontracts b,
supermag.smspec c
WHERE a.doctype = 'CO'
AND a.docstate > '1'
AND a.ID = b.ID
AND b.beginsat < SYSDATE
AND b.endsat > SYSDATE
AND c.doctype = 'CO'
AND c.docid = a.ID)
MINUS
SELECT y.article
FROM supermag.smgoods y
WHERE y.quantity != 0) x,
supermag.smcard s
WHERE x.article = s.article
ORDER BY 2 SELECT SUM (aa) "Кол-во заказов", SUM (bb) "Кол-во строк", SUM (cc) "Сумма",
SUM (dd) "Ассортимент"
FROM (SELECT COUNT (ID) aa, SUM (zz) bb, SUM (totalsum) cc, NULL dd
FROM (SELECT a.ID, COUNT (b.article) zz, a.totalsum
FROM supermag.smdocuments a, supermag.smspec b
WHERE a.doctype = 'OR'
AND a.createdat BETWEEN SYSDATE - 8 AND SYSDATE - 1
AND a.ID LIKE 'ЗПЦ%'
AND b.doctype = 'OR'
AND b.docid = a.ID
GROUP BY a.ID, a.totalsum)
UNION ALL
SELECT NULL aa, NULL bb, NULL cc, COUNT (yy) dd
FROM (SELECT DISTINCT (b.article) yy
FROM supermag.smdocuments a, supermag.smspec b
WHERE a.doctype = 'OR'
AND a.createdat BETWEEN SYSDATE - 8 AND SYSDATE
- 1
AND a.ID LIKE 'ЗПЦ%'
AND b.doctype = 'OR'
AND b.docid = a.ID)) SELECT SUM (aa) "Кол-во заказов", SUM (bb) "Кол-во строк", SUM (cc) "Сумма",
SUM (dd) "Ассортимент"
FROM (SELECT COUNT (ID) aa, SUM (zz) bb, SUM (totalsum) cc, NULL dd
FROM (SELECT a.ID, COUNT (b.article) zz, a.totalsum
FROM supermag.smdocuments a, supermag.smspec b
WHERE a.doctype = 'OR'
AND a.createdat BETWEEN SYSDATE - 8 AND SYSDATE - 1
AND a.ID LIKE 'ЗПП%'
AND b.doctype = 'OR'
AND b.docid = a.ID
GROUP BY a.ID, a.totalsum)
UNION ALL
SELECT NULL aa, NULL bb, NULL cc, COUNT (yy) dd
FROM (SELECT DISTINCT (b.article) yy
FROM supermag.smdocuments a, supermag.smspec b
WHERE a.doctype = 'OR'
AND a.createdat BETWEEN SYSDATE - 8 AND SYSDATE
- 1
AND a.ID LIKE 'ЗПП%'
AND b.doctype = 'OR'
AND b.docid = a.ID)) SELECT z.username "Менеджер", COUNT (z.ID) "Кол-во заказов",
SUM (b.totalsum) "Сумма"
FROM (SELECT a.ID, a.username
FROM supermag.smdoclog a
WHERE a.doctype = 'OR'
AND a.docdate BETWEEN SYSDATE - 15 AND SYSDATE - 1
AND a.oldstate = 1
AND a.newstate = 2
AND a.username IN
('ivanov', 'petrov', 'sidorov')
GROUP BY a.ID, a.username) z,
supermag.smdocuments b
WHERE z.ID = b.ID
GROUP BY z.username SELECT a.ID, b.NAME, b.dateend FROM supermag.smdocuments a, supermag.smauctions b WHERE a.doctype = 'MA' AND a.ID = b.ID AND b.dateend BETWEEN SYSDATE AND SYSDATE + 2
SELECT a.ID, b.NAME, b.datebegin FROM supermag.smdocuments a, supermag.smauctions b WHERE a.doctype = 'MA' AND a.ID = b.ID AND b.datebegin BETWEEN SYSDATE - 2 AND SYSDATE
SELECT a.ID, b.NAME, b.dateend FROM supermag.smdocuments a, supermag.smauctions b WHERE a.doctype = 'MA' AND a.docstate = 3 AND a.ID = b.ID AND b.dateend < SYSDATE;