Coldfusion Compare Two Query Results from Same Database

Question

I've done some research on this site for an issue I'm having, however, I'm finding that the solution is not exactly what I'm looking for, or the implementation doesn't relate to what I'm trying to do. Or, simply put, I just can't seem to figure it out. Here is my issue.

We have a monthly query that we would run that we would send to a third party of physicians, their degree, specialty and clinic. I have the query established already. But recently they wanted to just have us export new results from the previous months data, instead of the whole results list. So, I thought I would create a tool that I would start out simply importing the previous months data. And then taking the query I had been using, putting that in a coldfusion page, run it, and it would show me new records ran for the current month we're in, to the previous month. When I run the report of new data each month, it would save that data in the database with the columns r_month and r_year, which simply means report month/year. So to initially populate the database I just imported Octobers data so I can have a base with the r_month/year being "10" and "2014" respectively. There are 674 records. Then created my page and had a button that would run the same query, save those results, which the r_month and r_year is saved as "11" and "2014" respectively. When I do that, I have 682 records. So, for the month of November, there are 8 "different" or new records from the previous month (October). My question is, what is the best way to run a query that takes the data from October (10/2014) and compare it to the November's data (11/2014), and just give me the new 8 records that were new from November.

Sorry this is long, but wanted to give you guys a detail so you have as much information as possible. I don't really have a code sample I can provide, because apparently the way I was attempting before (using loops etc.) was just not working. Tried looping through previous month query and current month query, trying to find a difference, but that wasn't working. Once again, I've tried using similar samples I've found on here, but they are either not what I'm looking for, or I just can't figure them out. Basically at the end of the process, there needs to be a button that only exports the new records (in this example, the 8) into an excel sheet that we can simply email them.

Any help would be greatly appreciated.

Do you really need a separate table? Why not just add some sort of timestamp column that indicates when a record was last updated? Then simply filter on WHERE LastUpdated >= {startOfThisMonth} AND LastUpdated < {startOfNextMonth}? Then way you could easily retrieve new or changed records. — Leigh

Mark A Kruger Mark A Kruger · Accepted Answer · 2014-11-11T18:58:35

SOLUTION 1 - Since you are using SQL server you can do this pretty easily within the query. You have already logged the previous data so you presumably have a key for the "old" physicians in your log table. Try something like this:

<cfquery name="getNewPHys" datasource="#dsn#">
SELECT   * 
FROM     sourceTable 
WHERE    physID NOT IN 
         (SELECT physID FROM logtable 
          WHERE  daterange between #somerange# AND #someotherrange#)
</cfquery>

You would have to add your own values and vars but you get the idea.

NOTE: This is psuedo-code. you would OF COURSE use cfqueryparam for any of your variables.

SOLUTION 2 Another way to do this is by using a dateadded or lastUPdated table. Every time a row is updated you update the lastupdated column with the current date/time. Then selecting recent records is a matter of selecting any records which have been updated within your range. Taht's what Leigh suggested in her comment.

I would add one other comment. You seem to be trying to solve this problem without changing anything in your data table. That's not going to work. You need to think about your schema a bit more. For exmaple, solution 2 would involve adding an additional column and you could even add a MSSQL trigger that automatically updated that field whenever the record was updated. Wouldn't that work?

I still think we are missing something. Are you perchance overwriting your data each time? Or producing duplicate records - 674 this month, 682 next month with duplicates? If so, that's what you need to correct. Anything else is going to be a bolt on solution that creates more problems down the road.

Coldfusion Compare Two Query Results from Same Database

2 Answers