Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
BobRoberts
Posting Yak Master
109 Posts |
Posted - 2013-02-01 : 16:56:50
|
[code]It's hard even to describe this one, except to say I'm having troublefinding a SQL approach to it. I've created a minimal type of exampleto 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 updateTBL_SUMMARY. For the TBL_DETAIL, it's important to note that the number of rows and the contents of the LOCATION and TYPE columns willnever change. The only thing that will change are the valuesthemselves 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 willcontain the calculated value from the TBL_DETAIL. Those values comefrom 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)) * 2The 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--DDDB1--------APPLES---050--060--070--080B1--------GRAPES---007--012--008--016B1--------ORANGES--006--010--019--020B1--------PEARS----001--002--003--018MAIN------APPLES---100--200--300--400MAIN------GRAPES---026--016--002--031MAIN------ORANGES--022--009--014--024MAIN------PEARS----027--013--008--019And the data in the TBL_SUMMARY table before the query:SUMLOC--SUMTOTALB1------00000000MAIN----00000000After the query, the TBL_SUMMARY table should look like:SUMLOC--SUMTOTALB1------00000218MAIN----00000994But 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 ONGOSET QUOTED_IDENTIFIER ONGOSET ANSI_PADDING ONGOCREATE 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]GOSET ANSI_PADDING OFFGOINSERT [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 ONGOSET QUOTED_IDENTIFIER ONGOSET ANSI_PADDING ONGOCREATE TABLE [dbo].[TBL_SUMMARY]( [SUMLOC] [varchar](10) NULL, [SUMTOTAL] [int] NULL) ON [PRIMARY]GOSET ANSI_PADDING OFFGOINSERT [dbo].[TBL_SUMMARY] ([SUMLOC], [SUMTOTAL]) VALUES (N'B1', 218)INSERT [dbo].[TBL_SUMMARY] ([SUMLOC], [SUMTOTAL]) VALUES (N'MAIN', 994)[/code] |
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-02-01 : 17:33:29
|
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? |
|
|
bitsmed
Aged Yak Warrior
545 Posts |
Posted - 2013-02-01 : 17:45:07
|
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 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-02-02 : 03:06:33
|
[code]UPDATE sSET s.SUMTOTAL = s.SUMTOTAL + d.TotalFROM TBL_SUMMARY sINNER 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 )dON d.location = s.location[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
BobRoberts
Posting Yak Master
109 Posts |
Posted - 2013-02-02 : 12:44:17
|
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. |
|
|
BobRoberts
Posting Yak Master
109 Posts |
Posted - 2013-02-02 : 13:06:24
|
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
|
|
|
BobRoberts
Posting Yak Master
109 Posts |
Posted - 2013-02-02 : 13:44:05
|
Yes, this one does the trick completely. Thanks once again.quote: Originally posted by visakh16
UPDATE sSET s.SUMTOTAL = s.SUMTOTAL + d.TotalFROM TBL_SUMMARY sINNER 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 )dON d.location = s.location ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-02-03 : 10:58:53
|
welcome------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
|