Apportion numbers to new records

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 2000 Forums

SQL Server Development (2000)

Apportion numbers to new records

Author

Topic

Timmy6
Starting Member

8 Posts

Posted - 2009-01-19 : 09:54:30

I am relatively new to SQL but have a long background in Excel, and have been running a calculation in Excel for sometime now that is becomming too labour intensive

Therefore I would like to explore another method of calculating the numbers I need

In its simplest form I need to take one table of numbers, cross reference against another table which holds percentages and split the original table onto new lines by applying the percentages from the second table

I am assuming this can be done in SQL but I cannot find any examples

Please let me know if the request is unclear

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-19 : 09:56:57

can you provide some sample data to illustrate your scenario and explain what you want with reqd output

Timmy6
Starting Member

8 Posts

Posted - 2009-01-19 : 10:00:59

No problem

Table 1

Division, Value
D1, 160
D2, 200

Table 2

Division, Region, Regional Apportionment
D1, R1, 50%
D1, R2, 30%
D1, R3, 20%

Then using this data to create a new Table

Table New

Region, Value
R1, 80
R2, 48
R3, 32

Hope thats clearer

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-19 : 10:51:06

[code]
INSERT TableNew(Region,[Value])
SELECT t2.Region,t1.Value * t2.[Regional Apportionment]/100
FROM Table1 t1
INNER JOIN Table2 t2
ON t2.Division=t1.Division
[/code]

Timmy6
Starting Member

8 Posts

Posted - 2009-01-19 : 12:06:07

Thanks for that I will try when I get home

Just to clarify what are t1 and t2 referring too are they new objects or have you just abbreviated Table 1 and Table 2 which I used in my example

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2009-01-19 : 12:19:29

T1 & T2 are "aliases" for Table1 & Table2...it's a shorthand notation that becomes useful when you work with long table names i.e....mytablenameisverylongandanythingthislongis veryhardtoreadwhenembeddedinstoredprocedures

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-19 : 12:40:31

quote:
Originally posted by AndrewMurphy

T1 & T2 are "aliases" for Table1 & Table2...it's a shorthand notation that becomes useful when you work with long table names i.e....mytablenameisverylongandanythingthislongis veryhardtoreadwhenembeddedinstoredprocedures

also when you want to use same table more than once in same query, they help you to distinguish each instance.

Timmy6
Starting Member

8 Posts

Posted - 2009-01-19 : 16:26:08

Can I use these aliases in the actual SQL statement though or do I have to replace them with the proper names

I would of thought id have to define the aliases as in VBA before I can use them

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-20 : 02:57:39

yup...you can. nope you dont need to define aliases in t-sql seperately. you can just use them in selects,updates,deletes,...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources