SQL Help

Passacaglia · 05-31-2011, 10:06 AM

Is it possible to set a variable within a select statement?

I've got this code:

Quote:

case
when ([Financial_Analysis].[AxGage].[Policy Info Master].PTDate < @B) then '0'
when ([Financial_Analysis].[AxGage].[Policy Info Master].IssDate > @E) then '0'
when (([Financial_Analysis].[AxGage].[Policy Info Master].IssDate <= @B) and ([Financial_Analysis].[AxGage].[Policy Info Master].PTDate >= @E)) then polannprem*(cast(DATEDIFF(DAY, @B, @E) as float)+1)/(cast(DATEDIFF(DAY, @BY, @EY) as float)+1)
when (([Financial_Analysis].[AxGage].[Policy Info Master].IssDate > @B) and([Financial_Analysis].[AxGage].[Policy Info Master].PTDate >= @E))
then polannprem*(cast(DATEDIFF(DAY, [Financial_Analysis].[AxGage].[Policy Info Master].IssDate, @E)+1 as float))/(cast(DATEDIFF(DAY, @BY, @EY) as float)+1)
when (([Financial_Analysis].[AxGage].[Policy Info Master].IssDate <= @B) and ([Financial_Analysis].[AxGage].[Policy Info Master].PTDate < @E))
then polannprem*(cast(DATEDIFF(DAY, @B, [Financial_Analysis].[AxGage].[Policy Info Master].PTDate)+1 as float))/(cast(DATEDIFF(day, @BY, @EY) as float)+1)
when (([Financial_Analysis].[AxGage].[Policy Info Master].IssDate > @B) and ([Financial_Analysis].[AxGage].[Policy Info Master].PTDate < @E))
then polannprem*(cast(DATEDIFF(DAY, [Financial_Analysis].[AxGage].[Policy Info Master].IssDate, [Financial_Analysis].[AxGage].[Policy Info Master].PTDate)+1 as float))/(cast(DATEDIFF(DAY, @BY, @EY) as float)+1)
end as EarnedPremium

It's inside a select statement. I'd like to be able to change the local variables @B and @E, then run this code again, creating another column.

Thanks in advance!

bhlloy · 05-31-2011, 10:19 AM

What is the DBMS you are using?

Passacaglia · 05-31-2011, 10:20 AM

It's called 'huh?'

bhlloy · 05-31-2011, 10:21 AM

MS/SQL, MySql, Oracle etc....

It's very doable in PL/SQL if you are running Oracle, otherwise I'm not sure

Passacaglia · 05-31-2011, 10:23 AM

Oh, I think I can answer that! I use Microsoft SQL Server Management Studio.

Passacaglia · 05-31-2011, 10:23 AM

Obviously you're dealing with someone who knows very little about what he's doing here.

bhlloy · 05-31-2011, 10:24 AM

ah... then that's a good thing (I think) but not in my area of expertize... hopefully somebody can help you out

lordscarlet · 06-01-2011, 12:17 PM

Quote:

Originally Posted by Passacaglia

Is it possible to set a variable within a select statement?

I've got this code:

It's inside a select statement. I'd like to be able to change the local variables @B and @E, then run this code again, creating another column.

Thanks in advance!

I'm not sure you're asking the question the right way.

1) What do you mean by "creating another column"

as to the first part of the question, I'm going to attempt to answer but I may be answering the wrong question.

DECLARE @NewVar DATETIME

SELECT @NewVar = column FROM table

That's really not that explanatory, but... you can declare a variable and set it that way. How it fits into your entire query (which you did not paste) is hard to say. I'm also not sure that you want your new variable to be set based on a column in a select, or if you just want to do:

[original query]
SET @B = [new value]

If you can give us some more information, that would be helpful.

Passacaglia · 06-01-2011, 01:31 PM

Well, here's my query in entirety now, and this will probably show what I was trying to do, but ended up doing the long way:

Quote:

with query1 as (select
Financial_Analysis.AxGage.[Policy Info Master].CO,
Financial_Analysis.AxGage.[mapping DLM, Ext Form, Int Form].Product,
[Financial_Analysis].[AxGage].[Policy Info Master].IssDate as I,
[Financial_Analysis].[AxGage].[Policy Info Master].PTDate as P,
[Financial_Analysis].[AxGage].[Policy Info Master].polannprem as polannprem,
Financial_Analysis.AxGage.[Policy Info Master].IssYr,
Financial_Analysis.AxGage.[Policy Info Master].ISST,
Financial_Analysis.AxGage.[Policy Info Master].AGENT,
Underwriting_DB.dbo.Agent_Realigned.Status

From
Financial_Analysis.AxGage.[Policy Info Master]
INNER JOIN Financial_Analysis.AxGage.[mapping DLM, Ext Form, Int Form] on
Financial_Analysis.AxGage.[Policy Info Master].[Int Form] = Financial_Analysis.AxGage.[mapping DLM, Ext Form, Int Form].[Int Form]
LEFT OUTER JOIN Underwriting_DB.dbo.Altagt ON
Financial_Analysis.AxGage.[Policy Info Master].AGENT = Underwriting_DB.dbo.Altagt.Altcd
LEFT OUTER JOIN Underwriting_DB.dbo.Agent_Realigned ON
Underwriting_DB.dbo.Altagt.Agtcd = Underwriting_DB.dbo.Agent_Realigned.Agent)

select
CO,
Product,
IssYr,
ISST,
Agent,
Status,
case
when (P < convert(datetime,'04-01-2007')) then '0'
when (I > convert(datetime,'06-30-2007')) then '0'
when ((I <= convert(datetime,'04-01-2007')) and (P >= convert(datetime,'06-30-2007'))) then polannprem*(cast(DATEDIFF(DAY, convert(datetime,'04-01-2007'), convert(datetime,'06-30-2007')) as float)+1)/(cast(DATEDIFF(DAY, convert(datetime,'01-01-2007'), convert(datetime,'12-31-2007')) as float)+1)
when ((I > convert(datetime,'04-01-2007')) and(P >= convert(datetime,'06-30-2007')))
then polannprem*(cast(DATEDIFF(DAY, I, convert(datetime,'06-30-2007'))+1 as float))/(cast(DATEDIFF(DAY, convert(datetime,'01-01-2007'), convert(datetime,'12-31-2007')) as float)+1)
when ((I <= convert(datetime,'04-01-2007')) and (P < convert(datetime,'06-30-2007')))
then polannprem*(cast(DATEDIFF(DAY, convert(datetime,'04-01-2007'), P)+1 as float))/(cast(DATEDIFF(day, convert(datetime,'01-01-2007'), convert(datetime,'12-31-2007')) as float)+1)
when ((I > convert(datetime,'04-01-2007')) and (P < convert(datetime,'06-30-2007')))
then polannprem*(cast(DATEDIFF(DAY, I, P)+1 as float))/(cast(DATEDIFF(DAY, convert(datetime,'01-01-2007'), convert(datetime,'12-31-2007')) as float)+1)
end as EP2007Q2,
case
when (P < convert(datetime,'07-01-2007')) then '0'
when (I > convert(datetime,'09-30-2007')) then '0'
when ((I <= convert(datetime,'07-01-2007')) and (P >= convert(datetime,'09-30-2007'))) then polannprem*(cast(DATEDIFF(DAY, convert(datetime,'07-01-2007'), convert(datetime,'09-30-2007')) as float)+1)/(cast(DATEDIFF(DAY, convert(datetime,'01-01-2007'), convert(datetime,'12-31-2007')) as float)+1)
when ((I > convert(datetime,'07-01-2007')) and(P >= convert(datetime,'09-30-2007')))
then polannprem*(cast(DATEDIFF(DAY, I, convert(datetime,'09-30-2007'))+1 as float))/(cast(DATEDIFF(DAY, convert(datetime,'01-01-2007'), convert(datetime,'12-31-2007')) as float)+1)
when ((I <= convert(datetime,'07-01-2007')) and (P < convert(datetime,'09-30-2007')))
then polannprem*(cast(DATEDIFF(DAY, convert(datetime,'07-01-2007'), P)+1 as float))/(cast(DATEDIFF(day, convert(datetime,'01-01-2007'), convert(datetime,'12-31-2007')) as float)+1)
when ((I > convert(datetime,'07-01-2007')) and (P < convert(datetime,'09-30-2007')))
then polannprem*(cast(DATEDIFF(DAY, I, P)+1 as float))/(cast(DATEDIFF(DAY, convert(datetime,'01-01-2007'), convert(datetime,'12-31-2007')) as float)+1)
end as EP2007Q3,
case
when (P < convert(datetime,'10-01-2007')) then '0'
when (I > convert(datetime,'12-31-2007')) then '0'
when ((I <= convert(datetime,'10-01-2007')) and (P >= convert(datetime,'12-31-2007'))) then polannprem*(cast(DATEDIFF(DAY, convert(datetime,'10-01-2007'), convert(datetime,'12-31-2007')) as float)+1)/(cast(DATEDIFF(DAY, convert(datetime,'01-01-2007'), convert(datetime,'12-31-2007')) as float)+1)
when ((I > convert(datetime,'10-01-2007')) and(P >= convert(datetime,'12-31-2007')))
then polannprem*(cast(DATEDIFF(DAY, I, convert(datetime,'12-31-2007'))+1 as float))/(cast(DATEDIFF(DAY, convert(datetime,'01-01-2007'), convert(datetime,'12-31-2007')) as float)+1)
when ((I <= convert(datetime,'10-01-2007')) and (P < convert(datetime,'12-31-2007')))
then polannprem*(cast(DATEDIFF(DAY, convert(datetime,'10-01-2007'), P)+1 as float))/(cast(DATEDIFF(day, convert(datetime,'01-01-2007'), convert(datetime,'12-31-2007')) as float)+1)
when ((I > convert(datetime,'10-01-2007')) and (P < convert(datetime,'12-31-2007')))
then polannprem*(cast(DATEDIFF(DAY, I, P)+1 as float))/(cast(DATEDIFF(DAY, convert(datetime,'01-01-2007'), convert(datetime,'12-31-2007')) as float)+1)
end as EP2007Q4,
case
when (P < convert(datetime,'01-01-2008')) then '0'
when (I > convert(datetime,'03-31-2008')) then '0'
when ((I <= convert(datetime,'01-01-2008')) and (P >= convert(datetime,'03-31-2008'))) then polannprem*(cast(DATEDIFF(DAY, convert(datetime,'01-01-2008'), convert(datetime,'03-31-2008')) as float)+1)/(cast(DATEDIFF(DAY, convert(datetime,'01-01-2008'), convert(datetime,'12-31-2008')) as float)+1)
when ((I > convert(datetime,'01-01-2008')) and(P >= convert(datetime,'03-31-2008')))
then polannprem*(cast(DATEDIFF(DAY, I, convert(datetime,'03-31-2008'))+1 as float))/(cast(DATEDIFF(DAY, convert(datetime,'01-01-2008'), convert(datetime,'12-31-2008')) as float)+1)
when ((I <= convert(datetime,'01-01-2008')) and (P < convert(datetime,'03-31-2008')))
then polannprem*(cast(DATEDIFF(DAY, convert(datetime,'01-01-2008'), P)+1 as float))/(cast(DATEDIFF(day, convert(datetime,'01-01-2008'), convert(datetime,'12-31-2008')) as float)+1)
when ((I > convert(datetime,'01-01-2008')) and (P < convert(datetime,'03-31-2008')))
then polannprem*(cast(DATEDIFF(DAY, I, P)+1 as float))/(cast(DATEDIFF(DAY, convert(datetime,'01-01-2008'), convert(datetime,'12-31-2008')) as float)+1)
end as EP2008Q1,
case
when (P < convert(datetime,'04-01-2008')) then '0'
when (I > convert(datetime,'06-30-2008')) then '0'
when ((I <= convert(datetime,'04-01-2008')) and (P >= convert(datetime,'06-30-2008'))) then polannprem*(cast(DATEDIFF(DAY, convert(datetime,'04-01-2008'), convert(datetime,'06-30-2008')) as float)+1)/(cast(DATEDIFF(DAY, convert(datetime,'01-01-2008'), convert(datetime,'12-31-2008')) as float)+1)
when ((I > convert(datetime,'04-01-2008')) and(P >= convert(datetime,'06-30-2008')))
then polannprem*(cast(DATEDIFF(DAY, I, convert(datetime,'06-30-2008'))+1 as float))/(cast(DATEDIFF(DAY, convert(datetime,'01-01-2008'), convert(datetime,'12-31-2008')) as float)+1)
when ((I <= convert(datetime,'04-01-2008')) and (P < convert(datetime,'06-30-2008')))
then polannprem*(cast(DATEDIFF(DAY, convert(datetime,'04-01-2008'), P)+1 as float))/(cast(DATEDIFF(day, convert(datetime,'01-01-2008'), convert(datetime,'12-31-2008')) as float)+1)
when ((I > convert(datetime,'04-01-2008')) and (P < convert(datetime,'06-30-2008')))
then polannprem*(cast(DATEDIFF(DAY, I, P)+1 as float))/(cast(DATEDIFF(DAY, convert(datetime,'01-01-2008'), convert(datetime,'12-31-2008')) as float)+1)
end as EP2008Q2,
case
when (P < convert(datetime,'07-01-2008')) then '0'
when (I > convert(datetime,'09-30-2008')) then '0'
when ((I <= convert(datetime,'07-01-2008')) and (P >= convert(datetime,'09-30-2008'))) then polannprem*(cast(DATEDIFF(DAY, convert(datetime,'07-01-2008'), convert(datetime,'09-30-2008')) as float)+1)/(cast(DATEDIFF(DAY, convert(datetime,'01-01-2008'), convert(datetime,'12-31-2008')) as float)+1)
when ((I > convert(datetime,'07-01-2008')) and(P >= convert(datetime,'09-30-2008')))
then polannprem*(cast(DATEDIFF(DAY, I, convert(datetime,'09-30-2008'))+1 as float))/(cast(DATEDIFF(DAY, convert(datetime,'01-01-2008'), convert(datetime,'12-31-2008')) as float)+1)
when ((I <= convert(datetime,'07-01-2008')) and (P < convert(datetime,'09-30-2008')))
then polannprem*(cast(DATEDIFF(DAY, convert(datetime,'07-01-2008'), P)+1 as float))/(cast(DATEDIFF(day, convert(datetime,'01-01-2008'), convert(datetime,'12-31-2008')) as float)+1)
when ((I > convert(datetime,'07-01-2008')) and (P < convert(datetime,'09-30-2008')))
then polannprem*(cast(DATEDIFF(DAY, I, P)+1 as float))/(cast(DATEDIFF(DAY, convert(datetime,'01-01-2008'), convert(datetime,'12-31-2008')) as float)+1)
end as EP2008Q3,
case
when (P < convert(datetime,'10-01-2008')) then '0'
when (I > convert(datetime,'12-31-2008')) then '0'
when ((I <= convert(datetime,'10-01-2008')) and (P >= convert(datetime,'12-31-2008'))) then polannprem*(cast(DATEDIFF(DAY, convert(datetime,'10-01-2008'), convert(datetime,'12-31-2008')) as float)+1)/(cast(DATEDIFF(DAY, convert(datetime,'01-01-2008'), convert(datetime,'12-31-2008')) as float)+1)
when ((I > convert(datetime,'10-01-2008')) and(P >= convert(datetime,'12-31-2008')))
then polannprem*(cast(DATEDIFF(DAY, I, convert(datetime,'12-31-2008'))+1 as float))/(cast(DATEDIFF(DAY, convert(datetime,'01-01-2008'), convert(datetime,'12-31-2008')) as float)+1)
when ((I <= convert(datetime,'10-01-2008')) and (P < convert(datetime,'12-31-2008')))
then polannprem*(cast(DATEDIFF(DAY, convert(datetime,'10-01-2008'), P)+1 as float))/(cast(DATEDIFF(day, convert(datetime,'01-01-2008'), convert(datetime,'12-31-2008')) as float)+1)
when ((I > convert(datetime,'10-01-2008')) and (P < convert(datetime,'12-31-2008')))
then polannprem*(cast(DATEDIFF(DAY, I, P)+1 as float))/(cast(DATEDIFF(DAY, convert(datetime,'01-01-2008'), convert(datetime,'12-31-2008')) as float)+1)
end as EP2008Q4,
case
when (P < convert(datetime,'01-01-2009')) then '0'
when (I > convert(datetime,'03-31-2009')) then '0'
when ((I <= convert(datetime,'01-01-2009')) and (P >= convert(datetime,'03-31-2009'))) then polannprem*(cast(DATEDIFF(DAY, convert(datetime,'01-01-2009'), convert(datetime,'03-31-2009')) as float)+1)/(cast(DATEDIFF(DAY, convert(datetime,'01-01-2009'), convert(datetime,'12-31-2009')) as float)+1)
when ((I > convert(datetime,'01-01-2009')) and(P >= convert(datetime,'03-31-2009')))
then polannprem*(cast(DATEDIFF(DAY, I, convert(datetime,'03-31-2009'))+1 as float))/(cast(DATEDIFF(DAY, convert(datetime,'01-01-2009'), convert(datetime,'12-31-2009')) as float)+1)
when ((I <= convert(datetime,'01-01-2009')) and (P < convert(datetime,'03-31-2009')))
then polannprem*(cast(DATEDIFF(DAY, convert(datetime,'01-01-2009'), P)+1 as float))/(cast(DATEDIFF(day, convert(datetime,'01-01-2009'), convert(datetime,'12-31-2009')) as float)+1)
when ((I > convert(datetime,'01-01-2009')) and (P < convert(datetime,'03-31-2009')))
then polannprem*(cast(DATEDIFF(DAY, I, P)+1 as float))/(cast(DATEDIFF(DAY, convert(datetime,'01-01-2009'), convert(datetime,'12-31-2009')) as float)+1)
end as EP2009Q1,
case
when (P < convert(datetime,'04-01-2009')) then '0'
when (I > convert(datetime,'06-30-2009')) then '0'
when ((I <= convert(datetime,'04-01-2009')) and (P >= convert(datetime,'06-30-2009'))) then polannprem*(cast(DATEDIFF(DAY, convert(datetime,'04-01-2009'), convert(datetime,'06-30-2009')) as float)+1)/(cast(DATEDIFF(DAY, convert(datetime,'01-01-2009'), convert(datetime,'12-31-2009')) as float)+1)
when ((I > convert(datetime,'04-01-2009')) and(P >= convert(datetime,'06-30-2009')))
then polannprem*(cast(DATEDIFF(DAY, I, convert(datetime,'06-30-2009'))+1 as float))/(cast(DATEDIFF(DAY, convert(datetime,'01-01-2009'), convert(datetime,'12-31-2009')) as float)+1)
when ((I <= convert(datetime,'04-01-2009')) and (P < convert(datetime,'06-30-2009')))
then polannprem*(cast(DATEDIFF(DAY, convert(datetime,'04-01-2009'), P)+1 as float))/(cast(DATEDIFF(day, convert(datetime,'01-01-2009'), convert(datetime,'12-31-2009')) as float)+1)
when ((I > convert(datetime,'04-01-2009')) and (P < convert(datetime,'06-30-2009')))
then polannprem*(cast(DATEDIFF(DAY, I, P)+1 as float))/(cast(DATEDIFF(DAY, convert(datetime,'01-01-2009'), convert(datetime,'12-31-2009')) as float)+1)
end as EP2009Q2,
case
when (P < convert(datetime,'07-01-2009')) then '0'
when (I > convert(datetime,'09-30-2009')) then '0'
when ((I <= convert(datetime,'07-01-2009')) and (P >= convert(datetime,'09-30-2009'))) then polannprem*(cast(DATEDIFF(DAY, convert(datetime,'07-01-2009'), convert(datetime,'09-30-2009')) as float)+1)/(cast(DATEDIFF(DAY, convert(datetime,'01-01-2009'), convert(datetime,'12-31-2009')) as float)+1)
when ((I > convert(datetime,'07-01-2009')) and(P >= convert(datetime,'09-30-2009')))
then polannprem*(cast(DATEDIFF(DAY, I, convert(datetime,'09-30-2009'))+1 as float))/(cast(DATEDIFF(DAY, convert(datetime,'01-01-2009'), convert(datetime,'12-31-2009')) as float)+1)
when ((I <= convert(datetime,'07-01-2009')) and (P < convert(datetime,'09-30-2009')))
then polannprem*(cast(DATEDIFF(DAY, convert(datetime,'07-01-2009'), P)+1 as float))/(cast(DATEDIFF(day, convert(datetime,'01-01-2009'), convert(datetime,'12-31-2009')) as float)+1)
when ((I > convert(datetime,'07-01-2009')) and (P < convert(datetime,'09-30-2009')))
then polannprem*(cast(DATEDIFF(DAY, I, P)+1 as float))/(cast(DATEDIFF(DAY, convert(datetime,'01-01-2009'), convert(datetime,'12-31-2009')) as float)+1)
end as EP2009Q3,
case
when (P < convert(datetime,'10-01-2009')) then '0'
when (I > convert(datetime,'12-31-2009')) then '0'
when ((I <= convert(datetime,'10-01-2009')) and (P >= convert(datetime,'12-31-2009'))) then polannprem*(cast(DATEDIFF(DAY, convert(datetime,'10-01-2009'), convert(datetime,'12-31-2009')) as float)+1)/(cast(DATEDIFF(DAY, convert(datetime,'01-01-2009'), convert(datetime,'12-31-2009')) as float)+1)
when ((I > convert(datetime,'10-01-2009')) and(P >= convert(datetime,'12-31-2009')))
then polannprem*(cast(DATEDIFF(DAY, I, convert(datetime,'12-31-2009'))+1 as float))/(cast(DATEDIFF(DAY, convert(datetime,'01-01-2009'), convert(datetime,'12-31-2009')) as float)+1)
when ((I <= convert(datetime,'10-01-2009')) and (P < convert(datetime,'12-31-2009')))
then polannprem*(cast(DATEDIFF(DAY, convert(datetime,'10-01-2009'), P)+1 as float))/(cast(DATEDIFF(day, convert(datetime,'01-01-2009'), convert(datetime,'12-31-2009')) as float)+1)
when ((I > convert(datetime,'10-01-2009')) and (P < convert(datetime,'12-31-2009')))
then polannprem*(cast(DATEDIFF(DAY, I, P)+1 as float))/(cast(DATEDIFF(DAY, convert(datetime,'01-01-2009'), convert(datetime,'12-31-2009')) as float)+1)
end as EP2009Q4,
case
when (P < convert(datetime,'01-01-2010')) then '0'
when (I > convert(datetime,'03-31-2010')) then '0'
when ((I <= convert(datetime,'01-01-2010')) and (P >= convert(datetime,'03-31-2010'))) then polannprem*(cast(DATEDIFF(DAY, convert(datetime,'01-01-2010'), convert(datetime,'03-31-2010')) as float)+1)/(cast(DATEDIFF(DAY, convert(datetime,'01-01-2010'), convert(datetime,'12-31-2010')) as float)+1)
when ((I > convert(datetime,'01-01-2010')) and(P >= convert(datetime,'03-31-2010')))
then polannprem*(cast(DATEDIFF(DAY, I, convert(datetime,'03-31-2010'))+1 as float))/(cast(DATEDIFF(DAY, convert(datetime,'01-01-2010'), convert(datetime,'12-31-2010')) as float)+1)
when ((I <= convert(datetime,'01-01-2010')) and (P < convert(datetime,'03-31-2010')))
then polannprem*(cast(DATEDIFF(DAY, convert(datetime,'01-01-2010'), P)+1 as float))/(cast(DATEDIFF(day, convert(datetime,'01-01-2010'), convert(datetime,'12-31-2010')) as float)+1)
when ((I > convert(datetime,'01-01-2010')) and (P < convert(datetime,'03-31-2010')))
then polannprem*(cast(DATEDIFF(DAY, I, P)+1 as float))/(cast(DATEDIFF(DAY, convert(datetime,'01-01-2010'), convert(datetime,'12-31-2010')) as float)+1)
end as EP2010Q1,
case
when (P < convert(datetime,'04-01-2010')) then '0'
when (I > convert(datetime,'06-30-2010')) then '0'
when ((I <= convert(datetime,'04-01-2010')) and (P >= convert(datetime,'06-30-2010'))) then polannprem*(cast(DATEDIFF(DAY, convert(datetime,'04-01-2010'), convert(datetime,'06-30-2010')) as float)+1)/(cast(DATEDIFF(DAY, convert(datetime,'01-01-2010'), convert(datetime,'12-31-2010')) as float)+1)
when ((I > convert(datetime,'04-01-2010')) and(P >= convert(datetime,'06-30-2010')))
then polannprem*(cast(DATEDIFF(DAY, I, convert(datetime,'06-30-2010'))+1 as float))/(cast(DATEDIFF(DAY, convert(datetime,'01-01-2010'), convert(datetime,'12-31-2010')) as float)+1)
when ((I <= convert(datetime,'04-01-2010')) and (P < convert(datetime,'06-30-2010')))
then polannprem*(cast(DATEDIFF(DAY, convert(datetime,'04-01-2010'), P)+1 as float))/(cast(DATEDIFF(day, convert(datetime,'01-01-2010'), convert(datetime,'12-31-2010')) as float)+1)
when ((I > convert(datetime,'04-01-2010')) and (P < convert(datetime,'06-30-2010')))
then polannprem*(cast(DATEDIFF(DAY, I, P)+1 as float))/(cast(DATEDIFF(DAY, convert(datetime,'01-01-2010'), convert(datetime,'12-31-2010')) as float)+1)
end as EP2010Q2,
case
when (P < convert(datetime,'07-01-2010')) then '0'
when (I > convert(datetime,'09-30-2010')) then '0'
when ((I <= convert(datetime,'07-01-2010')) and (P >= convert(datetime,'09-30-2010'))) then polannprem*(cast(DATEDIFF(DAY, convert(datetime,'07-01-2010'), convert(datetime,'09-30-2010')) as float)+1)/(cast(DATEDIFF(DAY, convert(datetime,'01-01-2010'), convert(datetime,'12-31-2010')) as float)+1)
when ((I > convert(datetime,'07-01-2010')) and(P >= convert(datetime,'09-30-2010')))
then polannprem*(cast(DATEDIFF(DAY, I, convert(datetime,'09-30-2010'))+1 as float))/(cast(DATEDIFF(DAY, convert(datetime,'01-01-2010'), convert(datetime,'12-31-2010')) as float)+1)
when ((I <= convert(datetime,'07-01-2010')) and (P < convert(datetime,'09-30-2010')))
then polannprem*(cast(DATEDIFF(DAY, convert(datetime,'07-01-2010'), P)+1 as float))/(cast(DATEDIFF(day, convert(datetime,'01-01-2010'), convert(datetime,'12-31-2010')) as float)+1)
when ((I > convert(datetime,'07-01-2010')) and (P < convert(datetime,'09-30-2010')))
then polannprem*(cast(DATEDIFF(DAY, I, P)+1 as float))/(cast(DATEDIFF(DAY, convert(datetime,'01-01-2010'), convert(datetime,'12-31-2010')) as float)+1)
end as EP2010Q3,
case
when (P < convert(datetime,'10-01-2010')) then '0'
when (I > convert(datetime,'12-31-2010')) then '0'
when ((I <= convert(datetime,'10-01-2010')) and (P >= convert(datetime,'12-31-2010'))) then polannprem*(cast(DATEDIFF(DAY, convert(datetime,'10-01-2010'), convert(datetime,'12-31-2010')) as float)+1)/(cast(DATEDIFF(DAY, convert(datetime,'01-01-2010'), convert(datetime,'12-31-2010')) as float)+1)
when ((I > convert(datetime,'10-01-2010')) and(P >= convert(datetime,'12-31-2010')))
then polannprem*(cast(DATEDIFF(DAY, I, convert(datetime,'12-31-2010'))+1 as float))/(cast(DATEDIFF(DAY, convert(datetime,'01-01-2010'), convert(datetime,'12-31-2010')) as float)+1)
when ((I <= convert(datetime,'10-01-2010')) and (P < convert(datetime,'12-31-2010')))
then polannprem*(cast(DATEDIFF(DAY, convert(datetime,'10-01-2010'), P)+1 as float))/(cast(DATEDIFF(day, convert(datetime,'01-01-2010'), convert(datetime,'12-31-2010')) as float)+1)
when ((I > convert(datetime,'10-01-2010')) and (P < convert(datetime,'12-31-2010')))
then polannprem*(cast(DATEDIFF(DAY, I, P)+1 as float))/(cast(DATEDIFF(DAY, convert(datetime,'01-01-2010'), convert(datetime,'12-31-2010')) as float)+1)
end as EP2010Q4
From query1

GROUP BY
CO,
Product,
IssYr,
ISST,
Agent,
Status

Passacaglia · 06-01-2011, 01:38 PM

So basically, I have the same block of code except for the dates are changed, 11 times. It wasn't a huge deal to copy and paste the code, then change the dates, but it'd be nice for future programs to be able to run that code, then change the dates.

But now I've got another problem -- when I run this, it errors because

Quote:

Msg 8120, Level 16, State 1, Line 28
Column 'query1.P' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

I tried adding
Min(I) as I,
Max(P) as P,
Max(polannprem) as polannprem,

To my second set of select statements, but I got the same error.

bhlloy · 06-01-2011, 01:53 PM

Why do you need the group by in there? Your CASE WHEN statements aren't aggregate functions.

Passacaglia · 06-01-2011, 03:09 PM

Well I want to group by those fields. So for each combination of those 6 fields, I want the sum of the EP in each quarter.

bhlloy · 06-01-2011, 03:13 PM

In that case, you'd need the SUM() in there around your case statements I would have thought. CASE WHEN is not an aggregate function, so that's why it's giving you the error.

Disclaimer that this isn't my first language... so I may be off base.

Passacaglia · 06-01-2011, 03:14 PM

Yeah, I was just realizing that, too.

Passacaglia · 06-01-2011, 03:22 PM

Okay, I think I have what I want. I have to change all those I's and P's back to the full length name, I think, which I'll do tomorrow. But I think then it will work.

Passacaglia · 06-02-2011, 03:08 PM

I think I've got it!

Quote:

with query1 as (select
Financial_Analysis.AxGage.[Policy Info Master].CO,
Financial_Analysis.AxGage.[mapping DLM, Ext Form, Int Form].Product,
Financial_Analysis.AxGage.[Policy Info Master].IssYr,
Financial_Analysis.AxGage.[Policy Info Master].ISST,
Financial_Analysis.AxGage.[Policy Info Master].AGENT,
Underwriting_DB.dbo.Agent_Realigned.Status,
case
when ([Financial_Analysis].[AxGage].[Policy Info Master].PTDate < convert(datetime,'04-01-2007')) then '0'
when ([Financial_Analysis].[AxGage].[Policy Info Master].IssDate > convert(datetime,'06-30-2007')) then '0'
when (([Financial_Analysis].[AxGage].[Policy Info Master].IssDate <= convert(datetime,'04-01-2007')) and ([Financial_Analysis].[AxGage].[Policy Info Master].PTDate >= convert(datetime,'06-30-2007'))) then polannprem*(cast(DATEDIFF(DAY, convert(datetime,'04-01-2007'), convert(datetime,'06-30-2007')) as float)+1)/(cast(DATEDIFF(DAY, convert(datetime,'01-01-2007'), convert(datetime,'12-31-2007')) as float)+1)
when (([Financial_Analysis].[AxGage].[Policy Info Master].IssDate > convert(datetime,'04-01-2007')) and([Financial_Analysis].[AxGage].[Policy Info Master].PTDate >= convert(datetime,'06-30-2007')))
then polannprem*(cast(DATEDIFF(DAY, [Financial_Analysis].[AxGage].[Policy Info Master].IssDate, convert(datetime,'06-30-2007'))+1 as float))/(cast(DATEDIFF(DAY, convert(datetime,'01-01-2007'), convert(datetime,'12-31-2007')) as float)+1)
when (([Financial_Analysis].[AxGage].[Policy Info Master].IssDate <= convert(datetime,'04-01-2007')) and ([Financial_Analysis].[AxGage].[Policy Info Master].PTDate < convert(datetime,'06-30-2007')))
then polannprem*(cast(DATEDIFF(DAY, convert(datetime,'04-01-2007'), [Financial_Analysis].[AxGage].[Policy Info Master].PTDate)+1 as float))/(cast(DATEDIFF(day, convert(datetime,'01-01-2007'), convert(datetime,'12-31-2007')) as float)+1)
when (([Financial_Analysis].[AxGage].[Policy Info Master].IssDate > convert(datetime,'04-01-2007')) and ([Financial_Analysis].[AxGage].[Policy Info Master].PTDate < convert(datetime,'06-30-2007')))
then polannprem*(cast(DATEDIFF(DAY, [Financial_Analysis].[AxGage].[Policy Info Master].IssDate, [Financial_Analysis].[AxGage].[Policy Info Master].PTDate)+1 as float))/(cast(DATEDIFF(DAY, convert(datetime,'01-01-2007'), convert(datetime,'12-31-2007')) as float)+1)
end as EP2007Q2,
case
when ([Financial_Analysis].[AxGage].[Policy Info Master].PTDate < convert(datetime,'07-01-2007')) then '0'
when ([Financial_Analysis].[AxGage].[Policy Info Master].IssDate > convert(datetime,'09-30-2007')) then '0'
when (([Financial_Analysis].[AxGage].[Policy Info Master].IssDate <= convert(datetime,'07-01-2007')) and ([Financial_Analysis].[AxGage].[Policy Info Master].PTDate >= convert(datetime,'09-30-2007'))) then polannprem*(cast(DATEDIFF(DAY, convert(datetime,'07-01-2007'), convert(datetime,'09-30-2007')) as float)+1)/(cast(DATEDIFF(DAY, convert(datetime,'01-01-2007'), convert(datetime,'12-31-2007')) as float)+1)
when (([Financial_Analysis].[AxGage].[Policy Info Master].IssDate > convert(datetime,'07-01-2007')) and([Financial_Analysis].[AxGage].[Policy Info Master].PTDate >= convert(datetime,'09-30-2007')))
then polannprem*(cast(DATEDIFF(DAY, [Financial_Analysis].[AxGage].[Policy Info Master].IssDate, convert(datetime,'09-30-2007'))+1 as float))/(cast(DATEDIFF(DAY, convert(datetime,'01-01-2007'), convert(datetime,'12-31-2007')) as float)+1)
when (([Financial_Analysis].[AxGage].[Policy Info Master].IssDate <= convert(datetime,'07-01-2007')) and ([Financial_Analysis].[AxGage].[Policy Info Master].PTDate < convert(datetime,'09-30-2007')))
then polannprem*(cast(DATEDIFF(DAY, convert(datetime,'07-01-2007'), [Financial_Analysis].[AxGage].[Policy Info Master].PTDate)+1 as float))/(cast(DATEDIFF(day, convert(datetime,'01-01-2007'), convert(datetime,'12-31-2007')) as float)+1)
when (([Financial_Analysis].[AxGage].[Policy Info Master].IssDate > convert(datetime,'07-01-2007')) and ([Financial_Analysis].[AxGage].[Policy Info Master].PTDate < convert(datetime,'09-30-2007')))
then polannprem*(cast(DATEDIFF(DAY, [Financial_Analysis].[AxGage].[Policy Info Master].IssDate, [Financial_Analysis].[AxGage].[Policy Info Master].PTDate)+1 as float))/(cast(DATEDIFF(DAY, convert(datetime,'01-01-2007'), convert(datetime,'12-31-2007')) as float)+1)
end as EP2007Q3,
case
when ([Financial_Analysis].[AxGage].[Policy Info Master].PTDate < convert(datetime,'10-01-2007')) then '0'
when ([Financial_Analysis].[AxGage].[Policy Info Master].IssDate > convert(datetime,'12-31-2007')) then '0'
when (([Financial_Analysis].[AxGage].[Policy Info Master].IssDate <= convert(datetime,'10-01-2007')) and ([Financial_Analysis].[AxGage].[Policy Info Master].PTDate >= convert(datetime,'12-31-2007'))) then polannprem*(cast(DATEDIFF(DAY, convert(datetime,'10-01-2007'), convert(datetime,'12-31-2007')) as float)+1)/(cast(DATEDIFF(DAY, convert(datetime,'01-01-2007'), convert(datetime,'12-31-2007')) as float)+1)
when (([Financial_Analysis].[AxGage].[Policy Info Master].IssDate > convert(datetime,'10-01-2007')) and([Financial_Analysis].[AxGage].[Policy Info Master].PTDate >= convert(datetime,'12-31-2007')))
then polannprem*(cast(DATEDIFF(DAY, [Financial_Analysis].[AxGage].[Policy Info Master].IssDate, convert(datetime,'12-31-2007'))+1 as float))/(cast(DATEDIFF(DAY, convert(datetime,'01-01-2007'), convert(datetime,'12-31-2007')) as float)+1)
when (([Financial_Analysis].[AxGage].[Policy Info Master].IssDate <= convert(datetime,'10-01-2007')) and ([Financial_Analysis].[AxGage].[Policy Info Master].PTDate < convert(datetime,'12-31-2007')))
then polannprem*(cast(DATEDIFF(DAY, convert(datetime,'10-01-2007'), [Financial_Analysis].[AxGage].[Policy Info Master].PTDate)+1 as float))/(cast(DATEDIFF(day, convert(datetime,'01-01-2007'), convert(datetime,'12-31-2007')) as float)+1)
when (([Financial_Analysis].[AxGage].[Policy Info Master].IssDate > convert(datetime,'10-01-2007')) and ([Financial_Analysis].[AxGage].[Policy Info Master].PTDate < convert(datetime,'12-31-2007')))
then polannprem*(cast(DATEDIFF(DAY, [Financial_Analysis].[AxGage].[Policy Info Master].IssDate, [Financial_Analysis].[AxGage].[Policy Info Master].PTDate)+1 as float))/(cast(DATEDIFF(DAY, convert(datetime,'01-01-2007'), convert(datetime,'12-31-2007')) as float)+1)
end as EP2007Q4,
case
when ([Financial_Analysis].[AxGage].[Policy Info Master].PTDate < convert(datetime,'01-01-2008')) then '0'
when ([Financial_Analysis].[AxGage].[Policy Info Master].IssDate > convert(datetime,'03-31-2008')) then '0'
when (([Financial_Analysis].[AxGage].[Policy Info Master].IssDate <= convert(datetime,'01-01-2008')) and ([Financial_Analysis].[AxGage].[Policy Info Master].PTDate >= convert(datetime,'03-31-2008'))) then polannprem*(cast(DATEDIFF(DAY, convert(datetime,'01-01-2008'), convert(datetime,'03-31-2008')) as float)+1)/(cast(DATEDIFF(DAY, convert(datetime,'01-01-2008'), convert(datetime,'12-31-2008')) as float)+1)
when (([Financial_Analysis].[AxGage].[Policy Info Master].IssDate > convert(datetime,'01-01-2008')) and([Financial_Analysis].[AxGage].[Policy Info Master].PTDate >= convert(datetime,'03-31-2008')))
then polannprem*(cast(DATEDIFF(DAY, [Financial_Analysis].[AxGage].[Policy Info Master].IssDate, convert(datetime,'03-31-2008'))+1 as float))/(cast(DATEDIFF(DAY, convert(datetime,'01-01-2008'), convert(datetime,'12-31-2008')) as float)+1)
when (([Financial_Analysis].[AxGage].[Policy Info Master].IssDate <= convert(datetime,'01-01-2008')) and ([Financial_Analysis].[AxGage].[Policy Info Master].PTDate < convert(datetime,'03-31-2008')))
then polannprem*(cast(DATEDIFF(DAY, convert(datetime,'01-01-2008'), [Financial_Analysis].[AxGage].[Policy Info Master].PTDate)+1 as float))/(cast(DATEDIFF(day, convert(datetime,'01-01-2008'), convert(datetime,'12-31-2008')) as float)+1)
when (([Financial_Analysis].[AxGage].[Policy Info Master].IssDate > convert(datetime,'01-01-2008')) and ([Financial_Analysis].[AxGage].[Policy Info Master].PTDate < convert(datetime,'03-31-2008')))
then polannprem*(cast(DATEDIFF(DAY, [Financial_Analysis].[AxGage].[Policy Info Master].IssDate, [Financial_Analysis].[AxGage].[Policy Info Master].PTDate)+1 as float))/(cast(DATEDIFF(DAY, convert(datetime,'01-01-2008'), convert(datetime,'12-31-2008')) as float)+1)
end as EP2008Q1,
case
when ([Financial_Analysis].[AxGage].[Policy Info Master].PTDate < convert(datetime,'04-01-2008')) then '0'
when ([Financial_Analysis].[AxGage].[Policy Info Master].IssDate > convert(datetime,'06-30-2008')) then '0'
when (([Financial_Analysis].[AxGage].[Policy Info Master].IssDate <= convert(datetime,'04-01-2008')) and ([Financial_Analysis].[AxGage].[Policy Info Master].PTDate >= convert(datetime,'06-30-2008'))) then polannprem*(cast(DATEDIFF(DAY, convert(datetime,'04-01-2008'), convert(datetime,'06-30-2008')) as float)+1)/(cast(DATEDIFF(DAY, convert(datetime,'01-01-2008'), convert(datetime,'12-31-2008')) as float)+1)
when (([Financial_Analysis].[AxGage].[Policy Info Master].IssDate > convert(datetime,'04-01-2008')) and([Financial_Analysis].[AxGage].[Policy Info Master].PTDate >= convert(datetime,'06-30-2008')))
then polannprem*(cast(DATEDIFF(DAY, [Financial_Analysis].[AxGage].[Policy Info Master].IssDate, convert(datetime,'06-30-2008'))+1 as float))/(cast(DATEDIFF(DAY, convert(datetime,'01-01-2008'), convert(datetime,'12-31-2008')) as float)+1)
when (([Financial_Analysis].[AxGage].[Policy Info Master].IssDate <= convert(datetime,'04-01-2008')) and ([Financial_Analysis].[AxGage].[Policy Info Master].PTDate < convert(datetime,'06-30-2008')))
then polannprem*(cast(DATEDIFF(DAY, convert(datetime,'04-01-2008'), [Financial_Analysis].[AxGage].[Policy Info Master].PTDate)+1 as float))/(cast(DATEDIFF(day, convert(datetime,'01-01-2008'), convert(datetime,'12-31-2008')) as float)+1)
when (([Financial_Analysis].[AxGage].[Policy Info Master].IssDate > convert(datetime,'04-01-2008')) and ([Financial_Analysis].[AxGage].[Policy Info Master].PTDate < convert(datetime,'06-30-2008')))
then polannprem*(cast(DATEDIFF(DAY, [Financial_Analysis].[AxGage].[Policy Info Master].IssDate, [Financial_Analysis].[AxGage].[Policy Info Master].PTDate)+1 as float))/(cast(DATEDIFF(DAY, convert(datetime,'01-01-2008'), convert(datetime,'12-31-2008')) as float)+1)
end as EP2008Q2,
case
when ([Financial_Analysis].[AxGage].[Policy Info Master].PTDate < convert(datetime,'07-01-2008')) then '0'
when ([Financial_Analysis].[AxGage].[Policy Info Master].IssDate > convert(datetime,'09-30-2008')) then '0'
when (([Financial_Analysis].[AxGage].[Policy Info Master].IssDate <= convert(datetime,'07-01-2008')) and ([Financial_Analysis].[AxGage].[Policy Info Master].PTDate >= convert(datetime,'09-30-2008'))) then polannprem*(cast(DATEDIFF(DAY, convert(datetime,'07-01-2008'), convert(datetime,'09-30-2008')) as float)+1)/(cast(DATEDIFF(DAY, convert(datetime,'01-01-2008'), convert(datetime,'12-31-2008')) as float)+1)
when (([Financial_Analysis].[AxGage].[Policy Info Master].IssDate > convert(datetime,'07-01-2008')) and([Financial_Analysis].[AxGage].[Policy Info Master].PTDate >= convert(datetime,'09-30-2008')))
then polannprem*(cast(DATEDIFF(DAY, [Financial_Analysis].[AxGage].[Policy Info Master].IssDate, convert(datetime,'09-30-2008'))+1 as float))/(cast(DATEDIFF(DAY, convert(datetime,'01-01-2008'), convert(datetime,'12-31-2008')) as float)+1)
when (([Financial_Analysis].[AxGage].[Policy Info Master].IssDate <= convert(datetime,'07-01-2008')) and ([Financial_Analysis].[AxGage].[Policy Info Master].PTDate < convert(datetime,'09-30-2008')))
then polannprem*(cast(DATEDIFF(DAY, convert(datetime,'07-01-2008'), [Financial_Analysis].[AxGage].[Policy Info Master].PTDate)+1 as float))/(cast(DATEDIFF(day, convert(datetime,'01-01-2008'), convert(datetime,'12-31-2008')) as float)+1)
when (([Financial_Analysis].[AxGage].[Policy Info Master].IssDate > convert(datetime,'07-01-2008')) and ([Financial_Analysis].[AxGage].[Policy Info Master].PTDate < convert(datetime,'09-30-2008')))
then polannprem*(cast(DATEDIFF(DAY, [Financial_Analysis].[AxGage].[Policy Info Master].IssDate, [Financial_Analysis].[AxGage].[Policy Info Master].PTDate)+1 as float))/(cast(DATEDIFF(DAY, convert(datetime,'01-01-2008'), convert(datetime,'12-31-2008')) as float)+1)
end as EP2008Q3,
case
when ([Financial_Analysis].[AxGage].[Policy Info Master].PTDate < convert(datetime,'10-01-2008')) then '0'
when ([Financial_Analysis].[AxGage].[Policy Info Master].IssDate > convert(datetime,'12-31-2008')) then '0'
when (([Financial_Analysis].[AxGage].[Policy Info Master].IssDate <= convert(datetime,'10-01-2008')) and ([Financial_Analysis].[AxGage].[Policy Info Master].PTDate >= convert(datetime,'12-31-2008'))) then polannprem*(cast(DATEDIFF(DAY, convert(datetime,'10-01-2008'), convert(datetime,'12-31-2008')) as float)+1)/(cast(DATEDIFF(DAY, convert(datetime,'01-01-2008'), convert(datetime,'12-31-2008')) as float)+1)
when (([Financial_Analysis].[AxGage].[Policy Info Master].IssDate > convert(datetime,'10-01-2008')) and([Financial_Analysis].[AxGage].[Policy Info Master].PTDate >= convert(datetime,'12-31-2008')))
then polannprem*(cast(DATEDIFF(DAY, [Financial_Analysis].[AxGage].[Policy Info Master].IssDate, convert(datetime,'12-31-2008'))+1 as float))/(cast(DATEDIFF(DAY, convert(datetime,'01-01-2008'), convert(datetime,'12-31-2008')) as float)+1)
when (([Financial_Analysis].[AxGage].[Policy Info Master].IssDate <= convert(datetime,'10-01-2008')) and ([Financial_Analysis].[AxGage].[Policy Info Master].PTDate < convert(datetime,'12-31-2008')))
then polannprem*(cast(DATEDIFF(DAY, convert(datetime,'10-01-2008'), [Financial_Analysis].[AxGage].[Policy Info Master].PTDate)+1 as float))/(cast(DATEDIFF(day, convert(datetime,'01-01-2008'), convert(datetime,'12-31-2008')) as float)+1)
when (([Financial_Analysis].[AxGage].[Policy Info Master].IssDate > convert(datetime,'10-01-2008')) and ([Financial_Analysis].[AxGage].[Policy Info Master].PTDate < convert(datetime,'12-31-2008')))
then polannprem*(cast(DATEDIFF(DAY, [Financial_Analysis].[AxGage].[Policy Info Master].IssDate, [Financial_Analysis].[AxGage].[Policy Info Master].PTDate)+1 as float))/(cast(DATEDIFF(DAY, convert(datetime,'01-01-2008'), convert(datetime,'12-31-2008')) as float)+1)
end as EP2008Q4,

Passacaglia · 06-02-2011, 03:13 PM

Then:

Quote:

case
when ([Financial_Analysis].[AxGage].[Policy Info Master].PTDate < convert(datetime,'01-01-2009')) then '0'
when ([Financial_Analysis].[AxGage].[Policy Info Master].IssDate > convert(datetime,'03-31-2009')) then '0'
when (([Financial_Analysis].[AxGage].[Policy Info Master].IssDate <= convert(datetime,'01-01-2009')) and ([Financial_Analysis].[AxGage].[Policy Info Master].PTDate >= convert(datetime,'03-31-2009'))) then polannprem*(cast(DATEDIFF(DAY, convert(datetime,'01-01-2009'), convert(datetime,'03-31-2009')) as float)+1)/(cast(DATEDIFF(DAY, convert(datetime,'01-01-2009'), convert(datetime,'12-31-2009')) as float)+1)
when (([Financial_Analysis].[AxGage].[Policy Info Master].IssDate > convert(datetime,'01-01-2009')) and([Financial_Analysis].[AxGage].[Policy Info Master].PTDate >= convert(datetime,'03-31-2009')))
then polannprem*(cast(DATEDIFF(DAY, [Financial_Analysis].[AxGage].[Policy Info Master].IssDate, convert(datetime,'03-31-2009'))+1 as float))/(cast(DATEDIFF(DAY, convert(datetime,'01-01-2009'), convert(datetime,'12-31-2009')) as float)+1)
when (([Financial_Analysis].[AxGage].[Policy Info Master].IssDate <= convert(datetime,'01-01-2009')) and ([Financial_Analysis].[AxGage].[Policy Info Master].PTDate < convert(datetime,'03-31-2009')))
then polannprem*(cast(DATEDIFF(DAY, convert(datetime,'01-01-2009'), [Financial_Analysis].[AxGage].[Policy Info Master].PTDate)+1 as float))/(cast(DATEDIFF(day, convert(datetime,'01-01-2009'), convert(datetime,'12-31-2009')) as float)+1)
when (([Financial_Analysis].[AxGage].[Policy Info Master].IssDate > convert(datetime,'01-01-2009')) and ([Financial_Analysis].[AxGage].[Policy Info Master].PTDate < convert(datetime,'03-31-2009')))
then polannprem*(cast(DATEDIFF(DAY, [Financial_Analysis].[AxGage].[Policy Info Master].IssDate, [Financial_Analysis].[AxGage].[Policy Info Master].PTDate)+1 as float))/(cast(DATEDIFF(DAY, convert(datetime,'01-01-2009'), convert(datetime,'12-31-2009')) as float)+1)
end as EP2009Q1,
case
when ([Financial_Analysis].[AxGage].[Policy Info Master].PTDate < convert(datetime,'04-01-2009')) then '0'
when ([Financial_Analysis].[AxGage].[Policy Info Master].IssDate > convert(datetime,'06-30-2009')) then '0'
when (([Financial_Analysis].[AxGage].[Policy Info Master].IssDate <= convert(datetime,'04-01-2009')) and ([Financial_Analysis].[AxGage].[Policy Info Master].PTDate >= convert(datetime,'06-30-2009'))) then polannprem*(cast(DATEDIFF(DAY, convert(datetime,'04-01-2009'), convert(datetime,'06-30-2009')) as float)+1)/(cast(DATEDIFF(DAY, convert(datetime,'01-01-2009'), convert(datetime,'12-31-2009')) as float)+1)
when (([Financial_Analysis].[AxGage].[Policy Info Master].IssDate > convert(datetime,'04-01-2009')) and([Financial_Analysis].[AxGage].[Policy Info Master].PTDate >= convert(datetime,'06-30-2009')))
then polannprem*(cast(DATEDIFF(DAY, [Financial_Analysis].[AxGage].[Policy Info Master].IssDate, convert(datetime,'06-30-2009'))+1 as float))/(cast(DATEDIFF(DAY, convert(datetime,'01-01-2009'), convert(datetime,'12-31-2009')) as float)+1)
when (([Financial_Analysis].[AxGage].[Policy Info Master].IssDate <= convert(datetime,'04-01-2009')) and ([Financial_Analysis].[AxGage].[Policy Info Master].PTDate < convert(datetime,'06-30-2009')))
then polannprem*(cast(DATEDIFF(DAY, convert(datetime,'04-01-2009'), [Financial_Analysis].[AxGage].[Policy Info Master].PTDate)+1 as float))/(cast(DATEDIFF(day, convert(datetime,'01-01-2009'), convert(datetime,'12-31-2009')) as float)+1)
when (([Financial_Analysis].[AxGage].[Policy Info Master].IssDate > convert(datetime,'04-01-2009')) and ([Financial_Analysis].[AxGage].[Policy Info Master].PTDate < convert(datetime,'06-30-2009')))
then polannprem*(cast(DATEDIFF(DAY, [Financial_Analysis].[AxGage].[Policy Info Master].IssDate, [Financial_Analysis].[AxGage].[Policy Info Master].PTDate)+1 as float))/(cast(DATEDIFF(DAY, convert(datetime,'01-01-2009'), convert(datetime,'12-31-2009')) as float)+1)
end as EP2009Q2,
case
when ([Financial_Analysis].[AxGage].[Policy Info Master].PTDate < convert(datetime,'07-01-2009')) then '0'
when ([Financial_Analysis].[AxGage].[Policy Info Master].IssDate > convert(datetime,'09-30-2009')) then '0'
when (([Financial_Analysis].[AxGage].[Policy Info Master].IssDate <= convert(datetime,'07-01-2009')) and ([Financial_Analysis].[AxGage].[Policy Info Master].PTDate >= convert(datetime,'09-30-2009'))) then polannprem*(cast(DATEDIFF(DAY, convert(datetime,'07-01-2009'), convert(datetime,'09-30-2009')) as float)+1)/(cast(DATEDIFF(DAY, convert(datetime,'01-01-2009'), convert(datetime,'12-31-2009')) as float)+1)
when (([Financial_Analysis].[AxGage].[Policy Info Master].IssDate > convert(datetime,'07-01-2009')) and([Financial_Analysis].[AxGage].[Policy Info Master].PTDate >= convert(datetime,'09-30-2009')))
then polannprem*(cast(DATEDIFF(DAY, [Financial_Analysis].[AxGage].[Policy Info Master].IssDate, convert(datetime,'09-30-2009'))+1 as float))/(cast(DATEDIFF(DAY, convert(datetime,'01-01-2009'), convert(datetime,'12-31-2009')) as float)+1)
when (([Financial_Analysis].[AxGage].[Policy Info Master].IssDate <= convert(datetime,'07-01-2009')) and ([Financial_Analysis].[AxGage].[Policy Info Master].PTDate < convert(datetime,'09-30-2009')))
then polannprem*(cast(DATEDIFF(DAY, convert(datetime,'07-01-2009'), [Financial_Analysis].[AxGage].[Policy Info Master].PTDate)+1 as float))/(cast(DATEDIFF(day, convert(datetime,'01-01-2009'), convert(datetime,'12-31-2009')) as float)+1)
when (([Financial_Analysis].[AxGage].[Policy Info Master].IssDate > convert(datetime,'07-01-2009')) and ([Financial_Analysis].[AxGage].[Policy Info Master].PTDate < convert(datetime,'09-30-2009')))
then polannprem*(cast(DATEDIFF(DAY, [Financial_Analysis].[AxGage].[Policy Info Master].IssDate, [Financial_Analysis].[AxGage].[Policy Info Master].PTDate)+1 as float))/(cast(DATEDIFF(DAY, convert(datetime,'01-01-2009'), convert(datetime,'12-31-2009')) as float)+1)
end as EP2009Q3,
case
when ([Financial_Analysis].[AxGage].[Policy Info Master].PTDate < convert(datetime,'10-01-2009')) then '0'
when ([Financial_Analysis].[AxGage].[Policy Info Master].IssDate > convert(datetime,'12-31-2009')) then '0'
when (([Financial_Analysis].[AxGage].[Policy Info Master].IssDate <= convert(datetime,'10-01-2009')) and ([Financial_Analysis].[AxGage].[Policy Info Master].PTDate >= convert(datetime,'12-31-2009'))) then polannprem*(cast(DATEDIFF(DAY, convert(datetime,'10-01-2009'), convert(datetime,'12-31-2009')) as float)+1)/(cast(DATEDIFF(DAY, convert(datetime,'01-01-2009'), convert(datetime,'12-31-2009')) as float)+1)
when (([Financial_Analysis].[AxGage].[Policy Info Master].IssDate > convert(datetime,'10-01-2009')) and([Financial_Analysis].[AxGage].[Policy Info Master].PTDate >= convert(datetime,'12-31-2009')))
then polannprem*(cast(DATEDIFF(DAY, [Financial_Analysis].[AxGage].[Policy Info Master].IssDate, convert(datetime,'12-31-2009'))+1 as float))/(cast(DATEDIFF(DAY, convert(datetime,'01-01-2009'), convert(datetime,'12-31-2009')) as float)+1)
when (([Financial_Analysis].[AxGage].[Policy Info Master].IssDate <= convert(datetime,'10-01-2009')) and ([Financial_Analysis].[AxGage].[Policy Info Master].PTDate < convert(datetime,'12-31-2009')))
then polannprem*(cast(DATEDIFF(DAY, convert(datetime,'10-01-2009'), [Financial_Analysis].[AxGage].[Policy Info Master].PTDate)+1 as float))/(cast(DATEDIFF(day, convert(datetime,'01-01-2009'), convert(datetime,'12-31-2009')) as float)+1)
when (([Financial_Analysis].[AxGage].[Policy Info Master].IssDate > convert(datetime,'10-01-2009')) and ([Financial_Analysis].[AxGage].[Policy Info Master].PTDate < convert(datetime,'12-31-2009')))
then polannprem*(cast(DATEDIFF(DAY, [Financial_Analysis].[AxGage].[Policy Info Master].IssDate, [Financial_Analysis].[AxGage].[Policy Info Master].PTDate)+1 as float))/(cast(DATEDIFF(DAY, convert(datetime,'01-01-2009'), convert(datetime,'12-31-2009')) as float)+1)
end as EP2009Q4,
case
when ([Financial_Analysis].[AxGage].[Policy Info Master].PTDate < convert(datetime,'01-01-2010')) then '0'
when ([Financial_Analysis].[AxGage].[Policy Info Master].IssDate > convert(datetime,'03-31-2010')) then '0'
when (([Financial_Analysis].[AxGage].[Policy Info Master].IssDate <= convert(datetime,'01-01-2010')) and ([Financial_Analysis].[AxGage].[Policy Info Master].PTDate >= convert(datetime,'03-31-2010'))) then polannprem*(cast(DATEDIFF(DAY, convert(datetime,'01-01-2010'), convert(datetime,'03-31-2010')) as float)+1)/(cast(DATEDIFF(DAY, convert(datetime,'01-01-2010'), convert(datetime,'12-31-2010')) as float)+1)
when (([Financial_Analysis].[AxGage].[Policy Info Master].IssDate > convert(datetime,'01-01-2010')) and([Financial_Analysis].[AxGage].[Policy Info Master].PTDate >= convert(datetime,'03-31-2010')))
then polannprem*(cast(DATEDIFF(DAY, [Financial_Analysis].[AxGage].[Policy Info Master].IssDate, convert(datetime,'03-31-2010'))+1 as float))/(cast(DATEDIFF(DAY, convert(datetime,'01-01-2010'), convert(datetime,'12-31-2010')) as float)+1)
when (([Financial_Analysis].[AxGage].[Policy Info Master].IssDate <= convert(datetime,'01-01-2010')) and ([Financial_Analysis].[AxGage].[Policy Info Master].PTDate < convert(datetime,'03-31-2010')))
then polannprem*(cast(DATEDIFF(DAY, convert(datetime,'01-01-2010'), [Financial_Analysis].[AxGage].[Policy Info Master].PTDate)+1 as float))/(cast(DATEDIFF(day, convert(datetime,'01-01-2010'), convert(datetime,'12-31-2010')) as float)+1)
when (([Financial_Analysis].[AxGage].[Policy Info Master].IssDate > convert(datetime,'01-01-2010')) and ([Financial_Analysis].[AxGage].[Policy Info Master].PTDate < convert(datetime,'03-31-2010')))
then polannprem*(cast(DATEDIFF(DAY, [Financial_Analysis].[AxGage].[Policy Info Master].IssDate, [Financial_Analysis].[AxGage].[Policy Info Master].PTDate)+1 as float))/(cast(DATEDIFF(DAY, convert(datetime,'01-01-2010'), convert(datetime,'12-31-2010')) as float)+1)
end as EP2010Q1,
case
when ([Financial_Analysis].[AxGage].[Policy Info Master].PTDate < convert(datetime,'04-01-2010')) then '0'
when ([Financial_Analysis].[AxGage].[Policy Info Master].IssDate > convert(datetime,'06-30-2010')) then '0'
when (([Financial_Analysis].[AxGage].[Policy Info Master].IssDate <= convert(datetime,'04-01-2010')) and ([Financial_Analysis].[AxGage].[Policy Info Master].PTDate >= convert(datetime,'06-30-2010'))) then polannprem*(cast(DATEDIFF(DAY, convert(datetime,'04-01-2010'), convert(datetime,'06-30-2010')) as float)+1)/(cast(DATEDIFF(DAY, convert(datetime,'01-01-2010'), convert(datetime,'12-31-2010')) as float)+1)
when (([Financial_Analysis].[AxGage].[Policy Info Master].IssDate > convert(datetime,'04-01-2010')) and([Financial_Analysis].[AxGage].[Policy Info Master].PTDate >= convert(datetime,'06-30-2010')))
then polannprem*(cast(DATEDIFF(DAY, [Financial_Analysis].[AxGage].[Policy Info Master].IssDate, convert(datetime,'06-30-2010'))+1 as float))/(cast(DATEDIFF(DAY, convert(datetime,'01-01-2010'), convert(datetime,'12-31-2010')) as float)+1)
when (([Financial_Analysis].[AxGage].[Policy Info Master].IssDate <= convert(datetime,'04-01-2010')) and ([Financial_Analysis].[AxGage].[Policy Info Master].PTDate < convert(datetime,'06-30-2010')))
then polannprem*(cast(DATEDIFF(DAY, convert(datetime,'04-01-2010'), [Financial_Analysis].[AxGage].[Policy Info Master].PTDate)+1 as float))/(cast(DATEDIFF(day, convert(datetime,'01-01-2010'), convert(datetime,'12-31-2010')) as float)+1)
when (([Financial_Analysis].[AxGage].[Policy Info Master].IssDate > convert(datetime,'04-01-2010')) and ([Financial_Analysis].[AxGage].[Policy Info Master].PTDate < convert(datetime,'06-30-2010')))
then polannprem*(cast(DATEDIFF(DAY, [Financial_Analysis].[AxGage].[Policy Info Master].IssDate, [Financial_Analysis].[AxGage].[Policy Info Master].PTDate)+1 as float))/(cast(DATEDIFF(DAY, convert(datetime,'01-01-2010'), convert(datetime,'12-31-2010')) as float)+1)
end as EP2010Q2,
case
when ([Financial_Analysis].[AxGage].[Policy Info Master].PTDate < convert(datetime,'07-01-2010')) then '0'
when ([Financial_Analysis].[AxGage].[Policy Info Master].IssDate > convert(datetime,'09-30-2010')) then '0'
when (([Financial_Analysis].[AxGage].[Policy Info Master].IssDate <= convert(datetime,'07-01-2010')) and ([Financial_Analysis].[AxGage].[Policy Info Master].PTDate >= convert(datetime,'09-30-2010'))) then polannprem*(cast(DATEDIFF(DAY, convert(datetime,'07-01-2010'), convert(datetime,'09-30-2010')) as float)+1)/(cast(DATEDIFF(DAY, convert(datetime,'01-01-2010'), convert(datetime,'12-31-2010')) as float)+1)
when (([Financial_Analysis].[AxGage].[Policy Info Master].IssDate > convert(datetime,'07-01-2010')) and([Financial_Analysis].[AxGage].[Policy Info Master].PTDate >= convert(datetime,'09-30-2010')))
then polannprem*(cast(DATEDIFF(DAY, [Financial_Analysis].[AxGage].[Policy Info Master].IssDate, convert(datetime,'09-30-2010'))+1 as float))/(cast(DATEDIFF(DAY, convert(datetime,'01-01-2010'), convert(datetime,'12-31-2010')) as float)+1)
when (([Financial_Analysis].[AxGage].[Policy Info Master].IssDate <= convert(datetime,'07-01-2010')) and ([Financial_Analysis].[AxGage].[Policy Info Master].PTDate < convert(datetime,'09-30-2010')))
then polannprem*(cast(DATEDIFF(DAY, convert(datetime,'07-01-2010'), [Financial_Analysis].[AxGage].[Policy Info Master].PTDate)+1 as float))/(cast(DATEDIFF(day, convert(datetime,'01-01-2010'), convert(datetime,'12-31-2010')) as float)+1)
when (([Financial_Analysis].[AxGage].[Policy Info Master].IssDate > convert(datetime,'07-01-2010')) and ([Financial_Analysis].[AxGage].[Policy Info Master].PTDate < convert(datetime,'09-30-2010')))
then polannprem*(cast(DATEDIFF(DAY, [Financial_Analysis].[AxGage].[Policy Info Master].IssDate, [Financial_Analysis].[AxGage].[Policy Info Master].PTDate)+1 as float))/(cast(DATEDIFF(DAY, convert(datetime,'01-01-2010'), convert(datetime,'12-31-2010')) as float)+1)
end as EP2010Q3,
case
when ([Financial_Analysis].[AxGage].[Policy Info Master].PTDate < convert(datetime,'10-01-2010')) then '0'
when ([Financial_Analysis].[AxGage].[Policy Info Master].IssDate > convert(datetime,'12-31-2010')) then '0'
when (([Financial_Analysis].[AxGage].[Policy Info Master].IssDate <= convert(datetime,'10-01-2010')) and ([Financial_Analysis].[AxGage].[Policy Info Master].PTDate >= convert(datetime,'12-31-2010'))) then polannprem*(cast(DATEDIFF(DAY, convert(datetime,'10-01-2010'), convert(datetime,'12-31-2010')) as float)+1)/(cast(DATEDIFF(DAY, convert(datetime,'01-01-2010'), convert(datetime,'12-31-2010')) as float)+1)
when (([Financial_Analysis].[AxGage].[Policy Info Master].IssDate > convert(datetime,'10-01-2010')) and([Financial_Analysis].[AxGage].[Policy Info Master].PTDate >= convert(datetime,'12-31-2010')))
then polannprem*(cast(DATEDIFF(DAY, [Financial_Analysis].[AxGage].[Policy Info Master].IssDate, convert(datetime,'12-31-2010'))+1 as float))/(cast(DATEDIFF(DAY, convert(datetime,'01-01-2010'), convert(datetime,'12-31-2010')) as float)+1)
when (([Financial_Analysis].[AxGage].[Policy Info Master].IssDate <= convert(datetime,'10-01-2010')) and ([Financial_Analysis].[AxGage].[Policy Info Master].PTDate < convert(datetime,'12-31-2010')))
then polannprem*(cast(DATEDIFF(DAY, convert(datetime,'10-01-2010'), [Financial_Analysis].[AxGage].[Policy Info Master].PTDate)+1 as float))/(cast(DATEDIFF(day, convert(datetime,'01-01-2010'), convert(datetime,'12-31-2010')) as float)+1)
when (([Financial_Analysis].[AxGage].[Policy Info Master].IssDate > convert(datetime,'10-01-2010')) and ([Financial_Analysis].[AxGage].[Policy Info Master].PTDate < convert(datetime,'12-31-2010')))
then polannprem*(cast(DATEDIFF(DAY, [Financial_Analysis].[AxGage].[Policy Info Master].IssDate, [Financial_Analysis].[AxGage].[Policy Info Master].PTDate)+1 as float))/(cast(DATEDIFF(DAY, convert(datetime,'01-01-2010'), convert(datetime,'12-31-2010')) as float)+1)
end as EP2010Q4
From
Financial_Analysis.AxGage.[Policy Info Master]
INNER JOIN Financial_Analysis.AxGage.[mapping DLM, Ext Form, Int Form] on
Financial_Analysis.AxGage.[Policy Info Master].[Int Form] = Financial_Analysis.AxGage.[mapping DLM, Ext Form, Int Form].[Int Form]
LEFT OUTER JOIN Underwriting_DB.dbo.Altagt ON
Financial_Analysis.AxGage.[Policy Info Master].AGENT = Underwriting_DB.dbo.Altagt.Altcd
LEFT OUTER JOIN Underwriting_DB.dbo.Agent_Realigned ON
Underwriting_DB.dbo.Altagt.Agtcd = Underwriting_DB.dbo.Agent_Realigned.Agent)

select
CO,
Product,
SUM(EP2007Q2) as EP2007Q2,
SUM(EP2007Q3) as EP2007Q3,
SUM(EP2007Q4) as EP2007Q4,
SUM(EP2008Q1) as EP2008Q1,
SUM(EP2008Q2) as EP2008Q2,
SUM(EP2008Q3) as EP2008Q3,
SUM(EP2008Q4) as EP2008Q4,
SUM(EP2009Q1) as EP2009Q1,
SUM(EP2009Q2) as EP2009Q2,
SUM(EP2009Q3) as EP2009Q3,
SUM(EP2009Q4) as EP2009Q4,
SUM(EP2010Q1) as EP2010Q1,
SUM(EP2010Q2) as EP2010Q2,
SUM(EP2010Q3) as EP2010Q3,
SUM(EP2010Q4) as EP2010Q4,
IssYr,
ISST,
AGENT,
Status
FROM query1

GROUP BY
CO,
Product,
IssYr,
ISST,
AGENT,
Status

I probably could have just summed the case statements and not had to do a nested query, but whatever. It'll make people here more impressed by it if it's longer.

CrimsonFox · 06-02-2011, 07:02 PM

vote pass

Glengoyne · 06-03-2011, 12:47 PM

Quote:

Originally Posted by Passacaglia

Then:
..., but whatever. It'll make people here more impressed by it if it's longer.

That is exactly the opposite of how I evaluate code. When I see things like this it makes me grumble.

lordscarlet · 06-08-2011, 10:19 AM

Sorry, I got swamped.. Looks like you found your solution, but I'll try to take a look at it later today.

Passacaglia · 06-10-2011, 09:58 AM

Quote:

Originally Posted by Glengoyne

That is exactly the opposite of how I evaluate code. When I see things like this it makes me grumble.

Normally, I'd agree. Although, the people who are going to use it know even less SQL than I do. So they'll just open the file, change the dates, and run it, with only a passing glance at the rest of the code. But hey, I came here asking you guys for help on how to make it more efficient, so it's not like I'm intentionally making it longer!

Passacaglia · 06-10-2011, 10:09 AM

Quote:

Originally Posted by lordscarlet

Sorry, I got swamped.. Looks like you found your solution, but I'll try to take a look at it later today.

Thanks! Yeah, I'm not worried too much about changing the variables anymore, since someone who does know more about this stuff is going to make it into a function. But as I'm improving this, I have some more questions. Here's my code now:

Quote:

declare @B as date
declare @E as date
declare @BY as date
declare @EY as date
set @B = CONVERT(datetime,'2010-04-01')
set @E = CONVERT(datetime,'2010-06-30')
set @BY = DATEADD(yy, DATEDIFF(yy,0,@B), 0)
set @EY = DATEADD(dd,-1,DATEADD(yy,0,DATEADD(yy,DATEDIFF(yy,0,@B)+1,0)));

with query1 as (select
Policy_Number,
LEFT(Plan_Number_DLM,3) as DLM,
Issue_date,
Paid_to_date,
CASE Mode
WHEN '1' THEN 1*(CAST(Modal_Premium AS DECIMAL(9,2)))
WHEN '2' THEN 2*(CAST(Modal_Premium AS DECIMAL(9,2)))
WHEN '3' THEN 4*(CAST(Modal_Premium AS DECIMAL(9,2)))
WHEN '4' THEN 12*(CAST(Modal_Premium AS DECIMAL(9,2)))
WHEN '5' THEN 13*(CAST(Modal_Premium AS DECIMAL(9,2)))
WHEN 'B' THEN 26*(CAST(Modal_Premium AS DECIMAL(9,2)))
WHEN 'W' THEN 52*(CAST(Modal_Premium AS DECIMAL(9,2)))
ELSE (CAST(Modal_Premium AS DECIMAL(9,2)))
END AS Annualized_Premium,
convert(date,
((case when substring(issue_date,1,2) in ('19','20') and
substring(issue_date,3,1) in ('0','1','2','3','4','5','6','7','8','9') and
substring(issue_date,4,1) in ('0','1','2','3','4','5','6','7','8','9') then substring(issue_date,1,4) else '1900' end)+
(case when substring(issue_date,5,2) in ('01','02','03','04','05','06','07','08','09','10','11','12')
then substring(issue_date,5,2) else '01' end)+
(case when substring(issue_date,7,1) in ('0','1','2') and
substring(issue_date,8,1) in ('0','1','2','3','4','5','6','7','8','9') and
substring(issue_date,7,2) not in ('00','29') then substring(issue_date,7,2) else '28' end))) as I,
convert(date,
((case when substring(paid_to_date,1,2) in ('19','20') and
substring(paid_to_date,3,1) in ('0','1','2','3','4','5','6','7','8','9') and
substring(paid_to_date,4,1) in ('0','1','2','3','4','5','6','7','8','9') then substring(paid_to_date,1,4) else '1900' end)+
(case when substring(paid_to_date,5,2) in ('01','02','03','04','05','06','07','08','09','10','11','12')
then substring(paid_to_date,5,2) else '01' end)+
(case when substring(paid_to_date,7,1) in ('0','1','2') and
substring(paid_to_date,8,1) in ('0','1','2','3','4','5','6','7','8','9') and
substring(paid_to_date,7,2) not in ('00','29') then substring(paid_to_date,7,2) else '28' end))) as P
From NA.dbo.Traditional_Active
Where(LEFT(Plan_Number_DLM,3) = '930')

UNION ALL
select
Policy_Number,
LEFT(Plan_Number_DLM,3) as DLM,
Issue_date,
Paid_to_date,
CASE Mode
WHEN '1' THEN 1*(CAST(Modal_Premium AS DECIMAL(9,2)))
WHEN '2' THEN 2*(CAST(Modal_Premium AS DECIMAL(9,2)))
WHEN '3' THEN 4*(CAST(Modal_Premium AS DECIMAL(9,2)))
WHEN '4' THEN 12*(CAST(Modal_Premium AS DECIMAL(9,2)))
WHEN '5' THEN 13*(CAST(Modal_Premium AS DECIMAL(9,2)))
WHEN 'B' THEN 26*(CAST(Modal_Premium AS DECIMAL(9,2)))
WHEN 'W' THEN 52*(CAST(Modal_Premium AS DECIMAL(9,2)))
ELSE (CAST(Modal_Premium AS DECIMAL(9,2)))
END AS Annualized_Premium,
convert(date,
((case when substring(issue_date,1,2) in ('19','20') and
substring(issue_date,3,1) in ('0','1','2','3','4','5','6','7','8','9') and
substring(issue_date,4,1) in ('0','1','2','3','4','5','6','7','8','9') then substring(issue_date,1,4) else '1900' end)+
(case when substring(issue_date,5,2) in ('01','02','03','04','05','06','07','08','09','10','11','12')
then substring(issue_date,5,2) else '01' end)+
(case when substring(issue_date,7,1) in ('0','1','2') and
substring(issue_date,8,1) in ('0','1','2','3','4','5','6','7','8','9') and
substring(issue_date,7,2) not in ('00','29') then substring(issue_date,7,2) else '28' end))) as I,
convert(date,
((case when substring(paid_to_date,1,2) in ('19','20') and
substring(paid_to_date,3,1) in ('0','1','2','3','4','5','6','7','8','9') and
substring(paid_to_date,4,1) in ('0','1','2','3','4','5','6','7','8','9') then substring(paid_to_date,1,4) else '1900' end)+
(case when substring(paid_to_date,5,2) in ('01','02','03','04','05','06','07','08','09','10','11','12')
then substring(paid_to_date,5,2) else '01' end)+
(case when substring(paid_to_date,7,1) in ('0','1','2') and
substring(paid_to_date,8,1) in ('0','1','2','3','4','5','6','7','8','9') and
substring(paid_to_date,7,2) not in ('00','29') then substring(paid_to_date,7,2) else '28' end))) as P
From NA.dbo.Traditional_Rejected
Where(LEFT(Plan_Number_DLM,3) = '930'))

select
Policy_Number,
DLM,
Issue_date,
Paid_to_date,
Annualized_Premium,
I,
P,
case
when (P < @B) then '0'
when (I > @E) then '0'
when ((I <= @B) and (P >= @E)) then 1*(cast(DATEDIFF(DAY, @B, @E) as float)+1)/(cast(DATEDIFF(DAY, @BY, @EY) as float)+1)
when ((I > @B) and(P >= @E))
then 1*(cast(DATEDIFF(DAY, I, @E)+1 as float))/(cast(DATEDIFF(DAY, @BY, @EY) as float)+1)
when ((I <= @B) and (P < @E))
then 1*(cast(DATEDIFF(DAY, @B, P)+1 as float))/(cast(DATEDIFF(day, @BY, @EY) as float)+1)
when ((I > @B) and (P < @E))
then 1*(cast(DATEDIFF(DAY, I, P)+1 as float))/(cast(DATEDIFF(DAY, @BY, @EY) as float)+1)
end as Exposure,
case
when (P < @B) then '0'
when (I > @E) then '0'
when ((I <= @B) and (P >= @E)) then Annualized_Premium*(cast(DATEDIFF(DAY, @B, @E) as float)+1)/(cast(DATEDIFF(DAY, @BY, @EY) as float)+1)
when ((I > @B) and(P >= @E))
then Annualized_Premium*(cast(DATEDIFF(DAY, I, @E)+1 as float))/(cast(DATEDIFF(DAY, @BY, @EY) as float)+1)
when ((I <= @B) and (P < @E))
then Annualized_Premium*(cast(DATEDIFF(DAY, @B, P)+1 as float))/(cast(DATEDIFF(day, @BY, @EY) as float)+1)
when ((I > @B) and (P < @E))
then Annualized_Premium*(cast(DATEDIFF(DAY, I, P)+1 as float))/(cast(DATEDIFF(DAY, @BY, @EY) as float)+1)
end as EarnedPremium
FROM query1

1. So the first query is selecting the same fields from two different tables -- From NA.dbo.Traditional_Active and From NA.dbo.Traditional_Rejected. But in both cases, I'm restricting which rows is takes (the left 3 digits of Plan_Number_DLM are 930, in this case). What I want is for people to be able to easily type in what restrictions they want to put on the data, but I'd like them to be able to do it in one spot (preferably high up near where the dates are set) instead of having to remember to do it in both places. Is this possible?
2. I have this as a nested query because I'm defining "I" and "P" in the first query, then using them in the 2nd query, and that's the only way I know how to do it. Is it possible to use "I" and "P" in my case/when statements that define Exposure and EarnedPremium, without having to make a separate query? Along the same lines, EarnedPremium could be more easily defined as Exposure * Annualized_Premium, but it seemed easier to just recalculate it then to create another query that can use Exposure.

Thanks again!

06-08-2011, 10:19 AM	#20
lordscarlet Pro Starter Join Date: Oct 2005 Location: Washington, DC	Sorry, I got swamped.. Looks like you found your solution, but I'll try to take a look at it later today. __________________ Sixteen Colors ANSI/ASCII Art Archive "...the better half of the Moores..." -cthomer5000

05-31-2011, 10:19 AM	#2
bhlloy Coordinator Join Date: Nov 2003	What is the DBMS you are using?

05-31-2011, 10:20 AM	#3
Passacaglia Coordinator Join Date: Oct 2000 Location: Big Ten Country	It's called 'huh?'

05-31-2011, 10:21 AM	#4
bhlloy Coordinator Join Date: Nov 2003	MS/SQL, MySql, Oracle etc.... It's very doable in PL/SQL if you are running Oracle, otherwise I'm not sure

05-31-2011, 10:23 AM	#5
Passacaglia Coordinator Join Date: Oct 2000 Location: Big Ten Country	Oh, I think I can answer that! I use Microsoft SQL Server Management Studio.

05-31-2011, 10:24 AM	#7
bhlloy Coordinator Join Date: Nov 2003	ah... then that's a good thing (I think) but not in my area of expertize... hopefully somebody can help you out

06-01-2011, 01:53 PM	#11
bhlloy Coordinator Join Date: Nov 2003	Why do you need the group by in there? Your CASE WHEN statements aren't aggregate functions.

06-01-2011, 03:09 PM	#12
Passacaglia Coordinator Join Date: Oct 2000 Location: Big Ten Country	Well I want to group by those fields. So for each combination of those 6 fields, I want the sum of the EP in each quarter.

06-01-2011, 03:13 PM	#13
bhlloy Coordinator Join Date: Nov 2003	In that case, you'd need the SUM() in there around your case statements I would have thought. CASE WHEN is not an aggregate function, so that's why it's giving you the error. Disclaimer that this isn't my first language... so I may be off base.

06-01-2011, 03:14 PM	#14
Passacaglia Coordinator Join Date: Oct 2000 Location: Big Ten Country	Yeah, I was just realizing that, too.

06-01-2011, 03:22 PM	#15
Passacaglia Coordinator Join Date: Oct 2000 Location: Big Ten Country	Okay, I think I have what I want. I have to change all those I's and P's back to the full length name, I think, which I'll do tomorrow. But I think then it will work.

06-02-2011, 07:02 PM	#18
CrimsonFox Head Coach Join Date: Dec 2009	vote pass

Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)