Yeah, I was assuming the whole row was needed. Probably should have asked what the expected outcome was.

If you aren't dealing with a large amount of data something like this should work.

SELECT * FROM tableName a

WHERE financialYear = (SELECT MAX(financialYear) FROM tableName b

WHERE a.memberID = b.memberID)

Yeah that is odd. Have you checked the identity seed and increment? I am not sure what else would be causing that.

You could have had rows deleted or a rollback occur. The identity column will increment even if a rollback occurs. See the below script for an example.

CREATE TABLE...

A union generates more rows not more columns. Does this return what you expect?

SELECT col1, col2, col3 FROM Table1 WHERE (EXISTS (SELECT col5 FROM Table2 WHERE col1 =c5))

UNION ALL

SELECT...

Ok, I think this will work. You can replace all of the table names with your actual tables except for the #temp table. This will loop through each...

Well, I have already done a loop and thats just a nicer word for cursor. I don't have access to a test environment right now but I will write...

I have a solution that works using a loop and dynamic SQL. But this has some stipulations with it. I changed the table variables to be temp tables...

I don't think you should be converting to varchar. Just try where colName between 'mm/dd/yy' and 'mm/dd/yy'

This is really quick and probably dirty. I am trying to come up with a better way to determine the joins cause right now you will need a UNION...

Something like this should work.

create table Table1

(

col1 varchar(10), col2 varchar(10)

)

create table Table2

(

c1 varchar(10), c2 varchar(10)

)

insert into Table1 values('ABC','USA')

insert into Table1 values('XYZ','USA')

insert into Table2 values('ABC','USA')

SELECT a.* FROM table1 a

LEFT JOIN table2...

SELECT * FROM OPENROWSET('SQLNCLI','Server=server\instance;

...

Try this

select *

from openrowset('SQLNCLI','Server=(local);Trusted_Connection=yes;',

'exec dbo.sp_who') AS a

Try converting to decimal and then to int. Or trimming off the .00 on the end of each string.

Yeah, I was just creating a temp table so I had something to work with. Since you have the actual source tables you won't have to do that. ...