Nested agregated query with multiple table joins

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)

Nested agregated query with multiple table joins

Author

Topic

psychotic_savage
Starting Member

25 Posts

Posted - 2012-02-15 : 05:28:08

Hi All,

I have a query I need to re-write as it is taking forever (20+ seconds) to run.
The original query is as follows:


Select cli_id
,cli_companyName
,cli_password
, count(distinct hov_id)'hovCnt'
,count(distinct det_id)'DetCnt'
, sum(tra_AmtDebit)'TotalDebit'
, sum(tra_AmtCredit)'TotalCredit'
,  'wITHdRAWN' = (Select  sum(tra_AmtDebit) 
	From trans  with(NOLOCK)  
	inner join Debtors on tra_det_id = det_id  
	where  trs_ven_id =5 and tra_cli_ID = clients.CLI_ID 
		AND  (det_status=10) 
		and (tra_transtype='HAndover' or tra_AmtDebit = 0) ) 
From trans with(NOLOCK) 
inner join ClientHandOver with(NOLOCK) on tra_hov_id =hov_id 
inner join Debtors with(NOLOCK) on tra_det_id = det_id  
inner join Clients with(NOLOCK) on tra_cli_id = cli_id 
where cli_isAdminOrder = 0 
		and cli_CompanyStatus <> 5 
		and trs_ven_id =5 
		and (tra_transtype='HAndover' or tra_AmtDebit = 0) 
		And det_sta_id =57 
group by cli_companyName,cli_id, cli_password 
order by cli_companyname

After checking the execution plan I found the time is coming from the trans table.
Due to its size and constant updating the indexes tend to get fragmented very quickly.

Is there a way to not have the nested aggregation 'wITHdRAWN'
Bearing in mind the det_status=10 within that nest would need to only apply to the 'wITHdRAWN' and not the other fields in the top level select.

Thanks

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-02-15 : 07:06:07

May be possible to use a case expression like this:


...
	SUM(tra_AmtCredit)'TotalCredit',
	'wITHdRAWN' = SUM
	( 
		CASE WHEN
	    	   trs_ven_id = 5
	    	   AND tra_cli_ID = clients.CLI_ID
	    	   AND (det_status = 10)
	    	   AND (tra_transtype = 'HAndover' OR tra_AmtDebit = 0)
		THEN tra_AmtDebit
		ELSE 0
		END
	)	
FROM
....

psychotic_savage
Starting Member

25 Posts

Posted - 2012-02-15 : 07:12:54

Thanks sunitabeck.
This looks like it may do what I need.
Just have to run it through with some sample data and compare.

Thanks you are a *

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources