1 Reply Latest reply: Sep 23, 2011 2:54 PM by Dave Hewlett RSS

    Specify Quarter dates

      Hi everyone

       

      This is my first post as am a little stuck on a date issue.

       

      I have a scenario where there are two types of clients and quarterly valutions are done as per dates below

       

      Days per quarter:

      Quarterly:

      Q1 – Ending 31/03/yyyy – 90

      Q2 – Ending 30/06/yyyy – 91

      Q3 – Ending 30/09/yyyy – 92

      Q4 – Ending 31/12/yyyy - 92

       

      Quartertax

      Q1 – Ending 05/04/yyyy – 95

      Q2 – Ending 30/06/yyyy – 86

      Q3 – Ending 30/09/yyyy – 92

      Q4 – Ending 31/12/yyyy - 92

       

      I am unable to figure out a function whereby I can specify the dates in each quarter for past years and futureproofing so that I can assign the correct quarter to the dates within that quarter.

       

       

      Any help would be greatly appreciated.

       

      Many Thanks

      Sye

        • Specify Quarter dates

          I would recommend creating a date table that includes quarters. Pre-creating your date data will have all kinds of benefits not the least of which include simplified development and better performance. Here's a snippet of code that I would use to pre-prepare quarters:

           

          select

          //include all your other date attributes like month, week, day of year, year, etc...

          [QuarterID] = convert(INT,convert(varchar(4),datepart(YYYY,getdate()))+convert(varchar(2),datepart(QQ,getdate()))) ,

          [Quarter_YYYY-QQ] = convert(varchar(4),datepart(YYYY,getdate()))+'-'+'Q'+convert(varchar(2),datepart(QQ,getdate())) ,

          [Quarter_YYYY-Q] = convert(varchar(4),datepart(YYYY,getdate()))+'-'+convert(varchar(2),datepart(QQ,getdate())) ,

          [Quarter_QQ-YYYY] = 'Q'+convert(varchar(2),datepart(QQ,getdate()))+'-'+convert(varchar(4),datepart(YYYY,getdate())),

          [Quarter_Q-YYYY] = convert(varchar(2),datepart(QQ,getdate()))+'-'+convert(varchar(4),datepart(YYYY,getdate())) ,

          [Quarter_QQ] = 'Q'+convert(varchar(2),datepart(QQ,getdate())) ,

          [Quarter_Q] = convert(int,datepart(QQ,getdate()))

           

          Instead of getDate() you would want to create a script that would loop through days from "startdate" to "finishdate" to pre-build a calendar. However, the above code would work for current day as-is.

           

          On a related note, you may also want to add offsets (current Q is 0, last Q is -1, prior is -2, etc) to your date table so you can easily do QTD and Current vs Previous evaluations.