Calculate quarterly data

• 4EverandEver

  Default port

  Points: 1485

  More actions

  October 13, 2011 at 1:49 am

  #246471

  I need to calculate quartely bonus for consultants. The business rule is a consultant need to achieve 110% of his targets for each month in a quater to qualify for a bonus of 1,500. If for example a consultant achieve 110% of his target for two month and 100% for one month then he doesnt qualify for the bonus.

  This is what if have so far which gives me consultant data for a quarter, how do i check if 110% was achieved or not in each month in a quater;

  SELECT CommissionDate ,

  DATEPART(MONTH, CommissionDate) 'Month' ,

  Consultant ,

  Disbursals ,

  Target ,

  Target * 1.1 '%'

  FROM dbo.TblCommissions

  WHERE [CommissionDate] BETWEEN DATEADD(qq, DATEDIFF(qq, 0, GETDATE()) - 1, 0)

  AND GETDATE()

  --AND consultant = 'EMP1234'

  GROUP BY CommissionDate ,

  Consultant ,

  Disbursals ,

  Target

  Thanks in advance.

• Cadavre

  SSC-Forever

  Points: 41690

  More actions

  October 13, 2011 at 2:37 am

  #1393864

  4EverandEver (10/13/2011)

  ---

  I need to calculate quartely bonus for consultants. The business rule is a consultant need to achieve 110% of his targets for each month in a quater to qualify for a bonus of 1,500. If for example a consultant achieve 110% of his target for two month and 100% for one month then he doesnt qualify for the bonus.

  This is what if have so far which gives me consultant data for a quarter, how do i check if 110% was achieved or not in each month in a quater;

  SELECT CommissionDate ,

  DATEPART(MONTH, CommissionDate) 'Month' ,

  Consultant ,

  Disbursals ,

  Target ,

  Target * 1.1 '%'

  FROM dbo.TblCommissions

  WHERE [CommissionDate] BETWEEN DATEADD(qq, DATEDIFF(qq, 0, GETDATE()) - 1, 0)

  AND GETDATE()

  --AND consultant = 'EMP1234'

  GROUP BY CommissionDate ,

  Consultant ,

  Disbursals ,

  Target

  Thanks in advance.

  Hello and welcome to SSC!

  It seems that your DDL script has become detached from your post, or perhaps you were unaware of the benefits of providing one.

  When you have time, please read this article[/url] about the best way to provide us with working sample data and DDL scripts. This will allow the unpaid volunteers of this site to provide you with working, tested code for your particular problem.

  Thanks!

  ---

  Forever trying to learn
  My blog - http://www.cadavre.co.uk/
  For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
  For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  ---

• Jared

  SSC Guru

  Points: 61793

  More actions

  October 13, 2011 at 12:13 pm

  #1394281

  You will probably want to do a couple of joins to pivot the data so that your select looks more like this:

  SELECT CommissionDate, Consultant, Disbursals, Target1, Target1 * 1.1 AS BonusTarget1, Actual1, Target2, Target2 * 1.1 AS BonusTarget2, Actual2, Target3, Target3 * 1.1 AS BonusTarget3, Actual3

  FROM...

  This would be in a CTE and then you could select from this to get your final result using a case statement.

  As mentioned, please include your DDL and it will be easier for us to put together a query for you.

  Thanks,

  Jared

  Jared
  CE - Microsoft