[ОТВЕТИТЬ]
Опции темы
05.09.2013 16:53  
PavelGS
Подскажите как выбрать из БД кол-во дней без остатка за месяц ?
 
05.09.2013 17:04  
OlegON
Нарываешься на ответ "запросом"? :) Тут есть где-то запрос от Mtirt по вычислению остатка на дату, соответственно, тебе понадобятся те даты, где оно =0 и ооочень шустрый сервер. Я бы порекомендовал потихоньку создавать табличку предрасчетов остатков каждый день, да и то, это если у тебя двигать задним числом особо не любят... хотя можно прикрутить, чтобы процедура предрасчета смотрела даты документов по smdoclog и включала расчет не только прошедшего дня.
 
05.09.2013 17:07  
PavelGS
Цитата:
Сообщение от OlegON
Нарываешься на ответ "запросом"? :) Тут есть где-то запрос от Mtirt по вычислению остатка на дату, соответственно, тебе понадобятся те даты, где оно =0 и ооочень шустрый сервер. Я бы порекомендовал потихоньку создавать табличку предрасчетов остатков каждый день.
:)
Это будет выгрузка 1-н раз в месяц.
Помогите с запросом камрады .
 
05.09.2013 17:12  
OlegON
Да хоть раз в год, если у тебя такой таблички не будет, запрос будет считать в лучшем случае до конца следующего месяца, если за месяц и, например, по всем активным артикулам.
По запросу наврал, писала не Mtirt, а svtl, мои извинения: Функция для получения остатка на дату - Страница 3
 
05.09.2013 17:17  
PavelGS
Цитата:
Сообщение от OlegON
Да хоть раз в год, если у тебя такой таблички не будет, запрос будет считать в лучшем случае до конца следующего месяца, если за месяц и, например, по всем активным артикулам.
По запросу наврал, писала не Mtirt, а svtl, мои извинения: Функция для получения остатка на дату - Страница 3
Вот половина запроса, работает 5 - 8 мин по 24 магазинам

Код:
SELECT 
    C.ARTICLE AS PRODUCT_ID,
    SL.ID AS STORE_ID,
    '' AS Company_Id,
    '' AS Network_Id,
    C.SHORTNAME AS PRODUCT_DESCRIPTION,
    contr.POSTID AS  SUPPLIER_ID,
    contr.POSTNAME AS   SUPPLIER_DESCRIPTION,
    0 MANDATORY_ASSORTMENT, -- группа А
    0 AS BRAND_TYPE,
    '' AS CUSTOMER_INFO_1,
    '' AS CUSTOMER_INFO_2,
    '' AS CUSTOMER_INFO_3,
    '' AS CUSTOMER_INFO_4,
    '' AS PRODUCT_TRACKING,
    M.ID AS PRODUCT_UNIT_CODE,
    0 AS SKU_PACKAGING,
    1 AS PRODUCT_UNIT_VALUE,
    CLAS.ID_CAT_N1 AS ID_CAT_N1,
    CLAS.ID_CAT_N2 AS ID_CAT_N2,
    CLAS.ID_CAT_N3 AS ID_CAT_N3,
    CLAS.ID_CAT_N4 AS ID_CAT_N4,
    CLAS.ID_CAT_N5 AS ID_CAT_N5,
    (CASE
        WHEN contrRC.POSTID = 2679 THEN 1
        ELSE 2
    END) AS DSD_WAREHOUSE,
    '' AS ORDER_MINIMUM_QUANTITY,
    '' AS PAYMENT_TERM,
    '' AS PRODUCT_ID2,
    '' AS FACING_QUANTITY,
    '' AS FACING_SIZE,
    '' AS FACING_LOCATION,
    '' AS SAFETY_STOCK,
    '' AS LINEAR_STOCK,
    PROZN.PRICE - PZAK.PRICE AS MARGIN_VALUE,
    CP.PROPVAL AS LEAD_TIME_REPLENISHMENT,
    '' AS AVERAGE_BEST_BEFORE_DATE,
    TORC.QUANTITY AS STOCK_ADJUSTMENT,
    (select supermag.SMGETPRICEONDATERL(C.ARTICLE,PROZN.PRICETYPE,SL.ID,TO_DATE('01.08.13','DD.MM.YY')) from dual) AS SALES_PRICE_START,
    (select supermag.SMGETPRICEONDATERL(C.ARTICLE,PROZN.PRICETYPE,SL.ID,TO_DATE('20.08.13','DD.MM.YY')) from dual) AS SALES_PRICE_END,
    SALE.SALE / SALE.QUANTITY AS AVERAGE_SALES_PRICE,
    TAX.RATE AS VAT,
    (CASE 
        WHEN startgoods.QUANTITY > 0 THEN startgoods.QUANTITY
        ELSE 0
    END) AS STOCK_QUANTITY_START,
    (CASE 
        WHEN endgoods.QUANTITY > 0 THEN endgoods.QUANTITY
        ELSE 0
    END) AS STOCK_QUANTITY_END,
     (CASE 
        WHEN (startgoods.QUANTITY +endgoods.QUANTITY) >0 THEN (startgoods.QUANTITY +endgoods.QUANTITY) /2
        ELSE 0
     END) AS AVERAGE_STOCK_QUANTITY,
    PRIHOD.QUANTITY AS QUANTITY_RECEIVE_PERIODE,
    SALE.QUANTITY AS QUANTITY_SALES_PERIODE,
    SALE.MAXQUANTITY AS HIGHEST_DAY_SALE_QUANTITY,
    '' AS MINIMUM_SHELVE_SIZE,
    '' AS QUANTITY_SALES_FORECAST_PERIOD,
    '' AS QUANTITY_SALES_FC_NEXT_PERIOD,
    '' AS QUANTITY_SALES_FT_NEXT_PERIOD,
    MADATES.DATEBEGIN AS PROMOTION_START_DATE,
    MADATES.DATEEND AS PROMOTION_END_DATE,
    MAKEY.pKEY AS PROMOTION_ID,
    1 AS PERMANENT_TEMPORARY,
    PZAK.PRICE AS MINIMUM_COST_PRICE,
    -- если по неделям
    '' AS F_S_Q_END_FIRST_WEEK,
    '' AS F_S_Q_END_SECOND_WEEK,
    '' AS F_S_Q_END_THIRD_WEEK,
    '' AS F_S_Q_END_FOURTH_WEEK,
    '' AS F_S_Q_END_FIFTH_WEEK,
    '' AS Q_RECEIVED_FIRST_WEEK,
    '' AS Q_RECEIVED_SECOND_WEEK,
    '' AS Q_RECEIVED_THIRD_WEEK,
    '' AS Q_RECEIVED_FOURTH_WEEK,
    '' AS Q_RECEIVED_FIFTH_WEEK,
    '' AS Q_SOLD_FIRST_WEEK,
    '' AS Q_SOLD_SECOND_WEEK,
    '' AS Q_SOLD_THIRD_WEEK,
    '' AS Q_SOLD_FOURTH_WEEK,
    '' AS Q_SOLD_FIFTH_WEEK,
    '' AS PERIOD_ORDER_MODE,
    '' AS FIRST_WEEK_ORDER_MODE,
    '' AS SECOND_WEEK_ORDER_MODE,
    '' AS THIRD_WEEK_ORDER_MODE,
    '' AS FOURTH_WEEK_ORDER_MODE,
    '' AS FIFTH_WEEK_ORDER_MODE,
    -- конец по неделям
    0 AS REBATE_VALUE,
    2 AS REBATE_INCLUDED_IN_COST_OR_NOT,
    '' AS Q_STOCK_TARGET_DAY,
    '' AS SEASONAL_PRODUCT,
    '' AS SEASON_START_DATE,
    '' AS SEASON_END_DATE,
    '' AS PRODUCT_ALTERNATIVE_CODE,
    reviz.QUANTITY AS STOCK_CORRECTION_QUANTITY,
    '' AS PRESENTATION_STOCK,
    1 AS NORMAL_BUY_SOLD,
    0 AS IMPORT

    
FROM
    SUPERMAG.SMCARD C

    RIGHT JOIN SUPERMAG.SMSTORELOCATIONS SL ON SL.ACCEPTED=1 AND SL.IDCLASS=1 AND SL.ID NOT IN (6)
    LEFT JOIN SUPERMAG.SAMeasurement M ON M.ID=C.IDMEASUREMENT
    LEFT JOIN (SELECT 
        S.ARTICLE,
        COL.LOCATIONID as LOCATIONID ,
        CI.ID AS POSTID,
        CI.NAME AS POSTNAME
        FROM SUPERMAG.SMDOCUMENTS D,SUPERMAG.SMSPEC S ,SUPERMAG.SMCLIENTINFO CI,SUPERMAG.SMCONTRACTLOCATIONS COL, SUPERMAG.SMCONTRACTS SMC
        WHERE
        S.DOCID = D.ID
        AND S.DOCTYPE = D.DOCTYPE
        AND D.DOCTYPE = 'CO'
        AND D.CLIENTINDEX = CI.ID
        AND COL.DOCTYPE = D.DOCTYPE
        AND COL.ID = D.ID
        AND D.DOCSTATE IN (2,3)
        AND SMC.ID = D.ID
        AND SMC.DOCTYPE = D.DOCTYPE
        AND CI.ID not in (2679)
        AND (SMC.ENDSAT >= sysdate or SMC.ENDSAT IS NULL )
        AND SMC.BEGINSAT < sysdate
        ORDER BY  D.CREATEDAT ASC) contr ON contr.LOCATIONID = SL.ID AND contr.ARTICLE = C.ARTICLE
        
            LEFT JOIN (SELECT 
        S.ARTICLE,
        COL.LOCATIONID as LOCATIONID ,
        CI.ID AS POSTID
        FROM SUPERMAG.SMDOCUMENTS D,SUPERMAG.SMSPEC S ,SUPERMAG.SMCLIENTINFO CI,SUPERMAG.SMCONTRACTLOCATIONS COL, SUPERMAG.SMCONTRACTS SMC
        WHERE
        S.DOCID = D.ID
        AND S.DOCTYPE = D.DOCTYPE
        AND D.DOCTYPE = 'CO'
        AND D.CLIENTINDEX = CI.ID
        AND COL.DOCTYPE = D.DOCTYPE
        AND COL.ID = D.ID
        AND D.DOCSTATE IN (2,3)
        AND SMC.ID = D.ID
        AND SMC.DOCTYPE = D.DOCTYPE
        AND CI.ID in (2679)
        AND (SMC.ENDSAT >= sysdate or SMC.ENDSAT IS NULL )
        ORDER BY  D.CREATEDAT ASC) contrRC ON contrRC.LOCATIONID = SL.ID AND contrRC.ARTICLE = C.ARTICLE
        
        LEFT JOIN (
            select
            C.ARTICLE AS ARTICLE,
                SUBSTR(CL.TREE,0,INSTR(CL.TREE,'.',1,1)-1) AS ID_CAT_N1, 
                SUBSTR(CL.TREE,INSTR(CL.TREE,'.',1,1)+1,INSTR(CL.TREE,'.',1,2)-(INSTR(CL.TREE,'.',1,1)+1) ) AS ID_CAT_N2,
                SUBSTR(CL.TREE,INSTR(CL.TREE,'.',1,2)+1,INSTR(CL.TREE,'.',1,3)-(INSTR(CL.TREE,'.',1,2)+1) ) AS ID_CAT_N3,
                SUBSTR(CL.TREE,INSTR(CL.TREE,'.',1,3)+1,INSTR(CL.TREE,'.',1,4)-(INSTR(CL.TREE,'.',1,3)+1) ) AS ID_CAT_N4,
                SUBSTR(CL.TREE,INSTR(CL.TREE,'.',1,4)+1,INSTR(CL.TREE,'.',1,5)-(INSTR(CL.TREE,'.',1,4)+1) ) AS ID_CAT_N5,
                CL.NAME AS NAME_CAT
            from SUPERMAG.SACARDCLASS CL, SUPERMAG.SMCARD C 
            WHERE 
            C.IDCLASS = CL.ID) CLAS ON CLAS.ARTICLE = C.ARTICLE
            
            LEFT JOIN SUPERMAG.SMPRICES PZAK ON PZAK.ARTICLE = C.ARTICLE AND PZAK.STORELOC = SL.ID AND PZAK.PRICETYPE=1 
            LEFT JOIN SUPERMAG.SMPRICES PROZN ON PROZN.ARTICLE = C.ARTICLE AND PROZN.STORELOC = SL.ID AND PROZN.PRICETYPE > 1  AND PROZN.PRICETYPE <> 24 
            LEFT JOIN SUPERMAG.SMCLIENTPROPERTIES CP ON CP.IDCLIENT=CONTR.POSTID AND CP.PROPID=TO_CHAR('ZAKAZ'||TO_CHAR(SL.ID)) 
            LEFT JOIN (SELECT 
                        S.ARTICLE AS ARTICLE,
                        D.LOCATIONFROM AS LOCATIONFROM,
                        SUM(S.QUANTITY) AS  QUANTITY
                        FROM SUPERMAG.SMDOCUMENTS D, SUPERMAG.SMSPEC S 
                        WHERE
                        D.ID = S.DOCID
                        AND D.DOCTYPE = S.DOCTYPE
                        AND D.DOCSTATE = 3
                        AND D.DOCTYPE = 'WO'
                        AND D.CLIENTINDEX = '2679'
                        AND D.CREATEDAT BETWEEN TO_DATE('01.08.13','DD.MM.YY') AND TO_DATE('20.08.13','DD.MM.YY')
                        GROUP BY S.ARTICLE , D.LOCATIONFROM) TORC ON TORC.LOCATIONFROM = SL.ID AND TORC.ARTICLE = C.ARTICLE
                        
              LEFT JOIN (SELECT
                    ARTICLE,
                    SUM (QUANTITY) AS QUANTITY,
                    SUM (QUANTITY * ITEMPRICE) AS SALE,
                    (CASE
                       WHEN MAX (QUANTITY) > 0 THEN  MAX (QUANTITY)
                       ELSE 0   
                    END) AS MAXQUANTITY,
                    (CASE
                        WHEN MAX (QUANTITY * ITEMPRICE) > 0 THEN MAX (QUANTITY * ITEMPRICE)
                        ELSE 0 
                    END) AS MAXSALE,
                    (CASE 
                        WHEN MIN (QUANTITY) > 0 THEN MIN (QUANTITY)
                        ELSE 0
                    END) AS MINQUANTITY,
                    (CASE
                        WHEN MIN (QUANTITY * ITEMPRICE) > 0 THEN MIN (QUANTITY * ITEMPRICE)
                        ELSE 0                     
                    END) AS MINSALE,
                    MX
                    FROM(
                    SELECT 
                    S.ARTICLE AS ARTICLE,
                    S.ITEMPRICE AS ITEMPRICE,
                    (CASE
                        WHEN D.DOCTYPE = 'CS' THEN S.QUANTITY
                        ELSE  S.QUANTITY*-1
                    END) AS QUANTITY,
                    (CASE 
                        WHEN D.DOCTYPE = 'CS' THEN D.LOCATIONFROM
                        ELSE D.LOCATIONTO
                    END) AS MX
                     FROM SUPERMAG.SMDOCUMENTS D, SUPERMAG.SMSPEC S 
                    WHERE
                        D.DOCTYPE = S.DOCTYPE
                        AND S.DOCID = D.ID
                        AND D.DOCTYPE IN ('CS','CR')
                        AND D.CREATEDAT BETWEEN TO_DATE('01.08.13','DD.MM.YY') AND TO_DATE('20.08.13','DD.MM.YY'))
                    GROUP BY MX,ARTICLE) SALE ON SALE.ARTICLE = C.ARTICLE AND SL.ID = SALE.MX
                    
              LEFT JOIN (select
                    TAX.ARTICLE AS ARTICLE, 
                        (CASE
                            WHEN TAX.TAXGROUPID =2 then 10
                            WHEN TAX.TAXGROUPID =3 then 18
                            ELSE 0
                        END) AS RATE
                        from  SUPERMAG.SMCARDTAX tax) TAX ON TAX.ARTICLE = C.ARTICLE
                        
                        LEFT JOIN (SELECT /*+ USE_MERGE(T1,G) */ G.ARTICLE, 
       G.STORELOC, 
        G.QUANTITY + T1.QUANTITY AS QUANTITY 
  FROM SUPERMAG.SMGOODS G 
       LEFT JOIN (SELECT ARTICLE, 
                         MX, 
                         SUM(QUANTITY) AS QUANTITY 
                    FROM (SELECT S1.ARTICLE, 
                                 CASE WHEN D1.DOCTYPE IN ('WI', 'CR') THEN S1.QUANTITY 
                                      ELSE S1.QUANTITY * -1 END QUANTITY, 
                                 CASE WHEN D1.DOCTYPE IN ('WI', 'CR') THEN D1.LOCATIONTO 
                                      ELSE D1.LOCATIONFROM END MX 
                            FROM SUPERMAG.SMDOCUMENTS D1, 
                                 SUPERMAG.SMSPEC S1 
                           WHERE D1.DOCTYPE IN ('CR', 'WI', 'CS', 'WO') 
                             AND D1.CREATEDAT >= TO_DATE ('01.08.13', 'DD.MM.YY') 
            AND S1.DOCID = D1.ID 
            AND S1.DOCTYPE = D1.DOCTYPE) GROUP BY ARTICLE, MX) T1 
         ON T1.ARTICLE = G.ARTICLE 
            AND T1.MX = G.STORELOC) startgoods ON startgoods.ARTICLE = C.ARTICLE AND startgoods.STORELOC = SL.ID



LEFT JOIN (SELECT /*+ USE_MERGE(T1,G) */ G.ARTICLE, 
       G.STORELOC, 
        G.QUANTITY + T1.QUANTITY AS QUANTITY 
  FROM SUPERMAG.SMGOODS G 
       LEFT JOIN (SELECT ARTICLE, 
                         MX, 
                         SUM(QUANTITY) AS QUANTITY 
                    FROM (SELECT S1.ARTICLE, 
                                 CASE WHEN D1.DOCTYPE IN ('WI', 'CR') THEN S1.QUANTITY 
                                      ELSE S1.QUANTITY * -1 END QUANTITY, 
                                 CASE WHEN D1.DOCTYPE IN ('WI', 'CR') THEN D1.LOCATIONTO 
                                      ELSE D1.LOCATIONFROM END MX 
                            FROM SUPERMAG.SMDOCUMENTS D1, 
                                 SUPERMAG.SMSPEC S1 
                           WHERE D1.DOCTYPE IN ('CR', 'WI', 'CS', 'WO') 
                             AND D1.CREATEDAT >= TO_DATE ('20.08.13', 'DD.MM.YY') 
            AND S1.DOCID = D1.ID 
            AND S1.DOCTYPE = D1.DOCTYPE) GROUP BY ARTICLE, MX) T1 
         ON T1.ARTICLE = G.ARTICLE 
            AND T1.MX = G.STORELOC) endgoods ON endgoods.ARTICLE = C.ARTICLE AND endgoods.STORELOC = SL.ID  
            
            LEFT JOIN (SELECT ARTICLE, 
                         MX, 
                         SUM(QUANTITY) AS QUANTITY 
                    FROM (SELECT S1.ARTICLE, 
                                 CASE WHEN D1.DOCTYPE =  'WO' THEN S1.QUANTITY *-1 
                                      ELSE S1.QUANTITY  END QUANTITY, 
                                 CASE WHEN D1.DOCTYPE IN ('WI') THEN D1.LOCATIONTO 
                                      ELSE D1.LOCATIONFROM END MX 
                            FROM SUPERMAG.SMDOCUMENTS D1, 
                                 SUPERMAG.SMSPEC S1 
                           WHERE D1.DOCTYPE IN ('WI','WO') 
                             AND D1.CREATEDAT >= TO_DATE ('01.08.13', 'DD.MM.YY') 
                             AND D1.CREATEDAT <= TO_DATE ('20.08.13', 'DD.MM.YY')
            AND S1.DOCID = D1.ID 
            AND S1.DOCTYPE = D1.DOCTYPE) GROUP BY ARTICLE, MX) PRIHOD ON PRIHOD.MX = SL.ID AND PRIHOD.ARTICLE = C.ARTICLE
            
     LEFT JOIN (SELECT 
        S.ARTICLE AS ARTICLE,
        AA.LOCID AS MX,
        TO_DATE(AC.DATEBEGIN,'DD.MM.YY' ) AS DATEBEGIN, 
        TO_DATE(AC.DATEEND,'DD.MM.YY' ) AS DATEEND
    FROM 
        SUPERMAG.SMAUCTIONS AC, 
        SUPERMAG.SMSPEC S ,
        SUPERMAG.SMAUCTIONATOMS AA
    WHERE
        AC.DOCTYPE = 'MA'
        AND S.DOCTYPE = AC.DOCTYPE
        AND S.DOCID = AC.ID
        AND AA.AUCID = AC.ID
        AND AA.DOCTYPE = AC.DOCTYPE
        AND (LOWER(AC.NAME) LIKE '%акция%')
        AND (AC.DATEEND >=TO_DATE('20.08.12','DD.MM.YY'))) MADATES ON MADATES.MX = SL.ID AND MADATES.ARTICLE = C.ARTICLE
        
       LEFT JOIN (SELECT 
        S.ARTICLE AS ARTICLE,
        AA.LOCID AS MX,
        (CASE 
            WHEN (LOWER(AC.NAME) LIKE '%акция%') THEN 4
            WHEN (LOWER(AC.NAME) LIKE '%супер%') THEN 6
            ELSE 0
        END) AS pKEY
    FROM 
        SUPERMAG.SMAUCTIONS AC, 
        SUPERMAG.SMSPEC S ,
        SUPERMAG.SMAUCTIONATOMS AA
    WHERE
        AC.DOCTYPE = 'MA'
        AND S.DOCTYPE = AC.DOCTYPE
        AND S.DOCID = AC.ID
        AND AA.AUCID = AC.ID
        AND AA.DOCTYPE = AC.DOCTYPE
        AND  (AC.DATEBEGIN >=TO_DATE('01.08.12','DD.MM.YY'))         AND(AC.DATEBEGIN <=TO_DATE('20.08.12','DD.MM.YY')) 
        ) MAKEY ON MAKEY.MX = SL.ID AND MAKEY.ARTICLE = C.ARTICLE
        
        LEFT JOIN (SELECT 
ARTICLE,
MX,
SUM(QUANTITY) AS QUANTITY 
FROM
(select
    S.ARTICLE,
    CASE WHEN D.DOCTYPE in ('WO','LA') THEN S.QUANTITY *-1 
             ELSE S.QUANTITY  
    END QUANTITY, 
    CASE 
           WHEN D.DOCTYPE IN ('WI','FA') THEN D.LOCATIONTO 
           ELSE D.LOCATIONFROM 
    END MX

from SUPERMAG.SMDOCUMENTS D, SUPERMAG.SMSPEC S
WHERE
--D.DOCTYPE
((D.OPCODE in (14,9) AND D.DOCTYPE in ('WO','WI')) or (D.DOCTYPE in ('FA','LA') AND D.DOCSTATE in (2,3) )) 
AND D.DOCSTATE in (2,3)
AND D.ID = S.DOCID
AND D.DOCTYPE = S.DOCTYPE
AND D.CREATEDAT >= TO_DATE('01.08.12','DD.MM.YY')
AND D.CREATEDAT <= TO_DATE('20.08.12','DD.MM.YY')
 
) GRoUP BY ARTICLE,MX 
) reviz ON reviz.MX = SL.ID AND reviz.ARTICLE = C.ARTICLE
 
 
Опции темы



Часовой пояс GMT +3, время: 01:24.

Все в прочитанное - Календарь - RSS - - Карта - Вверх 👫 Яндекс.Метрика
Форум сделан на основе vBulletin®
Copyright ©2000 - 2016, Jelsoft Enterprises Ltd. Перевод: zCarot и OlegON
В случае заимствования информации гипертекстовая индексируемая ссылка на Форум обязательна.