Computing change from first sales by employee

I'll use AdventureWorks to frame my question, as then I can extend any suggestions to the possible applications I need.

I want to create a calculation which finds the change of each employee's monthly sales amount from their first month's sales.

e.g. for each month it will show how their sales for that month differs from the first month they ever made a sale.

What I'm struggling with is that one employee may have made their first sale in 2001, while another made their first sale in 20003. I'd like to be able to show how each employee's sales change relative to their first month, i.e. in month 2, month 3, etc.

I am thinking that I will need to define a new calculated measure which is the number of months elapsed for each employee since their first sale and then define a calculation for their deltas per month, but I'm not sure.

I've read up on OpeningPeriod() and the "Opening Period Balance" template, but I'm stuck. Using AS 2005.

Thanks for any suggestions.

-Leif Kirschenbaum

Leif,

Here is an example that I think will give you what you are looking for. I included a measure called "First Months Sales" which is just to show how the calculation is working and is not really needed for the end result.

HTH,

Steve

WITH

MEMBER [Date].[Calendar].[First Month With Sales]

AS

Filter([Date].[Calendar].[Month].Members, [Measures].[Reseller Sales Amount] > 0)(0)

MEMBER [Measures].[First Months Sales]

AS

([Date].[Calendar].[First Month With Sales],

[Measures].[Reseller Sales Amount]),

FORMAT_STRING = "currency"

MEMBER [Measures].[Difference]

AS

IIF(Exists({[Date].[Calendar].CurrentMember},{Filter([Date].[Calendar].[Month].Members, [Measures].[Reseller Sales Amount] > 0)(0).Lead(1):NULL}).Count = 1 AND

[Measures].[Reseller Sales Amount] > 0,

[Measures].[Reseller Sales Amount] - ([Date].[Calendar].[First Month With Sales],[Measures].[Reseller Sales Amount]),

NULL),

FORMAT_STRING = "currency"

SELECT

{[Date].[Calendar].[Month].Members} ON COLUMNS,

{[Reseller].[Reseller].[Bike Rims Company],

[Reseller].[Reseller].[Certified Sports Supply]} *

{[Measures].[Reseller Sales Amount],

[Measures].[First Months Sales],

[Measures].[Difference]} ON ROWS

FROM

[Adventure Works]

|||Great!
Thanks.
I do need one other thing, I need a dimension which counts the months from the first month for each employee. Right now in the browser when I drag Employee hierarchy to the rows field and Calendar to the columns field the difference for each employee starts in a different month. It would be useful to be able to drag "Months of Employment" to the columns field.
Would I do:

CREATE MEMBER CURRENTCUBE.[Date].[Calendar].[Months of Employment]
AS
[Date].[Calendar].[Month] - [Date].[Calendar].[First Month With Sales],
FORMAT_STRING = "Standard",
NON_EMPTY_BEHAVIOR = { [Reseller Sales-Sales Amount] },
VISIBLE = 1 ;

That doesn't work.

CREATE MEMBER CURRENTCUBE.[Measures].[Months of Employment]

AS

[Date].[Calendar].[Month] - [Date].[Calendar].[First Month With Sales],

FORMAT_STRING = "Standard",

NON_EMPTY_BEHAVIOR = { [Reseller Sales-Sales Amount] },

VISIBLE = 1 ;

also doesn't work, as I can't drag "Months of Employment" to the columns field.|||

Leif,

Try the following:

MEMBER [Measures].[Months of Employment]

AS

IIF(Exists({[Date].[Calendar].CurrentMember},{Filter([Date].[Calendar].[Month].Members, [Measures].[Reseller Sales Amount] > 0)(0).Lead(1):NULL}).Count = 1,

{Filter([Date].[Calendar].[Month].Members, [Measures].[Reseller Sales Amount] > 0)(0):[Date].[Calendar].CurrentMember}.Count - 1,

NULL),

FORMAT_STRING = "#,#"

complex sql statement

I need to get multiple values for each row in a database, then do a calculation and insert the calculation and the accountnumber related to the calculation the data, into a different column. I get an error trying it this way...there is no real identifier, it is jsut something that needs to get done per row...any ideas on how I can accomplish this?

Declare @.NetCommissiondecimal

Declare @.AccountNumbervarchar(50)

Set

@.NetCommission=(select(CommissionRebate* Quantity)

from

Account A

Join

Trades Ton A.AccountNumber= T.AccountNumber)

Set

@.AccountNumber=(select A.AccountNumber

from

cmsAccount A

Join

Trades Ton A.AccountNumber= T.AccountNumber)

Insert

into Transaction

(

Payee

,

Deposit

,

AccountNumber

)

Values

(

'Account Credit'

,

@.NetCommission

,

@.AccountNumber

)

Hi

Insert

intoTransaction

(

Payee

........

Transaction is the key word,you have to choose another table name.

|||Or always embed such object names in [] or "" to avoid "syntax error":

Insert
into [Transaction]

(

Payee...|||

Even with the table name changed I am still getting a subquery error

Subquery returned more than 1 value.

All I really want to do is run a calculation on ech row in the database...any ideas?

Declare @.NetCommissiondecimal

Declare @.AccountNumbervarchar(50)

Set

@.NetCommission=(

select

(CommissionRebate* Quantity)

from

cmsAccount A

Join

cmsTrades Ton A.AccountNumber= T.AccountNumber)

Set

@.AccountNumber=(select A.AccountNumber

from

cmsAccount A

Join

cmsTrades Ton A.AccountNumber= T.AccountNumber)

Insert

into TradeTransaction

(

Payee

,

Deposit

,

AccountNumber

)

Values

(

'Account Credit'

,

@.NetCommission

,

@.AccountNumber

)

Go

|||Obviously the error indicates?that?some?subquery?returns?more?than?1?value?while?you're?trying?to?assign?the?returned?values?to?a?single?variable.?This?is?a?bad?logic?as?there?is?no?array?in?T-SQL.?So?my?suggestion?would?be?to?use?some?aggregation?funciton?or?TOP?keyword?to?restrict?the?subquery?to?return?only?1?value, for example:

Set @.NetCommission =
(select top 1 (CommissionRebate * Quantity)
from cmsAccount A Join
cmsTrades T on A.AccountNumber = T.AccountNumber)

Or:

Set @.NetCommission =
(select max(CommissionRebate * Quantity)
from cmsAccount A Join
cmsTrades T on A.AccountNumber = T.AccountNumber)|||

This gets me the two values I need, but I then need to insert these two values into a different table... how would I go about doing this?

Select

sum(A.CommissionRebate* T.Quantity)As NetCommission, T.AccountNumber

from

cmsTrades T

Join

cmsAccount Aon A.AccountNumber= T.AccountNumber

where

T.TradeDate='11/13/2006'

GROUP

BY T.AccountNumber

|||

Ok this one works but it only inserts the first row and runs successfully, but does not go on to insert the other rows...

Declare

@.NetCommissiondecimal

Declare

@.AccountNumberVarchar(50)

Select

@.NetCommission=Sum(A.CommissionRebate* T.Quantity), @.AccountNumber= T.AccountNumber

from

cmsTrades T

Join

cmsAccount Aon A.AccountNumber= T.AccountNumber

where

T.TradeDate='11/13/2006'

GROUP

BY T.AccountNumber

Insert

into appTransaction

(

Payee

,

Payment

,

AccountNumber

)

Values

(

'Client Credit'

,

@.NetCommission

,

@.AccountNumber

)

|||If you want to insert multiple rows you need to use Cursor to skip through the rows to be inserted, or store the rows in a temple table so that you can?fetch rows from it?into?some?table?in?a?single?insert?statement.|||

I did this with the cursor idea, worked for a second and now I am getting this message: Error converting data type varchar to decimal.

DECLARE

appCursorCursor

For

Select

T.AccountNumber,sum(CommissionRebate)/count(*)*sum(Quantity)as NetCommission

from

cmsTrades T

Join

cmsCalcs con c.AccountNumber= T.AccountNumber

where

T.TradeDate=CAST(YEAR(getdate()) as varchar) + RIGHT('00'+CAST(MONTH(getdate()) as varchar), 2) + RIGHT('00'+CAST(DAY(getdate())-1 as varchar), 2)

and

c

.Month= 11

GROUP

BY T.AccountNumber

Open

appCursor

Declare

@.NetCommissiondecimal, @.AccountNumberVarchar(50)

Fetch

Nextfrom appCursorInto @.NetCommission, @.AccountNumber

While

(@.@.Fetch_Status<>-1)

Begin

If

(@.@.Fetch_Status<>-2)

Insert

into appTransaction

(

Payee

,

Payment

,

AccountNumber

)

Values

(

'Client Credit'

,

@.NetCommission

,

@.AccountNumber

)

Fetch

Nextfrom appCursorInto @.NetCommission, @.AccountNumber

End

Close

appCursor

DEALLOCATE

appCursor

Go

|||I found this:

DECLARE
appCursor Cursor For
Select
T.AccountNumber, sum(CommissionRebate) / count(*) * sum(Quantity) as NetCommission
from
cmsTrades T
.....
Fetch Next from appCursor Into @.NetCommission, @.AccountNumber

Shouldn't this be?Fetch Next from appCursor Into @.AccountNumber, @.NetCommission? The same order as the returned fields in the select query?|||

Forget the cursor. You want to stay away from those as much as possible. Keep with set-based logic; it's the SQL way! :-)

Use this sort of approach instead:

INSERT INTO TradeTransaction
(
Payee,
Deposit,
AccountNumber
)
SELECT
'Account Credit',
CommissionRebate * Quantity,
A.AccountNumber
FROM
cmsAccount A
INNER JOIN
cmsTrades T on A.AccountNumber = T.AccountNumber

|||that works great, would there be an easy way to sum up the inserts by AccountNumber so instead of 36 inserts for one accountnumber I would have just 1 for each accountnumber for that day?|||

Yes, you might try this:

INSERT INTO TradeTransaction
(
Payee,
Deposit,
AccountNumber
)
SELECT
'Account Credit',
SUM(CommissionRebate * Quantity),
A.AccountNumber
FROM
cmsAccount A
INNER JOIN
cmsTrades T on A.AccountNumber = T.AccountNumber
GROUP BY
A.AccountNumber

|||That did the trick perfectly...thank you!

Complex calculation!!

Hi
I have got two tables and have one to many relationship on them.
i ll show you the sample data

Table 1
ID DoctorName Practice Full_Time Cost Expenditure
1 ABC qw yes $100
2 ABD wer no $566
3 ZXA ddf yes $22
........................

table 2
Practices
qw
wer
ffgg
hhjk
ddf
..
The scenario is like this
a doctor can work in more than one practice. and he can be a full time practioner or a part time practitioner

The problem is if a doctor is a full time practioner in 1 service and a part time practitioner in another, 75% of cost goes to first practice and the rest 25% goes to other

if the doctor is non full time in more than one practice, the cost gets distributed equally in all practices.

how shall i do this?
pls HELP!!

Moving to the T-SQL forum.

Complex calculation!!

Hi
I have got two tables and have one to many relationship on them.
i ll show you the sample data

Table 1
ID DoctorName Practice Full_Time Cost Expenditure
1 ABC qw yes $100
2 ABD wer no $566
3 ZXA ddf yes $22
........................

table 2
Practices
qw
wer
ffgg
hhjk
ddf
..
The scenario is like this
a doctor can work in more than one practice. and he can be a full time practioner or a part time practitioner

The problem is if a doctor is a full time practioner in 1 service and a part time practitioner in another, 75% of cost goes to first practice and the rest 25% goes to other

if the doctor is non full time in more than one practice, the cost gets distributed equally in all practices.

how shall i do this?
pls HELP!!

Moving to the T-SQL forum.