Adding a sequential number between a fiscal year help 2

[prover]

Hi all,

I need a way to assign the next sequential (permit) number to an approved application. The trick is that every fiscal year the numbers restart at zero and the fiscal year is between 7/1/xx and 6/30/xx.

for example if an application was approved on 8/4/03 i would need to get the next permit number between 7/1/03 and 6/30/04.

Here is what i'm playing with:

@ApprDate datetime

DECLARE @LastNo INT
DECLARE @NewNo  INT

SELECT @LastNo = (SELECT MAX(PermitNo) FROM tblApplication WHERE ApprDate BETWEEN '7/1/xx' AND 6/30/xx')

SELECT @NewNo = (@LastNo + 1)

return @NewNo

The problem is getting the fiscal year start end end date range that the ApprDate falls into.

 

[donutman]

Try this:

Create Procedure spNextPermit (@ApprDate datetime)
AS
Declare @ApprDate datetime, @BOY datetime, @EOY datetime
Declare @NewNo  INT
Set @BOY=cast('07/01/'+STR(YEAR(@ApprDate)-1) as datetime)
Set @EOY=cast('06/30/'+STR(YEAR(@ApprDate)) as datetime)
IF MONTH(@ApprDate)>6
   Begin 
      Set @BOY=DATEADD(yy,1,@BOY)
      Set @EOY=DATEADD(yy,1,@EOY)
   End

SELECT @NewNo = (SELECT ISNULL(MAX(PermitNo),0)+1
   FROM tblApplication WHERE ApprDate BETWEEN @BOY AND @EOY)
return @NewNo

-Karl

 

[donutman]

Oops. Take out the @ApprDate in the Declare statement.
-Karl

 

[TJRTech]

Don't forget a clusted index on tblApplication either on PermitNo, or ApprDate column (might wish to test both to see which one provides better performance).

Regards,
TR

 

[prover]

I came up with something similar myself:

DECLARE @retVal int
	DECLARE @FYStart datetime
	DECLARE @FYEnd datetime
	DECLARE @NewNo int
	
	SET @FYEnd = cast('7/1/' + STR(YEAR(@Date) + MONTH(@Date) / 7) as datetime)
	SET @FYStart = cast('7/1/' + STR(YEAR(@FYEnd) - 1) as datetime)
	
	--if nothing is returned we'll assume this is the 
	--first permit of the fiscal year. so we return 1.
	SELECT @retVal = isnull((SELECT MIN(PermitNo) FROM tblApplication WHERE ApprDate < @FYEnd AND ApprDate >= @FYStart),0)
	SELECT @NewNo = @retVal + 1
	
	return @NewNo

i'm not too sure about the permit number code though...

 

[prover]

oh and thanks for the tip TJ! I'll test both.