Column Update - Select - All Rows Updated

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.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Column Update - Select - All Rows Updated

Author

Topic

Jgr1
Starting Member

8 Posts

Posted - 2009-01-21 : 19:34:29

Hello Anyone! I have finally eliminated my pesky error (subquery returned > 1 row) by adding WHERE EXISTS select, however, filter on date (see hardcoded date below) and other filters, is ignored. Instead of the rows containing this date expected to be updated with individual calculated amount (actamt) for each sp_fund_report, all rows of all dates (the entire table) are updated with the final amount. I have tried every convoluted possible solution with joins, etc., to no avail.
I would greatly appreciate any input. I am fairly new to t-sql,(not to development).
Problem:
I need to accumulate a sum of column sp_end_bal for EACH record of a sp_fund+sp_report value, which is grouped, in sp_end_bal_d table for a specific date, which I have hardcoded for the moment. There are multiple 'sp_fund_report' values for a single date. The select and grouping works perfectly, but UPDATE and SET value functionality is well, difficult!

Here it is:
UPDATE sp_end_bal_d
SET sp_end_bal = sp_end_bal +
(SELECT SUM(b.p1 + b.p2 + b.p3 + b.p4 + b.p5 + b.p6 + b.p7 + b.p8 + b.p9 + b.p10 + b.p11 + b.p12 + b.opening_bal) actamt
FROM sp_bs_table as a
JOIN gl_actuals_5 as b on a.sp_bs_acct = left(b.internal_acct,24)
WHERE EXISTS
(SELECT sp_fund_report FROM sp_end_bal_d d
WHERE d.sp_end_bal_date = '20060701' and d.sp_fund_report = (a.sp_fund+a.sp_report)
and a.sp_fund+a.sp_report like '0100%' /*test only*/
GROUP BY d.sp_fund_report)
)
Thanks in advance to whomever!

thiyagu_rind
Starting Member

46 Posts

Posted - 2009-01-21 : 23:46:04

To make more clear, Can you send me the tables with some sample data.

Regards
Thiyagarajan
www.sqlhunt.blogspot.om

Jgr1
Starting Member

8 Posts

Posted - 2009-01-22 : 11:23:29

Certainly! Thanks!
Anything else needed?

Primary table sp_bs_table join to gl_actuals_5 to sum the (p1-p12+opening balance) fields and update the field shown in sp_end_bal_d.

Here are structures and samples:
SP_BS_TABLE

1.3.1 CREATE TABLE [sp_bs_table] (
[sp_bs_acct] [char] (24) COLLATE SQL_Latin1_General_CP437_BIN NOT NULL ,
[sp_fund] [char] (4) COLLATE SQL_Latin1_General_CP437_BIN NOT NULL ,
[sp_report] [char] (2) COLLATE SQL_Latin1_General_CP437_BIN NOT NULL ,
[sp_category] [char] (2) COLLATE SQL_Latin1_General_CP437_BIN NOT NULL ,
[qkey] [int] IDENTITY (1, 1) NOT NULL
) ON [PRIMARY]
select sp_bs_acct, sp_fund, sp_report from sp_bs_table
order by sp_fund, sp_report
sp_bs_table Sample:

0100 1 010099999999991113099999
0100 1 010099999999991115099999
0100 1 010099999999991111099999
0100 1 010099999999991118099999
0100 1 010099999999991117099999
0100 1 010099999999991181099999
0100 2 010099999999991184099999
0100 2 010099999999991182099999
0100 2 010099999999991183099999
0100 2 110099999999991119099999
1100 1 110099999999991115099999
1100 1 110099999999991113099999
1100 1 110099999999991118099999
1100 1 110099999999991117099999
1100 1 110099999999991111099999
1100 1 110099999999991182099999
1100 2 110099999999991181099999
1100 2 110099999999991184099999
1100 2 110099999999991183099999

1.4 GL_ACTUALS_8
The G/L actuals tables for the given year
1.4.1 CREATE TABLE [gl_actuals_8] (
[internal_acct] [char] (60) COLLATE SQL_Latin1_General_CP437_BIN NOT NULL ,
[data_file] [char] (2) COLLATE SQL_Latin1_General_CP437_BIN NOT NULL ,
[data_type] [char] (4) COLLATE SQL_Latin1_General_CP437_BIN NOT NULL ,
[year] [char] (4) COLLATE SQL_Latin1_General_CP437_BIN NOT NULL ,
[p1] [decimal](15, 2) NULL ,
[p2] [decimal](15, 2) NULL ,
[p3] [decimal](15, 2) NULL ,
[p4] [decimal](15, 2) NULL ,
[p5] [decimal](15, 2) NULL ,
[p6] [decimal](15, 2) NULL ,
[p7] [decimal](15, 2) NULL ,
[p8] [decimal](15, 2) NULL ,
[p9] [decimal](15, 2) NULL ,
[p10] [decimal](15, 2) NULL ,
[p11] [decimal](15, 2) NULL ,
[p12] [decimal](15, 2) NULL ,
[p13] [decimal](15, 2) NULL ,
[p14] [decimal](15, 2) NULL ,
[iacct_1] [int] NOT NULL ,
[iacct_2] [int] NOT NULL ,
[iacct_3] [int] NOT NULL ,
[iacct_4] [int] NOT NULL ,
[iacct_5] [int] NOT NULL ,
[iacct_6] [int] NOT NULL ,
[iacct_7] [int] NOT NULL ,
[iacct_8] [int] NOT NULL ,
[iacct_9] [int] NOT NULL ,
[iacct_10] [int] NOT NULL ,
[iacct_11] [int] NOT NULL ,
[iacct_12] [int] NOT NULL ,
[iacct_13] [int] NOT NULL ,
[iacct_14] [int] NOT NULL ,
[iacct_15] [int] NOT NULL ,
[iacct_16] [int] NOT NULL ,
[iacct_17] [int] NOT NULL ,
[iacct_18] [int] NOT NULL ,
[iacct_19] [int] NOT NULL ,
[iacct_20] [int] NOT NULL ,
[iacct_21] [int] NOT NULL ,
[iacct_22] [int] NOT NULL ,
[iacct_23] [int] NOT NULL ,
[iacct_24] [int] NOT NULL ,
[iacct_25] [int] NOT NULL ,
[opening_bal] [decimal](15, 2) NULL ,
[unposted_bal] [decimal](15, 2) NULL ,
[version_no] [smallint] NOT NULL ,
[qkey] [int] IDENTITY (1, 1) NOT NULL
) ON [PRIMARY]
GO

A small Sample (gl_actuals_5):

"select internal_acct,year,p1,p2,p3,p4,opening_bal from gl_actuals_5
where internal_acct like '0100%' or internal_acct like '1100%'
order by internal_acct"

010099999999992823099999001000000000000000000000000000000000 2005 .00 .00 .00 .00 .00
010099999999992889099999001000000000000000000000000000000000 2005 2100438.61 1590583.80 125078.32 327870.69 .00
010099999999993614099999001000000000000000000000000000000000 2005 .00 .00 .00 .00 .00
010099999999999001099999001000000000000000000000000000000000 2005 -2100438.61 -1590583.80 -125078.32 -327870.69 .00
110021100432000202099999001000000000000000000000000000000000 2005 3126.48 2977.61 3424.24 -595.50 .00
110021100432000203099999001000000000000000000000000000000000 2005 3445.65 3289.44 3779.23 3532.73 .00
110021100432000207099999001000000000000000000000000000000000 2005 843.08 -1056.52 .00 .00 .00
110021100432000228099999001000000000000000000000000000000000 2005 3672.47 3497.59 4022.25 3760.48 .00
110021100432000255099999001000000000000000000000000000000000 2005 5273.10 5075.31 5805.20 5300.40 .00

1.2 SP_END_BAL_TABLE
Table that stores ending cash balance for each day.
1.2.1 CREATE TABLE [sp_end_bal_table] (
[sp_fund] [char] (4) COLLATE SQL_Latin1_General_CP437_BIN NOT NULL ,
[sp_ending_bal] [decimal](19, 2) NULL ,
[qkey] [int] IDENTITY (1, 1) NOT NULL
) ON [PRIMARY]
GO

" select * from sp_end_bal_d
where sp_end_bal_date = '20060701' or sp_end_bal_date = '20060703'
order by sp_fund_report"

Small extract Sample:
01001 20070703 22848217.09 170186
01002 20070703 8814597.68 170266
11001 20070703 790849.09 170187
11002 20070703 3389608.26 170267
13001 20070703 830286.88 170188
13002 20070703 334452.98 170268
13901 20070703 173098.73 170189
14001 20070703 532365.30 170190
14002 20070703 2747623.78 170269
14101 20070703 9216.55 170191
14501 20070703 331496.23 170192
14601 20070703 19648.42 170193

THANK YOU FOR YOUR HELP Thiyagarajan!
Regards, JG

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-22 : 11:30:25

which column contain date values to identity p1,...p12?

Jgr1
Starting Member

8 Posts

Posted - 2009-01-22 : 12:37:39

You are referring to the gl_actuals_5 table, where '5' refers to year, in this case 2005. Fields p1, p2.. will be an accumulation of sums by internal_account for the entire year, which in this case is 2005 test data.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-22 : 12:45:05

does that mean your table will vary for each year? why is your design like this?

Jgr1
Starting Member

8 Posts

Posted - 2009-01-22 : 12:56:32

No, table structure does not vary. There exists the same table structure for each year (tables are named gl_actuals_x, where 'x' represents the year(0-9). We would only be using one table each year (for current year) - this year is gl_actuals_9. These are financial actuals, and if we combined them into the same table, there would be extreme cost, and it would be highly illogical to do so. We are only updating, reporting from one year at a time. I am only using older test data (2005) in this example. We are able to summarize by internal_account all periods (p1,p2..) for the entire year to capture the running totals for these account numbers. This structure has been in place for many years.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-22 : 13:06:43

ok..what should be sample output you're looking at?

Jgr1
Starting Member

8 Posts

Posted - 2009-01-22 : 13:15:26

The output would be a summarized $ amount, to update field sp_end_bal of sp_end_bal_d table for each record of sp_fund_report of sp_end_bal_d table. The summarized $ amount is derived from an accumulation of valid account numbers (sp_bs_acct = internal_account)of gl_account_5(p1+p2+..) fields by sp_fund+sp_report of sp_bs_table which match sp_fund_report of sp_end_bal_d.
JG

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-22 : 13:18:56

sorry but you're still not providing reqd info to give us solution. see this for how to post a question

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Jgr1
Starting Member

8 Posts

Posted - 2009-01-22 : 13:19:53

Here is sample output - expected update of 'sp_end_bal', col3 of sp_fund_report records for selected day '20060701':
col1 - 'sp_fund_report'; col2 - 'sp_end_bal_date'; col 3 - 'sp_end_bal'; col 4 qkey

01001 20060701 .00 39144
01002 20060701 .00 39145
11001 20060701 .00 39146
11002 20060701 .00 39147
13001 20060701 .00 39148
13002 20060701 .00 39149

Jgr1
Starting Member

8 Posts

Posted - 2009-01-22 : 13:25:36

I thought I sent all the information needed in the format specified. sorry.

Jgr1
Starting Member

8 Posts

Posted - 2009-01-22 : 13:36:24

I have reviewed the 'how to post', and I believe I sent the problem, expected output, DDL, sample data. do you wish me to resubmit all together?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources