0

How do I apply case function to a Leap Year that occurs every four years to a date formula?

I have this DATA ITEM 1 entitled INSURANCE TERM query in cognos that has a formula inside.

(12 * _days_between([Policy Expiration Date],[Policy Effective Date]))/365

Below is the property of the expression Policy Expiration Date in case you want to know. enter image description here

The formula is used to calculate the Insurance Term. 12 months multiplied to the number of days between [Policy Expiration Date],[Policy Effective Date] in the prompt below. Divided to 365 days as a regular year. enter image description here

How do I incorporate the case function between a regular year and a leap year? Something like this..

Case when Year([Policy Expiration Date]) in ('2013', '2014', '2015') then DATA ITEM 1 else DATE ITEM 2((12 * _days_between([Policy Expiration Date],[Policy Effective Date]))/366)

But I can't afford to write the in(2008-2032 leap year) because it would be too long. Is there a way to do this workaround?

BongReyes
  • 205
  • 2
  • 7
  • 19
  • 1
    I can't wrap my head around what you are trying to do but why not use modulo? case Year(PolicyExpirationDate) % 4 when 0 then DataItem2 else DataItem1 end. Or maybe even better would be to use DATEDIFF instead of that complicated formula but I have no idea what _days_between is. – Sean Lange Aug 13 '15 at 18:51
  • _days_between is a formula in cognos to get the number of days between the start date and end date. if the start date is January 1 and end date is February 28 so that's 31 days plus 28 days, assuming its not a Leap year. But if its a leap year then it should be 31 days from January + 29 days in February. While the Year([Policy Expiration Date]) is a function n cognos to get the year, let's say the user entered March 14, 2014, the use of Year([Policy Expiration Date]) is to get 2014 only. – BongReyes Aug 13 '15 at 19:00
  • 2
    Can you use DATEDIFF on the sql side? This will already handle leap years. I don't know anything about cognos so sorry if my comments don't make sense in relation to cognos. – Sean Lange Aug 13 '15 at 19:03
  • That's fine, Actually I appreciate your answer. Could you give me an example of using a leap year using DATEDIFF? I could apply it first in SQL server management studio then apply it in cognos afterwards. I just need to understand the logic. – BongReyes Aug 13 '15 at 19:06
  • Try this @BongReyes. CASE WHEN (mod(cast(Year([Policy Expiration Date]),integer),4) = 0) AND (mod(cast(Year([Policy Expiration Date]),integer),100) = 0) AND (mod(cast(Year([Policy Expiration Date]),integer),4) = 400) THEN DATA ITEM 1 ELSE DATE ITEM 2((12 * _days_between([Policy Expiration Date],[Policy Effective Date]))/366) END – rgstamayo Aug 14 '15 at 10:38
  • The gotcha there is that leap years only occur 97 times over 400 years, so 2100 is not a leap year, for example. Not likely to be a problem soon but should be noted in case SO is still around in 85 years and someone finds this. Yes, I'm an optimist. – chsh Aug 14 '15 at 11:46

1 Answers1

1

This is too long for a comment and likely incomplete as an answer.

Not quite sure I get the logic here. Do you have two date columns, one for begin and another for end?

Let's assume you want to know the difference of days between January 1st and March 1st. The way you are calculating this is challenging for leap years. But using datediff it handles this with no effort at all.

declare @StartDate date = '2012-01-01'
    , @EndDate date = '2012-03-01'

select DATEDIFF(day, @StartDate, @EndDate)

select @StartDate = '2015-01-01'
    , @EndDate = '2015-03-01'

select DATEDIFF(day, @StartDate, @EndDate)
Sean Lange
  • 33,028
  • 3
  • 25
  • 40
  • He wants to remove leap days from the calculation to normalize the metric. It's some sort of averaging calculation and he doesn't want the number to skew every four years. That's what I take away from it. – Johnsonium Aug 13 '15 at 21:18