Необходимо сделать следующее. Проанализировать информацию о продаже товаров по кассовым чекам. Сделать вывод о том сколько и какого товара продано.
Просмотрев документ "Структура БД. Раздел I" выяснил, что информация о кассовых чеках находится в таблицах SMCASHCHECKITEMS и SMCASHCHECKS.
Написал следующее.
Данный запрос выведет информацию о кассовых чеках за период с 01.01.2007 по 31.01.2007 с указанием касс и z-отчетов:
Код:
SELECT SMSTORELOCATIONS.NAME as SMSTORELOCATIONS_NAME,
SMCASHCHECKS.CHECKNUM as SMCASHCHECKS_CHECKNUM,
SMCASHCHECKS.PRINTTIME as SMCASHCHECKS_PRINTTIME,
SMCASHCHECKS.ZNUM as SMCASHCHECKS_ZNUM,
SMCASHZ.DESKNUM as SMCASHZ_DESKNUM,
SMCASHZ.CLOSEDATE as SMCASHZ_CLOSEDATE,
('с 01.01.2007 по 31.01.2007') as TIME_INTERVAL
FROM SMCASHCHECKS,
SMSTORELOCATIONS,
SMCASHZ
WHERE SUBSTR(SMCASHCHECKS.PRINTTIME, 0, 10) >=
to_date('01.1.2007', 'DD.MM.YYYY')
AND SUBSTR(SMCASHCHECKS.PRINTTIME, 0, 10) <=
to_date('31.1.2007', 'DD.MM.YYYY')
AND SMCASHCHECKS.OPCODE IN(1)
AND SMSTORELOCATIONS.ID = SMCASHCHECKS.LOCID
AND SMCASHCHECKS.ZNUM = SMCASHZ.ZNUM
AND SMCASHZ.LOCID = SMCASHCHECKS.LOCID
AND SMCASHCHECKS.DESKNUM = SMCASHZ.DESKNUM
AND SUBSTR(SMCASHZ.CLOSEDATE, 0, 10) >=
to_date('01.1.2007', 'DD.MM.YYYY')
AND SUBSTR(SMCASHZ.CLOSEDATE, 0, 10) <=
to_date('31.1.2007', 'DD.MM.YYYY')
GROUP BY SMSTORELOCATIONS.NAME,
SMCASHCHECKS.CHECKNUM,
SMCASHCHECKS.PRINTTIME,
SMCASHCHECKS.ZNUM,
SMCASHZ.DESKNUM,
SMCASHZ.CLOSEDATE
ORDER BY SMCASHCHECKS.ZNUM Эта таблица не содержит информацию об артикулах проданного товара. Я могу получить нужные мне данные следующим запросом:
Код:
SELECT SMSTORELOCATIONS.NAME as SMSTORELOCATIONS_NAME_1,
SMCASHCHECKITEMS.CHECKNUM as SMCASHCHECKITEMS_CHECKNUM,
SMCASHCHECKITEMS.ARTICLE as SMCASHCHECKITEMS_ARTICLE,
SUM(SMCASHCHECKITEMS.TOTALSUM) as
SMCASHCHECKITEMS_TOTALSUM,
SMCASHCHECKITEMS.ZNUM as SMCASHCHECKITEMS_ZNUM,
SMCASHZ.DESKNUM as SMCASHZ_DESKNUM_1,
SMCASHZ.CLOSEDATE as SMCASHZ_CLOSEDATE_1
FROM SMCASHCHECKITEMS,
SMSTORELOCATIONS,
SMCASHZ
WHERE SMSTORELOCATIONS.ID = SMCASHCHECKITEMS.LOCID
AND SMCASHCHECKITEMS.ZNUM = SMCASHZ.ZNUM
AND SMCASHCHECKITEMS.DESKNUM = SMCASHZ.DESKNUM
AND SMCASHZ.LOCID = SMCASHCHECKITEMS.LOCID
AND SUBSTR(SMCASHZ.CLOSEDATE, 0, 10) >=
to_date('01.1.2007', 'DD.MM.YYYY')
AND SUBSTR(SMCASHZ.CLOSEDATE, 0, 10) <=
to_date('31.1.2007', 'DD.MM.YYYY')
GROUP BY SMSTORELOCATIONS.NAME,
SMCASHCHECKITEMS.ARTICLE,
SMCASHCHECKITEMS.TOTALSUM,
SMCASHCHECKITEMS.CHECKNUM,
SMCASHCHECKITEMS.ZNUM,
SMCASHZ.DESKNUM,
SMCASHZ.CLOSEDATE
ORDER BY SMCASHCHECKITEMS.ZNUM Теперь мне нужно связать между собой эти данные. Конечный результат выглядит примерно так:
Код:
SELECT SMSTORELOCATIONS_NAME as "Место хранения",
SACARDCLASS.TREE as "Код узла",
SACARDCLASS.NAME as "Классификатор",
SMCASHCHECKITEMS_ARTICLE as "Артикул",
SMCARD.NAME as "Наименование товара",
SUM(SMCASHCHECKITEMS_TOTALSUM) as "Общая сумма по артикулу",
TIME_INTERVAL as "Интервал"
FROM (
SELECT SMSTORELOCATIONS.NAME as SMSTORELOCATIONS_NAME,
SMCASHCHECKS.CHECKNUM as SMCASHCHECKS_CHECKNUM,
SMCASHCHECKS.PRINTTIME as SMCASHCHECKS_PRINTTIME,
SMCASHCHECKS.ZNUM as SMCASHCHECKS_ZNUM,
SMCASHZ.DESKNUM as SMCASHZ_DESKNUM,
SMCASHZ.CLOSEDATE as SMCASHZ_CLOSEDATE,
('с 01.01.2007 по 31.01.2007') as TIME_INTERVAL
FROM SMCASHCHECKS,
SMSTORELOCATIONS,
SMCASHZ
WHERE SUBSTR(SMCASHCHECKS.PRINTTIME, 0, 10) >=
to_date('01.1.2007', 'DD.MM.YYYY')
AND SUBSTR(SMCASHCHECKS.PRINTTIME, 0, 10) <=
to_date('31.1.2007', 'DD.MM.YYYY')
AND SMCASHCHECKS.OPCODE IN(1)
AND SMSTORELOCATIONS.ID = SMCASHCHECKS.LOCID
AND SMCASHCHECKS.ZNUM = SMCASHZ.ZNUM
AND SMCASHZ.LOCID = SMCASHCHECKS.LOCID
AND SMCASHCHECKS.DESKNUM = SMCASHZ.DESKNUM
AND SUBSTR(SMCASHZ.CLOSEDATE, 0, 10) >=
to_date('01.1.2007', 'DD.MM.YYYY')
AND SUBSTR(SMCASHZ.CLOSEDATE, 0, 10) <=
to_date('31.1.2007', 'DD.MM.YYYY')
GROUP BY SMSTORELOCATIONS.NAME,
SMCASHCHECKS.CHECKNUM,
SMCASHCHECKS.PRINTTIME,
SMCASHCHECKS.ZNUM,
SMCASHZ.DESKNUM,
SMCASHZ.CLOSEDATE
ORDER BY SMCASHCHECKS.ZNUM
),
(
SELECT SMSTORELOCATIONS.NAME as SMSTORELOCATIONS_NAME_1,
SMCASHCHECKITEMS.CHECKNUM as SMCASHCHECKITEMS_CHECKNUM,
SMCASHCHECKITEMS.ARTICLE as SMCASHCHECKITEMS_ARTICLE,
(SMCASHCHECKITEMS.TOTALSUM) as
SMCASHCHECKITEMS_TOTALSUM,
SMCASHCHECKITEMS.ZNUM as SMCASHCHECKITEMS_ZNUM,
SMCASHZ.DESKNUM as SMCASHZ_DESKNUM_1,
SMCASHZ.CLOSEDATE as SMCASHZ_CLOSEDATE_1
FROM SMCASHCHECKITEMS,
SMSTORELOCATIONS,
SMCASHZ
WHERE SMSTORELOCATIONS.ID = SMCASHCHECKITEMS.LOCID
AND SMCASHCHECKITEMS.ZNUM = SMCASHZ.ZNUM
AND SMCASHCHECKITEMS.DESKNUM = SMCASHZ.DESKNUM
AND SMCASHZ.LOCID = SMCASHCHECKITEMS.LOCID
AND SUBSTR(SMCASHZ.CLOSEDATE, 0, 10) >=
to_date('01.1.2007', 'DD.MM.YYYY')
AND SUBSTR(SMCASHZ.CLOSEDATE, 0, 10) <=
to_date('31.1.2007', 'DD.MM.YYYY')
GROUP BY SMSTORELOCATIONS.NAME,
SMCASHCHECKITEMS.ARTICLE,
SMCASHCHECKITEMS.TOTALSUM,
SMCASHCHECKITEMS.CHECKNUM,
SMCASHCHECKITEMS.ZNUM,
SMCASHZ.DESKNUM,
SMCASHZ.CLOSEDATE
ORDER BY SMCASHCHECKITEMS.ZNUM
),
SACARDCLASS,
SMCARD
WHERE SMSTORELOCATIONS_NAME = SMSTORELOCATIONS_NAME_1
AND SACARDCLASS.ID = SMCARD.IDCLASS
AND SMCARD.ARTICLE = SMCASHCHECKITEMS_ARTICLE
AND SMCASHCHECKS_CHECKNUM = SMCASHCHECKITEMS_CHECKNUM
AND SMCASHCHECKS_ZNUM = SMCASHCHECKITEMS_ZNUM
AND SMCASHZ_DESKNUM = SMCASHZ_DESKNUM_1
AND SMCASHZ_CLOSEDATE = SMCASHZ_CLOSEDATE_1
GROUP BY SMSTORELOCATIONS_NAME,
SACARDCLASS.TREE,
SACARDCLASS.NAME,
SMCASHCHECKITEMS_ARTICLE,
SMCARD.NAME,
TIME_INTERVAL
ORDER BY SMCASHCHECKITEMS_ARTICLE Пока тестировал только на двух артикулах: вроде бы как правильно считается.
Попробовал задать выполнение по всем артикулам за указанный период и... не дождался результата. Время не фиксировал, но субъективно - очень долго.
Как мне оптимизировать запрос? Помогите, пожалуйста.