jeudi 4 juin 2015

How To Compare two dates, SQL/SSIS Task

So I've got a task that takes a random 20% of a table's results from the previous day to use as a control group. These results are put into a table, and then shoved into a .CSV file for use by the employer.

That works perfectly well. The only problem is, it's in a group of tasks that are often tested, which means that when the task gets repeated, more random data gets dumped into the file - meaning manual deletion of rows. I'm looking for a fix.

Because the process is run once a day, a unique key is the TransactionDateID, formatted INT (20150603). I need to check against that column to make sure that nothing has been run on that same day. The problem is exacerbated because it involves yesterday's records.

For example. In order to check todays date to see if it has been run, getDate() would be used to get today's date, then converted to INT (20150604). But I can't simply check to see if there is a numerical difference of 1, because once the month switches, a simple +1 will throw the entire thing out of whack:

(20150631) + 1 =/= (20150701)

I'm just wondering if this is going to be casting/converting back and forth because of the difference in variable types, or if there's something I can do with a BIT to add a column if the task has been completed for the day, something along those lines.


A colleague suggested using MAX(TransactionDateID) and then checking getDate() against that column.

Unfortunately, I run into a problem the following day:

Initial task run at 2015-06-04-09:30:ss:mm

2015-06-04-11:45:ss:mm etc.. > 2015-06-04-09:30:ss:mm, DO NOT RUN

2015-06-05-09:30:ss:mm etc.. > 2015-06-04-09:30:ss:mm, I want it to run ...




Aucun commentaire:

Enregistrer un commentaire