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
 Calculation

Author  Topic 

mohit3907
Starting Member

9 Posts

Posted - 2015-05-02 : 02:20:49
Calculation

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2015-05-02 : 04:27:01
Please provide sample data and expected output (from the sample data provided).
[url]http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx[/url]
Go to Top of Page

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2015-05-06 : 19:42:08
As output on the sampledata wasn't provided, I wasn't able to see if the following query, does the job:
with commision
as (select e.id
,sum(isnull(s.commision,0)) as commision
from dbo.employee as e
left outer join dbo.sales_product as s
on s.employeeid=e.id
group by e.id
)
select e.firstname
,e.lastname
,avg(ec.commision)
+sum(isnull(ac.commision,0)
*isnull(case t.title_code
when 'A' then t.ona
when 'EC' then t.onec
when 'JSC' then t.onjsc
when 'SSC' then t.onssc
end
,0
)
)
/100 as commision
from commision as ec
inner join dbo.employee as e
on e.id=ec.id
left outer join dbo.title as t
on t.id=e.titleid
left outer join dbo.employee as a
on a.promoterid=ec.id
left outer join commision as ac
on ac.id=a.id
group by e.firstname
,e.lastname
Go to Top of Page

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2015-05-07 : 17:27:55
quote:
Originally posted by mohit3907

King1 Knight1 (100+100*onEC(20)+200*onA(20))

King2 Knight2 (100+400*onJSC(10))


I don't agree with your numbers.
King1 has sales commision of 500 (not 100)
King4 has King2 as promoter, but King4 has no commision.
I have corrected the query to suit the needs of calculating the sampledata you provided (don't mind the fixed title_code for now - we will make this dynamic later, when you have confirmed, the query below calculates correct on the sampledata you provided):
with commision
as (select e.id
,sum(isnull(s.commision,0)) as commision
from dbo.employee as e
left outer join dbo.sales_product as s
on s.employeeid=e.id
group by e.id
)
select e.firstname
,e.lastname
,avg(ec.commision) as commision
,sum(isnull(ac.commision,0)
*isnull(case et.title_code
when 'A' then at.ona
when 'EC' then at.onec
when 'JSC' then at.onjsc
when 'SSC' then at.onssc
end
,0
)
)
/100 as extracommision
from commision as ec
inner join dbo.employee as e
on e.id=ec.id
left outer join dbo.title as et
on et.id=e.titleid
left outer join dbo.employee as a
on a.promoterid=ec.id
left outer join commision as ac
on ac.id=a.id
left outer join dbo.title as at
on at.id=a.titleid
group by e.firstname
,e.lastname
Go to Top of Page

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2015-05-08 : 14:32:51
Try this:
declare @sqlstring varchar(max);
declare @whenstring varchar(max);
set @whenstring=(select stuff((select ' when '''+right(c.name,len(c.name)-2)+''' then t.'+c.name
from sys.tables as t
inner join sys.columns as c
on c.object_id=t.object_id
and c.name like 'on%'
where t.name='title'
for xml path('')
)
,1,1,''
)
);
set @sqlstring='
with commision1(id,commision)
as (select e.id
,sum(isnull(s.commision,0))
from dbo.employee as e
left outer join dbo.sales_product as s
on s.employeeid=e.id
group by e.id
)
,commision2(id,promoterid,commision,extracommision,extracommisionpct)
as (select e.id
,p.id
,c.commision
,0
,0
from dbo.employee as e
left outer join dbo.employee as p
on p.id=e.promoterid
left outer join commision1 as c
on c.id=e.id
where c.commision>0
union all
select p.id
,p.promoterid
,0
,ec.commision+ec.extracommision
,isnull(case t.title_code
'+@whenstring+'
else 0
end
,0
)
from commision2 as ec
inner join dbo.employee as e
on e.id=ec.id
inner join dbo.employee as p
on p.id=ec.promoterid
inner join dbo.title as t
on t.id=e.titleid
where ec.commision+ec.extracommision>0
)
select id
,sum(commision) as commision
,sum(extracommision*extracommisionpct)/100 as extracommision
from commision2
group by id
order by id
';
exec(@sqlstring);
Go to Top of Page

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2015-05-08 : 17:06:32
quote:
Originally posted by mohit3907


... Below is what the result i get when i run this query...
Extra
1 500 46
2 100 250
3 200 0
4 0 5
5 500 0


Strange. I corrected the errors regarding the float field (see red sections):
declare @sqlstring varchar(max);
declare @whenstring varchar(max);
set @whenstring=(select stuff((select ' when '''+right(c.name,len(c.name)-2)+''' then t.'+c.name
from sys.tables as t
inner join sys.columns as c
on c.object_id=t.object_id
and c.name like 'on%'
where t.name='title'
for xml path('')
)
,1,1,''
)
);
set @sqlstring='
with commision1(id,commision)
as (select e.id
,sum(isnull(s.commision,0))
from dbo.employee as e
left outer join dbo.sales_product as s
on s.employeeid=e.id
group by e.id
)
,commision2(id,promoterid,commision,extracommision,extracommisionpct)
as (select e.id
,p.id
,c.commision
,cast(0 as float)
,0
from dbo.employee as e
left outer join dbo.employee as p
on p.id=e.promoterid
left outer join commision1 as c
on c.id=e.id
where c.commision>0
union all
select p.id
,p.promoterid
,cast(0 as float)
,ec.commision+ec.extracommision
,isnull(case t.title_code
'+@whenstring+'
else 0
end
,0
)
from commision2 as ec
inner join dbo.employee as e
on e.id=ec.id
inner join dbo.employee as p
on p.id=ec.promoterid
inner join dbo.title as t
on t.id=e.titleid
where ec.commision+ec.extracommision>0
)
select id
,sum(commision) as commision
,sum(extracommision*extracommisionpct)/100 as extracommision
from commision2
group by id
order by id
';
exec(@sqlstring);
The result I get is:

id commision extracommision
1 500 220
2 100 50
3 200 0
4 0 150
5 500 0
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2015-05-09 : 04:08:04
quote:
Originally posted by mohit3907

Do you think it will be eficient when there will be more than 10,000 records


10,000 isn't a lot. Depends how often the report is run. Run by one person, once every so often, not worth bothering trying to improve it. Run frequently then, no, this will not be efficient.

In general terms by using EXEC (@strSQL) then the optimiser will cache that EXACT query. Someone running the report with slightly different parameters will cause the optimiser to make a new query plan (and making that plan may take longer than executing the query).

To improve dynamic SQL performance you need to use sp_ExecuteSQL something like this:

EXEC sp_ExecuteSQL @strSQL,
N'@Param1 varchar(100), @Param2 int, ...',
@Param1 = @Param1,
@Param2 = @Param2

with all possible user-supplied values coded as @Variables in @strSQL and not as fixed values. Thus the only changes to @strSQL are, for example, the actual elements included in the WHERE clause or SELECT.

Thus the chances are good that the exact same @strSQL will be used by multiple people, albeit with different actual values for the @ParamVariables, and SQL will cache the query plan for any exactly matching @strSQL value.

Beyond that you need to optimise the query by having appropriate indexes for any JOINs and/or "Covering Indexes" for the WHERE clause etc.

Personally I would "print out" the actual SQL that is generated and hand optimise it. You might need to actually "store" the actual SQL generated (in a Logging Table) so you can see what variations are commonly used, in practice, by users and then work on optimising those.
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2015-05-09 : 04:11:07
Thanks for tidying up your earlier posts
Go to Top of Page
   

- Advertisement -