• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 99
  • Last Modified:

Query using IF-THEN-ELSE and Variables

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!
  • 3
  • 2
  • 2
  • +1
2 Solutions
ste5anSenior DeveloperCommented:
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.
Vitor MontalvãoMSSQL Senior EngineerCommented:
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.
Scott PletcherSenior DBACommented:
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
A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

GregMulhernAuthor Commented:
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!
Scott PletcherSenior DBACommented:
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.
Vitor MontalvãoMSSQL Senior EngineerCommented:
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.
GregMulhernAuthor Commented:
Thanks a lot Scott and Vitor.
Have a great day!
Vitor MontalvãoMSSQL Senior EngineerCommented:
Greg, you never closed this question.
Please do so if you already have your issue solved.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now