Skip to Content
Former Member
Jun 23, 2014 at 11:44 PM

Attempting to find the profit %


Hello Everyone,

I have been asked to write a report that returns the profitability of a user specified warehouse, within a user specified date period.

My code, which I believe to be achieving the required task is as follows -

declare @dteFr Date, @dteTo Date, @selWhs Varchar(3)

set @dteFr = '2011-01-01'

set @dteTo = '2014-05-30'

set @selWhs = '13'

T0.SlpName as 'Sales Person Name', sum(T0.StockValue) as 'Stock Value'
, sum(T0.LineTotal) as 'Net Sales', (sum(T0.LineTotal - T0.StockValue)) as 'Profit'
    select T2.SlpName, T0.StockValue, T0.LineTotal 
    from AU.dbo.INV1 T0
    inner join AU.dbo.OINV T1 on T1.DocEntry = T0.DocEntry
    inner join AU.dbo.OSLP T2 on T2.SlpCode = T1.SlpCode
    where T0.WhsCode LIKE @selWhs and T1.DocDate >= @dteFr and T1.DocDate <= @dteTo
    union all
    select T5.SlpName, T3.StockValue, - T3.LineTotal 
    from AU.dbo.RIN1 T3
    inner join AU.dbo.ORIN T4 on T4.DocEntry = T3.DocEntry
    inner join AU.dbo.OSLP T5 on T5.SlpCode = T4.SlpCode
    where T3.WhsCode LIKE @selWhs and T4.DocDate >= @dteFr and T4.DocDate <= @dteTo
) as T0

group by T0.SlpName

Essentially I am subtracting the total value of credits from the total value of invoices for the given warehouse.

I have been asked to provide a profit % within this report, and I am not sure of how to go about the task. My first thought is to simply divide the 'Net Sales' by the 'Stock Value' and then multiply by 100. Am I on the right track here, or am I missing something?

I am new to SQL coding, and am faced with the additional challenge of not being trained in book keeping. If anybody can suggest some useful links that can explain to me the difference between gross & net profit, along with how profit is calculated (preferably with relation to doing business in Australia) it will be greatly appreciated.

Kind Regards,