Query using IF-THEN-ELSE and Variables

Posted on 2016-09-04
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
  • 3
  • 2
  • 2
  • +1
LVL 32

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 45

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 250 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

ScottPletcher earned 250 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 >= DATEADD(DAY, DATEDIFF(DAY, 0, @process_datetime) - 1. 0) AND
   --up until the @process_datetime < @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

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!
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

LVL 69

Expert Comment

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 45

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 45

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

Backup Your Microsoft Windows Server®

Backup 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.

Join & Write a Comment

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

705 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now