functions on fields created with CASE...? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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 2000 Forums

Transact-SQL (2000)

functions on fields created with CASE...?

Author

Topic

ricks1683
Starting Member

14 Posts

Posted - 2007-05-16 : 09:58:49

Hello. I have a project that requires me to create a few fields using the CASE statement and then create additional fields in the same query using the already created fields to perform mathematical functions (in this example, i need to add the CASE'd columns and get totals). Here's the SQL as I have it now. I'll indicate in all red caps where and what i need the fields that I can't figure out...

select
Acct.Name,
Pol.Account#,
sum(case when asl between '193' and '212' then loss.Paid_Loss else 0 end) "AutoLossPaid",
sum(case when asl between '193' and '212' then loss.Loss_Reserve else 0 end) "AutoLossReserve",
sum(case when asl between '193' and '212' then (loss.Paid_Loss + loss.Loss_Reserve) else 0 end) "AutoLossTotal",
sum(case asl when '170' then loss.Paid_Loss else 0 end) "GLLossPaid",
sum(case asl when '170' then loss.Loss_Reserve else 0 end) "GLLossReserve",
sum(case asl when '170' then (loss.Paid_Loss + loss.Loss_Reserve) else 0 end) "GLLossTotal",
sum(case LOB when 'WCV' then loss.Paid_Loss else 0 end) "WCLossPaid",
sum(case LOB when 'WCV' then loss.Loss_Reserve else 0 end) "WCLossReserve",
sum(case LOB when 'WCV' then (loss.Paid_Loss + loss.Loss_Reserve) else 0 end) "WCLossTotal",
(AutoLossPaid + GLLossPaid + WCLossPaid) "TotalPaid",
(AutoLossReserve + GLLossReserve + WCLossReserve) "TotalReserve",
(AutoLossTotal + GLLossTotal + WCLossTotal) "TotalIncurred"
from dbo.PolicyInfo pol
join dbo.AccountInfo Acct on Acct.Account# = Pol.Account#
join dbo.ActgLossDetail Loss on loss.Policy# = Pol.Policy#
where loss.Kind = 'd' and Pol.INCEPT between '2003-05-01 00:00:00' and '2004-04-30 00:00:00'
and POL.Pftctr in ('YOH','YGA','YHI')
group by Acct.Name, Pol.Account#
order by Acct.Name

I've tried everything I can think of but i keep getting the error message saying that those columns do not exist. Granted, I know i could add all 3 case statements manually (i.e. typing them all in AGAIN) but that significantly slows down my query and I've got to run this thing many times a day.

I would really, REALLY appreciate any help with this.

Thanks!

Rick R.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-05-16 : 10:05:41

use a derived table. For example, if you define a+b as c, and then you want to reference c, you can do this:


select c
from
(
   select a+b as c
) x

- Jeff
http://weblogs.sqlteam.com/JeffS

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-05-16 : 10:06:52

[code]Select
[Name],
Account#,
AutoLossPaid,
AutoLossReserve,
AutoLossTotal,
GLLossPaid,
GLLossReserve,
GLLossTotal,
WCLossPaid,
WCLossReserve,
WCLossTotal,
(AutoLossPaid + GLLossPaid + WCLossPaid) TotalPaid,
(AutoLossReserve + GLLossReserve + WCLossReserve) TotalReserve,
(AutoLossTotal + GLLossTotal + WCLossTotal) TotalIncurred
from
(
select
Acct.Name,
Pol.Account#,
sum(case when asl between '193' and '212' then loss.Paid_Loss else 0 end) "AutoLossPaid",
sum(case when asl between '193' and '212' then loss.Loss_Reserve else 0 end) "AutoLossReserve",
sum(case when asl between '193' and '212' then (loss.Paid_Loss + loss.Loss_Reserve) else 0 end) "AutoLossTotal",
sum(case asl when '170' then loss.Paid_Loss else 0 end) "GLLossPaid",
sum(case asl when '170' then loss.Loss_Reserve else 0 end) "GLLossReserve",
sum(case asl when '170' then (loss.Paid_Loss + loss.Loss_Reserve) else 0 end) "GLLossTotal",
sum(case LOB when 'WCV' then loss.Paid_Loss else 0 end) "WCLossPaid",
sum(case LOB when 'WCV' then loss.Loss_Reserve else 0 end) "WCLossReserve",
sum(case LOB when 'WCV' then (loss.Paid_Loss + loss.Loss_Reserve) else 0 end) "WCLossTotal",
from dbo.PolicyInfo pol
join dbo.AccountInfo Acct on Acct.Account# = Pol.Account#
join dbo.ActgLossDetail Loss on loss.Policy# = Pol.Policy#
where loss.Kind = 'd' and Pol.INCEPT between '2003-05-01 00:00:00' and '2004-04-30 00:00:00'
and POL.Pftctr in ('YOH','YGA','YHI')
group by Acct.Name, Pol.Account#
) t
order by [Name][/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

ricks1683
Starting Member

14 Posts

Posted - 2007-05-16 : 10:28:41

Great! That worked flawlessly. Thanks to the both of you!

But that gives me another question though... What does the "t" after the ) that closes the derrived table stand for? What significance is it?

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-05-16 : 10:31:53

It is called alias and it is a way to refer to derived table.

Try running the query without putting it and you will come to know it's significance.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

ricks1683
Starting Member

14 Posts

Posted - 2007-05-16 : 10:50:38

Haha...Gotcha. I took it out initially thinking it was a typo but then when it errored i remembered who the expert was here and put it back in, hahah. Thanks for all the help!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources