where to find info on the Concept of SQL Tables

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

where to find info on the Concept of SQL Tables

Author

Topic

ryulite
Starting Member

13 Posts

Posted - 2006-02-07 : 03:20:26

i would like to know where can i find information on the concept of SQL like Base Table , View Table and Search Table as now i am using VS.Net 2005 and SQL Server 2005 to write a addressbook program and i am just a newbie to Sql and c#... i wish to write my sql commands in c# side before passing it into sql and may i know how to do it?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-02-07 : 03:25:52

Welcome to SQLTeam.
To learn SQL, get a good book on SQL. There are several good books listed here.
Alternative, learn from the net:
http://sqlcourse.com/intro.html
http://www.w3schools.com/sql/default.asp

----------------------------------
'KH'

ryulite
Starting Member

13 Posts

Posted - 2006-02-08 : 00:57:39

hi now i am working on a formula thing and it goes like this:

e.g:

actual= 5500000 and projected = 3550000 and difference= 1950000 and commission =?
and now i wish to calculate the commission.

and what the client wants is : 1950000 - 50000(this figure comes from the 'difference'.which means 1950000 i want to get rid of the 50000 to make it 1900000)* 1%

the formula i thought of goes like this: (actual - commission)- ((actual - commission)%100k)*0.01

and now my problem is when i express the forumula in SQL.. the answer i got is wrong. how can i express this out in SQL?

shallu1_gupta
Constraint Violating Yak Guru

394 Posts

Posted - 2006-02-08 : 01:29:29

Hi,
Whats the expected and actual value u are getting?
and the values you have substituted in the formula?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-02-08 : 01:58:32

quote:
Originally posted by ryulite
actual= 5500000 and projected = 3550000 and difference= 1950000 and commission =?
and now i wish to calculate the commission.

the formula goes like this: (actual - commission)- ((actual - commission)%100k)*0.01

Can you post the formula again ? This looks a bit weird.

----------------------------------
'KH'

ryulite
Starting Member

13 Posts

Posted - 2006-02-08 : 02:00:37

hi Shallu and KH.. i had this edited my post. is this much clearer now?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-02-08 : 02:06:29

quote:
i want to get rid of the 50000

How is this 50000 comes about ?

----------------------------------
'KH'

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-02-08 : 02:08:36

or is it you want to round down the 1950000 to 1900000 ?

----------------------------------
'KH'

ryulite
Starting Member

13 Posts

Posted - 2006-02-08 : 02:10:35

1950000 .... errmm... as very 100K = 1% commission and every 10K = 0.1% commission
is this much clearer?

so base on every 100K = 1% commission and every 10K = 0.1% commission .... i want to find out the total commission for rhis and you have to base it on the difference taken from actual - projected

ryulite
Starting Member

13 Posts

Posted - 2006-02-08 : 02:12:39

yes something like that KH

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-02-08 : 02:18:04

Is it like this ?

declare
	@actual		int,
	@projected	int

select	@actual		= 5500000,
	@projected	= 3550000

select 	((@actual - @projected) / 100000) * 100000 -- 100K
select	(@actual - @projected) % 100000	           -- remainder
-- Commission
select (((@actual - @projected) / 100000) * 100000) * 0.01	-- 1.0 %
	+ ((@actual - @projected) % 100000) * 0.001		-- 0.1 %

----------------------------------
'KH'

ryulite
Starting Member

13 Posts

Posted - 2006-02-08 : 02:19:26

is it possible to do everything into one select statement?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-02-08 : 02:23:40

the first 2 selected statement is just for illustration. The 3rd select statement calc the commission

----------------------------------
'KH'

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-02-08 : 02:29:23

>> but when i copy and paste into SQL.. it gives me null
are you using the Query Analyser to run this ?

declare
	@actual		int,
	@projected	int

select	@actual		= 5500000,
	@projected	= 3550000

-- Commission
select (((@actual - @projected) / 100000) * 100000) * 0.01	-- 1.0 %
	+ ((@actual - @projected) % 100000) * 0.001		-- 0.1 %

Result
=========
19050.000

----------------------------------
'KH'

ryulite
Starting Member

13 Posts

Posted - 2006-02-08 : 02:29:44

hi when i copy and paste this statement and add 'from sales'
i get all Null values

ryulite
Starting Member

13 Posts

Posted - 2006-02-08 : 02:30:05

yes i am

ryulite
Starting Member

13 Posts

Posted - 2006-02-08 : 02:31:36

KH.. i wish to do all the same to all the sales in the table.. and lets say inside this table i have 2 entries...

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-02-08 : 02:32:50

Post your table structure and same sample data with expected result.
Also refer to this. http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

----------------------------------
'KH'

ryulite
Starting Member

13 Posts

Posted - 2006-02-08 : 02:40:34

ID Actual Projected Commission
1 200000 180000 20
2 300000 250000 50
3 380000 300000 80
4 5500000 3550000 19050
total: 6380000

this is the expected display table structure and table structure is :
users int Allow Null- No
actual bigint Allow Null- Yes
projected bigint Allow Null- Yes
com bigint Allow Null- Yes

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-02-08 : 02:47:24

You can use the following to update the commission column.

update	yourtablename
	set	Commission	= (((Actual - Projected) / 100000) * 100000) * 0.01	-- 1.0 %
				+ ((Actual - Projected) % 100000) * 0.001		-- 0.1 %

----------------------------------
'KH'

ryulite
Starting Member

13 Posts

Posted - 2006-02-08 : 02:49:58

sorry KH.. i made a mistake.. all the data type should be in decimal and with 2 decimals places

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources