A different way of writing specific code.

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

General SQL Server Forums

New to SQL Server Programming

A different way of writing specific code.

Author

Topic

kotonikak
Yak Posting Veteran

92 Posts

Posted - 2012-08-23 : 11:36:27

I'm working on a code that does a variety of actuarial calculations and I need the code to distinguish between quarterly or annual data and do the specific calculations.

This code below gives me the NetEarnedPremium for 3 years

with NetEarnedPremiums as
(
select a.RptLOB,
a.Amount as NetEarnedPremium01,
b.Amount as NetEarnedPremium02,
c.Amount as NetEarnedPremium03

from CM.LedgerAmountByECMAccountByRptLOB as a
left join CM.LedgerAmountByECMAccountByRptLOB as b
on a.RptLOB=b.RptLOB
left join CM.LedgerAmountByECMAccountByRptLOB as c
on b.RptLOB=c.RptLOB
where a.ECMAccount='Net Earned Premium' and a.Period='2012' and
b.ECMAccount='Net Earned Premium' and b.Period='2013' and
c.ECMAccount='Net Earned Premium' and c.Period='2014'
)

select * into Table from NetEarnedPremiums

In the table CM.LedgerAmountByECMAccountByRptLOB, the column Period also has the values 2012Q1, 2012Q2, 2012Q3, ......., 2014Q4. Obviously, if I want the quarterly data, I'm going to have more than 3 columns with premium data in my NetEarnedPremiums table.

I wanted to differentiate the data with the parameter @UnitMonth.
When @UnitMonth=3 ---> Use quarterly data and give me 12 columns of premiums (3 years * 4 quarters), when @UnitMonth=12 ---> Use annual data and give me 3 columns of premiums (3 years).

What's a better way of doing this? Right now I'm using CTE's for everything because it's easy to refer to previous temp tables when doing my calculations. I'm not sure if cursors would do the trick or anything else...I would appreciate any help.

Thanks,

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-23 : 12:06:16

if you've datefield isnt it matter of applying grouping based on datepart(qq,datefield),datepart(yy,date) etc for quarterly,yearly etc aggregations?

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

kotonikak
Yak Posting Veteran

92 Posts

Posted - 2012-08-23 : 12:12:17

I'm sorry, I'm not quite sure what you mean.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-24 : 01:00:33

quote:
Originally posted by kotonikak

I'm sorry, I'm not quite sure what you mean.

you just need to apply group by based on datepart function on arguments qq,yy etc to get quarterly,yearly results

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

kotonikak
Yak Posting Veteran

92 Posts

Posted - 2012-08-24 : 11:42:07

Understandable but the years are not always going to be 2012,2013, and 2014. Next year they will be 2013,2014,2015 and so on.

kotonikak
Yak Posting Veteran

92 Posts

Posted - 2012-08-24 : 11:53:56

Basically, the years and the quarters (2012Q1,.....2015Q4) are in text format.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-24 : 12:10:32

thats not a problem

adding them in GROUP BY will group whatever value you've in table be in 2012,2013 or some other value. Didnt understand whats the concern in that

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

kotonikak
Yak Posting Veteran

92 Posts

Posted - 2012-08-24 : 14:22:29

I understand the group by statement. I guess I'm just not understanding the datepart function in this context.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-24 : 15:15:16

quote:
Originally posted by kotonikak

I understand the group by statement. I guess I'm just not understanding the datepart function in this context.

ok you've column with period value. i thought you've generic date field
in that case you can use

this is what i meant


SELECT *
FROM
(
SELECT RptLOB,Amount,Period
FROM CM.LedgerAmountByECMAccountByRptLOB
WHERE ECMAccount='Net Earned Premium' 
)t
PIVOT (SUM(Amount) FOR Period IN ([2012],[2013],...))p

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

kotonikak
Yak Posting Veteran

92 Posts

Posted - 2012-08-24 : 16:51:18

That's a cool way of looking at it. Never tried anything like that. Thank you!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-24 : 18:21:51

welcome

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources