Calculate quarterly data

Question

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.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Cadavre SSC-Forever Points: 41690 More actions · Answer 1

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 · Answer 2

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