reclaculating margin - 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

SQL Server 2005 Forums

Transact-SQL (2005)

reclaculating margin

Author

Topic

rookie_sql
Constraint Violating Yak Guru

443 Posts

Posted - 2009-07-30 : 07:19:55

Hi i've a margin report that i have developed in sql but i need to be able to calculate the margin at different levels depending on how i drill it.

What i've done so far is just calculate the margin at line level

but how do i get to to roll up and calculate correctly.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-30 : 07:21:22

table structure, sample data and required result please . .

KH
[spoiler]Time is always against us[/spoiler]

rookie_sql
Constraint Violating Yak Guru

443 Posts

Posted - 2009-07-30 : 10:59:54

here is my expected results.

i have Qty Cost Net Sale Margin all money data types
Prod Hier, is Impulse, Soft Drinks, 2 LTR BOTTLE MINERALS, product code and then dowen to invoice level, the line level detail is calculated ok, it just when i rol it up i can get it to recalculate for the different Prod Hier,

Values
Prod Hier Qty Cost Net Sale Margin
Impulse 2 11.74 17.08 54.13 wrong
Soft Drinks 2 11.74 17.08 54.13 wrong
2 LTR BOTTLE MINERALS 2 11.74 17.08 54.13 wrong
product 100270 2 11.74 17.08 54.13 wrong
inv_code 31757 1 7.5 11.99 37.44 ok
inv_code 33320 1 4.24 5.09 16.69 ok
Grand Total 2 11.74 17.08 54.13

Whats it should be like when calculated.

Values
Prod Hier Qty Cost Net Sale Margin
Impulse 2 11.74 17.08 31.25 ok
Soft Drinks 2 11.74 17.08 31.25 ok
2 LTR BOTTLE MINERALS 2 11.74 17.08 31.25 ok
product 100270 2 11.74 17.08 31.25 ok
inv_code 31757 1 7.5 11.99 37.44 ok
inv_code 33320 1 4.24 5.09 16.69 ok
Grand Total 2 11.74 17.08 54.13

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources