This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
select
a.InvoiceID,
a.CustomerCode,
a.CustName,
a.dimension,
a.dimension2_,
a.dimension3_ ,
a.SALES_AMOUNT,
a.COST_AMOUNT,
a.GROSS_PROFIT,
case
when SALES_AMOUNT =0 and COST_AMOUNT > 0 then -100
when SALES_AMOUNT =0 and COST_AMOUNT < 0 then 100
when SALES_AMOUNT =0 and COST_AMOUNT = 0 then 0
else GROSS_PROFIT/LineAmountMST * 100
END MARGIN_PER
from
(
select
InvoiceID,
CustomerCode,
CustName,
dimension,
dimension2_,
dimension3_,
sum(LineAmountMST) SALES_AMOUNT,
SUM(linecostAmount) COST_AMOUNT,
sum(LineAmountMST- linecostAmount) GROSS_PROFIT
--case
-- when SUM(lineamountmst)=0 and sum(linecostamount)> 0 then -100
-- when SUM(lineamountmst)=0 and sum(linecostamount)< 0 then 100
-- when SUM(lineamountmst)=0 and sum(linecostamount)= 0 then 0
-- else sum(LineAmountMST- linecostAmount)/sum(LineAmountMST)*100
--END MARGIN_PER
FROM
salesdim
where
invoicedate between @startdate and @enddate
and itembuyergroupid = @itembuyergroupid
and itemgroupid = @itemgroupid
and salesoriginid = @salesoriginid
and CustomerCode = @Cust
and dimension = @Dept
and dimension2_ = @CC
and dimension3_ = @Purpose
GROUP BY InvoiceID,CustomerCode,CustName,dimension,dimension2_,dimension3_
) a
where
a.MARGIN_PER between @marginFrom and @marginTo
order by a.MARGIN_PER desc
select * from (
select
a.InvoiceID,
a.CustomerCode,
a.CustName,
a.dimension,
a.dimension2_,
a.dimension3_ ,
a.SALES_AMOUNT,
a.COST_AMOUNT,
a.GROSS_PROFIT,
case
when SALES_AMOUNT =0 and COST_AMOUNT > 0 then -100
when SALES_AMOUNT =0 and COST_AMOUNT < 0 then 100
when SALES_AMOUNT =0 and COST_AMOUNT = 0 then 0
else GROSS_PROFIT/LineAmountMST * 100
END MARGIN_PER
from
(
select
InvoiceID,
CustomerCode,
CustName,
dimension,
dimension2_,
dimension3_,
sum(LineAmountMST) SALES_AMOUNT,
SUM(linecostAmount) COST_AMOUNT,
sum(LineAmountMST- linecostAmount) GROSS_PROFIT
--case
-- when SUM(lineamountmst)=0 and sum(linecostamount)> 0 then -100
-- when SUM(lineamountmst)=0 and sum(linecostamount)< 0 then 100
-- when SUM(lineamountmst)=0 and sum(linecostamount)= 0 then 0
-- else sum(LineAmountMST- linecostAmount)/sum(LineAmountMST)*100
--END MARGIN_PER
FROM
salesdim
where
invoicedate between @startdate and @enddate
and itembuyergroupid = @itembuyergroupid
and itemgroupid = @itemgroupid
and salesoriginid = @salesoriginid
and CustomerCode = @Cust
and dimension = @Dept
and dimension2_ = @CC
and dimension3_ = @Purpose
GROUP BY InvoiceID,CustomerCode,CustName,dimension,dimension2_,dimension3_
) a ) t
where
t.MARGIN_PER between @marginFrom and @marginTo
order by t.MARGIN_PER desc
-- a table variable to hold the values while they get eliminated by the margin calculation
Declare @Vartable1 table (
InvoiceID int IDENTITY(1,1) Primary Key,
CustomerCode WhateverDataType,
CustName WhateverDataType,
dimension WhateverDataType,
dimension2_ WhateverDataType,
dimension3_ WhateverDataType,
LineAmountMST WhateverDataType,
linecostAmount WhateverDataType,
lineamountmst WhateverDataType,
linecostamount WhateverDataType
--fill the table variable
insert into @Vartable1 (
InvoiceID,
CustomerCode,
CustName,
dimension,
dimension2_,
dimension3_,
LineAmountMST,
linecostAmount,
lineamountmst,
linecostamount)
select InvoiceID,CustomerCode,CustName,dimension,
dimension2_,dimension3_, LineAmountMST, linecostAmount,lineamountmst, linecostamount
FROM salesdim
where invoicedate between @startdate and @enddate
and itembuyergroupid = @itembuyergroupid
and itemgroupid = @itemgroupid
and salesoriginid = @salesoriginid
and CustomerCode = @Cust
and dimension = @Dept
and dimension2_ = @CC
and dimension3_ = @Purpose
--now get the final summing done outside the table variable
--and do the summing for MARGIN_PER inside a temptable, and eliminate the ones you don't need final summed
select InvoiceID,CustomerCode,CustName,dimension,dimension2_,dimension3_,
sum(LineAmountMST) SALES_AMOUNT,
SUM(linecostAmount) COST_AMOUNT,
sum(LineAmountMST- linecostAmount) GROSS_PROFIT, MARGIN_PER
from (
select InvoiceID,CustomerCode,CustName,dimension,
dimension2_,dimension3_, LineAmountMST, linecostAmount,lineamountmst, linecostamount,
case
when SUM(lineamountmst)=0 and sum(linecostamount)> 0 then -100
when SUM(lineamountmst)=0 and sum(linecostamount)< 0 then 100
when SUM(lineamountmst)=0 and sum(linecostamount)= 0 then 0
else
sum(LineAmountMST- linecostAmount)/sum(LineAmountMST)*100 END MARGIN_PER
from @Vartable1
where MARGIN_PER between @marginFrom and @marginTo
)
GROUP BY InvoiceID,CustomerCode,CustName,dimension,dimension2_,dimension3_
order by a.MARGIN_PER desc
select
InvoiceID,
CustomerCode,
CustName,
dimension,
dimension2_,
dimension3_,
sum(LineAmountMST) SALES_AMOUNT,
SUM(linecostAmount) COST_AMOUNT,
sum(LineAmountMST- linecostAmount) GROSS_PROFIT
INTO #temp
FROM
salesdim
where
invoicedate between @startdate and @enddate
and itembuyergroupid = @itembuyergroupid
and itemgroupid = @itemgroupid
and salesoriginid = @salesoriginid
and CustomerCode = @Cust
and dimension = @Dept
and dimension2_ = @CC
and dimension3_ = @Purpose
GROUP BY InvoiceID,CustomerCode,CustName,dimension,dimension2_,dimension3_
select
a.InvoiceID,
a.CustomerCode,
a.CustName,
a.dimension,
a.dimension2_,
a.dimension3_ ,
a.SALES_AMOUNT,
a.COST_AMOUNT,
a.GROSS_PROFIT,
case
when SALES_AMOUNT =0 and COST_AMOUNT > 0 then -100
when SALES_AMOUNT =0 and COST_AMOUNT < 0 then 100
when SALES_AMOUNT =0 and COST_AMOUNT = 0 then 0
else GROSS_PROFIT/LineAmountMST * 100
END MARGIN_PER
from
#temp a
where
a.MARGIN_PER between @marginFrom and @marginTo
order by a.MARGIN_PER desc
DROP TABLE #temp
If you are experiencing a similar issue, please ask a related question
Join the community of 500,000 technology professionals and ask your questions.