Merge select to totalize results

Asked

Viewed 85 times

0

Tabela desejada com o Select How to sum up the stored quantity of these products to be able to compare with the total order quantity for resale "QTD ORDER". How can I find the stored total and link to 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
sql sql-server

  • SUM( local 1 + local 2 + local 3 )

  • @Andretovar: Which column is the location information?

  • @Andretovar: Table STAT_PED_VENDA relates to which table and through which column? // Tip: In the FROM clause, do not mix old syntax with new syntax.

  • The column ( Estqlocarmazqtd ) with the stored quantities is in the ESTQ_LOC_ARMAZ table that presents the following primary keys : Empcod and Prodcodestr. I don’t know how to relate the table (ESTQ_LOC_ARMAZ) to the select I made totaling the amount of products in the sales order. @Josédiz

1 answer

0

A general rule of response:

You can turn the result of any select into a "virtual table" and make an operation on it. Don’t forget the T1 down there.

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

Browser other questions tagged sql sql-server

You are not signed in. Login or sign up in order to post.