Should you work for a public company, it is very likely that the fiscal year of your company may not be the same as the calendar year.

This in turn, means, that a bunch of your analyses may need to forecast financials for the fiscal quarters, rather than the calendar ones.

*How could you then calculate the number of remaining days until the end of the fiscal quarter?*

It follows pretty much the same logic as calculating the number of remaining days until the end of a calendar quarter

The only noticeable difference is that you can no longer use the *quarter* function

The work around is to base the calculation logic on months.

Good news is that all **Vertica**, **MySQL** and **SQL Server** offer simplified calculations for getting the month from a date

So, let’s assume fiscal year runs from November to Ocotber.

*Calculations will then look like these:*

----------------------------------------Vertica---------------------------------------------------------------------------------- | |

---calculate remaining days in fiscal quarter | |

select | |

case | |

when Month(Now()) in (11,12,1) then 'FQ1' | |

when Month(Now()) in (2,3,4) then 'FQ2' | |

when Month(Now()) in (5,6,7) then 'FQ3' | |

when Month(Now()) in (8,9,10) then 'FQ4' | |

end as CurrentFiscalQuarter, | |

case | |

when Month(Now()) in (11,12,1) Then datediff('dd', Date(Now()), to_date(Concat(Year(Now()),'-01-31'), 'YYYY-MM-DD')) | |

when Month(Now()) in (2,3,4) Then datediff('dd', Date(Now()), to_date(Concat(Year(Now()),'-04-30'), 'YYYY-MM-DD')) | |

when Month(Now()) in (5,6,7) Then datediff('dd', Date(Now()), to_date(Concat(Year(Now()),'-07-31'), 'YYYY-MM-DD')) | |

when Month(Now()) in (8,9,10) Then datediff('dd', Date(Now()), to_date(Concat(Year(Now()),'-10-31'), 'YYYY-MM-DD')) | |

end as RemainingDaysInQuarter | |

--------------------------------SQL Server-------------------------------------------------------------------------------------- | |

select | |

case | |

when Month(getdate()) in (11,12,1) then 'FQ1' | |

when Month(getdate()) in (2,3,4) then 'FQ2' | |

when Month(getdate()) in (5,6,7) then 'FQ3' | |

when Month(getdate()) in (8,9,10) then 'FQ4' | |

end as CurrentFiscalQuarter, | |

case | |

when Month(getdate()) in (11,12,1) | |

then datediff(day, getdate(), convert(date, Concat(Year(getdate())+1,'-01-31')) ) | |

when Month(getdate()) in (2,3,4) | |

then datediff(day, getdate(), convert(date, Concat(Year(getdate()),'-04-30')) ) | |

when Month(getdate()) in (5,6,7) | |

then datediff(day, getdate(), convert(date, Concat(Year(getdate()),'-07-31')) ) | |

when Month(getdate()) in (8,9,10) | |

then datediff(day, getdate(), convert(date, Concat(Year(getdate()),'-10-31')) ) | |

--------------------------------MySQL-------------------------------------------------------------------------------------- | |

select | |

case | |

when Month(Now()) in (11,12,1) then 'FQ1' | |

when Month(Now()) in (2,3,4) then 'FQ2' | |

when Month(Now()) in (5,6,7) then 'FQ3' | |

when Month(Now()) in (8,9,10) then 'FQ4' | |

end as CurrentFiscalQuarter, | |

case | |

when Month(Now()) in (11,12,1) | |

then timestampdiff(day, Now(), str_to_date(Concat(Year(Now())+1,'-01-31'), '%Y-%m-%d') ) | |

when Month(Now()) in (2,3,4) | |

then timestampdiff(day, Now(), str_to_date(Concat(Year(Now()),'-04-30'), '%Y-%m-%d') ) | |

when Month(Now()) in (5,6,7) | |

then timestampdiff(day, Now(), str_to_date(Concat(Year(Now()),'-07-31'), '%Y-%m-%d') ) | |

when Month(Now()) in (8,9,10) | |

then timestampdiff(day, Now(), str_to_date(Concat(Year(Now()),'-10-31'), '%Y-%m-%d') ) | |

end as Remaining_in_Fis_Quarter | |

--------------------------------PostgreSQL-------------------------------------------------------------------------------------- | |

select | |

case | |

when date_part('month', Now()) in (11,12,1) then 'FQ1' | |

when date_part('month', Now()) in (2,3,4) then 'FQ2' | |

when date_part('month', Now()) in (5,6,7) then 'FQ3' | |

when date_part('month', Now()) in (8,9,10) then 'FQ4' | |

end as CurrentFiscalQuarter, | |

case | |

when date_part('month', Now()) in (11,12,1) | |

then date_part('day', to_date(date_part('year', current_date)+1 || '-01-31', 'YYYY-MM-DD') - Now() ) + 1 | |

when date_part('month', Now()) in (2,3,4) | |

then date_part('day', to_date(date_part('year', current_date) || '-04-30', 'YYYY-MM-DD') - NoW() ) + 1 | |

when date_part('month', Now()) in (5,6,7) | |

then date_part('day', to_date(date_part('year', current_date) || '-07-31', 'YYYY-MM-DD') - Now() ) + 1 | |

when date_part('month', Now()) in (8,9,10) | |

then date_part('day', to_date(date_part('year', current_date) || '-10-31', 'YYYY-MM-DD') - Now() ) + 1 | |

end as Remaining_In_Fis_Quarter |

Happy SQL-ing!