Front Office Football Central

Front Office Football Central (https://forums.operationsports.com/fofc//index.php)
-   Off Topic (https://forums.operationsports.com/fofc//forumdisplay.php?f=6)
-   -   SQL Help (https://forums.operationsports.com/fofc//showthread.php?t=81519)

Passacaglia 05-31-2011 09:06 AM
SQL Help
 
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 09:19 AM
What is the DBMS you are using?

Passacaglia 05-31-2011 09:20 AM
It's called 'huh?'

bhlloy 05-31-2011 09: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 09:23 AM
Oh, I think I can answer that! I use Microsoft SQL Server Management Studio.

Passacaglia 05-31-2011 09:23 AM
Obviously you're dealing with someone who knows very little about what he's doing here. :)

bhlloy 05-31-2011 09: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 11:17 AM
Quote:
Originally Posted by Passacaglia (Post 2476952)
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 12: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 12: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 12:53 PM
Why do you need the group by in there? Your CASE WHEN statements aren't aggregate functions.

Passacaglia 06-01-2011 02: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 02: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 02:14 PM
Yeah, I was just realizing that, too.

Passacaglia 06-01-2011 02: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 02: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 02: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. :p

CrimsonFox 06-02-2011 06:02 PM
vote pass ;)

Glengoyne 06-03-2011 11:47 AM
Quote:
Originally Posted by Passacaglia (Post 2478652)
Then:
..., but whatever. It'll make people here more impressed by it if it's longer. :p


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

lordscarlet 06-08-2011 09: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 08:58 AM
Quote:
Originally Posted by Glengoyne (Post 2479402)
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! :p

Passacaglia 06-10-2011 09:09 AM
Quote:
Originally Posted by lordscarlet (Post 2481850)
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!


All times are GMT -5. The time now is 05:07 PM.

Powered by vBulletin Version 3.6.0
Copyright ©2000 - 2026, Jelsoft Enterprises Ltd.