Post reply

Relationship between tables

  • October 16, 2012 at 1:29 pm

    #268561

    Hi, this query returns almost all that I need, but NUMEROCCF from TPRD is missing. If I just add it to that, the number of rows grows exceeding what was expected and messing up query's results, including total value.

    So how to join NUMEROCCF to this query as additional info, so row's quantity remains the same??

    Thank you!

  • October 16, 2012 at 1:37 pm

    #1549761

    where's the query you are refering to? did you forget to paste it so we can help you?

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • October 17, 2012 at 8:03 am

    #1550018

    OMG, sorry... forgot to post the query, here you go:

    use corporerm

    select datepart(year,M.DATAEMISSAO) AS ANO, DATEPART(MONTH,M.DATAEMISSAO) AS MÊS, M.NUMEROMOV AS NOTA,

    (SELECT M.VALORLIQUIDO) AS 'VALOR TOTAL' FROM TITMMOV IM

    INNER JOIN DNATUREZA D ON D.CODNAT = D.CODNAT

    INNER JOIN TMOV M ON M.IDMOV = IM.IDMOV

    INNER JOIN TPRD P ON P.IDPRD = IM.IDPRD

    INNER JOIN TUND U ON U.CODUND = IM.CODUND

    WHERE D.CODNAT IN ('5.101', '5.101.01', '5.101.02', '5.101.03', '6.101', '6.101.02', '7.101.01', '5.124.01', '5.124', '7.101')

    AND M.STATUS not in ('C') AND CODTMV IN ('2.2.01', '2.2.02', '2.2.04', '2.2.14', '2.2.15')

    AND IM.CODCOLIGADA = 1 AND P.CODCOLIGADA = 1 AND DATEPART(YEAR,M.DATAEMISSAO)= 2012

    AND DATEPART(MONTH,M.DATAEMISSAO)= 9 AND M.SERIE = '1'

    GROUP BY DATEPART(YEAR,M.DATAEMISSAO),DATEPART(MONTH,M.DATAEMISSAO), M.NUMEROMOV, M.VALORLIQUIDO

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply