Query using IF-THEN-ELSE and Variables
Posted on 2016-09-04
I'd like to create a PROCEDURE that will be run each weekday.
The StartDateRange for the Query will always be the previous workday through the present. What happens today after the procedure is run each morning will be picked up tomorrow.
I need to use a variable to store the unique ID of each record listed in today's report (all the ones from yesterday and up to the present) so that when I run it tomorrow, it will only capture the records from today that appear after I ran the procedure this morning.
For example: Yesterday (Tuesday), I ran the procedure and captured records 1,2,3 from Monday and records 4,5 from Tuesday.
When I run the procedure today (Wednesday), I captured records 4,5,6,7,8 from Tuesday and 9,10,11 from this morning. Since 4,5 was captured in yesterdays procedure I don't want them to be included in today's list. It seems therefore, that if I had a variable that retained the record IDs from yesterday's procedure (1,2,3,4,5) and compared that with today's results (4,5,6,7,8,9,10) that it could eliminate the 4,5 from today's query results.
Then it would start all over. The variable would save today's 6-10 to compare against tomorrow's report. The IDs are not actually in numerical order, but each is a unique 9-digit account number.
At the same time, since I only run this each weekday morning (although records do come in on the weekend), I need to use an IF-THEN-ELSE (or something) to determine if I need to query the date TODAY()-1 or TODAY()-3. Tues - Fri would use the first and Mon would use the second to be able to go back to include Fri, Sat, Sun & Mon.
I can determine the Start Date Range in Excel with "IF(WEEKDAY(TODAY(),1)=2,TODAY()-3,TODAY()-1)". I don't know how to do the same thing in a SQL Procedure.
I presently go in and edit the date before each run and look at a text file of the previous run and remove any records in today's results that were already listed in the previous. I need this help so I can let others run the procedure without having access to edit the query as I have to do (and will be glad when I don't have to do so).
I'd really appreciate any help I could get. Thanks and Happy Labor Day!