x

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
more ▼

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

tstagliano gravatar image

tstagliano
70 1 1 1

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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

more ▼

answered Nov 20 '12 at 08:20 PM

Tim gravatar image

Tim
35.5k 32 40 138

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)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x401

asked: Nov 20 '12 at 08:10 PM

Seen: 1100 times

Last Updated: Nov 30 '12 at 02:32 AM