none
Age calculation in that Fiscal year

    질문

  • Hi Experts,

    How do I calculate age calculation for that fiscal year, my fiscal year starts from July 01 to next year June 30th?

    Example:

    If I have the date '2012-07-01' then my fiscal year will be 2013 till to 2013-06-30

    If its '2013-07-01' then my fiscal year will be 2014, till to 2014-06-30

    Here I need to pull the records who has age between 20 years & 0 days and 20 years & 364 days in that fiscal year?

    Thanks!


    Red

    2018년 7월 12일 목요일 오후 7:37

답변

  • How about this:

    DECLARE @fiscalYear int;
    SET @fiscalYear = 2012;
    DECLARE @startDateOfFiscalYear date;
    DECLARE @endDateOfFiscalYear date;
    
    SET @startDateOfFiscalYear = CAST(YEAR(@fiscalYear) AS char(4)) + '-07-01';
    SET @endDateOfFiscalYear = CAST(YEAR(@fiscalYear) + 1 AS char(4)) + '-06-30';
    
    SELECT * 
    FROM YourTable
    WHERE DATEADD(YEAR, 20, BirthDate) >= @startDateOfFiscalYear AND DATEADD(YEAR, 20, BirthDate) <= @endDateOfFiscalYear;


    A Fan of SSIS, SSRS and SSAS

    2018년 7월 13일 금요일 오후 8:28
  • To be precise I need pull all records who has age between 20 years & 0 days and 20 years & 364 days in time-period starting from '2012-07-01' to '2013-06-30'. The input will be the date of birth field of the person. Currently I'm using SQL Server 2008 and EOMONTH function doesn't work.

    I tried with below modified query but it didnt work:

    SELECT * FROM  Table
    WHERE (DATEADD(YEAR,20,birth_dtm)>= '2017-07-01' AND  DATEADD(YEAR,20,birth_dtm)<='2018-06-01'
    OR (DATEADD(DAY,364,DATEADD(YEAR,20,birth_dtm))>='2017-07-01' AND DATEADD(DAY,364,DATEADD(YEAR,20,birth_dtm))<='2018-06-01')
    )

    Thanks!


    Red

    But your date rang is not right, and the brackets are not in right place. Therefore, it should be like this.

    SELECT * FROM  Table
     WHERE (DATEADD(YEAR,20,birth_dtm)>= '2017-07-01' AND  DATEADD(YEAR,20,birth_dtm)<='2018-06-30')
     OR (DATEADD(DAY,364,DATEADD(YEAR,20,birth_dtm))>='2017-07-01' AND DATEADD(DAY,364,DATEADD(YEAR,20,birth_dtm))<='2018-06-30')
    --)

    Since the version of your SQL Server is SQL Server 2008, please try this query.

    DECLARE @V_DATE DATE='2012-7-1'
    
    SELECT [Field1 of person records],[Field2 of person records],[Field3 of person records],.... 
    FROM Your_table
    WHERE (DATEADD(YEAR,20,[date of birth field])>=@v_date AND  DATEADD(YEAR,20,[date of birth field])<=DATEADD(day,-1,DATEADD(month,12,@V_DATE))) 
    OR (DATEADD(DAY,364,DATEADD(YEAR,20,[date of birth field]))>=@v_date AND DATEADD(DAY,364,DATEADD(YEAR,20,[date of birth field]))<=DATEADD(day,-1,DATEADD(month,12,@V_DATE)))

    Best Regards,

    Will


    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.

    2018년 7월 16일 월요일 오전 9:17
    중재자

모든 응답

  • So what would be the user input? just a year?

    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    2018년 7월 12일 목요일 오후 7:45
  • Thanks for responding! The input will be the date of birth field and need to pull the person records who has age between 20 years & 0 days and 20 years & 364 days in that fiscal year.

    Thanks!


    Red

    2018년 7월 12일 목요일 오후 7:58
  • Declare @Date date='2012-7-02'
    Select Case when Month(@Date) >=7 then Year(@Date)+1 else Year(@date) end as FiscalYear

    mohammad waheed

    2018년 7월 12일 목요일 오후 9:02
  • Thanks for responding! The input will be the date of birth field and need to pull the person records who has age between 20 years & 0 days and 20 years & 364 days in that fiscal year.

    Thanks!


    Red

    Hi Red,

    You mean to want this?

    DECLARE @V_DATE DATE='2012-7-1'
    
    SELECT [Field1 of person records],[Field2 of person records],[Field3 of person records],.... 
    FROM Your_table
    WHERE (DATEADD(YEAR,20,[date of birth field])>=@v_date AND  DATEADD(YEAR,20,[date of birth field])<=EOMONTH(@v_date,11)) 
    OR (DATEADD(DAY,364,DATEADD(YEAR,20,[date of birth field]))>=@v_date AND DATEADD(DAY,364,DATEADD(YEAR,20,[date of birth field]))<=EOMONTH(@v_date,11))

    Best Regards,

    Will


    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.

    2018년 7월 13일 금요일 오전 5:21
    중재자
  • To be precise I need pull all records who has age between 20 years & 0 days and 20 years & 364 days in time-period starting from '2012-07-01' to '2013-06-30'. The input will be the date of birth field of the person. Currently I'm using SQL Server 2008 and EOMONTH function doesn't work.

    I tried with below modified query but it didnt work:

    SELECT * FROM  Table
    WHERE (DATEADD(YEAR,20,birth_dtm)>= '2017-07-01' AND  DATEADD(YEAR,20,birth_dtm)<='2018-06-01'
    OR (DATEADD(DAY,364,DATEADD(YEAR,20,birth_dtm))>='2017-07-01' AND DATEADD(DAY,364,DATEADD(YEAR,20,birth_dtm))<='2018-06-01')
    )

    Thanks!


    Red

    2018년 7월 13일 금요일 오후 1:07
  • DECLARE @dateOfToday date;
    DECLARE @startDateOfFiscalYear date;
    DECLARE @endDateOfFiscalYear date;
    
    SET @dateOfToday = GETDATE();
    SET @startDateOfFiscalYear = CASE WHEN MONTH(@dateOfToday) >= 7 THEN CAST(YEAR(@dateOfToday) AS char(4)) + '-07-01' ELSE CAST(YEAR(@dateOfToday) - 1 AS char(4)) + '-07-01' END;
    SET @endDateOfFiscalYear = CASE WHEN MONTH(@dateOfToday) >= 7 THEN CAST(YEAR(@dateOfToday) + 1 AS char(4)) + '-06-30' ELSE CAST(YEAR(@dateOfToday) AS char(4)) + '-06-30' END;
    --SELECT @dateOfToday, @startDateOfFiscalYear, @endDateOfFiscalYear;
    
    SELECT * 
    FROM YourTable
    WHERE DATEADD(YEAR, 20, BirthDate) >= @startDateOfFiscalYear AND DATEADD(YEAR, 20, BirthDate) <= @endDateOfFiscalYear;


    A Fan of SSIS, SSRS and SSAS

    2018년 7월 13일 금요일 오후 1:27
  • Thanks for your inputs. Some of the records were not pulled who has age between 20 years & 0 days and 20 years & 364 days in time-period starting from '2012-07-01' to '2013-06-30'. Only persons who have exact 20 years were pulled with this sql.

    DECLARE @dateOfToday date;
    DECLARE @startDateOfFiscalYear date;
    DECLARE @endDateOfFiscalYear date;

    SET @dateOfToday = GETDATE();
    SET @startDateOfFiscalYear = CASE WHEN MONTH(@dateOfToday) >= 7 THEN CAST(YEAR(@dateOfToday) AS char(4)) + '-07-01' ELSE CAST(YEAR(@dateOfToday) - 1 AS char(4)) + '-07-01' END;
    SET @endDateOfFiscalYear = CASE WHEN MONTH(@dateOfToday) >= 7 THEN CAST(YEAR(@dateOfToday) + 1 AS char(4)) + '-06-30' ELSE CAST(YEAR(@dateOfToday) AS char(4)) + '-06-30' END;
    --SELECT @dateOfToday, @startDateOfFiscalYear, @endDateOfFiscalYear;

    SELECT * FROM [dbo].[Field_ops_Children_Served]
    WHERE DATEADD(YEAR, 20, birth_dtm) >= @startDateOfFiscalYear AND DATEADD(YEAR, 20, birth_dtm) <= @endDateOfFiscalYear;


    Thanks!

    Red

    2018년 7월 13일 금요일 오후 6:15
  • If you want to to check the ages from 2012, you have to change the value of @dateOfToday to 2012. Maybe the variable name @dateOfTodayis not good.

    A Fan of SSIS, SSRS and SSAS

    2018년 7월 13일 금요일 오후 8:24
  • How about this:

    DECLARE @fiscalYear int;
    SET @fiscalYear = 2012;
    DECLARE @startDateOfFiscalYear date;
    DECLARE @endDateOfFiscalYear date;
    
    SET @startDateOfFiscalYear = CAST(YEAR(@fiscalYear) AS char(4)) + '-07-01';
    SET @endDateOfFiscalYear = CAST(YEAR(@fiscalYear) + 1 AS char(4)) + '-06-30';
    
    SELECT * 
    FROM YourTable
    WHERE DATEADD(YEAR, 20, BirthDate) >= @startDateOfFiscalYear AND DATEADD(YEAR, 20, BirthDate) <= @endDateOfFiscalYear;


    A Fan of SSIS, SSRS and SSAS

    2018년 7월 13일 금요일 오후 8:28
  • To be precise I need pull all records who has age between 20 years & 0 days and 20 years & 364 days in time-period starting from '2012-07-01' to '2013-06-30'. The input will be the date of birth field of the person. Currently I'm using SQL Server 2008 and EOMONTH function doesn't work.

    I tried with below modified query but it didnt work:

    SELECT * FROM  Table
    WHERE (DATEADD(YEAR,20,birth_dtm)>= '2017-07-01' AND  DATEADD(YEAR,20,birth_dtm)<='2018-06-01'
    OR (DATEADD(DAY,364,DATEADD(YEAR,20,birth_dtm))>='2017-07-01' AND DATEADD(DAY,364,DATEADD(YEAR,20,birth_dtm))<='2018-06-01')
    )

    Thanks!


    Red

    But your date rang is not right, and the brackets are not in right place. Therefore, it should be like this.

    SELECT * FROM  Table
     WHERE (DATEADD(YEAR,20,birth_dtm)>= '2017-07-01' AND  DATEADD(YEAR,20,birth_dtm)<='2018-06-30')
     OR (DATEADD(DAY,364,DATEADD(YEAR,20,birth_dtm))>='2017-07-01' AND DATEADD(DAY,364,DATEADD(YEAR,20,birth_dtm))<='2018-06-30')
    --)

    Since the version of your SQL Server is SQL Server 2008, please try this query.

    DECLARE @V_DATE DATE='2012-7-1'
    
    SELECT [Field1 of person records],[Field2 of person records],[Field3 of person records],.... 
    FROM Your_table
    WHERE (DATEADD(YEAR,20,[date of birth field])>=@v_date AND  DATEADD(YEAR,20,[date of birth field])<=DATEADD(day,-1,DATEADD(month,12,@V_DATE))) 
    OR (DATEADD(DAY,364,DATEADD(YEAR,20,[date of birth field]))>=@v_date AND DATEADD(DAY,364,DATEADD(YEAR,20,[date of birth field]))<=DATEADD(day,-1,DATEADD(month,12,@V_DATE)))

    Best Regards,

    Will


    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.

    2018년 7월 16일 월요일 오전 9:17
    중재자