Select dentro de Select trazendo saldo anterior e atual

[Diel16 0]

Boa tarde

Estou tentando fazer um select em 2 tabelas que traz o saldo anterior e o saldo atual conforme a data selecionada

segue meu sql o problema está apenas no ValorInicial e ValorFinal que traz o mesmo dado para todas codmoeda

 

SELECT s.codmoeda,
	ValorInicial = (SELECT 
		              SUM(s.ValorInicial)
	                 FROM SaldosBancarios s
	                WHERE s.CodFilial = '014'
	                  AND s.Data = '2012-10-17'               
	                  AND s.CodConta NOT LIKE 'POS%'
	                  AND (s.ValorInicial <> 0.00
	                   OR s.ValorFinal <> 0.00)
	), 
      Entrada = (SELECT  
			   ISNULL(sum(case when Valor>=0 then valor end),0)
		  FROM MovCorrente m
		 WHERE m.CodFilial = '014' 
		   AND m.DtMov BETWEEN '2012-10-17' AND '2012-10-18'
		   AND m.codmoeda = s.codmoeda
		   AND m.CodConta NOT LIKE 'POS%'),
      Saida = (SELECT  
				ISNULL(sum(case when m.Valor<0 then m.valor end),0)
		  FROM MovCorrente m
		 WHERE m.CodFilial = '014' 
		   AND m.DtMov BETWEEN '2012-10-17' AND '2012-10-18'
		   AND m.codmoeda = s.codmoeda
		   AND m.CodConta NOT LIKE 'POS%'), 	 
    ValorFinal = (SELECT 
		              SUM(s.ValorFinal)ValorFinal
	                 FROM SaldosBancarios s
	                WHERE s.CodFilial = '014'
	                  AND s.Data = '2012-10-18'                  
	                  AND s.CodConta NOT LIKE 'POS%'
	                  AND (s.ValorInicial <> 0.00
	                   OR s.ValorFinal <> 0.00)
	) 
 FROM SaldosBancarios s 
WHERE s.CodFilial = '014'
  AND s.Data BETWEEN '2012-10-17' AND '2012-10-18'
  AND s.CodConta NOT LIKE 'POS%'  
  AND (s.ValorInicial <> 0.00
  OR s.ValorFinal <> 0.00)
GROUP BY s.codmoeda

[advaldomesquita 93]

em casa subselect você tem que igualar o campo de moeda.

deixaria a estrutura parecida com isso:

SELECT saldo.codmoeda,
               ValorInicial = (SELECT 
                                     SUM(s.ValorInicial)
                                FROM SaldosBancarios s
                               WHERE s.CodFilial = '014'
                                 AND s.Data = '2012-10-17'               
                                 AND s.CodConta NOT LIKE 'POS%'
                                 AND (s.ValorInicial <> 0.00
                                  OR s.ValorFinal <> 0.00)
                                  and saldo.codmoeda = s.codmoeda
               ), 
      Entrada = (SELECT  
                                  ISNULL(sum(case when Valor>=0 then valor end),0)
                         FROM MovCorrente m
                        WHERE m.CodFilial = '014' 
                          AND m.DtMov BETWEEN '2012-10-17' AND '2012-10-18'
                          AND m.codmoeda = saldo.codmoeda
                          AND m.CodConta NOT LIKE 'POS%'),
      Saida = (SELECT  
                                       ISNULL(sum(case when m.Valor<0 then m.valor end),0)
                         FROM MovCorrente m
                        WHERE m.CodFilial = '014' 
                          AND m.DtMov BETWEEN '2012-10-17' AND '2012-10-18'
                          AND m.codmoeda = saldo.codmoeda
                          AND m.CodConta NOT LIKE 'POS%'),      
    ValorFinal = (SELECT 
                                     SUM(s.ValorFinal)ValorFinal
                                FROM SaldosBancarios s
                               WHERE s.CodFilial = '014'
                                 AND s.Data = '2012-10-18'                  
                                 AND s.CodConta NOT LIKE 'POS%'
                                 AND (s.ValorInicial <> 0.00
                                  OR s.ValorFinal <> 0.00)
                                  and saldo.codmoeda = s.codmoeda
               ) 
 FROM SaldosBancarios saldo 
WHERE saldo.CodFilial = '014'
  AND saldo.Data BETWEEN '2012-10-17' AND '2012-10-18'
  AND saldo.CodConta NOT LIKE 'POS%'  
  AND (saldo.ValorInicial <> 0.00
  OR saldo.ValorFinal <> 0.00)
GROUP BY saldo.codmoeda