arrays in SQL? - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

arrays in SQL?

Author

Topic

SqlGirl87
Starting Member

26 Posts

Posted - 2012-07-31 : 06:02:18

Hi Guys, lass in distress here!

I'm really stuck with something and I'm hoping someone can pleas help?

I'm not very advanced with SQL, joins is about as much as I know and I need to produce a script to recalculate account balances from their transactions; while I can do this for each one, there are over 2,000 account codes with sub accounts that need doing.

I was wondering if this can be done is some form of array or if that is supported din SQL?

Basically, there is a table ACCHEAD (account) header which has the ACC_ID fro the account id, YR_ID for the financial year, a YTD_VAL for the year to date value, and then m1_val, m2_val, m3_val etc to m12_val where the sum total value for each months transaction is stored.

The m1_val - m12_val as calculated from the ACCTRAN table where the transactions as stored. ACCTRAN will have the fields ACC_ID, gross, net, vat, YM_ID which is the year month, I.E April 2012 will be '201204'

So I am looking to produce something that can be run and it will recalculate the m1_val - m12_val values for each individual account based on its sum(gross) for the YM_ID corresponding to the correct Mx_VAL field.

Is Anyone able to assist with this please :)?

Thanks!

Grace

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-07-31 : 06:09:37

A table is an array.

This will give the monthly totals for each account
select ACC_ID, YM_ID, gross = sum(gross), net = sum(net), vat=sum(vat)
from ACCTRAN
group by ACC_ID, YM_ID

to flatten it
select ACC_ID, yr=YM_ID/100,
grossmth1 = sum(case when YM_ID%100 = 1 then gross else 0 end) ,
grossmth2 = sum(case when YM_ID%100 = 2 then gross else 0 end) ,
...
from ACCTRAN
group by ACC_ID, YM_ID/100

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

SqlGirl87
Starting Member

26 Posts

Posted - 2012-07-31 : 08:55:02

quote:
Originally posted by nigelrivett

A table is an array.

This will give the monthly totals for each account
select ACC_ID, YM_ID, gross = sum(gross), net = sum(net), vat=sum(vat)
from ACCTRAN
group by ACC_ID, YM_ID

to flatten it
select ACC_ID, yr=YM_ID/100,
grossmth1 = sum(case when YM_ID%100 = 1 then gross else 0 end) ,
grossmth2 = sum(case when YM_ID%100 = 2 then gross else 0 end) ,
...
from ACCTRAN
group by ACC_ID, YM_ID/100

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Thanks for reply but I need something a little more complex than that.

Creating the select statments to work different values out isn't the problem (the NET & vat can be ignored for this one)

What I need is something like the below that will calculate for EVERY account in one go without defining the accounts one by one:


begin transaction

UPDATE ACCHEAD
SET M1_VALL = sum(GROSS) from ACCTRAN where YM_ID = '201201'
SET M2_VALL = sum(GROSS) from ACCTRAN where YM_ID = '201202'
SET M3_VALL = sum(GROSS) from ACCTRAN where YM_ID = '201203'
SET M4_VALL = sum(GROSS) from ACCTRAN where YM_ID = '201204'
SET M5_VALL = sum(GROSS) from ACCTRAN where YM_ID = '201205'
SET M6_VALL = sum(GROSS) from ACCTRAN where YM_ID = '201206'
SET M7_VALL = sum(GROSS) from ACCTRAN where YM_ID = '201207'
SET M8_VALL = sum(GROSS) from ACCTRAN where YM_ID = '201208'
SET M9_VALL = sum(GROSS) from ACCTRAN where YM_ID = '201209'
SET M10_VALL = sum(GROSS) from ACCTRAN where YM_ID = '2012010'
SET M11_VALL = sum(GROSS) from ACCTRAN where YM_ID = '2012011'
SET M12_VALL = sum(GROSS) from ACCTRAN where YM_ID = '2012012'

rollback transaction
--commit transaction

I can add a WHERE ACC_ID = '[account code]' at the end of each of the above for it to work them out individualy but I need something that will work it
out for the 2,000+ accounts in go which is where I am stuck on, especially as the next stage is to do with acummulated values and account levels.

Once I have established that the account header has the correct monthly values I can easily recalculate the year to date balances from using the below I believe...


UPDATE ACCHEAD SET YTD_VALL = SUM(ISNULL(M1_VAL,0) + ISNULL(M2_VAL,0) + ISNULL(M3_VAL,0) + ISNULL(M4_VAL,0) + ISNULL(M5_VAL,0) + ISNULL(M6_VAL,0) + ISNULL(M7_VAL,0)
  + ISNULL(M8_VAL,0) + ISNULL(M9_VAL,0) + ISNULL(M10_VAL,0) + ISNULL(M11_VAL,0) + ISNULL(M12_VAL,0) WHERE year ='2012'

I believe that SQL is smart enough in this case to not require a where clause for the accoutnt type as it is the same table it will work them sum value out for each code individually?

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2012-07-31 : 09:13:33

Looks like your data model is a bit off (anytime you see COLNAME<n> that's the clue) but you should be able to work it out from what you've been given so far.
Use the sum & group by Nigel gave you and update M1_VAL = grossmth1, M2_VAL=grossmth2 etc. from his examples.
The final step is to make sure you join the account numbers.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-07-31 : 09:28:38

Something like this?

UPDATE		ah
SET		ah.M1_VALL = w.M1_VALL,
		ah.M2_VALL = w.M2_VALL,
		ah.M3_VALL = w.M3_VALL,
		ah.M4_VALL = w.M4_VALL,
		ah.M5_VALL = w.M5_VALL,
		ah.M6_VALL = w.M6_VALL,
		ah.M7_VALL = w.M7_VALL,
		ah.M8_VALL = w.M8_VALL,
		ah.M9_VALL = w.M9_VALL,
		ah.M10_VALL = w.M10_VALL,
		ah.M11_VALL = w.M11_VALL,
		ah.M12_VALL = w.M12_VALL
FROM		dbo.AccHead AS ah
INNER JOIN	(
			SELECT		AccountCode,
					SUM(CASE WHEN YM_ID = '201201' THEN Gross ELSE 0 END AS M1_VALL,
					SUM(CASE WHEN YM_ID = '201202' THEN Gross ELSE 0 END AS M2_VALL,
					SUM(CASE WHEN YM_ID = '201203' THEN Gross ELSE 0 END AS M3_VALL,
					SUM(CASE WHEN YM_ID = '201204' THEN Gross ELSE 0 END AS M4_VALL,
					SUM(CASE WHEN YM_ID = '201205' THEN Gross ELSE 0 END AS M5_VALL,
					SUM(CASE WHEN YM_ID = '201206' THEN Gross ELSE 0 END AS M6_VALL,
					SUM(CASE WHEN YM_ID = '201207' THEN Gross ELSE 0 END AS M7_VALL,
					SUM(CASE WHEN YM_ID = '201208' THEN Gross ELSE 0 END AS M8_VALL,
					SUM(CASE WHEN YM_ID = '201209' THEN Gross ELSE 0 END AS M9_VALL,
					SUM(CASE WHEN YM_ID = '201210' THEN Gross ELSE 0 END AS M10_VALL,
					SUM(CASE WHEN YM_ID = '201211' THEN Gross ELSE 0 END AS M11_VALL,
					SUM(CASE WHEN YM_ID = '201212' THEN Gross ELSE 0 END AS M12_VALL
			FROM		dbo.AccTran
			GROUP BY	AccountCode
		) AS w ON w.AccountCode = ah.ACC_ID

N 56°04'39.26"
E 12°55'05.63"

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-07-31 : 10:16:13

quote:
Originally posted by SqlGirl87

quote:
Originally posted by nigelrivett

A table is an array.

This will give the monthly totals for each account
select ACC_ID, YM_ID, gross = sum(gross), net = sum(net), vat=sum(vat)
from ACCTRAN
group by ACC_ID, YM_ID

to flatten it
select ACC_ID, yr=YM_ID/100,
grossmth1 = sum(case when YM_ID%100 = 1 then gross else 0 end) ,
grossmth2 = sum(case when YM_ID%100 = 2 then gross else 0 end) ,
...
from ACCTRAN
group by ACC_ID, YM_ID/100

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Thanks for reply but I need something a little more complex than that.

Creating the select statments to work different values out isn't the problem (the NET & vat can be ignored for this one)

What I need is something like the below that will calculate for EVERY account in one go without defining the accounts one by one:

begin transaction

UPDATE ACCHEAD
SET M1_VALL = sum(GROSS) from ACCTRAN where YM_ID = '201201'
SET M2_VALL = sum(GROSS) from ACCTRAN where YM_ID = '201202'
SET M3_VALL = sum(GROSS) from ACCTRAN where YM_ID = '201203'
SET M4_VALL = sum(GROSS) from ACCTRAN where YM_ID = '201204'
SET M5_VALL = sum(GROSS) from ACCTRAN where YM_ID = '201205'
SET M6_VALL = sum(GROSS) from ACCTRAN where YM_ID = '201206'
SET M7_VALL = sum(GROSS) from ACCTRAN where YM_ID = '201207'
SET M8_VALL = sum(GROSS) from ACCTRAN where YM_ID = '201208'
SET M9_VALL = sum(GROSS) from ACCTRAN where YM_ID = '201209'
SET M10_VALL = sum(GROSS) from ACCTRAN where YM_ID = '2012010'
SET M11_VALL = sum(GROSS) from ACCTRAN where YM_ID = '2012011'
SET M12_VALL = sum(GROSS) from ACCTRAN where YM_ID = '2012012'

rollback transaction
--commit transaction

I can add a WHERE ACC_ID = '[account code]' at the end of each of the above for it to work them out individualy but I need something that will work it
out for the 2,000+ accounts in go which is where I am stuck on, especially as the next stage is to do with acummulated values and account levels.

Once I have established that the account header has the correct monthly values I can easily recalculate the year to date balances from using the below I believe...
UPDATE ACCHEAD SET YTD_VALL = SUM(ISNULL(M1_VAL,0) + ISNULL(M2_VAL,0) + ISNULL(M3_VAL,0) + ISNULL(M4_VAL,0) + ISNULL(M5_VAL,0) + ISNULL(M6_VAL,0) + ISNULL(M7_VAL,0)
  + ISNULL(M8_VAL,0) + ISNULL(M9_VAL,0) + ISNULL(M10_VAL,0) + ISNULL(M11_VAL,0) + ISNULL(M12_VAL,0) WHERE year ='2012'
I believe that SQL is smart enough in this case to not require a where clause for the accoutnt type as it is the same table it will work them sum value out for each code individually?

Doesn't that do it? look at the second query I gave.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

SqlGirl87
Starting Member

26 Posts

Posted - 2012-08-06 : 05:16:02

quote:
Originally posted by nigelrivett
quote:
Originally posted by SqlGirl87

quote:
Originally posted by nigelrivett

A table is an array.

This will give the monthly totals for each account
select ACC_ID, YM_ID, gross = sum(gross), net = sum(net), vat=sum(vat)
from ACCTRAN
group by ACC_ID, YM_ID

to flatten it
select ACC_ID, yr=YM_ID/100,
grossmth1 = sum(case when YM_ID%100 = 1 then gross else 0 end) ,
grossmth2 = sum(case when YM_ID%100 = 2 then gross else 0 end) ,
...
from ACCTRAN
group by ACC_ID, YM_ID/100

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Thanks for reply but I need something a little more complex than that.

Creating the select statments to work different values out isn't the problem (the NET & vat can be ignored for this one)

What I need is something like the below that will calculate for EVERY account in one go without defining the accounts one by one:

begin transaction

UPDATE ACCHEAD
SET M1_VALL = sum(GROSS) from ACCTRAN where YM_ID = '201201'
SET M2_VALL = sum(GROSS) from ACCTRAN where YM_ID = '201202'
SET M3_VALL = sum(GROSS) from ACCTRAN where YM_ID = '201203'
SET M4_VALL = sum(GROSS) from ACCTRAN where YM_ID = '201204'
SET M5_VALL = sum(GROSS) from ACCTRAN where YM_ID = '201205'
SET M6_VALL = sum(GROSS) from ACCTRAN where YM_ID = '201206'
SET M7_VALL = sum(GROSS) from ACCTRAN where YM_ID = '201207'
SET M8_VALL = sum(GROSS) from ACCTRAN where YM_ID = '201208'
SET M9_VALL = sum(GROSS) from ACCTRAN where YM_ID = '201209'
SET M10_VALL = sum(GROSS) from ACCTRAN where YM_ID = '2012010'
SET M11_VALL = sum(GROSS) from ACCTRAN where YM_ID = '2012011'
SET M12_VALL = sum(GROSS) from ACCTRAN where YM_ID = '2012012'

rollback transaction
--commit transaction

I can add a WHERE ACC_ID = '[account code]' at the end of each of the above for it to work them out individualy but I need something that will work it
out for the 2,000+ accounts in go which is where I am stuck on, especially as the next stage is to do with acummulated values and account levels.

Once I have established that the account header has the correct monthly values I can easily recalculate the year to date balances from using the below I believe...
UPDATE ACCHEAD SET YTD_VALL = SUM(ISNULL(M1_VAL,0) + ISNULL(M2_VAL,0) + ISNULL(M3_VAL,0) + ISNULL(M4_VAL,0) + ISNULL(M5_VAL,0) + ISNULL(M6_VAL,0) + ISNULL(M7_VAL,0)
  + ISNULL(M8_VAL,0) + ISNULL(M9_VAL,0) + ISNULL(M10_VAL,0) + ISNULL(M11_VAL,0) + ISNULL(M12_VAL,0) WHERE year ='2012'
I believe that SQL is smart enough in this case to not require a where clause for the accoutnt type as it is the same table it will work them sum value out for each code individually?
Doesn't that do it? look at the second query I gave.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Hi,

Sorry, it's been so long since I used SQL properly and been thrown into to something to cover for someone who has gone on long term sick; I didn't understand the /100 or %100 or how to build a full script around that - but thanks very much for the reply!

SqlGirl87
Starting Member

26 Posts

Posted - 2012-08-06 : 05:26:03

quote:

Originally posted by SwePeso

Something like this?

UPDATE		ah
SET		ah.M1_VALL = w.M1_VALL,
		ah.M2_VALL = w.M2_VALL,
		ah.M3_VALL = w.M3_VALL,
		ah.M4_VALL = w.M4_VALL,
		ah.M5_VALL = w.M5_VALL,
		ah.M6_VALL = w.M6_VALL,
		ah.M7_VALL = w.M7_VALL,
		ah.M8_VALL = w.M8_VALL,
		ah.M9_VALL = w.M9_VALL,
		ah.M10_VALL = w.M10_VALL,
		ah.M11_VALL = w.M11_VALL,
		ah.M12_VALL = w.M12_VALL
FROM		dbo.AccHead AS ah
INNER JOIN	(
			SELECT		AccountCode,
					SUM(CASE WHEN YM_ID = '201201' THEN Gross ELSE 0 END AS M1_VALL,
					SUM(CASE WHEN YM_ID = '201202' THEN Gross ELSE 0 END AS M2_VALL,
					SUM(CASE WHEN YM_ID = '201203' THEN Gross ELSE 0 END AS M3_VALL,
					SUM(CASE WHEN YM_ID = '201204' THEN Gross ELSE 0 END AS M4_VALL,
					SUM(CASE WHEN YM_ID = '201205' THEN Gross ELSE 0 END AS M5_VALL,
					SUM(CASE WHEN YM_ID = '201206' THEN Gross ELSE 0 END AS M6_VALL,
					SUM(CASE WHEN YM_ID = '201207' THEN Gross ELSE 0 END AS M7_VALL,
					SUM(CASE WHEN YM_ID = '201208' THEN Gross ELSE 0 END AS M8_VALL,
					SUM(CASE WHEN YM_ID = '201209' THEN Gross ELSE 0 END AS M9_VALL,
					SUM(CASE WHEN YM_ID = '201210' THEN Gross ELSE 0 END AS M10_VALL,
					SUM(CASE WHEN YM_ID = '201211' THEN Gross ELSE 0 END AS M11_VALL,
					SUM(CASE WHEN YM_ID = '201212' THEN Gross ELSE 0 END AS M12_VALL
			FROM		dbo.AccTran
			GROUP BY	AccountCode
		) AS w ON w.AccountCode = ah.ACC_ID

N 56°04'39.26"
E 12°55'05.63"

Thanks, that was really helpful and has almost done it. Been googling and looking for a 'fix' but hit a brick wall.

The script seems to build a result like:

YM_ID M1_VALL M2_VALL M3_VALL M4_VALL M5_VALL etc etc
201201 VALUE NULL NULL NULL NULL
201202 NULL VALUE NULL NULL NULL
201203 NULL NULL VALUE NULL NULL
201204 NULL NULL NULL VALUE NULL
201205 NULL NULL NULL NULL VALUE

The update then seems to be taking the first value only as only M1 is being updated in the table, everything else is getting NULL and I can; work out how to put it all into 1 line per account code

Thanks

Grace

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-08-06 : 08:09:35

No it does. My case constructors above will NOT produce a NULL value at all.
I don't think you copied and pastes the correct solution.

N 56°04'39.26"
E 12°55'05.63"

SqlGirl87
Starting Member

26 Posts

Posted - 2012-08-06 : 10:21:00

quote:
Originally posted by SwePeso

No it does. My case constructors above will NOT produce a NULL value at all.
I don't think you copied and pastes the correct solution.

N 56°04'39.26"
E 12°55'05.63"

It won't work with a zero value, when I try it it doesn't update the values for the periods after 1, so that is why I used NULL.
I think I have it working now though:

I'm now assuming you meant to close the brackets around END? You hadn't closed them so I wrote it as


CASE WHEN YM_ID = '201203' THEN SUM(Gross) ELSE 0 END AS M3_VALL

Because I was getting an incorrect use of SUM error but I have now run it as


SUM(CASE WHEN YM_ID = '201203' THEN Gross ELSE NULL END) AS M3_VALL

for each period and it seems to be working!

massively appreciate your help, couldn't have done it without you.

Thanks to everyone,

Grace

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-06 : 10:27:14

it should be
SUM(CASE WHEN YM_ID = '201203' THEN Gross ELSE 0 END) AS M3_VALL

and so far as you do it correctly for all related fields it wont throw any error

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

SqlGirl87
Starting Member

26 Posts

Posted - 2012-08-06 : 12:06:03

quote:
Originally posted by visakh16

it should be
SUM(CASE WHEN YM_ID = '201203' THEN Gross ELSE 0 END) AS M3_VALL

and so far as you do it correctly for all related fields it wont throw any error

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

I appreciate the reply, and perhaps I might be doing something wrong with the syntax somewhere else but when I do it with a 0 only the first column is updated with the sum value, every other one gets the value 0 inserted; if I use NULL then they are all updated with the correct period sum value. After testing it all looks correct so will stick to this method for now.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources