I ran the code again you gave, this time its giving me the following error.

Msg 195, Level 15, State 10, Line 3

'ROW_NUMBER' is not a recognized function name.

Msg 156, Level...

Thanks, somehow I am not getting the same output for the following two queries. 2nd one is fetching more records than the first one.

1)

select LoanID from service.dbo.loan where nextcoupondate <=...

Thank you Luis and Zzartin,

I wasn't thinking about the self joins. And no need of Left joins with where clause..

Still not sure why am I missing few records when I am running the new query.

OLD Query.

SELECT

(SELECT Count (h.TransactionAmt)

FROM dbo.Company

CROSS JOIN dbo.CollectorCodeHistory AS CC

...

Thank you all, I was just not doing it under the row groups.

Thank you Luis.

Thank you guys for all your help !!!

Would the following 2 statements fetch the same result?

1)AND (CC.EndDate >= H.SysDateTime OR (CC.EndDate IS NULL AND GETDATE() >= H.SysDateTime))

2)AND ISNULL(CC.EndDate, GETDATE()) >=...

Thanks Scott.

I am getting the following error:

Msg 4104, Level 16, State 1, Line 5

The multi-part identifier "CC.CollectorCode" could not be bound.

Msg 4104, Level 16, State 1, Line 6

The multi-part identifier...

Thanks Luis,

The reason for the sub query would be to reference the collector code, for example David is cc.collectorcode = '3'

Thank you so much,

The dilemma I am facing is that job is averaging 21 hours and it is scheduled for everyday, so the job pretty much keeps running all day....

Also what would be a faster way of updating the acquired from table with the new table, instead of doing it manually for each discrepancy.

Awsum, I am so grateful to you. So I went ahead and exported the source_office to database Service and Server FS3, naming the new table source_office1.

One thing I was looking...

Quick question. Am I importing the table source office into the tempdb databse or the service database where the acquiredfrom table is present?

Thanks Sartori, I am new to this and have not done this before, if we go with the second option, are you suggesting to download the contents of the below...

That might be complicated, any other one time fix solutions possible?