CODEWITHSUNDEEP
sql-serverstored-procedurescoalesce
user1571352
user1571352

Reputation: 139

coalesce two records into one

I have a table that stores two values; 'total' and 'owing' for each customer. Data is uploaded to the table using two files, one that brings in 'total' and the other brings in 'owing'. This means I have two records for each customerID:

customerID:--------Total:--------- Owing:

1234----------------  1000----------NULL

1234-----------------NULL-----------200

I want to write a stored procedure that merges the two records together:

customerID:--------Total:--------- Owing:

1234----------------  1000----------200

I have seen examples using COALESCE so put together something like this:

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

--Variable declarations

DECLARE @customer_id varchar(20)
DECLARE @total decimal(15,8)
DECLARE @owing decimal(15,8)
DECLARE @customer_name_date varchar(255)
DECLARE @organisation varchar(4)
DECLARE @country_code varchar(2)
DECLARE @created_date datetime

--Other Variables
DECLARE @totals_staging_id int

--Get the id of the first row in the staging table
SELECT @totals_staging_id = MIN(totals_staging_id)
from TOTALS_STAGING

--iterate through the staging table
WHILE @totals_staging_id is not null
BEGIN

update TOTALS_STAGING

SET 
total = coalesce(@total, total),
owing = coalesce(@owing, owing)

where totals_staging_id = @totals_staging_id

END
END

Any Ideas?

Upvotes: 0

Views: 525

Answers (4)

Dan McCann
Dan McCann

Reputation: 403

This should work:

SELECT CustomerID, 
       COALESCE(total1, total2) AS Total, 
       COALESCE(owing1, owing2) AS Owing
FROM 
(SELECT row1.CustomerID AS CustomerID,
        row1.Total AS total1,
        row2.Total AS total2,
        row1.Owing AS owing1,
        row2.Owing AS owing2
FROM YourTable row1 INNER JOIN YourTable row2 ON row1.CustomerID = row2.CustomerID
WHERE row1.Total IS NULL AND row2.Total IS NOT NULL) temp
--Note:  Alter the WHERE clause as necessary to ensure row1 and row2 are unique.

...but note that you'll need some mechanism to ensure row1 and row2 are unique. My WHERE clause is an example based on the data you provided. You'll need to tweak this to add something more specific to your business rules.

Upvotes: 0

Ravi Singh
Ravi Singh

Reputation: 2080

Try this :

CREATE TABLE #Temp
(
  CustomerId int,
  Total int,
  Owing int
)

insert into #Temp
values (1024,100,null),(1024,null,200),(1025,10,null)



Create Table #Final 
(
  CustomerId int,
  Total int,
  Owing int
)

insert into #Final
values (1025,100,50)



MERGE #Final AS F
USING 
(SELECT customerid,sum(Total) Total,sum(owing) owing FROM #Temp
 group by #Temp.customerid
) AS a

ON (F.customerid = a.customerid)
WHEN MATCHED THEN UPDATE SET F.Total = F.Total + isnull(a.Total,0)
                              ,F.Owing = F.Owing + isnull(a.Owing,0)
WHEN NOT MATCHED THEN
INSERT (CustomerId,Total,Owing)
VALUES (a.customerid,a.Total,a.owing);

select * from #Final

drop table #Temp
drop table #Final

Upvotes: 0

Evgeniy Chekan
Evgeniy Chekan

Reputation: 2665

SELECT t1.customerId, t1.total, t2.owing FROM test t1 JOIN test t2 ON ( t1.customerId = t2.customerId) WHERE t1.total IS NOT NULL AND t2.owing IS NOT NULL

Wondering why aren't you just using UPDATE on a second file execution?

Upvotes: 1

Kaf
Kaf

Reputation: 33849

Except for COUNT, aggregate functions ignore null values. Aggregate functions are frequently used with the GROUP BY clause of the SELECT statement. MSDN

So you don't need to worry about null values with summing. Following will give your merging records together. Fiddle-demo

select customerId,
       sum(Total) Total,
       sum(Owing) Owing
from T
Group by customerId

Upvotes: 0

Related Questions