quote:

---

Server: Msg 8623, Level 16, State 1, Line 1
Internal Query Processor Error: The query processor could not produce a query plan. Contact your primary support provider for more information.

---

-- udf called in update query
CREATE FUNCTION [dbo].[rfn_GetFiscalYear] 
(	@FYB int,@Date datetime )
RETURNS int AS  
BEGIN 
-- Purpose : Get Fiscal year from input date
	DECLARE @FY int, @AddAmt int
	SELECT @AddAmt = 0
	IF ((@FYB > 1) and (datepart(mm,@Date) >= @FYB))
		SELECT @AddAmt = 1 
	SELECT @FY = datepart(yyyy,@Date) + @AddAmt
	RETURN @FY
END

-- create temp table
	CREATE TABLE #DrawingData
	(	pnID			int,
		Part_Number		varchar(50),
		Part_Nomenclature	varchar(512),
		Vendor_PN		varchar(50),
		Next_Assy		varchar(50),
		Reason_Initiated	varchar(512),
		External_Case_Num	varchar(50),
		Log_in_Date		datetime,
		Case_Status		varchar(50),
		Recommended_Resolution	varchar(30),
		Log_Out_Date		datetime,
		Case_Number		int,
		FiscalYear		char(4),
		Case_Num_Cmplte		varchar(50)
	)
	GO

	INSERT INTO #DrawingData
	(	pnID, Part_Number, Part_Nomenclature, Vendor_PN, Next_Assy, Reason_Initiated, 
		External_Case_Num, Log_in_Date, Case_Status, Recommended_Resolution, Log_Out_Date
	)
	SELECT 1,'lskjdf','lakdjfsd','ladkjfd','ljdlfkjad','lfjalskdfj','test1', getdate(),'lsjkdflkdj','lakdjfdj',getdate() union all
	SELECT 1,'lskjdf','lakdjfsd','ladkjfd','ljdlfkjad','lfjalskdfj','test2', getdate(),'lsjkdflkdj','lakdjfdj',getdate() union all
	SELECT 1,'lskjdf','lakdjfsd','ladkjfd','ljdlfkjad','lfjalskdfj','test3', getdate(),'lsjkdflkdj','lakdjfdj',getdate() union all
	SELECT 1,'lskjdf','lakdjfsd','ladkjfd','ljdlfkjad','lfjalskdfj','test4', getdate(),'lsjkdflkdj','lakdjfdj',getdate() union all
	SELECT 1,'lskjdf','lakdjfsd','ladkjfd','ljdlfkjad','lfjalskdfj','test5', getdate(),'lsjkdflkdj','lakdjfdj',getdate() union all
	SELECT 1,'lskjdf','lakdjfsd','ladkjfd','ljdlfkjad','lfjalskdfj','test6', getdate(),'lsjkdflkdj','lakdjfdj',getdate() union all
	SELECT 1,'lskjdf','lakdjfsd','ladkjfd','ljdlfkjad','lfjalskdfj','test7', getdate(),'lsjkdflkdj','lakdjfdj',getdate() union all
	SELECT 1,'lskjdf','lakdjfsd','ladkjfd','ljdlfkjad','lfjalskdfj','test8', getdate(),'lsjkdflkdj','lakdjfdj',getdate() union all
	SELECT 1,'lskjdf','lakdjfsd','ladkjfd','ljdlfkjad','lfjalskdfj','test9', getdate(),'lsjkdflkdj','lakdjfdj',getdate() union all
	SELECT 1,'lskjdf','lakdjfsd','ladkjfd','ljdlfkjad','lfjalskdfj','test10', getdate(),'lsjkdflkdj','lakdjfdj',getdate()
-- This can be run multiple times or added to for larger recordsets

-- update table to add case number and fiscal year info
	UPDATE c 
	SET 	Case_Number = (		SELECT isnull(COUNT(d.Log_In_Date),0)+1 
					FROM #DrawingData d 
					WHERE 
					(	dbo.rfn_GetFiscalYear(10, d.Log_in_Date) = dbo.rfn_GetFiscalYear(10, c.Log_in_Date) 
					AND	(	d.Log_In_Date < c.Log_In_Date 
						OR	(	d.Log_In_Date = c.Log_In_Date
							AND	left(d.External_Case_Num,10) < left(c.External_Case_Num,10)
							)
						)
					)
				),
		FiscalYear = dbo.rfn_GetFiscalYear(10, c.Log_in_Date)
	FROM #DrawingData c
	GO

CREATE FUNCTION [dbo].[rfn_GetFiscalYear] 
(	@FYB int,@Date datetime )
RETURNS int AS  
BEGIN 
-- Purpose : Get Fiscal year from input date
	RETURN year(@Date) + CASE WHEN month(@Date) >= @FYB AND (@FYB > 1) THEN 1 ELSE 0 END
END