none
Fiscal Year 10/1 - 9/30 RRS feed

  • Question

  • Hello everyone, Thank you all for the assistance. I hate to keep posting, but I did figure out how to calculate the Fiscal Year, I isolated the month and year and did an iff statement to get the fiscal year. Now I just want to be able to see the data from the current fiscal year only. Below is my SQL.

    SELECT DISTINCT Training.RANK, Training.[LAST NAME], Training.[F NAME], Training.[Current Rifle Qual Class Cd], Training.[Current Rifle Qual Dt], Training.[Rifle Exception Code], Training.[DOD ID], CDate(Nz([Current Rifle Qual Dt],"0")) AS [Date], Year([Date]) AS CalendarYear, Month([Date]) AS CalendarMonth, IIf([CalendarMonth]>=10,[CalendarYear]+1,[CalendarYear]) AS [Fiscal Year] FROM Training ORDER BY Training.[Current Rifle Qual Dt]; 


    LDW

    Wednesday, September 2, 2015 4:15 PM

Answers

All replies

  • Add where:

    SELECT DISTINCT 
    	training.rank, 
        training.[last name], 
        training.[f name], 
        training.[current rifle qual class cd], 
        training.[current rifle qual dt], 
        training.[rifle exception code], 
        training.[dod id], 
        Cdate(Nz([current rifle qual dt],"0")) AS [Date], 
        Year([date])                           AS CalendarYear, 
        Month([date])                          AS CalendarMonth, 
        year([date]) + Iif([calendarmonth]>=10,1,0) AS [Fiscal Year] 
    FROM            
    	training 
    WHERE
    	 year([date]) + Iif([calendarmonth]>=10,1,0) = 2015
    ORDER BY        
    	training.[current rifle qual dt];

    But I thinkt better way is add table fiscalYears:

    fiscalYear, start_date,end_date,iscurrent

    2015,10/01/2014,9/30/2015,1

    2016,10/01/2016,9/30/2016,0

    and in query:

    SELECT DISTINCT 
    	t.rank, 
        t.[last name], 
        t.[f name], 
        t.[current rifle qual class cd], 
        t.[current rifle qual dt], 
        t.[rifle exception code], 
        t.[dod id], 
        Cdate(Nz(t.[current rifle qual dt],"0")) AS [Date], 
        t.fiscalYear AS [Fiscal Year] 
    FROM            
    	training as t
    	inner join fiscalYears as y on Cdate(Nz(t.[current rifle qual dt],"0")) between y.start_date and y.end_date
    WHERE
    	 isCurrent=1
    ORDER BY        
    	t.[current rifle qual dt];


    Michał

    Wednesday, September 2, 2015 5:02 PM
  • I am trying to have it automatically populate the current fiscal year without having to enter a date.  = 2015 I will have to modify it when I hit 10/1/2015 because that is the start of FY 2016. 

    LDW

    Wednesday, September 2, 2015 5:27 PM
  • Use Date() function:

    WHERE
    	 (year([date]) + Iif([calendarmonth]>=10,1,0)) = (year(Date()) + IIF(month(Date())>=10,1,0))


    Michał

    Wednesday, September 2, 2015 6:06 PM
  • Below is the sql based off your recommendation Michal, however, it didn't work. Did I do something wrong?

    SELECT DISTINCT Training.RANK, Training.[LAST NAME], Training.[F NAME], Training.[Current Rifle Qual Class Cd], Training.[Current Rifle Qual Dt], Training.[Rifle Exception Code], Training.[DOD ID], CDate(Nz([Current Rifle Qual Dt],"0")) AS [Date], Year([Date]) AS CalendarYear, Month([Date]) AS CalendarMonth, IIf([CalendarMonth]>=10,[CalendarYear]+1,[CalendarYear]) AS [Fiscal Year] FROM Training WHERE (year([date]) + Iif([calendarmonth]>=10,1,0)) = (year(Date()) + IIF(month(Date())>=10,1,0)) ORDER BY Training.[Current Rifle Qual Dt];


    LDW

    Wednesday, September 2, 2015 6:36 PM
  • You cannot use column aliases in where and select:

    SELECT DISTINCT 
    	training.rank, 
        training.[last name], 
        training.[f name], 
        training.[current rifle qual class cd], 
        training.[current rifle qual dt], 
        training.[rifle exception code], 
        training.[dod id], 
        Cdate(Nz([current rifle qual dt],"0")) AS [Date], 
        Year(Cdate(Nz([current rifle qual dt],"0"))) AS CalendarYear, 
        Month(Cdate(Nz([current rifle qual dt],"0"))) AS CalendarMonth, 
        year(Cdate(Nz([current rifle qual dt],"0"))) + Iif(Month(Cdate(Nz([current rifle qual dt],"0")))>=10,1,0) AS [Fiscal Year] 
    FROM            
        training 
    WHERE
        (year(Cdate(Nz([current rifle qual dt],"0"))) + Iif(Month(Cdate(Nz([current rifle qual dt],"0")))>=10,1,0)) = (year(Date()) + IIF(month(Date())>=10,1,0)) 
    ORDER BY        
    	training.[current rifle qual dt];


    Michał

    Wednesday, September 2, 2015 6:49 PM
  • I am getting an undefined expression error.

    SELECT DISTINCT Training.RANK, Training.[LAST NAME], Training.[F NAME], Training.[Current Rifle Qual Class Cd], Training.[Current Rifle Qual Dt], Training.[Rifle Exception Code], Training.[DOD ID], Cdate(Nz([current rifle qual dt],"0")) AS [Date], Year(Cdate(Nz([current rifle qual dt],"0"))) AS CalendarYear, Month(Cdate(Nz([current rifle qual dt],"0"))) AS CalendarMonth, year(Cdate(Nz([current rifle qual dt],"0"))) + Iif(Month(Cdate(Nz([current rifle qual dt],"0")))>=10,1,0) AS [Fiscal Year]

    FROM training

    WHERE  (year(Cdate(Nz([current rifle qual dt],"0"))) + Iif(Month(Cdate(Nz([current rifle qual dt],"0")))>=10,1,0)) = (year(Date()) + IIF(month(Date())>=10,1,0))

    ORDER BY training.[current rifle qual dt];


    LDW

    Wednesday, September 2, 2015 7:42 PM
  • LDW,

    Where is the undefined expression?  try:

    SELECT DISTINCT
    *
    FROM   training 
    WHERE   (Year(Cdate(Nz([current rifle qual dt], "0"))) 
                + Iif(Month(Cdate(Nz([current rifle qual dt], "0")))>=10, 1, 0)) = ( 
           Year(DATE()) + Iif(Month(DATE())>=10, 1, 0) ) 
    ORDER  BY training.[current rifle qual dt]; 


    Michał


    Wednesday, September 2, 2015 8:05 PM
  • I am still getting undefined expression.  If I take out the where statement it works

    LDW

    Wednesday, September 2, 2015 8:29 PM
  • Ok, try wich statment get error:

    #1:

    SELECT top 1
        Year(Cdate(Nz([current rifle qual dt], "0"))) 
                + Iif(Month(Cdate(Nz([current rifle qual dt], "0")))>=10, 1, 0)
    FROM   training 
    

    #2

    SELECT top 1
        Year(DATE()) + Iif(Month(DATE())>=10, 1, 0)
    FROM   training 


    Michał

    Wednesday, September 2, 2015 8:45 PM