SQL Query Help - 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

SQL Query Help

Author

Topic

zafarthesultan
Starting Member

16 Posts

Posted - 2010-01-08 : 02:40:25

there are two tables in my database 1)master 2) salary
I need to convert the following logic to SQL Query. Please help. All of my efforts has produced nothing. The problem is:

1)for every member in the master table count the number of members he has introduced(a member can introduce n other members)
2)check if the member is in salary table (he/she has drawn at least 1 cheque)
if yes
{
if the number of introduced members is unchanged (equal to his last drawn cheque for)
{
do nothing
}
else
{
calculate his/her salary for the month (number of members he/she has introduced X 100)
update the table
}
}
else
{
calculate his/her salary for the month (number of members he/she has introduced X 100)
insert into table
}

thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-08 : 02:42:39

can you show some sample data from tables and then post expected output from them?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-01-08 : 02:47:58

on top of what visakh asked,

1)for every member in the master table count the number of members he has introduced(a member can introduce n other members)

Only 1 level or including all hierarchy of members introduced

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-08 : 02:49:35

quote:
Originally posted by khtan

on top of what visakh asked,

1)for every member in the master table count the number of members he has introduced(a member can introduce n other members)

Only 1 level or including all hierarchy of members introduced

KH
[spoiler]Time is always against us[/spoiler]

I really feel OP is taking of hierarachy. thats why i wanted to see some data before i give him a soln

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-01-08 : 02:55:40

in that case. . zafarthesultan, also tell us the version of SQL Server you are using.

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-08 : 03:22:38

just in case you're in doubt,use this to find version and compatibilty level


SELECT @@VERSION

EXEC sp_dbcmptlevel 'Your database name here'

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-08 : 03:23:49

or even


SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')

zafarthesultan
Starting Member

16 Posts

Posted - 2010-01-08 : 04:18:36

The Master Table is as
snumber memberid introducer businessvalue
1 b a 30
2 c a 50
3 d b 70
4 e c 45

Now what I need to do is:
I need to calculate the total number of business value generated(payable to the introducer) by each member for example in the above table:
business value of a:80 (a has introduced b and c)
business value of b:70 (b has introduced d)
business value of c:45 (c has introduced e)
business value of d:0 (not introduced anyone)
business value of e:0 (not introduced anyone)

salary table
when a member registers master table holds his details and the entries in the salary table are null except memberid
memberid businessvalue date
e null null

now i have to calculate the comission for each member (=businessvalue) but it must not repeat if at the time of calculation it remain same as last calculated commision

I hope this is clear. Please help. Thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-01-08 : 06:38:50

for your requirement, if is best if you can add a intro date into your master table. And this can satisfy your commission calculation requirement easily.

run this and see


declare @master table
(
	snumber 	int,
	memberid 	char(1),
	introducer 	char(1),
	businessvalue	int,
	intro_date	datetime
)
insert into @master
select	1, 'b', 'a', 30, '2009-11-10'	union all
select	2, 'c', 'a', 50, '2009-12-25'	union all
select	3, 'd', 'b', 70, '2009-12-05'	union all
select	4, 'e', 'c', 45, '2009-12-30'

--	Business Value Query
select	m.memberid, isnull(sum(businessvalue), 0) as businessvalue
from	(
		select	memberid from @master
		union
		select	introducer from @master
	) m
	left join @master t	on	m.memberid	= t.introducer
group by m.memberid

declare	@start	datetime,
	@end	datetime

-- For the Month of 2009 December
select	@start	= '2009-12-01',
	@end	= '2009-12-31'

--	Business Value Query for the selected period
select	m.memberid, isnull(sum(businessvalue), 0) as businessvalue
from	(
		select	memberid from @master
		union
		select	introducer from @master
	) m
	left join @master t	on	m.memberid	= t.introducer
				and	t.intro_date	>= @start
				and	t.intro_date	< dateadd(day, 1, @end)
group by m.memberid

-- 	Commission Query (for the selected period)
select	m.memberid, isnull(count(t.memberid), 0) * 100 as Commission
from	(
		select	memberid from @master
		union
		select	introducer from @master
	) m
	left join @master t	on	m.memberid	= t.introducer
				and	t.intro_date	>= @start
				and	t.intro_date	< dateadd(day, 1, @end)
group by m.memberid

KH
[spoiler]Time is always against us[/spoiler]

zafarthesultan
Starting Member

16 Posts

Posted - 2010-01-08 : 08:18:42

Thanks a lot Sir. I have been learning a lot from you wizards. Now one more thing, the result set returned by the above query is exactly what I want but if you don't mind I want the result to be stored in salary table without overwriting previous values. Thanking you a lot. It means the salary generated for each member each month.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-01-08 : 08:25:12

[code]
insert into salary (memberid, businessvalue, date)
select t.introducer, isnull(count(t.memberid), 0) * 100 as Commission, @start
from @master t
where t.intro_date >= @start
and t.intro_date < dateadd(day, 1, @end)
group by m.memberid
[/code]

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources