A bit more clearer:rolleyes:. But again, this would be easier to come up with a solution if DLL and insert statements...

select party_code, sum(inst_amt) as Total_instamt, sum(paid_amt) as paid

from @table1

where paid_date < '2012-11-27'

group by party_code

The previously posted query...

A MUCH better solution!

LinksUp (9/18/2013)

---

. . If it does, there should not be...

I know you posted a solution that works for you, but if you could humor me. . .

Could you try the following code? I am interested to know if it...

I did not see a way to do this without multiple update statements. But since this is a clean up job, running 2 updates should not be a big deal!

Try...

Now we get to the crux of the issue. The null thing was a red herring. It had nothing to do with what you...

Study up on constraints as well as Foreign Keys. These help keep the "bad" data out of the database regardless of where the data is coming from.

As you saw...

As was requested by Mr. Lange, PROPERLY FORMED DLL, insert statements and expected output make it easier to provide a tested solution. So, the following is just air code that...

INSERT INTO #Total_Count (NUM_DEATHS_1870, NUM_DEATHS_1880)

(SELECT COUNT (*), 0

FROM [dbo].[1870_1880_DAT]

WHERE YR_Died = '1870')

INSERT INTO #Total_Count (NUM_DEATHS_1870, NUM_DEATHS_1880)

(SELECT 0, COUNT (*)

FROM [dbo].[1870_1880_DAT]

WHERE YR_Died = 1880)

Your posted DLL is not complete. So nobody can run your code to see how it works. Creation and insert statements are needed for [dbo].[1870_1880_DAT]

How do you remove the nulls?...

This link might be helpful.

http://stackoverflow.com/questions/5145637/querying-data-by-joining-two-tables-in-two-database-on-different-servers]

You have a query that returns 99.9% of what you want. All that is needed is to subtract the 2 weights.

select *, two.ITM$Weight - one.ITM$Weight as WeightDiff

from etc...

This query does what you requested with the provided dll and data.

;WITH cte(col) as (

SELECT col from #table1

...

smrobin (9/13/2013)

---

Thanks for the reply - the problem I have is that I don't always know what the number will be. I have the rules to which I apply...