Join select to total results

0

Howtototalthestoredquantityoftheseproductstobeabletocomparewiththetotalorderquantityforresale"QTD REQUEST". How can I do to find the total stored and join the result?

Select:

  SELECT ipv.ProdCodEstr 'CÓDIGO DO PRODUTO',
                       p.ProdNome AS 'NOME PRODUTO',
                       SUM (CAST(ipv.ItPedVendaQtd AS REAL)) AS 'QTD PEDIDO',
                           'total por produto' AS 'local 1',
                           'total por produto' AS 'local 2',
                           'total por produto' AS 'local 3'
FROM PED_VENDA pv WITH (nolock), STAT_PED_VENDA spv WITH (nolock), ITEM_PED_VENDA ipv WITH (nolock)
LEFT JOIN ESTQ_LOC_ARMAZ EstLoc WITH (nolock) ON (EstLoc.ProdCodEstr = ipv.ProdCodEstr)
LEFT JOIN PRODUTO p on(p.ProdCodEstr = ipv.ProdCodEstr)
WHERE ipv.PedVendaNum = pv.PedVendaNum
  AND p.ProdCodEstr = ipv.ProdCodEstr
  AND ipv.ItPedVendaServ LIKE 'Não'
  AND spv.StatPedVendaCod NOT IN ('08',
                                  '10',
                                  '11',
                                  '12',
                                  '13',
                                  '14',
                                  '15',
                                  '16')
  AND pv.PedVendaData BETWEEN '2017-01-01' AND -- Data iinicial
 '2017-12-01' -- Data final

GROUP BY ipv.ProdCodEstr,
         p.ProdNome
    
asked by anonymous 28.03.2017 / 15:43

1 answer

0

A general response:

You can transform the result of any select into a "virtual table" and make an operation on it. Do not forget the T1 downstairs.

In your case:

select sum('QTD PEDIDO')
from
(
 SELECT ipv.ProdCodEstr 'CÓDIGO DO PRODUTO',
                       p.ProdNome AS 'NOME PRODUTO',
                       SUM (CAST(ipv.ItPedVendaQtd AS REAL)) AS 'QTD PEDIDO',
                           'total por produto' AS 'local 1',
                           'total por produto' AS 'local 2',
                           'total por produto' AS 'local 3'
FROM PED_VENDA pv WITH (nolock), STAT_PED_VENDA spv WITH (nolock), ITEM_PED_VENDA ipv WITH (nolock)
LEFT JOIN ESTQ_LOC_ARMAZ EstLoc WITH (nolock) ON (EstLoc.ProdCodEstr = ipv.ProdCodEstr)
LEFT JOIN PRODUTO p on(p.ProdCodEstr = ipv.ProdCodEstr)
WHERE ipv.PedVendaNum = pv.PedVendaNum
  AND p.ProdCodEstr = ipv.ProdCodEstr
  AND ipv.ItPedVendaServ LIKE 'Não'
  AND spv.StatPedVendaCod NOT IN ('08',
                                  '10',
                                  '11',
                                  '12',
                                  '13',
                                  '14',
                                  '15',
                                  '16')
  AND pv.PedVendaData BETWEEN '2017-01-01' AND -- Data iinicial
 '2017-12-01' -- Data final

GROUP BY ipv.ProdCodEstr,
         p.ProdNome
) 
As T1
    
29.03.2017 / 16:02