/* SQL Stored procedure written by Matthew Espindola for Microsoft SQL Server databases*/
/* This procedure will run and populate a table dbo.RESULTS based on the inputted date values */
/* https://www.mrc-productivity.com/techblog/?p=11216 */

CREATE PROCEDURE dbo.POPULATE_DATASET
(@user AS VARCHAR(99) OUTPUT,
@date_start AS DATE OUTPUT,
@date_end AS DATE OUTPUT)

AS
BEGIN

/* Clear results table for any existing records associated to the current user prior to INSERT */
DELETE FROM MRCWORKLIB.dbo.RESULTS WHERE CREATEUSER=@user;

/* Re-populate dataset */
INSERT INTO MRCWORKLIB.dbo.RESULTS 
SELECT CNUM, PNUM, MTDSALES, MTDCOST, REGION, SNUM, SHIPDATE, SHIPYEAR, SHIPQTR, SHIPMONTH, @user, GETDATE()
FROM MRCWORKLIB.dbo.SALESHIST 
WHERE SHIPDATE BETWEEN @date_start and @date_end;

END


