Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Relationship between tables Expand / Collapse
Author
Message
Posted Tuesday, October 16, 2012 1:29 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, October 17, 2012 10:52 AM
Points: 2, Visits: 3
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!
Post #1373494
Posted Tuesday, October 16, 2012 1:37 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 4:42 PM
Points: 14,445, Visits: 37,858
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!
Post #1373496
Posted Wednesday, October 17, 2012 8:03 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, October 17, 2012 10:52 AM
Points: 2, Visits: 3
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
Post #1373806
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse