SQL Insert Consolidation

My attached stored procedure works...but I'd like to consolidate my code that does the inserts...

First...
I would only want each insert to run if the inbound afsAmount (@P_R1, @P_Fed...etc) is > 0

In addition...If there's a way to do the 4 inserts with a single block...some kind of fetch...would be another consolidation.

The problem is that in the fetch there would be 4 "runs" but the items being inserted need to be grouped in two's...@afsSourceDetail + (var) and @P_ that's going into the afsAmount column. All the other data is the same for the inserts.

I've just never done a fetch with a "group" of two's.

,@afsSourceDetail + '-Reg' / ,@P_R1
,@afsSourceDetail + '-Fed' / ,@P_Fed
,@afsSourceDetail + '-St' / ,@P_St
,@afsSourceDetail + '-City' /  ,@P_City

ALTER PROCEDURE [dbo].[sp_cfa_AfsSplitLT_Taxes](@afsid int )
AS
Begin
	Declare @P_Fed decimal
	Declare @P_St decimal
	Declare @P_City decimal
	Declare @P_R1 money
	Declare @afsSource varchar(20)
	Declare @afsDate datetime
	Declare @afsStatus int
	Declare @afsTransaction int
	Declare @afsSourceDetail varchar(20)


	Select	@P_Fed			=	z.[P01-Fed],
			@P_St			=	z.[P01-St],
			@P_City			=	z.[P01-City],
			@P_R1			=	z.[P01-Reg],
			@afsSource		=	z.afsSource,
			@afsSourceDetail =	z.afsSourceDetail,
			@afsDate		=	z.afsDate,
			@afsStatus		=	z.afsStatus,
			@afsTransaction	=	z.afsTransaction	


	from (Select	cast(p.fedW2G / 100 * x.afsAmount as money) [P01-Fed], 
					cast(p.stateW2G / 100 * x.afsAmount as money) [P01-St],
					cast(p.cityW2G / 100 * x.afsAmount as money) [P01-City],		
					cast(x.afsAmount - (p.fedW2G / 100 * x.afsAmount) 
								- (p.stateW2G / 100 * x.afsAmount) 
								- (p.cityW2G / 100 * x.afsAmount) as money) [P01-Reg],
					x.afsAmount,
					x.afsSource,
					x.afsSourceDetail,
					x.afsDate,
					x.afsStatus,
					x.afsTransaction
	From	proc_lt.dbo.p_deals p
	 Join  
	(Select top 1 SUBSTRING(afsSource,4,4) DealID, * 
	from	proc_cfa.dbo.P_AvailableForSale
	Where	LEFT(afsSource,2) = 'LT' 
			and afsSourceDetail = 'ALL' 
			and afsTransaction = 0
	order by afsSource) x
	on	p.DealID = x.DealID
	and x.afsid = @afsid) z


	--Insert #1 top record - less taxes
	Begin
	INSERT INTO PROC_CFA.dbo.P_AvailableForSale
			   (afsSource
			   ,afsSourceDetail
			   ,afsDate
			   ,afsAmount
			   ,afsStatus
			   ,afsTransaction)
		 VALUES
			   (@afsSource
			   ,@afsSourceDetail + '-Reg'
			   ,@afsDate
			   ,@P_R1
			   ,@afsStatus
			   , @afsTransaction )
	End

	--Insert #2 Federal Tax Data
	Begin
		INSERT INTO PROC_CFA.dbo.P_AvailableForSale
				   (afsSource
				   ,afsSourceDetail
				   ,afsDate
				   ,afsAmount
				   ,afsStatus
				   ,afsTransaction)
			 VALUES
				   (@afsSource
				   ,@afsSourceDetail + '-Fed'
				   ,@afsDate
				   ,@P_Fed
				   ,@afsStatus
				   , @afsTransaction )
	End
	
	--Insert #3 State Tax Data
	Begin
		Insert Into PROC_CFA.dbo.P_AvailableForSale
				   (afsSource
				   ,afsSourceDetail
				   ,afsDate
				   ,afsAmount
				   ,afsStatus
				   ,afsTransaction)
			 Values
				   (@afsSource
				   ,@afsSourceDetail + '-St'
				   ,@afsDate
				   ,@P_St
				   ,@afsStatus
				   , @afsTransaction )
	End

    --Insert #4 City Tax Data
	Begin
	INSERT INTO PROC_CFA.dbo.P_AvailableForSale
			   (afsSource
			   ,afsSourceDetail
			   ,afsDate
			   ,afsAmount
			   ,afsStatus
			   ,afsTransaction)
		 VALUES
			   (@afsSource
			   ,@afsSourceDetail + '-City'
			   ,@afsDate
			   ,@P_City
			   ,@afsStatus
			   , @afsTransaction )
	End

	Delete from proc_cfa.dbo.P_AvailableForSale where afsid = @afsid
	Delete from proc_cfa.dbo.P_AvailableForSale where afsAmount = 0

	Select @afsSource as afsSource
End

Open in new window

Larry Bristersr. DeveloperAsked:
Who is Participating?
 
Kevin CrossChief Technology OfficerCommented:
Hi.

Have a look at this:
ALTER PROCEDURE [dbo].[sp_cfa_AfsSplitLT_Taxes](@afsid int )
AS
Begin
   ;WITH taxesCTE(P_Fed, P_St, P_City, P_R1, afsSource, afsSourceDetail, afsDate, afsStatus, afsTransaction) AS (
      Select cast(p.fedW2G / 100 * x.afsAmount as money) [P01-Fed], 
			 cast(p.stateW2G / 100 * x.afsAmount as money) [P01-St],
			 cast(p.cityW2G / 100 * x.afsAmount as money) [P01-City],		
			 cast(x.afsAmount - (p.fedW2G / 100 * x.afsAmount) - (p.stateW2G / 100 * x.afsAmount) - (p.cityW2G / 100 * x.afsAmount) as money) [P01-Reg],
			 x.afsAmount,
			 x.afsSource,
			 x.afsSourceDetail,
			 x.afsDate,
			 x.afsStatus,
			 x.afsTransaction
      From proc_lt.dbo.p_deals p
	  Join (Select top 1 SUBSTRING(afsSource,4,4) DealID, * from proc_cfa.dbo.P_AvailableForSale Where LEFT(afsSource,2) = 'LT' and afsSourceDetail = 'ALL' and afsTransaction = 0 order by afsSource) x
	  on p.DealID = x.DealID and x.afsid = @afsid
   )
   INSERT INTO PROC_CFA.dbo.P_AvailableForSale
			   (afsSource
			   ,afsSourceDetail
			   ,afsDate
			   ,afsAmount
			   ,afsStatus
			   ,afsTransaction)
	SELECT afsSource, afsSourceDetail+ '-Reg', afsDate, P_R1, afsStatus, afsTransaction
	FROM taxesCTE
	UNION
	SELECT afsSource, afsSourceDetail+ '-Fed', afsDate, P_Fed, afsStatus, afsTransaction
	FROM taxesCTE
	UNION
	SELECT afsSource, afsSourceDetail+ '-St', afsDate, P_St, afsStatus, afsTransaction
	FROM taxesCTE
	UNION
	SELECT afsSource, afsSourceDetail+ '-City', afsDate, P_City, afsStatus, afsTransaction
	FROM taxesCTE
	;

	Delete from proc_cfa.dbo.P_AvailableForSale where afsid = @afsid
	Delete from proc_cfa.dbo.P_AvailableForSale where afsAmount = 0

	--Select @afsSource as afsSource
End

Open in new window


The last line would have to be fixed since this method utilizes common table expression versus the variables which made @afsSource available at this point.  

Since you have to maintain this long-term, I will explain what I am thinking and then would like you to try to put it together yourself just to make sure I am not putting anything in place that you are not comfortable maintaining yourself.

In the above, you have this:
(Select top 1 SUBSTRING(afsSource,4,4) DealID, * from proc_cfa.dbo.P_AvailableForSale Where LEFT(afsSource,2) = 'LT' and afsSourceDetail = 'ALL' and afsTransaction = 0 order by afsSource) x
        on p.DealID = x.DealID and x.afsid = @afsid

My thought is for performance I would move the highlighted filter to inside the subquery/derived table.  Additionally, if you have other afsid values in the data other than what you are looking for you may get incorrect TOP 1 result otherwise unless you only want to update the rows when the afsid is the top most value and not when it isn't.  

Anyway, taking that a step further, you can use the above query as what you use to fill the afs values that are common to all the tax calculations.

Then you can do a similar union for the inserts of the different tax municipailities.

However, doing it this new way, you will have @afsSource available at the end.  

Hope that helps!
0
 
Larry Bristersr. DeveloperAuthor Commented:
mwvisa1:,
Great points and it gives me a good start.
Made some minor changes ...the @afsid is a UID
The Select top 1  was because SQL 2008 was yammering at me about that line and I added it.

I also added the 4 where (amount> > 0 lines in the union section.

Points awarded shortly...
Final code attached
ALTER PROCEDURE [dbo].[sp_cfa_AfsSplitLT_Taxes](@afsid int )  
AS 
--Get afsSource for return
Begin
	Declare @afsSource nvarchar(20)
	Set @afsSource = (Select afsSource from proc_cfa.dbo.P_Availableforsale where afsid = @afsid)
End

--Add taxes to Available for sale
Begin  
   ;WITH taxesCTE(P_Fed, P_St, P_City, P_R1, afsAmount, afsSource, afsSourceDetail, afsDate, afsStatus, afsTransaction) AS (  
      Select cast(p.fedW2G / 100 * x.afsAmount as money) [P_Fed],   
             cast(p.stateW2G / 100 * x.afsAmount as money) [P_St],  
             cast(p.cityW2G / 100 * x.afsAmount as money) [P_City],                 
             cast(x.afsAmount - (p.fedW2G / 100 * x.afsAmount) - (p.stateW2G / 100 * x.afsAmount) - (p.cityW2G / 100 * x.afsAmount) as money) [P_R1],  
             x.afsAmount,  
             x.afsSource,  
             x.afsSourceDetail,  
             x.afsDate,  
             x.afsStatus,  
             x.afsTransaction  
      From proc_lt.dbo.p_deals p  
			Join	(Select top 1 SUBSTRING(afsSource,4,4) DealID, * 
					from proc_cfa.dbo.P_AvailableForSale 
					Where LEFT(afsSource,2) = 'LT' and afsSourceDetail = 'ALL' and afsTransaction = 0 order by afsSource) x  
			on p.DealID = x.DealID and x.afsid = @afsid  
   ) 
   
   --Insert all rows with one insert/ where amount > 0 
	INSERT INTO PROC_CFA.dbo.P_AvailableForSale  
						   (afsSource  
						   ,afsSourceDetail  
						   ,afsDate  
						   ,afsAmount  
						   ,afsStatus  
						   ,afsTransaction)  
	SELECT afsSource, afsSourceDetail+ '-Reg', afsDate, P_R1, afsStatus, afsTransaction  
	FROM taxesCTE  where P_R1 > 0
	UNION  
	SELECT afsSource, afsSourceDetail+ '-Fed', afsDate, P_Fed, afsStatus, afsTransaction  
	FROM taxesCTE where P_Fed > 0 
	UNION  
	SELECT afsSource, afsSourceDetail+ '-St', afsDate, P_St, afsStatus, afsTransaction  
	FROM taxesCTE  where P_St > 0  
	UNION  
	SELECT afsSource, afsSourceDetail+ '-City', afsDate, P_City, afsStatus, afsTransaction  
	FROM taxesCTE where P_City > 0 
        ; 
        --Remove original afsid   
	Delete from proc_cfa.dbo.P_AvailableForSale where afsid = @afsid  
 
End 

Select @afsSource as afsSource

Open in new window

0
 
Larry Bristersr. DeveloperAuthor Commented:
This is the kind of answer that is "over the top" helpful.

Example with explanation.

Remind me to buy you a sody-pop sometime!
0
 
Kevin CrossChief Technology OfficerCommented:
*laughing* Glad it helped.
Best regards and happy coding,

Kevin
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.