It's hard even to describe this one, except to say I'm having trouble
finding a SQL approach to it.  I've created a minimal type of example
to illustrate the basic idea, and so I should be able to apply any 
solution to the real data.

I have one table TBL_DETAIL whose contents I want to use to update
TBL_SUMMARY.  For the TBL_DETAIL, it's important to note that the 
number of rows and the contents of the LOCATION and TYPE columns will
never change.  The only thing that will change are the values
themselves in the AAA, BBB, CCC, and DDD columns.

The number of rows and contents of the SUMLOC column in the 
TBL_SUMMARY also will not change, but the SUMTOTAL column will
contain the calculated value from the TBL_DETAIL.  Those values come
from AAA, BBB, CCC, and DDD according to this formula:


(APPLES.AAA - (ORANGES.AAA + PEARS.AAA)) * 2 +

 APPLES.BBB - (ORANGES.BBB + PEARS.BBB) +

(APPLES.DDD - (ORANGES.DDD + PEARS.DDD)) * 2


The above formula filled in with values from the below table would look like:

For B1:

((50 - (6 + 1)) * 2) +

(60 - (10 + 2)) +

((80 - (20 + 18)) * 2)

The total of the above for B1 would be 218.


For MAIN:

((100 - (22 + 27)) * 2) +

(200 - (9 + 13)) +

((400 - (24 + 19)) * 2)

The total of the above for MAIN would be 994.


Here is the data in the TBL_DETAIL table:

LOCATION--TYPE-----AAA--BBB--CCC--DDD
B1--------APPLES---050--060--070--080
B1--------GRAPES---007--012--008--016
B1--------ORANGES--006--010--019--020
B1--------PEARS----001--002--003--018
MAIN------APPLES---100--200--300--400
MAIN------GRAPES---026--016--002--031
MAIN------ORANGES--022--009--014--024
MAIN------PEARS----027--013--008--019

And the data in the TBL_SUMMARY table before the query:


SUMLOC--SUMTOTAL
B1------00000000
MAIN----00000000


After the query, the TBL_SUMMARY table should look like:

SUMLOC--SUMTOTAL
B1------00000218
MAIN----00000994

But the big mystery is, what would the query look like?

Forgot to include the test table creation script, which might come in handy:

USE [Northwind]
GO
/****** Object:  Table [dbo].[TBL_DETAIL]    Script Date: 02/02/2013 11:27:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[TBL_DETAIL](
	[LOCATION] [varchar](10) NULL,
	[TYPE] [varchar](20) NULL,
	[AAA] [int] NULL,
	[BBB] [int] NULL,
	[CCC] [int] NULL,
	[DDD] [int] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[TBL_DETAIL] ([LOCATION], [TYPE], [AAA], [BBB], [CCC], [DDD]) VALUES (N'B1', N'APPLES', 50, 60, 70, 80)
INSERT [dbo].[TBL_DETAIL] ([LOCATION], [TYPE], [AAA], [BBB], [CCC], [DDD]) VALUES (N'B1', N'GRAPES', 7, 12, 8, 16)
INSERT [dbo].[TBL_DETAIL] ([LOCATION], [TYPE], [AAA], [BBB], [CCC], [DDD]) VALUES (N'B1', N'ORANGES', 6, 10, 19, 20)
INSERT [dbo].[TBL_DETAIL] ([LOCATION], [TYPE], [AAA], [BBB], [CCC], [DDD]) VALUES (N'B1', N'PEARS', 1, 2, 3, 18)
INSERT [dbo].[TBL_DETAIL] ([LOCATION], [TYPE], [AAA], [BBB], [CCC], [DDD]) VALUES (N'MAIN', N'APPLES', 100, 200, 300, 400)
INSERT [dbo].[TBL_DETAIL] ([LOCATION], [TYPE], [AAA], [BBB], [CCC], [DDD]) VALUES (N'MAIN', N'GRAPES', 26, 16, 2, 31)
INSERT [dbo].[TBL_DETAIL] ([LOCATION], [TYPE], [AAA], [BBB], [CCC], [DDD]) VALUES (N'MAIN', N'ORANGES', 22, 9, 14, 24)
INSERT [dbo].[TBL_DETAIL] ([LOCATION], [TYPE], [AAA], [BBB], [CCC], [DDD]) VALUES (N'MAIN', N'PEARS', 27, 13, 8, 19)
/****** Object:  Table [dbo].[TBL_SUMMARY]    Script Date: 02/02/2013 11:27:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[TBL_SUMMARY](
	[SUMLOC] [varchar](10) NULL,
	[SUMTOTAL] [int] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[TBL_SUMMARY] ([SUMLOC], [SUMTOTAL]) VALUES (N'B1', 218)
INSERT [dbo].[TBL_SUMMARY] ([SUMLOC], [SUMTOTAL]) VALUES (N'MAIN', 994)

select location
      ,(sum(case when type='APPLES' then aaa else 0 end)-sum(case when type='ORANGES' or type='PEARS' then aaa else 0 end))*2
      + sum(case when type='APPLES' then bbb else 0 end)-sum(case when type='ORANGES' or type='PEARS' then bbb else 0 end)
      +(sum(case when type='APPLES' then ddd else 0 end)-sum(case when type='ORANGES' or type='PEARS' then ddd else 0 end))*2
  from tbl_detail
 group by location

UPDATE s
SET s.SUMTOTAL = s.SUMTOTAL + d.Total
FROM TBL_SUMMARY s
INNER JOIN (SELECT location,
            (SUM(CASE WHEN TYPE = 'APPLES' THEN AAA ELSE -1 * AAA END) * 2) +
            SUM(CASE WHEN TYPE = 'APPLES' THEN BBB ELSE -1 * BBB END) +
            (SUM(CASE WHEN TYPE = 'APPLES' THEN DDD ELSE -1 * DDD END) * 2) AS Total
            FROM TBL_DETAIL
            WHERE TYPE IN ('APPLES','ORANGES','PEARS')
            GROUP BY location
             )d
ON d.location = s.location

quote:

---

Originally posted by James K

Do you have only 3 possible values in the TYPE column and four columns, or is this just an example (and the real data has many more TYPEs and many more columns?

---

quote:

---

Originally posted by bitsmed

Maybe this will work:

select location
      ,(sum(case when type='APPLES' then aaa else 0 end)-sum(case when type='ORANGES' or type='PEARS' then aaa else 0 end))*2
      + sum(case when type='APPLES' then bbb else 0 end)-sum(case when type='ORANGES' or type='PEARS' then bbb else 0 end)
      +(sum(case when type='APPLES' then ddd else 0 end)-sum(case when type='ORANGES' or type='PEARS' then ddd else 0 end))*2
  from tbl_detail
 group by location

---

quote:

---

Originally posted by visakh16

UPDATE s
SET s.SUMTOTAL = s.SUMTOTAL + d.Total
FROM TBL_SUMMARY s
INNER JOIN (SELECT location,
            (SUM(CASE WHEN TYPE = 'APPLES' THEN AAA ELSE -1 * AAA END) * 2) +
            SUM(CASE WHEN TYPE = 'APPLES' THEN BBB ELSE -1 * BBB END) +
            (SUM(CASE WHEN TYPE = 'APPLES' THEN DDD ELSE -1 * DDD END) * 2) AS Total
            FROM TBL_DETAIL
            WHERE TYPE IN ('APPLES','ORANGES','PEARS')
            GROUP BY location
             )d
ON d.location = s.location

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

---