Improve aggrigate queries? - 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 2005 Forums

Transact-SQL (2005)

Improve aggrigate queries?

Author

Topic

lynda
Starting Member

21 Posts

Posted - 2009-06-09 : 12:58:17

I am no SQL expert by any stretch. I kind of bumble my way along and tend to get the results I need.

I have a looping query that gathers totals out of some tables and it has to execute a separate select for each total since I can only gather 1 total per query.

Here is the body of the query that I run:

while @days > 0

-- Calculate # corrections
BEGIN

select
@corrections = count(cc.firstdataupdate)

from payments pay

inner join payment_CreditCards paycc WITH(NOLOCK) on pay.id=paycc.id
inner join creditcards cc WITH(NOLOCK) on pay.creditcardid = cc.id
LEFT JOIN Lynda.dbo.Transactions T WITH(NOLOCK) on PAY.id = t.paymentID
LEFT JOIN Lynda.dbo.AdminUsers AU WITH(NOLOCK) ON AU.id = T.adminUserId

where datepart(day,pay.paymentdate) = @days AND
datepart(month,pay.paymentdate) = @month
AND datepart(year,pay.paymentdate) = @year
AND len(cc.firstDataUpdate) > 3

-- Calculate total # billed

select
@totalbilled = count(pay.amount)

from payments pay

inner join payment_CreditCards paycc WITH(NOLOCK) on pay.id=paycc.id
inner join creditcards cc WITH(NOLOCK) on pay.creditcardid = cc.id
LEFT JOIN Lynda.dbo.Transactions T WITH(NOLOCK) on PAY.id = t.paymentID
LEFT JOIN Lynda.dbo.AdminUsers AU WITH(NOLOCK) ON AU.id = T.adminUserId

where datepart(day,pay.paymentdate) = @days AND
datepart(month,pay.paymentdate) = @month
AND datepart(year,pay.paymentdate) = @year
and AU.ID=1 -- Billing job generated...

-- Calculate total # approved

select @numapproved =count(cc.firstdataupdate)

from payments pay

inner join payment_CreditCards paycc WITH(NOLOCK) on pay.id=paycc.id
inner join creditcards cc WITH(NOLOCK) on pay.creditcardid = cc.id
LEFT JOIN Lynda.dbo.Transactions T WITH(NOLOCK) on PAY.id = t.paymentID
LEFT JOIN Lynda.dbo.AdminUsers AU WITH(NOLOCK) ON AU.id = T.adminUserId

where datepart(day,pay.paymentdate) = @days AND
datepart(month,pay.paymentdate) = @month
AND datepart(year,pay.paymentdate) = @year
and AU.ID=1 -- Billing job generated...
AND len(cc.firstDataUpdate) > 3 and paycc.reasoncode=1

-- Calculate revenue from corrections

select
@totalRevenue = isnull(sum(pay.amount), 0)

from payments pay

inner join payment_CreditCards paycc WITH(NOLOCK) on pay.id=paycc.id
inner join creditcards cc WITH(NOLOCK) on pay.creditcardid = cc.id
LEFT JOIN Lynda.dbo.Transactions T WITH(NOLOCK) on PAY.id = t.paymentID
LEFT JOIN Lynda.dbo.AdminUsers AU WITH(NOLOCK) ON AU.id = T.adminUserId

where datepart(day,pay.paymentdate) = @days AND
datepart(month,pay.paymentdate) = @month
AND datepart(year,pay.paymentdate) = @year
and AU.ID=1 -- Billing job generated...
AND len(cc.firstDataUpdate) > 3 and paycc.reasoncode=1

-- Calculate percent

select @percentapproved =
CASE @totalbilled
WHEN 0 then '0%' -- If total billed is 0, just return 0 as percent to avoid potential divide by 0 error
else cast(round(CAST((CAST(@numapproved as float) / CAST(@totalbilled as float))*10 as float),1) as nvarchar(10)) +'%'
END

-- Calculate expirations updpated

select
@expired = count(pay.amount)

from payments pay

inner join payment_CreditCards paycc WITH(NOLOCK) on pay.id=paycc.id
inner join creditcards cc WITH(NOLOCK) on pay.creditcardid = cc.id
LEFT JOIN Lynda.dbo.Transactions T WITH(NOLOCK) on PAY.id = t.paymentID
LEFT JOIN Lynda.dbo.AdminUsers AU WITH(NOLOCK) ON AU.id = T.adminUserId

where datepart(day,pay.paymentdate) = @days AND
datepart(month,pay.paymentdate) = @month
AND datepart(year,pay.paymentdate) = @year
and AU.ID=1 -- Billing job generated...
AND cc.firstDataUpdate = 'EXPIRY'

-- Calculate accounts updated

select
@cardscorrected = count(pay.amount)

from payments pay

inner join payment_CreditCards paycc WITH(NOLOCK) on pay.id=paycc.id
inner join creditcards cc WITH(NOLOCK) on pay.creditcardid = cc.id
LEFT JOIN Lynda.dbo.Transactions T WITH(NOLOCK) on PAY.id = t.paymentID
LEFT JOIN Lynda.dbo.AdminUsers AU WITH(NOLOCK) ON AU.id = T.adminUserId

where datepart(day,pay.paymentdate) = @days AND
datepart(month,pay.paymentdate) = @month
AND datepart(year,pay.paymentdate) = @year
and AU.ID=1 -- Billing job generated...
AND cc.firstDataUpdate = 'UPDATE'

insert into #tmpfd
select

cast(@year as nvarchar(4)) + '-' + cast(@month as nvarchar(2)) + '-' + CAST(@days as nvarchar(2)) as [Billing Date],
@corrections as [Number of Corrections],
@totalbilled as [Total accounts billed],
@numapproved as [Total corrected Billings Approved],
@totalRevenue as [Total Revenue from Corrections],
@expired as [Expiration Dates Corrected],
@cardscorrected as [Account Numbers Updated],
@percentapproved as [Percent improvement]

set @days = @days-1
END

select * from #tmpfd -- Return results
drop table #tmpfd -- dump the temp table

Is there a more effiecient way to do this? The joins remain the same for the queries, only the select for the totals and the where clauses vary.

Just looking to improve what I am doing :)

Thanks!

Mike

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-09 : 13:12:43

you dont need to loop for each day, what you need is just this


select
[Billing Date],
corrections as [Number of Corrections],
totalbilled as [Total accounts billed],
numapproved as [Total corrected Billings Approved],
totalRevenue as [Total Revenue from Corrections],
expired as [Expiration Dates Corrected],
cardscorrected as [Account Numbers Updated],
percentapproved = 
CASE totalbilled
WHEN 0 then '0%'	
else cast(round(CAST((CAST(numapproved as float) / CAST(totalbilled as float))*10 as float),1) as nvarchar(10)) +'%'
END
from
(
select	[Billing Date]=dateadd(dd,datediff(dd,0,pay.paymentdate),0),
corrections = count(case when len(cc.firstDataUpdate) > 3 then cc.firstdataupdate else null end),
totalbilled = count(case when AU.ID=1 then pay.amount else null end),
numapproved =count(case when AU.ID=1 AND len(cc.firstDataUpdate) > 3 and paycc.reasoncode=1 then cc.firstdataupdate else null end),
totalRevenue = sum(case when AU.ID=1 AND len(cc.firstDataUpdate) > 3 and paycc.reasoncode=1 then pay.amount else 0 end),
expired = count(case when AU.ID=1 AND cc.firstDataUpdate = 'EXPIRY' then pay.amount else null end),
cardscorrected = count(case when and AU.ID=1 AND cc.firstDataUpdate = 'UPDATE' then pay.amount else null end)
from payments pay
inner join payment_CreditCards paycc WITH(NOLOCK) on pay.id=paycc.id
inner join creditcards cc WITH(NOLOCK) on pay.creditcardid = cc.id
LEFT JOIN Lynda.dbo.Transactions T WITH(NOLOCK) on PAY.id = t.paymentID
LEFT JOIN Lynda.dbo.AdminUsers AU WITH(NOLOCK) ON AU.id = T.adminUserId

where 
pay.paymentdate > = dateadd(mm,@month-1,dateadd(yy,@year-1900,0))
AND 
pay.paymentdate < dateadd(mm,@month,dateadd(yy,@year-1900,0))
GROUP BY dateadd(dd,datediff(dd,0,pay.paymentdate),0)
)t

lynda
Starting Member

21 Posts

Posted - 2009-06-09 : 13:41:20

Wow - That is a HUGE improvement! Thank you SO much! Much to learn here :)

One last question: How can I truncate the time value off of the [Billing Date] column?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-09 : 13:46:57

quote:
Originally posted by lynda

Wow - That is a HUGE improvement! Thank you SO much! Much to learn here :)

One last question: How can I truncate the time value off of the [Billing Date] column?

the query i gave truncates the timepart. it always returns 00:00:00 for time. sql server always returns time part along with date until sql 2005. to get date part alone use date formatting functions available at your front end as its always better to do formatting at presentation layer. however if you still want to do it in sql for any reason then use convert function


select convert(varchar(11),[Billing Date],103),
....

lynda
Starting Member

21 Posts

Posted - 2009-06-09 : 13:47:19

Oh - one other question (I think I have most of what you did here figured out)...

In this:

GROUP BY dateadd(dd,datediff(dd,0,pay.paymentdate),0)
)t

What is the "t" for?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-09 : 13:49:37

quote:
Originally posted by lynda

Oh - one other question (I think I have most of what you did here figured out)...

In this:

GROUP BY dateadd(dd,datediff(dd,0,pay.paymentdate),0)
)t

What is the "t" for?

its the alias given to inner query. we're actually forming a derived table out of inner query and t is short name given to it

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-06-09 : 13:53:48

from
(
select [Billing Date]=dateadd(dd,datediff(dd,0,pay.paymentdate),0),
...
from payments pay
inner join payment_CreditCards paycc WITH(NOLOCK) on pay.id=paycc.id
...
where
pay.paymentdate > = dateadd(mm,@month-1,dateadd(yy,@year-1900,0))
AND
pay.paymentdate < dateadd(mm,@month,dateadd(yy,@year-1900,0))
GROUP BY dateadd(dd,datediff(dd,0,pay.paymentdate),0)
)t
This construct is called a DERIVED TABLE and it needs a name.
In this case the name is: t

Greetings
Webfred

No, you're never too old to Yak'n'Roll if you're too young to die.

lynda
Starting Member

21 Posts

Posted - 2009-06-09 : 14:01:16

Ahh excellent. Learning lots of new things here today :)

Thanks a ton gentlemen!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-09 : 14:02:00

welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources