locked
How can I populate current year ,if fisical year is null (for eg : fisical year 2018-june 2019) RRS feed

  • Question

  • Hi All,

    I have a fisical year column which will having null. when I populate data into that column I need to test the current financial year and populate current year.

    which means if fisical year is null need to to check for financial year and populate data as below.

    July 2018 - june 2019 then 2018

    july 2019 - june 2020 then 2019

    Thanks

    AVS

    Wednesday, October 31, 2018 4:57 PM

Answers

  • My Issue got solved below is the code.

    DECLARE @FinancialPeriod TABLE( 
     startdate date
    ,Enddate date  
    ,Period varchar(25) --= 'FY'--values FY,Q,M
    ); 
     declare @FiscalStartMonthNo int=7,
    @Period varchar(2) = 'FY'

    ;With CTE
    AS
    (
    SELECT DATEADD(mm,DATEDIFF(mm,0,GETDATE()) + @FiscalStartMonthNo - MONTH(GETDATE())-CASE WHEN @FiscalStartMonthNo > MONTH(GETDATE()) THEN 12 ELSE 0 END,0) AS StartDate
    )
    insert into @FinancialPeriod SELECT StartDate,DATEADD(yy,1,StartDate)-1 AS EndDate, + DATENAME(yyyy,DATEADD(yy,1,StartDate)-1) AS Period
    FROM CTE 

    • Marked as answer by avs sai Tuesday, November 6, 2018 4:33 PM
    Tuesday, November 6, 2018 4:33 PM

All replies

  • You should use a calendar table with a fiscal calendar then join your rows to the calendar table.

    Please see:

    https://www.codeproject.com/Articles/647950/Create-and-Populate-Date-Dimension-for-Data-Wareho

    Wednesday, October 31, 2018 5:19 PM
  • Hi AVS,

     

    According to your description, my understanding is that you want to populate data into financial year column.  If anything is misunderstood, please tell me.

     

    Please refer to the following code.

     

    update a set FisicalYear=(case when DATEPART(mm,a.CurrentDate)>=7 and DATEPART(mm,a.CurrentDate)<=12 
    then DATEPART(yy,a.CurrentDate)
    else DATEPART(yy,a.CurrentDate)-1 end)
    from Table_1 a 
    
    select * from Table_1
    
    /**Results
    CurrentDate FisicalYear
    ----------- --------------------------------------------------
    2018-03-01  2017
    2017-11-01  2017
    2019-05-01  2018
    2020-11-01  2020
    */

     

    Best Regards,

    Emily


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Thursday, November 1, 2018 10:10 AM
  • My Issue got solved below is the code.

    DECLARE @FinancialPeriod TABLE( 
     startdate date
    ,Enddate date  
    ,Period varchar(25) --= 'FY'--values FY,Q,M
    ); 
     declare @FiscalStartMonthNo int=7,
    @Period varchar(2) = 'FY'

    ;With CTE
    AS
    (
    SELECT DATEADD(mm,DATEDIFF(mm,0,GETDATE()) + @FiscalStartMonthNo - MONTH(GETDATE())-CASE WHEN @FiscalStartMonthNo > MONTH(GETDATE()) THEN 12 ELSE 0 END,0) AS StartDate
    )
    insert into @FinancialPeriod SELECT StartDate,DATEADD(yy,1,StartDate)-1 AS EndDate, + DATENAME(yyyy,DATEADD(yy,1,StartDate)-1) AS Period
    FROM CTE 

    • Marked as answer by avs sai Tuesday, November 6, 2018 4:33 PM
    Tuesday, November 6, 2018 4:33 PM