Leap year difficulties... Crossing February 29, 20xx

I Work in the insurance world and have a problem dealing with Feb 29.. Not just a leap year but the actual date of feb29. My current formula says...
 
IF  (datediff('day',pol2exp,pol2eff)and<365) and (datediff('day',polteff,originalnewdate)and<365) THEN New ELSE Renewal END
 
SO the formula works 99.9% of the time. Here is what the formula is actually doing.  The first datediff looks at last years policy and says am I less than 365. A policy is typically exactly 365. For example, 1/1/2015 - 1/1/2016 or 3/1/2015 - 3/1/2016. A policy can start on any date and end on any date, but sometimes a policy is 6 months. The first 12 months of a policy is new and any transaction after 12 months is renewal.
 
Here is an example of the issue..  Feb 29, 2012 is a leap year date.
 
The first date is poleffdate and the second date is polexpdate
 
Policy A -  9/1/2011 - 3/1/2012 a six month policy
Policy B - 3/1/2012 - 9/1/2012 another six month policy.
 
Policy A passes the first test of being less than 365 days, but policy B will fail because I will bill the last day of that policy. I will issue the final bill on 9/1/2012 (polteff). Polteff is 366 days away from Policy A so it falls into the renewal category and s/b new.
 
Second example,
 
Policy A -  3/1/2012 - 9/1/2012 a six month policy
Policy B - 9/1/2012 - 3/1/2013 another six month policy.
 
Policy A passes the first test of being less than 365 days, and policy B will pass because it is less the 365 from one. I will issue the final bill on 9/1/2013 (polteff). Polteff is 365 days away from Policy A so it falls into the New category where it belongs.
 
It is the day of February 29th and not just the leap year. When both policies cross Feb 29, I'll get 366 on the second policy and it falls into renewal and not new.
 
SO I need something that works with the formula above where February 29th is between poleffdate and polexpdate of policy A. and  February 29th is between poleffdate and polexpdate of policy B. then the datediff('day',polteff,originalnewdate) can be 366
 
otherwise my original formula will work.
Michael FranzCFOAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PortletPaulfreelancerCommented:
Did you know that 90+% of the world's population don't use m/d/yyyy as their preferred date format? It's not a huge deal, but yyyy-mm-dd is a way easier format to use for a potentially international audience (like me).

OK. putting that aside: In both your examples, I presume they are for the same person. e.g,

sample 1
FRED Policy A -  2011-09-01 - 2012-03-01 a six month policy
FRED Policy B - 2012-03-01 - 2012-09-01 another six month policy.

sample2
FRED Policy A -  2012-03-01 - 2012-09-01 a six month policy
FRED Policy B - 2012-09-01 - 2013-03-01 another six month policy.

and I'm not seeing how those rows work into the formula that references:
pol2exp, pol2eff, polteff ,originalnewdate

Could you rework those examples into source table rows, using the column names of the tables.
0
PortletPaulfreelancerCommented:
perhaps this will assist. Note a CROSS APPLY is used so that the alias [days_in_year] can be reused as if it is a column
SELECT
        [pol2exp]
      , [pol2eff]
      , ca.days_in_year
      , datediff(day,pol2exp,pol2eff)
      , CASE WHEN datediff(day,pol2exp,pol2eff) < ca.days_in_year 
             THEN 'renew' 
             ELSE 'new' 
         END
FROM table1
CROSS APPLY (
      SELECT 
         CASE WHEN ISDATE(CAST(YEAR(pol2eff) AS char(4)) + '0229') = 1 
              THEN 366 
              ELSE 365 
         END
      ) ca (days_in_year)

Open in new window

Using some dummy data:
CREATE TABLE Table1
    ([blah] varchar(130), [pol2exp] datetime, [pol2eff] datetime, [blah2] varchar(250))
;
    
INSERT INTO Table1
    ([blah], [pol2exp], [pol2eff], [blah2])
VALUES
    ('FRED Policy A', '2011-09-01 00:00:00', '2012-03-01 00:00:00', 'a six month policy'),
    ('FRED Policy B', '2012-03-01 00:00:00', '2012-09-01 00:00:00', 'another six month policy.'),
    ('FRED Policy A', '2012-03-01 00:00:00', '2012-09-01 00:00:00', 'a six month policy'),
    ('FRED Policy B', '2012-09-01 00:00:00', '2013-03-01 00:00:00', 'another six month policy.')
;

Open in new window

I produced this result:
|                     pol2exp |                     pol2eff | days_in_year |     |       |
|-----------------------------|-----------------------------|--------------|-----|-------|
| September, 01 2011 00:00:00 |     March, 01 2012 00:00:00 |          366 | 182 | renew |
|     March, 01 2012 00:00:00 | September, 01 2012 00:00:00 |          366 | 184 | renew |
|     March, 01 2012 00:00:00 | September, 01 2012 00:00:00 |          366 | 184 | renew |
| September, 01 2012 00:00:00 |     March, 01 2013 00:00:00 |          365 | 181 | renew |

Open in new window

see: http://sqlfiddle.com/#!3/1c261/1
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Michael FranzCFOAuthor Commented:
Thank you for the help... Sorry for the late response. It took me a few trys but I think I got it.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.