I'm trying to run a query in SQL Server 2012 which sums up all inventory transactions for the end of each month.
I'm simplifying the problem here. I can't use grouping.
I've created this stored procedure.
The idea was to call the procedure and hand it the parameters for the end of month dates for the last 12 months.
CREATE PROCEDURE [dbo].[ICAgeing] (@CutOffDate varchar(6)) AS BEGIN INSERT INTO CDStockAgeing (ItemValue, ItemQty, YearMonth) SELECT SUM(TRANSCOST) AS TotalCost, SUM(Quantity) AS Qty, @CutOffDate FROM ICIVAL WHERE LEFT(TRANSDATE,6) <= @CutOffDate END
I would then call the procedure and get the 'YearMonth' values in the format '201512', '201511', '201510' etc. for the last 12 months as @CutOffDate.
I seem to be unable to change the parameter however. The select statement does not run.
Would appreciate any advice.
DECLARE @CutOffDate varchar (6); SET @CutOffDate = 'SELECT YearMonth FROM Past12Months'; EXEC CDStockAgeing @CutOffdate