Calculation - 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

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]

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

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

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);

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

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.

Kristen
Test

22859 Posts

Posted - 2015-05-09 : 04:11:07

Thanks for tidying up your earlier posts

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources