Simplify Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Simplify Query

Author

Topic

psfaro
Starting Member

49 Posts

Posted - 2012-02-29 : 06:09:18

Hi
I have this query:

select cod_conta, orc_ano=orc_actual, cabiment=(select ...),
comprom =(select ..)
from CCTOFN

I need to perform in the same query diferences between "orc_ano" and "cabiment" that is a SubSelect

The question is if there is any simply way instead of

.. saldo=Orc_ano - (select ...)

Or i must use Joins instead of "sub selects"

Regards

Pedro

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-02-29 : 06:46:02

it depends on the subqueries.

N 56°04'39.26"
E 12°55'05.63"

psfaro
Starting Member

49 Posts

Posted - 2012-02-29 : 06:51:33

Hi,

I can use Left Join (select ....) and with this way i can have the result.

i think it's more simple to have in the query the "subselects " and can use the returns values

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-02-29 : 07:06:52

Without seeing the complete query, all we can assist with are educated guesses.

N 56°04'39.26"
E 12°55'05.63"

psfaro
Starting Member

49 Posts

Posted - 2012-02-29 : 07:28:36

The folowing methods performs the same result.

The question is that in method 1 saldo1 and saldo2 must
specify the subquery
and in method 2 is calculted.

Is Possible in method 1 ?

Method 1:(i prefer this but for calculations is more confused)
select cod_conta,nome_conta
, orc_ano=a.orc_actual
,cabim=(SELECT ISNULL(SUM(vl_encglob),0) FROM CCTEGL WHERE conta_orca=a.cod_conta AND ano='2012' and convert(varchar(10),data_reg,120) <= '2012-02-29')
,comprom=(select isnull(sum(valor_enc),0) from EAQE WHERE ORC_FINANC=a.cod_conta AND ano='2012' and data_enc BETWEEN '2012-01-01' and '2012-02-29')
,process=(select isnull(sum(valor_fact),0) from TPFP WHERE ORC_FINANC=a.cod_conta AND ano='2012' and data_lanca BETWEEN '2012-01-02' and '2012-02-29')
,saldo1=a.orc_actual-(SELECT ISNULL(SUM(vl_encglob),0) FROM CCTEGL WHERE conta_orca=a.cod_conta AND ano='2012' and convert(varchar(10),data_reg,120) <= '2012-02-29')
,saldo2=a.orc_actual-(select isnull(sum(valor_enc),0) from EAQE WHERE ORC_FINANC=a.cod_conta AND ano='2012' and data_enc BETWEEN '2012-01-01' and '2012-02-29')
from CCTOFN a
WHERE left(a.cod_conta,1) IN ('3','4','6')
and a.ano='2012'
order BY a.cod_conta

Method 2:(with Left Join ....)

select cod_conta,nome_conta
, orc_ano=a.orc_actual,b.cabim,c.comprom,d.process,saldo1=a.orc_actual-b.cabim,saldo2=a.orc_actual-c.comprom
from CCTOFN a
LEFT JOIN (select conta_orca,cabim=isnull(sum(vl_encglob),0) from CCTEGL where ano='2012' and convert(varchar(10),data_reg,120) <= '2012-02-29' GROUP BY conta_orca) b
ON a.cod_conta=b.conta_orca
LEFT JOIN (select orc_financ,comprom=isnull(sum(valor_enc),0) from EAQE where ano='2012' and data_enc BETWEEN '2012-01-01' and '2012-02-29' GROUP BY orc_financ) c
ON a.cod_conta=c.orc_financ
LEFT JOIN (select orc_financ,process=isnull(sum(valor_fact),0) from TPFP where ano='2012' and data_lanca BETWEEN '2012-01-02' and '2012-02-29' GROUP BY orc_financ) d
ON a.cod_conta=d.orc_financ
WHERE left(a.cod_conta,1) IN ('3','4','6')
and a.ano='2012'
order BY a.cod_conta

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-02-29 : 07:38:51

I would use the LEFT JOIN query for performance. A correlated subquery is executed for each row unless SQL Server can translate the subsuery to a LEFT JOIN.

SELECT		a.cod_conta,
		a.nome_conta,
		a.orc_actual AS orc_ano,
		b.cabim,
		c.comprom,
		d.process,
		a.orc_actual - b.cabim AS saldo1,
		a.orc_actual - c.comprom AS saldo2
FROM		dbo.CCTOFN AS a 
LEFT JOIN	(
			SELECT		conta_orca,
					ISNULL(SUM(vl_encglob), 0) AS cabim
			FROM		dbo.CCTEGL
			WHERE		ano = 2012
					AND data_reg < '20120301'
			GROUP BY	conta_orca
		) AS b ON b.conta_orca = a.cod_conta
LEFT JOIN	(
			SELECT		orc_financ,
					ISNULL(SUM(valor_enc), 0) AS comprom
			FROM		dbo.EAQE
			WHERE		ano = 2012
					AND data_enc >= '20120101'
					AND data_enc  < '20120301'
			GROUP BY	orc_financ
		) AS c ON c.orc_financ = a.cod_conta
LEFT JOIN	(
			SELECT		orc_financ,
					ISNULL(SUM(valor_fact), 0) AS process
			FROM		dbo.TPFP
			WHERE		ano = 2012
					AND data_lanca >= '20120102'
					AND data_lanca < '20120301'
			GROUP BY	orc_financ
		) d ON d.orc_financ = a.cod_conta
WHERE		a.cod_conta LIKE '[346]%'
		AND a.ano = 2012
ORDER BY	a.cod_conta

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources