UPDATE T
SET T.Final = CASE WHEN  F.SumAmt = F.SumMagicAmt 
		  THEN 
		     	CASE WHEN F.SumUnit = F.SumMagicUnits 
		  	     THEN 'GOOD' 
			     ELSE 'BAD'
			END
		  ELSE
		        'BAD'
		  END,
    T.Reason = CASE WHEN  (F.SumAmt <> F.SumMagicAmt) AND (F.SumUnit <> F.SumMagicUnits)
	            THEN  'Amounts Units Mismatch'
		   WHEN  (F.SumAmt = F.SumMagicAmt) AND (F.SumUnit <> F.SumMagicUnits)
		   THEN  'Units Mismatch'
		   WHEN  (F.SumAmt <> F.SumMagicAmt) AND (F.SumUnit = F.SumMagicUnits)
		   THEN  'Amounts Mismatch'
		   ELSE  NULL
	       END
FROM TestTable2 T
INNER JOIN 
(
  SELECT code,midvendor,oDate,Marketid ,SUM(Amount) AS [SumAmt],SUM(MagicAmount)AS [SumMagicAmt],SUM(Units) AS [SumUnit],SUM  (MagicUnits) AS [SumMagicUnits] 
  FROM TestTable2
  GROUP BY code,midvendor,oDate,Marketid
)F
   ON T.code = F.code
      AND T.midvendor = F.midvendor
      AND T.oDate = F.oDate
      AND T.Marketid = F.Marketid

quote:

---

Originally posted by kunal.mehta

declare @TEST table (
[code] [varchar](5) NOT NULL,
[midvendor] [varchar](5) NOT NULL,
[odate] [varchar](6) NOT NULL,
[orderid] [varchar](10) NULL,
[marketid] [varchar](5) NOT NULL,
[Amount] [float] NULL,
[MagicAmount] [float] NULL,
[Units] [int] NULL,
[MagicUnits] [int] NULL,
[Final] [varchar](10) NULL,
[Reason] [varchar](30) NULL
)


insert into @TEST values ('BAY1','MAD','DEC21',NULL,'1070',0,48526,NULL,8,NULL,NULL)
insert into @TEST values ('BAY1','MAD','DEC21','161711','1070',70965,0,9,NULL,NULL,NULL)
insert into @TEST values ('BAY1','MKA','DEC21',NULL,'1070',0,372419,NULL,43,NULL,NULL)
insert into @TEST values ('BAY1','MKA','DEC21','161891','1070',389392,0,49,NULL,NULL,NULL)
insert into @TEST values ('BAY1','MLD','DEC21','162102','1070',0,0,0,NULL,NULL,NULL)
insert into @TEST values ('BAY1','MLD','DEC21',NULL,'1070',0,35408,NULL,446,NULL,NULL)
insert into @TEST values ('BAY1','MLD','DEC21','M1620','1070',35408,0,446,NULL,NULL,NULL)
insert into @TEST values ('BAY1','NOD','DEC21',NULL,'1070',0,38053,NULL,359,NULL,NULL)
insert into @TEST values ('BAY1','NOD','DEC21','MO20','1070',38053,0,286,NULL,NULL,NULL)
insert into @TEST values ('BAY1','NOD','DEC21','MO21','1070',0,0,73,NULL,NULL,NULL)
insert into @TEST values ('BAY1','MTT','DEC21',NULL,'1070',0,100,NULL,5,NULL,NULL)
insert into @TEST values ('BAY1','MTT','DEC21',NULL,'1070',0,100,NULL,10,NULL,NULL)
insert into @TEST values ('BAY1','MTT','DEC21',NULL,'1070',0,100,NULL,20,NULL,NULL)
insert into @TEST values ('BAY1','MTT','DEC21','405423','1070',170,0,7,NULL,NULL,NULL)
insert into @TEST values ('BAY1','MTT','DEC21','405534','1070',130,0,28,NULL,NULL,NULL)
insert into @TEST values ('BAY1','MVT','DEC21',NULL,'1070',0,117216,NULL,223,NULL,NULL)
insert into @TEST values ('BAY1','MVT','DEC21','4055-8','1070',117216,0,223,NULL,NULL,NULL)
insert into @TEST values ('BAY1','TOM','DEC21',NULL,'1070',0,121328,NULL,118,NULL,NULL)
insert into @TEST values ('BAY1','TOM','DEC21','F4054','1070',1213,0,118,NULL,NULL,NULL)
insert into @TEST values ('BAY1','SAM','DEC21',NULL, '1070',0,425702,NULL,277,NULL,NULL)
insert into @TEST values ('BAY1','SAM','DEC21','F0556','1070',425702,0,278,NULL,NULL,NULL)

;with myCTE as
(
select code,midvendor,oDate,Marketid ,
case when ( SUM(amount) = sum(MagicAmount)) then 1 else 0 end 'AmountMatch',
case when ( SUM(units) = sum(Magicunits)) then 1 else 0 end 'UnitMatch'
from @TEST
group by code,midvendor,oDate,Marketid
)

select t.code,t.midvendor,t.oDate,t.Marketid,t.orderid,t.Amount,t.MagicAmount,t.Units,t.MagicUnits ,
case when m.AmountMatch + m.UnitMatch=2 then 'Good' else 'Bad' end 'Final',
case
when m.AmountMatch + m.UnitMatch=2 then null
when m.AmountMatch + m.UnitMatch=0 then 'Amounts Units Mis Match'
else case when m.AmountMatch = 1 then 'Amounts Mis Match' else 'Units Mis Match' end
end 'Reason'

from @TEST t
left outer join myCTE m
on t.code = m.code and t.midvendor = m.midvendor and t.oDate=m.odate and t.Marketid=m.marketid

---

Update T set T.Final=case when a1=a2 and a3=a4 then 'Good' else 'Bad' end from 
(select *,sum(amount)over(partition by code,midvendor,odate,marketid)a1,
sum(MagicAmount)over(partition by code,midvendor,odate,marketid)a2,
sum(units)over(partition by code,midvendor,odate,marketid)a3,
sum(Magicunits)over(partition by code,midvendor,odate,marketid)a4
 from testtable2)T