Вопрос по запросу

[axapter]

Пока выгружаю из таблиц CUSTINVOICEJOUR и CUSTINVOICETRANS и получается такой запрос

Declare @StartDate date ='2017-07-01'
Declare @EndDate date ='2017-07-21'

SELECT	
	CUSTINVOICEJOUR.ORDERACCOUNT,
	CUSTINVOICEJOUR.INVOICEACCOUNT,
	CUSTINVOICEJOUR.INVOICEDATE,
        CUSTINVOICEJOUR.REFNUM,
        CUSTINVOICETRANS.ITEMID,
	CUSTINVOICETRANS.QTY,	
	CASE WHEN CUSTINVOICETRANS.QTY>0 THEN CUSTINVOICETRANS.QTY END Отгрузка,
	CASE WHEN CUSTINVOICETRANS.QTY<0 THEN -1 * CUSTINVOICETRANS.QTY END Возврат,
FROM
	CUSTINVOICEJOUR
INNER JOIN
	CUSTINVOICETRANS
ON
	(CUSTINVOICEJOUR.SALESID=CUSTINVOICETRANS.SALESID
	AND
	CUSTINVOICEJOUR.INVOICEID=CUSTINVOICETRANS.INVOICEID
	AND
	CUSTINVOICEJOUR.INVOICEDATE=CUSTINVOICETRANS.INVOICEDATE
	AND
	CUSTINVOICEJOUR.NUMBERSEQUENCEGROUP=CUSTINVOICETRANS.NUMBERSEQUENCEGROUP)
WHERE	
	(CUSTINVOICEJOUR.INVOICEDATE >= @StartDate AND CUSTINVOICEJOUR.INVOICEDATE <=@EndDate)	
	AND
	CUSTINVOICEJOUR.ORDERACCOUNT='00001ГРД'
	AND
	CUSTINVOICETRANS.ITEMID = 00001

Время выполнения запроса 5 секунд. Но в результате нету возвратов. А возвраты были. Почему так? Тут особенность в том, что в день поставки отгружается продукция и принимается возврат от предыдущих поставок

Выручка будет как расчетная?

Напоминаю, нужно в итоге получить таблицу со следующими колонками:
Код юр. лица, Код ТТ, SKU, Дата, Отгрузки шт., Возврат шт., Реализация шт..

[sukhanchik]

Цитата:

Сообщение от axapter

Время выполнения запроса 5 секунд.

Сделайте еще джойн по DataareaId. Время может уменьшиться. Запросы из кода X++ и запросы, которые АХ посылает к БД немного разные. АХ посылает запрос еще с фильтрацией по DataareaId по данным табличкам.

Цитата:

Сообщение от axapter

Но в результате нету возвратов. А возвраты были. Почему так?

Сложно дать ответ не видя данные. Думаю, что Вам надо вручную найти пару накладных и на них оттестировать конечный вид запроса. Возможно он немножко изменится. На форуме можно дать только концептуальное направление. До финального вида нужно уже идти самостоятельно

Цитата:

Сообщение от axapter

Выручка будет как расчетная?

В конкретном Вами приведенном запросе выручки вообще нет, т.к. запрос выбирает только количество.

[axapter]

Цитата:

Сообщение от sukhanchik

Сделайте еще джойн по DataareaId. Время может уменьшиться.

Теперь быстрее стало

[axapter]

Цитата:

Сообщение от sukhanchik

Сложно дать ответ не видя данные. Думаю, что Вам надо вручную найти пару накладных и на них оттестировать конечный вид запроса. Возможно он немножко изменится. На форуме можно дать только концептуальное направление. До финального вида нужно уже идти самостоятельно

Если отгрузка, то фиксируется через Сведения о заявках (Заявка) - Заявка на продажу. Если возврат, то Сведения о заказе на покупку (Заказ на покупку). В аксапте отчет о продажах по документам (если присутствуют поля Номер заказа и Номер документа) на один день указывает отдельную строку для отгрузки, и отдельную строку для возврата.


Запрос на выгрузку

Declare @StartDate date ='2017-01-01'
Declare @EndDate date ='2017-01-31'

SELECT	
	CUSTINVOICEJOUR.INVOICEACCOUNT AS [ ],	
	CUSTINVOICETRANS.ITEMID AS [ ],
	CONVERT(nvarchar(10),CUSTINVOICEJOUR.INVOICEDATE, 104) AS [],		
	CASE WHEN CUSTINVOICETRANS.QTY>0 THEN CAST(CUSTINVOICETRANS.QTY AS Integer) ELSE 0 END AS [],
	CASE WHEN CUSTINVOICETRANS.QTY<0 THEN CAST(-1*CUSTINVOICETRANS.QTY AS INTEGER)ELSE 0 END AS []
FROM
	CUSTINVOICEJOUR
INNER JOIN
	CUSTINVOICETRANS
ON
	(CUSTINVOICEJOUR.SALESID=CUSTINVOICETRANS.SALESID
	AND
	CUSTINVOICEJOUR.INVOICEID=CUSTINVOICETRANS.INVOICEID
	AND
	CUSTINVOICEJOUR.INVOICEDATE=CUSTINVOICETRANS.INVOICEDATE
	AND
	CUSTINVOICEJOUR.NUMBERSEQUENCEGROUP=CUSTINVOICETRANS.NUMBERSEQUENCEGROUP
	AND
	CUSTINVOICEJOUR.DATAAREAID=CUSTINVOICETRANS.DATAAREAID)
WHERE	
	(CUSTINVOICEJOUR.INVOICEDATE >= @StartDate AND CUSTINVOICEJOUR.INVOICEDATE <=@EndDate)	
	AND
	CUSTINVOICEJOUR.ORDERACCOUNT='00001грд'
	AND
	CUSTINVOICETRANS.ITEMID = 00001

Интересно получается. Выходят как и положительные, так и отрицательные QTY. Если отрицательные, то это обратные проводки накладной (сторно). Но не возвраты.

На что нужно обратить внимание, чтобы выгрузить возвраты?

[axapter]

Получается для получения возвратов надо еще использовать таблицу PurchTable? Важно получить и дату клиента, и дату проводки (поставки)?

[axapter]

Важно получить и дату клиента, и дату проводки (поставки)

[Владимир Максимов]

Цитата:

Сообщение от axapter

Интересно получается. Выходят как и положительные, так и отрицательные QTY. Если отрицательные, то это обратные проводки накладной (сторно). Но не возвраты.

На что нужно обратить внимание, чтобы выгрузить возвраты?

В стандартном функционале задача не то, чтобы не решаемая, но решаемая сложным способом. Обычно делают кастомизацию, добавляя в накладную признак того, что это сторно. Но если решать "в общем случае", то логика следующая

1. В рамках одной накладной, как правило, не могут быть одновременно и отгрузки и возвраты. Т.е. либо все строки имеют положительно количество, либо отрицательное. "Смеси" быт не может. Если это не так, то это ошибка.

2. Сторно - это полная копия отгрузки. Т.е. в сумме с отгрузкой получим ноль. Если это не так, то это ошибка.

Следовательно, анализировать надо не каждую накладную в отдельности, а сумму накладных в разрезе заказа (SalesId).

Правда, тут есть некоторые сложности с датой, поскольку, в общем случае, дата как сторно, так и дата нового проведения после сторно может быть любой. Т.е., в общем случае, под датой надо понимать дату накладной созданной самой последней. Но, опять же, есть сложности идентификации того, какую запись считать созданной "последней"

Для простоты, можно считать, что дата новой накладной не может быть меньше даты исходной накладной. Т.е. достаточно будет взять максимальное значение даты. Хотя, повторюсь, в общем случае, это может быть и не так

PHP код:

Declare @StartDate date ='2017-01-01'
Declare @EndDate date ='2017-01-31'

select 
    tmp.INVOICEACCOUNT AS [],
    tmp.ITEMID  AS [],
    CONVERT(nvarchar(10),tmp.INVOICEDATE, 104) AS [],        
    CASE WHEN tmp.QTY>0 THEN CAST(tmp.QTY AS Integer) ELSE 0 END AS [],
    CASE WHEN tmp.QTY<0 THEN CAST(-1*tmp.QTY AS INTEGER)ELSE 0 END AS []

from (
    SELECT    
        CUSTINVOICEJOUR.SALESID,
        CUSTINVOICEJOUR.INVOICEACCOUNT,    
        CUSTINVOICETRANS.ITEMID,
        -- В общем случае, дата последней накладной может быть любой
        -- но для простоты, считаем, что она не может быть меньше ранее созданных
        max(CUSTINVOICEJOUR.INVOICEDATE) AS InvoiceDate,        
        sum(CUSTINVOICETRANS.QTY) AS qty
    FROM CUSTINVOICEJOUR
    INNER JOIN CUSTINVOICETRANS    ON    CUSTINVOICEJOUR.SALESID                = CUSTINVOICETRANS.SALESID
            AND    CUSTINVOICEJOUR.INVOICEID            = CUSTINVOICETRANS.INVOICEID
            AND    CUSTINVOICEJOUR.INVOICEDATE            = CUSTINVOICETRANS.INVOICEDATE
            AND    CUSTINVOICEJOUR.NUMBERSEQUENCEGROUP    = CUSTINVOICETRANS.NUMBERSEQUENCEGROUP
    WHERE    CUSTINVOICEJOUR.ORDERACCOUNT    = N'00001грд'
        AND    CUSTINVOICETRANS.ITEMID            = N'00001'
        -- Явно указать значение DataAreaId
        AND CUSTINVOICEJOUR.DATAAREAID    = N'XXX'
        AND CUSTINVOICETRANS.DATAAREAID = N'XXX'
    group by 
        -- Основная группировка по SalesId, остальное - это уточнение и детализация
        CUSTINVOICEJOUR.SALESID,
        CUSTINVOICEJOUR.INVOICEACCOUNT,    
        CUSTINVOICETRANS.ITEMID
    -- Ограничение по датам для результата выборки
    having max(CUSTINVOICEJOUR.INVOICEDATE) between @StartDate and @EndDate
    -- Исключаем из рассмотрения сторно без последующей отгрузки
    AND sum(CUSTINVOICETRANS.QTY) <> 0
    ) as tmp 


[axapter]

Цитата:

Сообщение от axapter

Выручка будет как расчетная?

Напоминаю, нужно в итоге получить таблицу со следующими колонками:
Код юр. лица, Код ТТ, SKU, Дата, Отгрузки шт., Возврат шт., Реализация шт..

Извиняюсь, не выручка, а реализация шт.

[potential]

Цитата:

Сообщение от axapter

не выручка, а реализация шт.

присмотритесь к сообщению где вам намекают плясать от InventTrans

[axapter]

Чем INVOICEDATE отличается от DATAAREAID?

[S.Kuskov]

Цитата:

Сообщение от axapter

Чем INVOICEDATE отличается от DATAAREAID?

Типом данных как минимум

[axapter]

Цитата:

Сообщение от S.Kuskov

Типом данных как минимум

INVOICEDATE это DateTime, а DATAAREAID это nvarchar(4). Насколько я правильно понимаю, что эти даты совпадают по строкам. В запросе в конструкциях ON и WHERE что лучше использовать?

[mdanko2000]

Цитата:

Сообщение от axapter

INVOICEDATE это DateTime, а DATAAREAID это nvarchar(4). Насколько я правильно понимаю, что эти даты совпадают по строкам. В запросе в конструкциях ON и WHERE что лучше использовать?

INVOICEDATE - дата накладной
DATAAREAID внутренний идентификатор компании
IMHO: в ON сцепка по обеим полям обязательна
а в Where желательно указать из какой компании DATAAREAID делается выборка
IMHO2: во все индексы генерируемые из среды, ядро всегда добавляет DATAAREAID при генерации на сервере