Executing Stored procedure with prior day filter

We have a reporting system that imports data from our ERP system by way of a stored procedure. The reporting system runs an EXEC StoredProcedureName with a start and end date that is manually adjusted every day. What our end goal would like to be is to have a predefined filter for the previous day so that it will only import the prior day activity. Below is an example of what our execute sql statement looks like.

EXEC dbo.ProphixSalesExport @Start_Date = N'11/20/12', @End_Date = N'11/20/12'

Any insight would be appreciated

Tom

asked Nov 20 '12 at 08:10 PM in Default

tstagliano
70 ● 1 ● 1 ● 1

(comments are locked)

1 answer:

Is your start and end date always the same day?

You could always declare a variable, set the variable to the previous day using getdate ()-1 and use the variable in your sproc.

declare @date date
set @date = getdate () -1

answered Nov 20 '12 at 08:20 PM

Tim
31.5k ● 20 ● 31 ● 116

the start and end date is usually the same day except for Mondays when we need to include saturday and sunday activity

Nov 20 '12 at 08:22 PM tstagliano

Then you could wrap the logic into an if else statement with if day of week = Monday then set @startdate = getdate ()-3 and @enddate = get date () -1 else set @startdate = getdate ()-1 and @enddate = getdate () -1. Then pass those values to your stored procedure.

You could declare a string for your sql statement

declare @sql nvarchar (300)
declare @startdate varchar(8)
declare @enddate varchar(8)

set @startdate = convert(varchar(8), getdate()-1,1)
set @enddate = convert(varchar(8),getdate()-1,1)

set @sql = 'EXEC dbo.[ProphixSalesExport] ' +@startdate + ', ' + @enddate +''

You can wrap this in the if else for your Monday scenario.

Nov 20 '12 at 10:10 PM Tim

,I understand the string statement but i am having trouble with is understanding how to wrap that in the if else statement for the Monday scenario. Thanks again for your guidance.

Nov 21 '12 at 01:50 PM tstagliano

select datename(dw, current_timestamp)

That gives you the day of week. If you pass in a particular date from a variable, you could do an if check:

declare @DayOfWeek varchar(15); select @DayOfWeek = datename(dw, '2913-06-13');

if @DayOfWeek = 'Monday' print 'It''s Monday!' else print 'It''s not Monday!'

Nov 21 '12 at 02:11 PM Kevin Feasel

Tim, You statement worked for one day worth of data, but the next day this process ran, it took that same number and imported in. For example, on the 19th, the wuery returned 9million units and on the 20th there was actually 7million units but the query still added 9million to the report and so on for the next 2 weeks.

Any other suggestions?

Nov 29 '12 at 04:00 PM tstagliano

(comments are locked)