How to Optimize this 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)

How to Optimize this query??

Author

Topic

RaghaSM
Yak Posting Veteran

52 Posts

Posted - 2011-11-15 : 10:10:59

Can somebody help me to optimize this query ?

select T.TransactionId,
T.number,
T.Sales,
(select count (ID) as ExemptCount
from SaleDetails
where transactionid=T.Transactionid and isExempt=1 )
as ExemptCount,
(select count (ID) as TaxableCount
from SaleDetails
where transactionid=T.Transactionid and isExempt=0 )
as TaxableCount
from dbo.[Transaction] as T

Thanks
Ragha

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-15 : 10:14:16

[code]
select T.TransactionId,
T.number,
T.Sales,
S.ExemptCount,
S.TaxableCount
from dbo.[Transaction] as T
inner join (select transactionid,count(case when isExempt=1 then ID else null end) as ExemptCount,
count(case when isExempt=0 then ID else null end) as TaxableCount
from SaleDetails
group by transactionid) S
ON S.transactionid = T.transactionid
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-11-15 : 10:15:01

SELECT T.TransactionId, T.number, T.Sales,
SUM(CASE WHEN isExempt=1 THEN 1 END) AS ExemptCount,
SUM(CASE WHEN isExempt=0 THEN 1 END) AS TaxableCount
FROM dbo.[Transaction] AS T
INNER JOIN SaleDetails AS S
ON S.transactionid=T.Transactionid
GROUP BY T.TransactionId, T.number, T.Sales

RaghaSM
Yak Posting Veteran

52 Posts

Posted - 2011-11-15 : 10:30:43

Thank you both for the quick response. I really appreciate all your help.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-15 : 10:36:11

wc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-11-16 : 02:55:33

None of the above are following the original query logic IF there are Transactions with no corresponding rows in SalesDetails.
A correlated subquery acts like an outer query.

SELECT		t.TransactionID,
		t.Number,
		t.Sales,
		ISNULL(w.ExemptCount, 0) AS ExemptCount,
		ISNULL(w.TaxableCount, 0) AS TaxableCount
FROM		dbo.[Transaction] AS t
LEFT JOIN	(
			SELECT		TransactionID,
					SUM(CASE WHEN IsExempt = 1 THEN 1 ELSE 0 END) AS ExemptCount,
					SUM(CASE WHEN IsExempt = 0 THEN 1 ELSE 0 END) AS TaxableCount
			FROM		dbo.SalesDetailes
			GROUP BY	TransactionID
		) AS w ON w.TransactionID = t.TransactionID

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