gistfile1.sql

SELECT tabela1.*
    FROM (
		SELECT
		IFNULL((
		  SELECT qtty
		  FROM sqlsi.stkchk a
		  WHERE 1=1
			AND a.storeno = store.no
			AND a.ym = '201303'
			AND a.prdno = pmm.prdno
			AND a.grade = pmm.grade
		),0) AS saldos_f,
		IFNULL((
		  SELECT qtty
		  FROM sqldados.stkchk a
		  WHERE 1=1
			AND a.storeno = store.no
			AND a.ym = '201303'
			AND a.prdno = pmm.prdno
			AND a.grade = pmm.grade
		),0) AS saldos_g,
		(
		  SELECT SUM(b.qtty)
		  FROM sqlsi.inv a FORCE INDEX(i7)
		  INNER JOIN sqlsi.iprd b USING(invno)
		  WHERE 1=1
			AND a.storeno = store.no
			AND a.date BETWEEN '20130401' AND '20130430'
			AND b.prdno = pmm.prdno
			AND b.grade = pmm.grade
		)+
		(
		  SELECT SUM(a.qtty)*(-1)
		  FROM sqlsi.xalog2 a
		  WHERE 1=1
			AND a.storeno = store.no
			AND a.date BETWEEN '20130401' AND '20130430'
			AND a.xatype = 11
			AND a.doc LIKE 'C.CF.%'
			AND a.prdno = pmm.prdno
			AND a.grade = pmm.grade
		)+
		(
		  SELECT SUM(a.qtty)*(-1)
		  FROM sqlsi.xalog2 a
		  WHERE 1=1
			AND a.storeno = store.no
			AND a.date BETWEEN '20130401' AND '20130430'
			AND qtty < 0
			AND a.prdno = pmm.prdno
			AND a.grade = pmm.grade
		)+
		(
		  SELECT SUM(IF(qtty > 0, qtty, 0))
		  FROM sqlsi.xedprd a
		  WHERE storeno = store.no
			AND a.date BETWEEN '20130401' AND '20130430'
			AND a.prdno = pmm.prdno
			AND a.grade = pmm.grade
		)+
		(
		  SELECT SUM(IF(qtty > 0, qtty, 0))
		  FROM sqlsi.stkmov a
		  WHERE a.storeno = store.no
			AND a.date BETWEEN '20130401' AND '20130430'
			AND a.prdno = pmm.prdno
			AND a.grade = pmm.grade
		)
		AS entradas_f,
		(
		  SELECT SUM(b.qtty)
		  FROM sqldados.inv a force index(i7)
		  INNER JOIN sqldados.iprd b ON
			(b.invno = a.invno)
		  WHERE 1=1
			AND a.storeno = store.no
			AND a.date BETWEEN '20130401' AND '20130430'
			AND b.prdno = pmm.prdno
			AND b.grade = pmm.grade
		)+
		(
		  SELECT SUM(a.qtty)*(-1)
		  FROM sqldados.xalog2 a
		  INNER JOIN sqlpdv.pxa ON (a.storeno = pxa.storeno AND a.pdvno = pxa.pdvno AND a.xano = pxa.xano)
		  WHERE 1=1
			AND a.storeno = store.no
			AND a.date BETWEEN '20130401' AND '20130430'
			AND a.qtty < 0
			AND a.prdno = pmm.prdno
			AND a.grade = pmm.grade
		)+
		(
		  SELECT SUM(IF(a.qtty > 0, a.qtty, 0))
		  FROM sqldados.xedprd a
		  WHERE a.storeno = store.no
			AND a.date BETWEEN '20130401' AND '20130430'
			AND a.prdno = pmm.prdno
			AND a.grade = pmm.grade
		)+
		(
		  SELECT SUM(IF(qtty > 0, qtty, 0)) AS entrada_g
		  FROM sqldados.stkmov a
		  WHERE a.storeno = store.no
			AND a.date BETWEEN '20130401' AND '20130430'
			AND a.prdno = pmm.prdno
			AND a.grade = pmm.grade
		)AS entradas_g,
		(
		  SELECT SUM(b.qtty*1000)
		  FROM sqlsi.nf a
		  INNER JOIN sqlsi.xaprd b ON (a.storeno = b.storeno AND a.pdvno = b.pdvno AND a.xano = b.xano)
		  WHERE 1=1
			AND a.storeno = store.no
			AND a.issuedate BETWEEN '20130401' AND '20130430'
			AND a.status = 0
			AND b.prdno = pmm.prdno
			AND b.grade = pmm.grade
		)+
		(
		  SELECT SUM(a.qtty) AS saida_f
		  FROM sqlsi.xalog2 a
		  WHERE 1=1
		  AND a.storeno = store.no
		  AND a.date BETWEEN '20130401' AND '20130430'
		  
			AND a.prdno = pmm.prdno
		   AND a.grade = pmm.grade
			AND a.qtty > 2
		)+
		(
		  SELECT SUM(IF(qtty < 0, (-1)*qtty, 0))
		  FROM sqlsi.xedprd a
		  WHERE a.storeno = store.no
			AND a.date BETWEEN '20130401' AND '20130430'
			AND a.prdno = pmm.prdno
			AND a.grade = pmm.grade
		)+
		(
		  SELECT SUM(IF(qtty < 0, (-1)*qtty, 0))
		  FROM sqlsi.stkmov a
		  WHERE a.storeno = store.no
			AND a.date BETWEEN '20130401' AND '20130430'
			AND a.prdno = pmm.prdno
			AND a.grade = pmm.grade
		)AS saidas_f, 
		(
		  SELECT SUM(b.qtty*1000)
		  FROM sqldados.nf a
		  INNER JOIN sqldados.xaprd b ON (a.storeno = b.storeno AND a.pdvno = b.pdvno AND a.xano = b.xano)
		  LEFT JOIN sqlpdv.pxa ON (pxa.storeno = a.storeno AND pxa.pdvno = a.pdvno AND pxa.xano = a.xano)
		  WHERE 1=1
			AND a.storeno = store.no
			AND a.issuedate BETWEEN '20130401' AND '20130430'
			AND a.status = 0
			AND ISNULL(pxa.xano)
			AND b.prdno = pmm.prdno
			AND b.grade = pmm.grade
		)+
		(
		  SELECT SUM(b.qtty*1000)
		  FROM sqldados.nf a
		  INNER JOIN sqldados.xaprd b ON (a.storeno = b.storeno AND b.pdvno = 0 AND a.xano = b.xano)
		  LEFT JOIN sqlpdv.pxa ON (pxa.storeno = a.storeno AND pxa.pdvno = a.pdvno AND pxa.xano = a.xano)
		  WHERE 1=1
			AND a.storeno = store.no
			AND a.issuedate BETWEEN '20130401' AND '20130430'
			AND a.status = 0
			AND ISNULL(pxa.xano)
			AND b.prdno = pmm.prdno
			AND b.grade = pmm.grade
		)+
		(
		  SELECT SUM(qtty)
		  FROM sqldados.xalog2 a
		  WHERE 1=1
			AND a.storeno = store.no
			AND a.date BETWEEN '20130401' AND '20130430'
			AND a.prdno = pmm.prdno
			AND a.grade = pmm.grade
			AND a.qtty > 2
		)+
		(
		  SELECT SUM(IF(a.qtty < 0, (-1)*a.qtty, 0))
		  FROM sqldados.xedprd a
		  WHERE a.storeno = store.no
			AND a.date BETWEEN '20130401' AND '20130430'
			AND a.prdno = pmm.prdno
			AND a.grade = pmm.grade
		)+
		(
		  SELECT SUM(IF(qtty < 0, (-1)*qtty, 0)) AS saida_g
		  FROM sqldados.stkmov a
		  WHERE a.storeno = store.no
			AND a.date BETWEEN '20130401' AND '20130430'
			AND a.prdno = pmm.prdno
			AND a.grade = pmm.grade
		)AS saidas_g,

		store.no AS storeno, pmm.prdno, pmm.grade,
		p.name AS nome_produto,
		prp_g.cost AS valor_g,
		prp_f.cost AS valor_f
	  FROM sqldados.store
	  LEFT JOIN webpdv.produto_movimento_mes pmm ON
		(pmm.storeno = store.no AND pmm.ym BETWEEN  '201304' AND '201304')
	  INNER JOIN webpdv.produto p ON
		(p.prdno = pmm.prdno)
	  INNER JOIN sqlsi.prp AS prp_f ON
		(prp_f.prdno = pmm.prdno AND prp_f.storeno = store.no)
	  INNER JOIN sqldados.prp AS prp_g ON
		(prp_g.prdno = pmm.prdno AND prp_g.storeno = store.no)
	  WHERE store.no = 1
	  GROUP BY storeno, prdno, grade
	) AS tabela1
	#HAVING estoque_final_f != estoque_final_g