Difference present in a group

Transact-SQL
1

/*Hello Techie,

May some one please help me, how to acheive this.
I have 3 tables, need to find out sum on a group for two table and the difference of sum need to
to fill in third table

SG-- Common column

*/

DECLARE @T1 TABLE
(
Year VARCHAR (10),
Month VARCHAR (10),
SMC VARCHAR (10),
SG VARCHAR (10),
SP VARCHAR (10),
NPS VARCHAR (10)
)

INSERT @T1

SELECT '2015', '5' ,'4', 'A', '6621', '119.7' UNION ALL
SELECT '2014', '7' ,'1', 'A', '6621', '49.3' UNION ALL
SELECT '2014', '9' ,'1', 'A', '6650', '34.3' UNION ALL
SELECT '2015', '2' ,'1', 'A', '6650', '-641.01' UNION ALL
SELECT '2014', '12','1', 'A', '6605', '8.04' UNION ALL
SELECT '2014', '7' ,'2', 'B', '6605', '82.11' UNION ALL
SELECT '2014', '6' ,'1', 'B', '6605', '12.24' UNION ALL
SELECT '2015', '4', '6', 'B', '8888', '23.1'

SELECT * FROM @T1
-- WE need to calculate sum NPS in table @t1 for each group (group by SG)

declare @t2 table
(

MONTH VARCHAR (10),
SMC VARCHAR (10),
SG VARCHAR (10),
ASL VARCHAR (10)
)

insert @t2

SELECT '1', '4', 'A' ,'20.47' UNION ALL
SELECT '1', '4', 'A' ,'95.29' UNION ALL
SELECT '1', '4', 'A' ,'504.81' UNION ALL
SELECT '1', '5', 'A' ,'106501.63' UNION ALL
SELECT '1', '6', 'A' ,'8643.33' UNION ALL
SELECT '1', '7', 'A' ,'1728.78' UNION ALL
SELECT '1', '9', 'B' ,'1027768.35' UNION ALL
SELECT '1', '9', 'B' ,'430224.56' UNION ALL
SELECT '1', '9', 'B' ,'627892.8' UNION ALL
SELECT '1', '9', 'B', '7699.68'

SELECT * FROM @T2

-- Here we need sum ASL FOR EACH SG (GROUP BY SG)

Declare @t3 table
(
SMC VARCHAR (10),
SG VARCHAR (10),
SP VARCHAR (10),
CBP VARCHAR (10)
)

insert @t3

select '2', 'A','6621', '' union all
select '2', 'A','6621' , '' union all
select '2', 'A','6650' , '' union all
select '2', 'A','6650' , '' union all
select '2', 'A','6605' , '' union all
select '2', 'A','6605' , '' union all
select '2', 'B','6605', '' union all
select '2', 'B','8888' , '' union all
select '2', 'B','8888' , '' union all
select '2', 'B','8888' , ''

select * from @t3

-- CBP value i required to populated based on difference of sum NPS- sum ASL
-- for each SP available in a group of SG

Thanks a lot

2

Would you expect the same values of CBP for the repeated rows of SG in t3?

Here is one way to do it - it may not be exactly what you are looking for; it will give repeated values

UPDATE t3 SET 
	CBP = ISNULL(a.NPSSum,0) - ISNULL(b.ASLSum,0)
FROM
	@t3 t3
	OUTER APPLY
	(
		SELECT
			SUM(CAST(NPS AS FLOAT )) AS NPSSum
		FROM
			@t1 t1
		WHERE
			t1.SG = t3.SG
	) a
	OUTER APPLY
	(
		SELECT
			SUM(CAST(ASL AS FLOAT )) AS ASLSum
		FROM
			@t2 t2
		WHERE
			t2.SG = t3.SG
	) b;
1 Like
3

Hi James,

While running the above query i am getting this error

Msg 232, Level 16, State 2, Line 71
Arithmetic overflow error for type varchar, value = -2093467.940000.

Kindly suggest.

Thanks a lot

4

In your DDL for Table @T3, you're storing the result as a VARCHAR(10), increasing the size of this would prevent the error.

However, I would recommend using the correct data types, rather than storing everything as VARCHAR

5

Thanks a lot James and Dohsan... :smile: