Query using IF-THEN-ELSE and Variables

Posted on 2016-09-04
Medium Priority
Last Modified: 2016-09-26
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!
Question by:GregMulhern
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +1
LVL 35

Expert Comment

ID: 41784374
Please post a concise and complete example. Include table DDL (preferably table variables) and sample data INSERT statements as runnable T-SQL script. Cause it's hard to get the idea behind your description without table definitions and data.
LVL 51

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 1000 total points
ID: 41784376
I think the only way to know if a record has been processed is to add a Boolean field in the table that will flag if the record has been part if a previous report.

Another options are:
  1. Only process until last day at midnight so records from today's morning will be all processed tomorrow
  2. Run the process every day at exact hour (say at 08:00AM) so you know that you'll always need to grab the last 24h (from yesterday's 08:00AM until today's 08:00AM)

In above cases you just need to test if it's Monday or not so you can grab records from weekends as well.
LVL 69

Accepted Solution

Scott Pletcher earned 1000 total points
ID: 41786302
You could keep a separate table of rows to process.

--use a variable instead of GETDATE() so that prior date(s) could be re-run if ever necessary.
DECLARE @process_datetime datetime

IF @process_datetime IS NULL
    SET @process_datetime = GETDATE()

--insert new rows (only) for the current time period.
INSERT INTO dbo.rows_to_process ( ID, needs_processed, process_datetime, ... )
SELECT ID, 1 AS needs_processed, @process_datetime, ...
FROM dbo.main_table mt
    --from day before @process_datetime
    mt.date >= DATEADD(DAY, DATEDIFF(DAY, 0, @process_datetime) - 1. 0) AND
   --up until the @process_datetime
    mt.date < @process_date AND
    --if ID already exists in table, don't INSERT again
    NOT EXISTS(SELECT 1 FROM dbo.rows_to_process rtp WHERE rtp.ID = mt.ID)

--process rows in table withe "needs_processed" flag = 1
--when done processing a row, set its "needs_processed" flag to 0
Get real performance insights from real users

Key features:
- Total Pages Views and Load times
- Top Pages Viewed and Load Times
- Real Time Site Page Build Performance
- Users’ Browser and Platform Performance
- Geographic User Breakdown
- And more


Author Comment

ID: 41786717
Thanks all !

While Vitor was pointing me in the right direction, Scott came along with more detail (code) and a nice way to work it out.  BUT, that made me think a little deeper and it seems that I'm overkilling it using Dates at all.

Please tell me what you think about this idea.

I can add the new column, Process, and set all existing values to 1.
Any new records that are added to the db daily will have a NULL value in that field.
I would include in the WHERE search criteria 'Process IS NULL'.
Once I retrieve all the qualified records, I would Set ALL NULL Process Values to 1.

How does this sound?  
If this sounds like it will work, maybe Scott or someone will share with me how you would code this. I need to be careful not to change the NULL Process  Value until I have retrieved the records.

I'm not worried about ever running it again, I can always just run an ad hoc query.

Thanks again!
LVL 69

Expert Comment

by:Scott Pletcher
ID: 41786733
Sounds good overall.

To be safe, I would still get a list of the keys and use that to update the flag in the table.  That way if new row(s) come in, you won't accidentally set it(them) to 1 before being processed.

Also, I would use 0 rather than NULL.  If you can avoid three-valued/NULL logic, it's best to do it.  I've been a DBA for 30 years, and it's still tricky at times for me to deal with NULLable columns.
LVL 51

Expert Comment

by:Vitor Montalvão
ID: 41787304
I also prefer 0 and 1 instead of NULL and 1. You can add the new column with a default of 0 so when a record is created it will be flagged immediately as no processed yet.

Author Comment

ID: 41787688
Thanks a lot Scott and Vitor.
Have a great day!
LVL 51

Expert Comment

by:Vitor Montalvão
ID: 41808760
Greg, you never closed this question.
Please do so if you already have your issue solved.

Featured Post

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question