Forum Discussion

bethf's avatar
bethf
Frequent Visitor
6 years ago
Solved

SQL Semi annual due date

Hello,  I need to calculate a semi annual date due date based on an issue date. 

I have a case statement for Annual and Quarterly but wondering if there is a statement that would calcualte semi annual?

 

case when ps.PaymentMode = 'Annual' then
   case when (DATEADD(year, datediff(year, ps.[IssueDate], getdate()), ps.IssueDate ) > getdate() )
   then dateadd(year, datediff(year, ps.issuedate, getdate()), ps.issuedate)
   else dateadd(year, datediff(year, ps.issuedate, getdate())+1, ps.issuedate) end
 
   Else
  case when ps.paymentmode = 'Quarterly' then
   case when (dateadd(quarter, datediff(quarter, ps.issuedate, getdate()), ps.issuedate) > getdate() )
   then dateadd(quarter, datediff(quarter, ps.issuedate, getdate()), ps.issuedate)
  else dateadd(quarter, datediff(quarter, ps.issuedate, getdate())+1, ps.issuedate)  end

   end
   end as 'Premium Due Date'

 

Thanks!

  • Hi  bethf , 

    It seems that you want to get result of last or next 6 months based on today, right? Ifso, you could try to use relative date slicer or use measure to achieve this goal. You could refer to my sample for details.

    Best Regards,
    Zoe Zhi

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

5 Replies

  • dax's avatar
    dax
    Community Support

    Hi bethf , 

    I am not sure your requirement, did you want to use powerbi to get the result or you just use SQL code to achieve this? If possible, could you please inform me more detailed information(such as your expected output and your sample data)? Then I will help you more correctly.

    Please do mask sensitive data before uploading.

    Thanks for your understanding and support.
    Best Regards,
    Zoe Zhi

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • bethf's avatar
      bethf
      Frequent Visitor

      So what I am trying to acheive is an semi annual due date. 

      For example if today is 6/24/2020 and the issue date was 1/1/2020 then the next semi annual date would be 1/1/2021, given that the first semi annual due date,6/1/2020, has already passed. Does that make sense?

      This would be in SQL that I would then pass to PBI

  • bethf ,The information you have provided is not making the problem clear to me. Can you please explain with an example.
    Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.
    Appreciate your Kudos.

    • bethf's avatar
      bethf
      Frequent Visitor

      So what I am trying to acheive is an semi annual due date. 

      For example if today is 6/24/2020 and the issue date was 1/1/2020 then the next semi annual date would be 1/1/2021, given that the first semi annual due date,6/1/2020, has already passed. Does that make sense?

      This would be in SQL that I would then pass to PBI.

       

       

       

      • dax's avatar
        dax
        Community Support

        Hi  bethf , 

        It seems that you want to get result of last or next 6 months based on today, right? Ifso, you could try to use relative date slicer or use measure to achieve this goal. You could refer to my sample for details.

        Best Regards,
        Zoe Zhi

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.