Остатки на дату.

[Recoilme]

Цитата:

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

Извините, что встрял, этот запрос вообще вернет полную лажу, так как вместо SUM(dbo.INVENTSUM.POSTEDQTY) надо MAX(dbo.INVENTSUM.POSTEDQTY)

а вот с chel не согласен.В запросе нет ограничений на InventSum, а сам он никогда не чиститься, даже если всё = 0.

Не понял... Что за наезд на мой маленький запросик? С какой это стати там макс должен быть???

[Alexius]

Цитата:

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

Извините, что встрял, этот запрос вообще вернет полную лажу,

запрос действительно не даст корректных остатков на дату как я писал выше,

Цитата:

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

так как вместо SUM(dbo.INVENTSUM.POSTEDQTY) надо MAX(dbo.INVENTSUM.POSTEDQTY)

ну проблему с умножением этого остатка на кол-во проводок это действительно решит, остается еще включить в запрос остатки сформированные проводками до 01.01.06

Цитата:

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

а вот с chel не согласен.В запросе нет ограничений на InventSum, а сам он никогда не чиститься, даже если всё = 0.

и это прекрасно, что не чиститься !

[chel]

Цитата:

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

ну проблему с умножением этого остатка на кол-во проводок это действительно решит, остается еще включить в запрос остатки сформированные проводками до 01.01.06

Зачем включать? Там же left outer join стоит. Включать не придется. Те остатки, по которым нет проводок после 01.01 - все равно попасть должны.

[Alexius]

Цитата:

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

Зачем включать? Там же left outer join стоит. Включать не придется. Те остатки, по которым нет проводок после 01.01 - все равно попасть должны.

Согласно обсуждаемому запросу НЕТ, так как есть условия на поля из таблицы InventTrans

[Recoilme]

Цитата:

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

ну проблему с умножением этого остатка на кол-во проводок это действительно решит, остается еще включить в запрос остатки сформированные проводками до 01.01.06

Стоп-стоп-стоп. А зачем их включать? Идиотская задача была в том чтобы из текущих остатков вычесть проводки сформированный с даты Ф и вычесть их, чтобы получуть остатки на дату Ф....

[Wamr]

Цитата:

Не понял...

Есть 1 InventSum и есть 3 InventTrans (по одной номенклатуре-аналитике)
выборка будет такая
InventSum1, InventTrans1
InventSum1, InventTrans2
InventSum1, InventTrans3
а теперь суммируем..

[Recoilme]

Цитата:

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

Есть 1 InventSum и есть 3 InventTrans (по одной номенклатуре-аналитике)
выборка будет такая
InventSum1, InventTrans1
InventSum1, InventTrans2
InventSum1, InventTrans3
а теперь суммируем..

Торопился, сорри
И продатаареаид - тоже забЫл потому что мы аксапту запускаем с ключём -ноудатаареаид
Вобщем тормоз я
А chel всё равно не прав и карфаген должен быть разрушен (С)
Вобщем всем пока, до новых встреч в эфире

[Recoilme]

ТЫТЫСЫБЕГИН

SELECT ФОРАПДЭЙТ TOP 100 dbo.INVENTSUM.ITEMID AS ITEMID, MAX(dbo.INVENTSUM.POSTEDQTY) - SUM(dbo.INVENTTRANS.QTY) AS QTY,
dbo.INVENTSUM.INVENTDIMID
FROM dbo.INVENTSUM LEFT OUTER JOIN
dbo.INVENTTRANS ON dbo.INVENTSUM.INVENTDIMID = dbo.INVENTTRANS.INVENTDIMID AND
dbo.INVENTSUM.ITEMID = dbo.INVENTTRANS.ITEMID
WHERE (dbo.INVENTTRANS.DATEPHYSICAL >= CONVERT(DATETIME, '2006-01-01 00:00:00', 102)) AND ДАТААРЕАИД = ЧТОТО AND
((dbo.INVENTTRANS.STATUSRECEIPT = 1) OR
(dbo.INVENTTRANS.STATUSISSUE = 1) OR
(dbo.INVENTTRANS.STATUSRECEIPT = 2) OR
(dbo.INVENTTRANS.STATUSISSUE = 2) OR
(dbo.INVENTTRANS.STATUSRECEIPT = 3) OR
(dbo.INVENTTRANS.STATUSISSUE = 3))
GROUP BY dbo.INVENTSUM.ITEMID, dbo.INVENTSUM.INVENTDIMID

ТЫТЫСЫКОММИТ

Теперь всё правильно???

[Alexius]

Цитата:

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

Теперь всё правильно???

НЕА

ТЫТЫСЫБЕГИН

SELECT ФОРАПДЭЙТ TOP 100 dbo.INVENTSUM.DATAAREAID, dbo.INVENTSUM.ITEMID AS ITEMID, MAX(dbo.INVENTSUM.POSTEDQTY) - ISNULL(SUM(dbo.INVENTTRANS.QTY),0) /*Спасибо Владимиру Максимову*/ AS QTY,
dbo.INVENTSUM.INVENTDIMID
FROM dbo.INVENTSUM LEFT OUTER JOIN
dbo.INVENTTRANS ON dbo.INVENTSUM.INVENTDIMID = dbo.INVENTTRANS.INVENTDIMID AND
dbo.INVENTSUM.ITEMID = dbo.INVENTTRANS.ITEMID AND
dbo.INVENTSUM.DATAAREAID = dbo.INVENTTRANS.DATAAREAID
WHERE dbo.INVENTSUM.DATAAREAID = 'ABC' AND ((dbo.INVENTTRANS.RECID IS NULL) OR ((dbo.INVENTTRANS.DATEPHYSICAL >= CONVERT(DATETIME, '2006-01-01 00:00:00', 102)) AND ((dbo.INVENTTRANS.STATUSRECEIPT = 1) OR
(dbo.INVENTTRANS.STATUSISSUE = 1) OR
(dbo.INVENTTRANS.STATUSRECEIPT = 2) OR
(dbo.INVENTTRANS.STATUSISSUE = 2) OR
(dbo.INVENTTRANS.STATUSRECEIPT = 3) OR
(dbo.INVENTTRANS.STATUSISSUE = 3)) )
GROUP BY dbo.INVENTSUM.DATAAREAID, dbo.INVENTSUM.ITEMID, dbo.INVENTSUM.INVENTDIMID

ТЫТЫСЫКОММИТ

[chel]

Alexius, а где же обещанное включение в запрос проводок "до 01.01.06"?

И кстати, мы углубились уже в такие запросы, которые собственно из аксапты "никак"

[Alexius]

Цитата:

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

Alexius, а где же обещанное включение в запрос проводок "до 01.01.06"?

Проводки и не надо включать, я имел в виду остатки, хранящиеся в InventSum по которым не было проводок после 01.01.06 (то есть они сформированы проводками до 01.01.06), и это сделано

[chel]

Цитата:

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

Проводки и не надо включать, я имел в виду остатки, хранящиеся в InventSum по которым не было проводок после 01.01.06 (то есть они сформированы проводками до 01.01.06), и это сделано

Да, точно... Что можно сказать - жжошь. Пеши чаще.

[Vadik]

Быстрый (и простой) способ получить суммы и количества по InventTrans на дату - индексированный (или материализованный, в зависимости от СУБД) view. Из аксапты подхватывается
Единственный косяк - возможные проблемы с блокировками и более тяжелая операция обновления таблицы

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

Цитата:

Теперь всё правильно???

Ну, если совсем уж правильно, то нужно:

MAX(InventSum) - IsNull(SUM(InventTrans),0)

И DataAreaId на обе таблицы

Да, при LEFT JOIN в директиве WHERE допустимы только условия на таблицу InventSum, а все условия на таблицу InventTrans надо перенести в условия объединения:

SELECT
	InventSum.ItemId,
	InventSum.InventDimId,
	MAX(InventSum.PostedQty) - IsNull(SUM(InventTrans.Qty),0) as Qty
FROM InventSum
LEFT JOIN InventTrans ON InventSum.InventDimId = InventTrans.InventDimId
		AND InventSum.ItemId = InventTrans.ItemId
		AND InventSum.DataAreaId = InventTrans.DataAreaId
		AND InventTrans.DatePhysical >= '20060101'
		AND (InventTrans.StatusIssue IN (1,2,3) 
			OR InventTrans.StatusReceipt IN (1,2,3))
WHERE InventSum.DataAreaId = ...
GROUP BY 
	InventSum.ItemId,
	InventSum.InventDimId

[Wamr]

пора перемещаться на SQL.RU
Alexius дело говорит, слушайте его

[Recoilme]

Цитата:

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

пора перемещаться на SQL.RU
Alexius дело говорит, слушайте его

Боюсь там за такие запросы руки оторвут и обратно, на axforum вышлют, чтоб устрашились.

[Wamr]

Еще ремарка
SUM(ISNULL(dbo.INVENTTRANS.QTY, 0))

[Wamr]

chel синеньким выделено

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

Поскольку это все-таки форму по AXAPTA, а не филиал sql.ru, то я перевел это добро в синтаксис AXAPTA

PHP код:

static void Sum_Date_new(Args _args)
{
    #AviFiles
    SysOperationProgress    sysOperationProgress;

    Qty             ostatok;
    Int               intLine,
                      progressTotal;
    int               startTime;
    FromDate    fromDate    = 06\\05\\2005;

    InventSum   inventSum;
    InventTrans inventTrans;
    InventDim   inventDim;
    ;

    progressTotal = (select count(RecId) from inventSum).recId;

    sysOperationProgress = new SysOperationProgress();
    sysOperationProgress.setAnimation(#AviTransfer);
    sysOperationProgress.setCaption('Расчет остатка на дату');
    sysOperationProgress.setTotal(progressTotal);
    sysOperationProgress.setText("Подготовка расчета...");
    sysOperationProgress.update(true);

// Если надо, включаем режим грязного чтения
//    inventSum.selectLocked(false);  

    startTime = timenow();
    while select itemId, inventDimId, maxof(PhysicalInvent)
        from inventSum
        group by itemId, inventDimId
            outer join sum(Qty)
            from inventTrans
            where inventTrans.ItemId == inventSum.ItemId
                && inventTrans.inventDimId == inventSum.InventDimId
                && ((inventTrans.DateInvent > fromDate) 
                    || (! inventTrans.DateInvent && inventTrans.DatePhysical > fromDate))
                && (inventTrans.StatusIssue == StatusIssue::Sold
                    || inventTrans.StatusIssue == StatusIssue::Deducted
                    || inventTrans.StatusIssue == StatusIssue::Picked
                    || inventTrans.StatusReceipt == StatusReceipt::Purchased
                    || inventTrans.StatusReceipt == StatusReceipt::Received
                    || inventTrans.StatusReceipt == StatusReceipt::Registered)
                exists join recId
                from inventDim
                where inventDim.inventDimId == inventSum.InventDimId
                    && (inventDim.InventLocationId == 'Склад')
    {
        intLine++;
        ostatok += inventSum.PhysicalInvent - inventTrans.Qty;
        sysOperationProgress.setText(strFmt('Осталось %1 из %2',progressTotal-intLine,progressTotal));
        sysOperationProgress.setCount(intLine);

    }

    sysOperationProgress.kill();
    info(strfmt('Количество строк %1', intLine));
    info(strfmt('На %1 остаток %2 шт', fromDate, ostatok));
    info(strfmt('%1', 'Процесс успешно завершен'));
    info(strfmt('Время длительности процесса: %1', time2str(timenow() - startTime,1,1)));

} 


exists join по InventDim сделан, чтобы не возится с группировками еще и по полям из InventDim. Если делать напрямую в Query Analyzer, то можно заменить на INNER JOIN.

Так вот, большая и жирная ложка дегдя в этой бочке меда заключается в том, что такой запрос выполняется МЕДЛЕННЕЕ, чем цикл по InventSumDatePhysicalDim:: onHandQty()

На том же тестовом примере ЭТОТ запрос выполнялся примерно 1 час 20 минут. Через класс - около 50 минут. Результат одинаковый

Хотя, справедливости ради, следует отметить, что этот же самый запрос написанный напрямую в Query Analyzer выполнился примерно за минуту.

[Ronin]

Владимиру:
Вы удивитесь, посмотрев в профайлере как Аксапта выполняет этот запрос :-)