[ТЕМА ЗАКРЫТА]
Опции темы
30.08.2007 11:54  
nekrom@nt.grave
Провертье кто-ндь или скажите где откуда можно получить те же данные
Код:
SELECT c.article as "Арт",
       q.subarticle as "СубАрт",
       c.name as "Наименование",
       NVL(inprices.price, 0) as "Зак цен",
       NVL(outprices.price,0) as "Отп цен",
       NVL(buy.quantity, 0) as "Приход(шт)",
       NVL(inprices.price, 0)*NVL(buy.quantity, 0) as "Приход($)",
       NVL(buy.quantity, 0)-NVL(saleret.quantity, 0) as "Реализация (шт)",
       NVL(buy.quantity, 0)*NVL(outprices.price,0)-NVL(saleret.quantity, 0)*NVL(outprices.price,0) as "Реализация ($)",
       (NVL(outprices.price,0)-NVL(inprices.price, 0))*NVL(buy.quantity, 0)-NVL(saleret.quantity, 0) as "Наценка",
       NVL(saleret.quantity, 0) as "Возврат(шт)",
       NVL(saleret.quantity, 0)*NVL(outprices.price,0) as "Возврат($)",
       saleret.createdat as "Возврат(дата)",--as "Возврат($)",
       NVL(q.quantity,0)-NVL(pr.quantity,0)+NVL(rs.quantity,0) as "Остаток(шт)",
       NVL((NVL(q.quantity,0)-NVL(pr.quantity,0)+NVL(rs.quantity,0))*
       NVL(outprices.price,0),0) as "Остаток($)"
  FROM supermag.smcard c,
       (
        SELECT q.article,
               qs.subarticle,
               NVL(qs.quantity,q.quantity) as quantity
          FROM supermag.smgoods q,
               supermag.smgoodsscale qs
         WHERE q.article=qs.article(+)
           AND q.storeloc=qs.storeloc(+)
           AND q.storeloc=:mesto
       ) q,
       (
        SELECT dc.article,
               ss.subarticle,
               SUM(NVL(ss.quantity,dc.quantity)) as quantity
          FROM supermag.smdocuments d,
               supermag.smspec dc,
               supermag.smspecscale ss
         WHERE d.doctype=dc.doctype
           AND d.doctype=ss.doctype(+)
           AND d.id=dc.docid
           AND d.id=ss.docid(+)
           AND d.docstate=3
           AND TO_DATE(d.createdat) >= :edate --('01.07.2007')
           AND d.locationto = :mesto
        GROUP BY dc.article, ss.subarticle
       ) pr,
       (
        SELECT dc.article,
               ss.subarticle,
               SUM(NVL(ss.quantity,dc.quantity)) as quantity
          FROM supermag.smdocuments d,
               supermag.smspec dc,
               supermag.smspecscale ss
         WHERE d.doctype=dc.doctype
           AND d.doctype=ss.doctype(+)
           AND d.id=dc.docid
           AND d.id=ss.docid(+)
           AND d.docstate=3
           AND TO_DATE(d.createdat) >=:edate-- ('01.07.2007')
           AND d.locationfrom = :mesto
           AND d.clientindex=:client
        GROUP BY dc.article, ss.subarticle
       ) rs,
       (
       SELECT dc.article,
               ss.subarticle,
               SUM(NVL(ss.quantity,dc.quantity)) as quantity
          FROM supermag.smdocuments d,
               supermag.smspec dc,
               supermag.smspecscale ss
         WHERE d.doctype=dc.doctype
           AND d.doctype=ss.doctype(+)
           AND d.id=dc.docid
           AND d.id=ss.docid(+)
           AND d.docstate=3
           AND (d.opcode = :flag_buy) -- 0 if sklad; 4 if tochka
           AND TO_DATE(d.createdat) between :sdate and :edate--to_date('01.07.2007', 'DD.MM.YYYY')
           AND not(ss.quantity = 0)
           AND d.locationto = :mesto
        GROUP BY dc.article, ss.subarticle
       ) buy,
       (
        SELECT dc.article,
               ss.subarticle,
               d.createdat,
               SUM(NVL(ss.quantity,dc.quantity)) as quantity
          FROM supermag.smdocuments d,
               supermag.smspec dc,
               supermag.smspecscale ss
         WHERE d.doctype=dc.doctype
           AND d.doctype=ss.doctype(+)
           AND d.id=dc.docid
           AND d.id=ss.docid(+)
           AND d.docstate=3
           AND d.opcode = 3
           AND TO_DATE(d.createdat) >= :sdate
           AND d.locationto = :mesto
           AND d.clientindex = :client--client!!!
        GROUP BY dc.article, ss.subarticle, d.createdat
       ) saleret,
       (SELECT DISTINCT his.PRICE, his.ARTICLE, qs.subarticle
            FROM SUPERMAG.SMPRICEHISTORY his, SUPERMAG.SMLOCPRICES prt,
            supermag.smgoodsscale qs
              WHERE his.PRICETYPE = :prtype--prt.PRICETYPE
              AND his.article=qs.article(+)
              AND prt.LOCID = his.STORELOC
              AND his.STORELOC = :mesto
              AND (bitand(prt.FLAGS, 2) = 2)
              AND his.RECID = (SELECT MAX(h.RECID)
                               FROM SUPERMAG.SMPRICEHISTORY h
                               WHERE h.STORELOC = his.STORELOC
                                 AND h.ARTICLE = his.ARTICLE
                                 AND h.PRICETYPE = his.PRICETYPE
                                 AND TRUNC(h.EVENTTIME) <= TRUNC(:edate))
        ) outprices ,     
       (
       SELECT DISTINCT his.PRICE, his.ARTICLE, qs.subarticle
            FROM SUPERMAG.SMPRICES his, supermag.smgoodsscale qs
              WHERE his.PRICETYPE = 1
              AND his.STORELOC = 1
              AND his.article=qs.article(+)
       ) inprices
 WHERE q.article=pr.article(+)
   AND q.subarticle=pr.subarticle(+)
   AND q.article=rs.article(+)
   AND q.subarticle=rs.subarticle(+)
   AND c.article=q.article(+)
   AND q.article=buy.article(+)
   AND q.subarticle=buy.subarticle(+)
   AND q.article=saleret.article(+)
   AND q.subarticle=saleret.subarticle(+)
   AND q.article=outprices.article(+)
   AND q.subarticle=outprices.subarticle(+)
   AND q.article=inprices.article(+)
   AND q.subarticle=inprices.subarticle(+)
   AND c.name like :name
 
31.08.2007 15:50  
nekrom@nt.grave
очень большая просьба хотя бы на вскидку сказать прально или нет считается остаток и реализация
 
 
Опции темы



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

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