Hitachi Vantara Pentaho Community Forums
Results 1 to 2 of 2

Thread: how to find fiscalquarter based on the start_date using mysql query

  1. #1
    Join Date
    Jul 2012
    Posts
    200

    Default how to find fiscalquarter based on the start_date using mysql query

    Hi,

    How to find out the fiscal quarter of the day based on the start_date suppose in my example After writing this query my resultset will be like below


    SELECT
    MIN(START_DATE) AS start_date, MONTH(MIN(start_date)) AS p FROM xyz GROUP BY YEAR(START_DATE)

    Code:
    start_date p 
    2001-01-01 1
    2002-01-01 1
    2003-01-01 1
    2004-01-01 1
    2005-01-01 1
    2006-01-01 1
    2007-01-01 1
    2013-04-01 4
    2018-02-01 2
    2019-01-01 1

    For each year the minimum value of date and month is shown like above. From this result I should calculate the quarternumber of year i.e whichever the minimum start_date as displayed it has to become as quarter1.Suppose my minimum date for year 2013 is '2013-04-01' then Quarter has to show it as 1 (4,5,6-Q1,7,8,9-Q2,10-11-12-Q3,1,2,3-Q4) and if my minimum start date for the year 2018 is '2018-02-01' then the quarter has to show it like this(2,3,4-Q1,5,6,7-Q2,8,9,10-Q3,11,12,1-Q4)
    Based on this above date now my sample output look like which is attached in below sheet
    How to do in this query .. help me to resolve this issue. I am not able to get any logic how to design the query based on the above scenario...
    My sample input and output file which is in database it is attached below.

    Attached Files Attached Files
    Last edited by yvkumar; 06-24-2013 at 11:15 PM.

  2. #2
    Join Date
    Apr 2008
    Posts
    4,696

    Default

    Why does it have to be in the MySQL query?

    You could do this with a value mapper step.

    In your query, extract the month number from the date, and then map that value to a quarter.
    If you need this to be field-programmable, then you could use a text file and a stream lookup.
    **THIS IS A SIGNATURE - IT GETS POSTED ON (ALMOST) EVERY POST**
    I'm no expert.
    Take my comments at your own risk.

    PDI user since PDI 3.1
    PDI on Windows 7 & Linux

    Please keep in mind (and this may not apply to this thread):
    No forum member is going to do your work for you. We will help you sort out how to do a specific part of the work, as best we can, in the timelines that our work will allow us.
    Signature Updated: 2014-06-30

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.