SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Summing Across Rows Based on Formula
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

BobRoberts
Posting Yak Master

USA
107 Posts

Posted - 02/01/2013 :  16:56:50  Show Profile  Reply with Quote

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)



Edited by - BobRoberts on 02/02/2013 12:34:29

James K
Flowing Fount of Yak Knowledge

3568 Posts

Posted - 02/01/2013 :  17:33:29  Show Profile  Reply with Quote
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?
Go to Top of Page

bitsmed
Constraint Violating Yak Guru

323 Posts

Posted - 02/01/2013 :  17:45:07  Show Profile  Reply with Quote
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

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 02/02/2013 :  03:06:33  Show Profile  Reply with Quote

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/

Go to Top of Page

BobRoberts
Posting Yak Master

USA
107 Posts

Posted - 02/02/2013 :  12:44:17  Show Profile  Reply with Quote
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?



That's a good question. The real data has 9 types and 12 columns, but they never vary - they are always there and in the same sequence, so I figured that reducing it to 3 TYPE's and 4 columns would retain the essence of the task.
Go to Top of Page

BobRoberts
Posting Yak Master

USA
107 Posts

Posted - 02/02/2013 :  13:06:24  Show Profile  Reply with Quote
Yes, your solution works perfectly in coming up with the column counts, but part of the problem as stated is updating the TBL_SUMMARY table with those counts. I've tried a few things, and I'm having some trouble doing that.



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



Go to Top of Page

BobRoberts
Posting Yak Master

USA
107 Posts

Posted - 02/02/2013 :  13:44:05  Show Profile  Reply with Quote
Yes, this one does the trick completely. Thanks once again.


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/



Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 02/03/2013 :  10:58:53  Show Profile  Reply with Quote
welcome

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

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000