Change of total after insert

[Naith]

Hey,

I'm doing a straight insert from one table to another with no where clause, and am noticing that the totals of each measure in both tables are different, when I would expect them to be the same in either table.

INSERT INTO TableA
(Sales_Org_ID,
sales_org,
pl2_classid,
pl2_class,
pl6_ppnid,
pl6_ppn,
hq_id,
hq_name,
customer_sold_to_id,
customer_sold_to,
sales_district_id,
sales_district,
fquarter,
fweek,
bill_qty,
bill_amt_$,
ts_adjd_qty,
st_rptd_qty,
st_ttl_adjd_qty,
inv_rptd_qty,
inv_demo_qty,
inv_boh_qty,
inv_coh_qty,
inv_eoh_qty,
xfer_i_qty,
xfer_o_qty,
xfer_i_ttl_adjd,
xfer_o_ttl_adjd,
intran_ttl_adjd,
mdate)
SELECT
cast(Sales_Org_ID as smallint),
sales_org,
pl2_classid,
pl2_class,
pl6_ppnid,
pl6_ppn,
hq_id,
hq_name,
customer_sold_to_id,
customer_sold_to,
sales_district_id,
sales_district,
convert(decimal(18,1),right(fquarter,4) + '.' + left(fquarter,1)),
( 
	case when convert(int,round(fweek,1)) >= 10 
	then convert(decimal(18,2),right(fweek,4) + '.' + left(fweek,2))
	else convert(decimal(18,2),right(fweek,4) + '.0' + left(fweek,1)) 
	end
),
sum(convert(int,replace(bill_qty,',',''))),
sum(convert(money,bill_amt_$)),
sum(convert(int,replace(ts_adjd_qty,',',''))),
sum(convert(int,replace(st_rptd_qty,',',''))),
sum(convert(int,replace(st_ttl_adjd_qty,',',''))),
sum(convert(int,replace(inv_rptd_qty,',',''))),
sum(convert(int,replace(inv_demo_qty,',',''))),
sum(convert(int,replace(inv_boh_qty,',',''))),
sum(convert(int,replace(inv_coh_qty,',',''))),
sum(convert(int,replace(inv_eoh_qty,',',''))),
sum(convert(int,replace(xfer_i_qty,',',''))),
sum(convert(int,replace(xfer_o_qty,',',''))),
sum(convert(int,replace(xfer_i_ttl_adjd,',',''))),
sum(convert(int,replace(xfer_o_ttl_adjd,',',''))),
sum(convert(int,replace(intran_ttl_adjd,',',''))),
convert(varchar, GETDATE(), 112)
FROM TableB
GROUP BY
sales_org_id,
sales_org,
pl2_classid,
pl2_class,
pl6_ppnid,
pl6_ppn,
hq_id,
hq_name,
customer_sold_to_id,
customer_sold_to,
sales_district_id,
sales_district,
fquarter,
fweek
ORDER BY FQuarter,FWeek

TableA was empty before running the statement. Now, when I check the sum of a particular column, I get:

select sum(convert(int,replace(ts_adjd_qty,',',''))) from TableA (3283713)
select sum(convert(int,replace(ts_adjd_qty,',',''))) from TableB (3474840)

..which is a loss of around 200,000. The group by shouldn't be affecting this, so what is the reason for it?

Cheers

 

[jbenson001]

Is there a trigger on tableA that may be changing values somewhere?

 

[Naith]

Nope. This is a brand new schema.

 

[jbenson001]

The group by shouldn't be affecting this, so what is the reason for it?

perhaps it is the group by

 

[SQLSister]

do the selct inthe insert statment and see if you are getting the same number of records as in the original table.

Aslo I notice you are converting it to an int. perhaps that is the discrepancy, that the rounding is for each group in one and for each record in the other.

Questions about posting. See faq183-874
Click here to help with Hurricane Relief

 

[Naith]

Hi SQLSister,

There's around half the records in TableA (592660) as there are in B (961750) - which I guess is down to the fact that there is summation going on in the insert.

I've noticed also that two fields (hq_id and customer_sold_to_id) primarily contain numerical data, except for a cache of values which are '#'.

These '#' values don't seem to make it into the destination table, despite the fact that the datatypes are the same, and there is no warning about some data not making it in.

Can anyone suggest an alternative way to get the data transferred between these tables, so I can see the impact?

N

 

[Naith]

Incidentally, the int conversion is a necessary because TableB.ts_adjd_qty is varchar, as data cleansing takes place after this point.

The only reason it's in the TableA sum example is just because it was copied and pasted. Removing this conversion from the check doesn't affect the result.

 

[arrowhouse]

What types are hq_id and customer_sold_to_id in Table A and Table B?

If you run a select only on table B where either the hq_id or customer_sold_to_id = '#' what sum do you get for sum(convert(int,replace(ts_adjd_qty,',','')))?

 

[Naith]

Those two fields are both varchar 255, and the value of that statement is 977 for customer_sold_to_id, and 4101 for hq_id.

 

[arrowhouse]

It seems to me you have established that the # id's havn't come accross, so are there any others...
Can you do a

SELECT DISTINCT hq_id, customer_sold_to_id FROM TABLE B
and
SELECT DISTINCT hq_id, customer_sold_to_id FROM TABLE A

How many rows come back for each statement(if they are very big tables you might want to do a count of the distinct rows instead)

If everything has gone ok you should have the same number of rows, if not you will need to find out which ones have gone missing and track it down from there so something like this (assuming table B is your source table and table A is the summary table):

SELECT B.hq_id, B.customer_sold_to_id, A.ts_adjd_qty , B.ts_adjd_qty FROM
(SELECT hq_id, customer_sold_to_id, SUM(sum(convert(int,replace(ts_adjd_qty,',',''))) ) as ts_adjd_qty FROM TABLE B group by hq_id, customer_sold_to_id) B
LEFT OUTER JOIN
(SELECT hq_id, customer_sold_to_id, SUM(sum(convert(int,replace(ts_adjd_qty,',',''))) ) as ts_adjd_qty FROM TABLE A group by hq_id, customer_sold_to_id) A
ON B.hq_id = A.hq_id AND B.customer_sold_to_id = A.customer_sold_to_id
WHERE A.ts_adjd_qty IS NULL --we are missing a row
OR (A.ts_adjd_qty <> B.ts_adjd_qty ) --we have dif qty

That should give you a good idea of where the discrepancies lie & then you can try an track them down from there.