Front Office Football Central  

Go Back   Front Office Football Central > Main Forums > Off Topic
Reload this Page SQL Help
Register FAQ Members List Calendar Mark Forums Read Statistics

Reply
 
Thread Tools
Old 05-31-2011, 09:06 AM   #1
Passacaglia
Coordinator
 
Join Date: Oct 2000
Location: Big Ten Country
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!
Passacaglia is offline   Reply With Quote
Old 05-31-2011, 09:19 AM   #2
bhlloy
Coordinator
 
Join Date: Nov 2003
What is the DBMS you are using?
bhlloy is offline   Reply With Quote
Old 05-31-2011, 09:20 AM   #3
Passacaglia
Coordinator
 
Join Date: Oct 2000
Location: Big Ten Country
It's called 'huh?'
Passacaglia is offline   Reply With Quote
Old 05-31-2011, 09: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
bhlloy is offline   Reply With Quote
Old 05-31-2011, 09: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.
Passacaglia is offline   Reply With Quote
Old 05-31-2011, 09:23 AM   #6
Passacaglia
Coordinator
 
Join Date: Oct 2000
Location: Big Ten Country
Obviously you're dealing with someone who knows very little about what he's doing here.
Passacaglia is offline   Reply With Quote
Old 05-31-2011, 09: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
bhlloy is offline   Reply With Quote
Old 06-01-2011, 11:17 AM   #8
lordscarlet
Pro Starter
 
Join Date: Oct 2005
Location: Washington, DC
Quote:
Originally Posted by Passacaglia View Post
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.
__________________
Sixteen Colors ANSI/ASCII Art Archive

"...the better half of the Moores..." -cthomer5000
lordscarlet is offline   Reply With Quote
Old 06-01-2011, 12:31 PM   #9
Passacaglia
Coordinator
 
Join Date: Oct 2000
Location: Big Ten Country
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 is offline   Reply With Quote
Old 06-01-2011, 12:38 PM   #10
Passacaglia
Coordinator
 
Join Date: Oct 2000
Location: Big Ten Country
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.
Passacaglia is offline   Reply With Quote
Old 06-01-2011, 12: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.
bhlloy is offline   Reply With Quote
Old 06-01-2011, 02: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.
Passacaglia is offline   Reply With Quote
Old 06-01-2011, 02: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.
bhlloy is offline   Reply With Quote
Old 06-01-2011, 02:14 PM   #14
Passacaglia
Coordinator
 
Join Date: Oct 2000
Location: Big Ten Country
Yeah, I was just realizing that, too.
Passacaglia is offline   Reply With Quote
Old 06-01-2011, 02: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.
Passacaglia is offline   Reply With Quote
Old 06-02-2011, 02:08 PM   #16
Passacaglia
Coordinator
 
Join Date: Oct 2000
Location: Big Ten Country
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,
Last edited by Passacaglia : 06-02-2011 at 02:11 PM.
Passacaglia is offline   Reply With Quote
Old 06-02-2011, 02:13 PM   #17
Passacaglia
Coordinator
 
Join Date: Oct 2000
Location: Big Ten Country
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.
Passacaglia is offline   Reply With Quote
Old 06-02-2011, 06:02 PM   #18
CrimsonFox
Head Coach
 
Join Date: Dec 2009
vote pass
CrimsonFox is offline   Reply With Quote
Old 06-03-2011, 11:47 AM   #19
Glengoyne
Grizzled Veteran
 
Join Date: Sep 2003
Location: Fresno, CA
Quote:
Originally Posted by Passacaglia View Post
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.
Glengoyne is offline   Reply With Quote
Old 06-08-2011, 09: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
lordscarlet is offline   Reply With Quote
Old 06-10-2011, 08:58 AM   #21
Passacaglia
Coordinator
 
Join Date: Oct 2000
Location: Big Ten Country
Quote:
Originally Posted by Glengoyne View Post
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 is offline   Reply With Quote
Old 06-10-2011, 09:09 AM   #22
Passacaglia
Coordinator
 
Join Date: Oct 2000
Location: Big Ten Country
Quote:
Originally Posted by lordscarlet View Post
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!
Passacaglia is offline   Reply With Quote
Reply

« Previous Thread | Next Thread »

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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
vB code is On
Smilies are On
[IMG] code is On
HTML code is On
Forum Jump


All times are GMT -5. The time now is 04:54 PM.


Contact Us - Front Office Football Central - Archive - Top

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