Jump to content
Fórum Script Brasil
  • 0

Não esta agrupando por - GROUP BY TPRD.NOMEFANTASIA


Question

NOME    SALDO_ANTERIOR    TOT_ENTRADAS    TOTAL_BAIXAS    SALDO_ATUAL
AA.- 71.91 X 76.27 X 1.35 X 1.14    944,6800    2,0000    2,0000    122701,8160
ABRAC 9MM 9 X 13 001315    944,6800    8,0000    8,0000    122701,8160
ABRACADEIRA DE NYLON PRETA 5X390MM    944,6800    2997,0000    903,0000    122701,8160
ACUCAR REFINADO GUARANI PCT 1KG    944,6800    369,0000    237,0000    122701,8160
ADESIVO INSTANTANEO100G      944,6800    2,0000    1,0000    122701,8160
 

Saldo anterior e saldo atual - esta agrupando eu gostaria de viesse por item igual TOT_ENTRADAS    TOTAL_BAIXAS

segue abaixo o script:

SET DATEFORMAT dmy;
SELECT TPRD.NOMEFANTASIA AS NOME,
    (SELECT (SUM(CASE WHEN(TMOV.CODTMV IN ('1.2.02', '4.1.01', '4.1.02'))THEN TITMMOV.QUANTIDADE ELSE '0' END))
     FROM TMOV
     left JOIN TITMMOV ON (TITMMOV.IDMOV = TMOV.IDMOV 
          AND TITMMOV.CODCOLIGADA = TMOV.CODCOLIGADA)
     JOIN TPRD ON (TPRD.IDPRD = TITMMOV.IDPRD 
          AND TPRD.CODCOLIGADA = TITMMOV.CODCOLIGADA)
      WHERE TMOV.CODCOLIGADA = '1' AND TMOV.CODTMV IN ('1.2.02', '4.1.01', '4.1.02') 
      AND TMOV.STATUS not in ('C')
      AND TPRD.codigoprd like :Cod_Estruturado
        AND TMOV.DATAEMISSAO < :DATAINI_D )
              -           
    (SELECT (SUM(CASE WHEN(TMOV.CODTMV IN ('1.2.01','4.1.03'))THEN TITMMOV.QUANTIDADE ELSE '0' END))
    FROM TMOV
    left JOIN TITMMOV ON (TITMMOV.IDMOV = TMOV.IDMOV 
        AND TITMMOV.CODCOLIGADA = TMOV.CODCOLIGADA)
    JOIN TPRD ON (TPRD.IDPRD = TITMMOV.IDPRD
        AND TPRD.CODCOLIGADA = TITMMOV.CODCOLIGADA)
    WHERE TMOV.CODCOLIGADA = '1' AND TMOV.CODTMV IN ('1.2.01','4.1.03')
    AND TMOV.STATUS not in ('C')
    AND TPRD.codigoprd like :Cod_Estruturado
        AND TMOV.DATAEMISSAO < :DATAINI_D )AS SALDO_ANTERIOR,     
                        
        
        
        SUM(CASE WHEN(TMOV.CODTMV IN ('1.2.02', '4.1.01', '4.1.02') )THEN TITMMOV.QUANTIDADE ELSE '0' END)AS TOT_ENTRADAS,
        SUM(CASE WHEN(TMOV.CODTMV IN ('1.2.01','4.1.03'))THEN TITMMOV.QUANTIDADE ELSE '0' END) AS TOTAL_BAIXAS,

        (SELECT (SUM(CASE WHEN(TMOV.CODTMV IN ('1.2.02', '4.1.01', '4.1.02'))THEN TITMMOV.QUANTIDADE ELSE '0' END))
FROM TMOV
            INNER JOIN TITMMOV
                ON (TITMMOV.IDMOV = TMOV.IDMOV
                AND TITMMOV.CODCOLIGADA = TMOV.CODCOLIGADA)

            INNER JOIN TPRD
                ON (TPRD.IDPRD = TITMMOV.IDPRD
                AND TPRD.CODCOLIGADA = TITMMOV.CODCOLIGADA)
        WHERE
                TMOV.CODCOLIGADA = '1'
            AND TMOV.CODTMV IN ('1.2.02', '4.1.01', '4.1.02')
            AND TMOV.STATUS not in  ('C')            
            AND TPRD.codigoprd like :Cod_Estruturado
            AND TMOV.DATAEMISSAO <= :DATAFIM_D ) 
        -
        (SELECT (SUM(CASE WHEN(TMOV.CODTMV IN ('1.2.01','4.1.03'))THEN TITMMOV.QUANTIDADE ELSE '0' END))
        FROM TMOV
            INNER JOIN TITMMOV
                ON (TITMMOV.IDMOV = TMOV.IDMOV
                AND TITMMOV.CODCOLIGADA = TMOV.CODCOLIGADA)

            INNER JOIN TPRD
                ON (TPRD.IDPRD = TITMMOV.IDPRD
                AND TPRD.CODCOLIGADA = TITMMOV.CODCOLIGADA)
        WHERE
                TMOV.CODCOLIGADA = '1'
            AND TMOV.CODTMV IN ('1.2.01','4.1.03')
            AND TMOV.STATUS not in ('C')
            AND TPRD.codigoprd like :Cod_Estruturado
            AND TMOV.DATAEMISSAO <= :DATAFIM_D ) as saldo_atual              
FROM TMOV
INNER JOIN TITMMOV ON (TITMMOV.IDMOV = TMOV.IDMOV AND TITMMOV.CODCOLIGADA = TMOV.CODCOLIGADA)
INNER JOIN TPRD     ON (TPRD.IDPRD = TITMMOV.IDPRD AND TPRD.CODCOLIGADA = TITMMOV.CODCOLIGADA)
WHERE     TMOV.CODCOLIGADA = '1'
    AND TMOV.CODTMV IN ('1.2.01','4.1.03', '1.2.02', '4.1.01', '4.1.02')
    aND TMOV.STATUS not in ('C') AND 
    TPRD.codigoprd like :Cod_Estruturado
    AND tmov.dataemissao BETWEEN :DATAINI_D  
    AND :DATAFIM_D    
GROUP BY TPRD.NOMEFANTASIA
 ORDER BY TPRD.NOMEFANTASIA

 

 

 

Link to post
Share on other sites

0 answers to this question

Recommended Posts

There have been no answers to this question yet

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Answer this question...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Cloud Computing


  • Forum Statistics

    • Total Topics
      148682
    • Total Posts
      644511
×
×
  • Create New...