[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now


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 52

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 70

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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


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 70

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 52

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 52

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

Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

Question has a verified solution.

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

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
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.
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.

656 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