Table changed, new query

Question

question

Rawler asked Sep 9, '14

Table changed, new query

Hi guys, I have this old table that was using a Stored Procedure for querying, but now that table has one new column and the query must use that new column. My problem is: I need to keep querying that old table with the old columns until a date (when the new column was added) and after that date I have to query the new column. My question is: Since a couple of Stored Procedures query that table, I have chosen to create an unique index for the date column and add in the WHERE clause the date until it must search and added an union with the same query but with the new column and also in the WHERE added a date to start searching from there. So there is a better way to accomplish this? Is this way ok? This is an example of what I tried to explain: -- Old Query SELECT DISTINCT ColumnA, ColumnB FROM TableA WHERE ColumnA IS NOT NULL AND MONTH(DateColumn) = @aMONTH AND YEAR(DateColumn) = @aYEAR -- New Query SELECT DISTINCT ColumnA, ColumnB FROM TableA WHERE ColumnA IS NOT NULL AND MONTH(DateColumn) = @aMONTH AND YEAR(DateColumn) = @aYEAR AND TableA.DateColumn <= '08/08/2014' UNION SELECT DISTINCT convert(varchar(50), TableB.NewColumn), ColumnB FROM TableA INNER JOIN TableB ON TableA.NewColumn = TableB.NewColumn WHERE ColumnA IS NOT NULL AND MONTH(DateColumn) = @aMONTH AND YEAR(DateColumn) = @aYEAR AND TableA.DateColumn >= '08/08/2014' Regards, raúl

query

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2014-09-09T21:48:29Z

KenJ answered Sep 9, '14

I've seen a similar approach in the past. It seems reasonable. Looking at the date for the transition, it looks like you will still be using the "old" column, even after the cutoff date if that's what TableA.DateColumn says for that row. You would see a mix of "old" and "new" columns in the same result set. Is that your intent?

2

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Rawler commented · Sep 09, 2014 at 09:58 PM

Hi KenJ, Thanks for your answer, and yes I have to show the mixed data like trying to everything seems the same. I didn't get the GetDate() use there; if I use that function, won't it ignore the DateColumn? Regards, Raúl

0 ·

KenJ commented · Sep 10, 2014 at 12:38 AM

the getdate() was for a hard cutoff where you would never use the "old" column. Since you need to show that column based on the date that is held in the DateColumn column, your approach is fine. I'll remove that bit from the answer to remove the confusion.

0 ·

question