Topic
  • 1 reply
  • Latest Post - ‏2013-10-29T12:51:09Z by DPotterSQL
LouLou
LouLou
2 Posts

Pinned topic Query Calculation changing time periods into days

‏2013-10-24T13:57:27Z |

I have a field "Time Period" , the options are "Months", "Weeks", "Days", Hours.  In another column is a number for instance if it says "Month" and the "number column" indicates 6, I want to change the 6 months to days.  I would use the average number of days per month as 30.4375.  

I need the syntax for the calculation:  If [Time Period] = "Month" then [Number Field] *30.4375

If [Time Period] = "Week" then [Number Field] *7

If [Time Period] = "Day" then [Number Field] * 1

If [Time Period] = "hours" then [Number Field]  * 1/24

 

I just need a clean calculation that has the proper syntax.

Instead of "IF" a "Case" statement is preferred.

 

Thanks for any help you can provide.

  • DPotterSQL
    DPotterSQL
    60 Posts

    Re: Query Calculation changing time periods into days

    ‏2013-10-29T12:51:09Z  

    If it is only the syntax for a case statement you need then you can find it below.

    CASE [Time Period] 
    WHEN 'Month' THEN 
     [Number Field] *30.4375 
    WHEN 'Week' THEN [Number Field] *7 
    WHEN 'Day' THEN [Number Field] * 1 
    WHEN 'Hours' THEN 
    [Number Field]  * (1/24) 
    END